Free!  FoxPro to WPF screen converter
   Almost free!  One day of FREE VFP to WPF evaluation of your FoxPro app ONSITE
   Free!  Read the foreword to Les' new book (available here)...
Skip Navigation Links

Moving your DBFs to SQL Server

A complete utility for converting DBF tables to SQL Tables, with all the little tricks you'll need to make your data ".NET-friendly".


Les Pinter

Login or register to download source code

Introduction

Before you can build your .NET application, you have to move your data to SQL. This article provides a handy utility to do just that, with source code and customization tips that can get you started right.

The problem

One of the main reasons for migrating FoxPro applications to .NET has nothing to do with the FoxPro language; it's the data. FoxPro DBFs have three serious flaws:

  • They're not secure; anyone having access to your hard drive can read the data. There are ways around it, but they're not great;
  • They create index traffic that can be crippling. If a table has 100,000 records, each workstation that opens the table with its CDX loads the entire index; every time the table is updated, a complete copy of the updated index is sent to each workstation that has the table open. The resulting delay can bring your application to its knees; and
  • FoxPro indexes can become corrupted; if they do, you have to get all of your users to log out in order to reconstruct the index; and some of them may not want to log out.

In fact, we're often contacted by people who don't want to rewrite their application; they just want to support SQL Server, usually because their prospective client, or the new IT manager, insists. Okay, we can do that. We still need our DBFs moved to SQL. Here's how to do it.

Creating the database

SQL stores its tables in a single monolithic file with the extension MDF. (It also creates a log file with the extension LDF, which it adds to every time you do anything with your data. You can use the log file to reconstruct your database since the last backup, if you did one. But that's another topic.)

You can see what SQL Servers are available on your computer by opening a Command Prompt (!run cmd in FoxPro, or get it from Start, All Programs, Accessories) and type oSQL -L, as shown in Fig. 1:



Fig. 1 - Finding available SQL servers

Once you do, you can create a connection string by creating a text file called x.udl on the desktop, doubleclicking to open it, going to page 1 and selecting Microsoft OLEDB Provider for SQL Server, then typing in one of the Server names - here, I've used (local). (Note that the USE Windows NT Integrated Security radiobutton is selected. For development, that's easier than a userid and password.)



Fig. 2 - Creating a connection string

Unless you've already created a database for your DBFs, you'll need to create one. If you care where the MDF/LDF files are located, you can use the SQL Management Console to create your database. Otherwise, our little program will put it in something like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\YourFileHere.MDF. I put mine in C:\SQLDataBases.

After selecting a database and closing the Wizard, you can open x.udl with textpad and cut and paste the last line into FoxPro, using the SQLStringConnect function, which returns an integer value (1, 2, etc) that is your connection to SQL. For example, the following code uses sp_tables, one of a hundred or so Stored Procedures that come with SQL, to list the tables in the NORTHWIND database (Note that I broke the connection string to make it fit better on a web page.)

Handle = ;
SQLSTRINGCONNECT("Driver={SQL Server};Server=(local);Integrated Security=SSPI;
                  Persist Security Info=False;Database=NORTHWIND;")
SQLEXEC( Handle, "sp_tables")
LIST OFF FIELDS TABLE_NAME FOR TABLE_TYPE = 'TABLE'

You can create your SQL database in FoxPro. Just use master as the database name in the SQLStringCommand above to return a value for Handle, then enter this:

SQLEXEC ( Handle, "CREATE DATABASE MyData")

Simple as that. However, the program that follows will create the database for you if you haven't already created it. I just thought you should know what's going on.

The Conversion Program

The DBFtoSQL screen that is the subject of this article appears in Fig. 3, below:



Fig. 3 - DBFtoSQL.SCX

We first connect to the master database just to make sure we can continue:



Fig. 4 - Connecting to the database (just to 'master')

Next, we pick a database, or create a new one. Note that this database will be put wherever the default database directory for SQL Server is, which might be deep in Program Files. Create it yourself in the SQL Management Console if you want to give it a home that's easier to find:



Fig. 5 - Connecting to an existing database





Fig. 6 - Creating a new database

By default, the last DBF directory you picked appears in the Directory Path textbox; Click on the button with the three little dots to pick a new one:



Fig. 7 - Locating your DBF directory

Finally, click on the Load button to load the selected directory's DBFs into the left listbox:



Fig. 8 - Loading the Available DBF list

The various buttons do what you'd expect. If you download the sourcecode, you'll see how this form works. However, we'll only document the code that does the actual table creation and uploading.



Fig. 9 - Selecting the tables to upload

When you click Upload, the tables are uploaded; a message tells you when you're done.



Fig. 10 - The tables have been uploaded

The Code

Once you've selected the tables you want to upload by moving them to the right-hand list, you can click the Upload button to create one table of the same name as your DBF and move the records in the DBF to the SQL table, one at a time. We skip the table names that might be present but are not part of your tables; if you want to add others (the Stonefield Data Tools tables, for example), feel free to do so. Note that the table names are delimited with slashes, both before and after. I also record the start time to brag about the upload time at the end. The MigrateTable method does the heavy lifting. Any errors encountered while executing the INSERT statements that actually move the data will be listed in the SQLERRLOG cursor at the end of the process.

Source code

PROCEDURE cmdUpload.Click

	WITH THIS.Parent

	IF .List2.ListCount = 0
	    MESSAGEBOX( [No tables in right side list], 64, [Nothing selected], 3000 )
	    RETURN
	ENDIF

	THISFORM.StartTime = SECONDS()

	FOR K = 1 TO .List2.ListCount

		Alias = .List2.List(K)
		IF [/] + ALIAS + [/] $ [/FOXUSER/SQLERRLOG/DATADICT/]
	       LOOP
		ENDIF

	    .List2.ListIndex = K
	    .List2.SetFocus

	    SELECT 4
	    USE ( .List2.List(K) )
	    WAIT WINDOW [Moving ] + ALIAS() NOWAIT

	    THISFORM.MigrateTable()

	    USE IN 4

	ENDFOR

	Msg = TRANSFORM (.List2.ListCount) + [ table(s) uploaded in ] ;
	    + TRANSFORM(SECONDS() - THISFORM.StartTime) + [ seconds]
	MESSAGEBOX( Msg, 0, [Done], 10000 )

	IF USED ( [SQLERRLOG] )
	   SELECT  SQLERRLOG
	   BROWSE TITLE [Errors encountered: ]
	   USE
	ENDIF

	THISFORM.Release

	ENDWITH

ENDPROC

The BuildInsertCommand creates an INSERT statement for each record. I create my own record counter to display in the WAIT WINDOW PreviewString NOWAIT statement because RECNO() and RECCOUNT() use actual record number and count respectively, which would be confusing if you have a large number of deleted records in the DBF.

The BuildInsertCommand method creates the SQL-INSERT statement. However, the SQLEXEC function call used to send the manufactured INSERT statement to SQL has a limitation; the first parameter can't exceed 254 characters. That's a problem if your tables have lots of columns.

The workaround, believe it or not, is to create an array of strings containing chunks of the complete statement that are all shorter than the 254-character limitation, then calling the function with the statements concatenated together, like this:

SQLEXEC ( Handle, Strings(1)+Strings(2)+...)

So if the generated INSERT statement exceeds 254 characters, I break it into "chunks" and store each one in a subscripted variable, called "strings" in my code, then create a SQLEXEC statement that looks like the above and use macro expansion to call it. There are a few other ways that will also work, but so does this one.

PROCEDURE MigrateTable

WITH THISFORM

	SET DELETED ON
	COUNT TO nRecCount
	cRecCount = TRANSFORM(nRecCount)

	cmd  = [DROP TABLE "] + ALIAS() + ["]
	SQLEXEC( .Handle, Cmd )

	.CreateTable()   && See the next code listing
	LOCAL I as Integer
	LOCAL K as Integer
	LOCAL PreviewString as String
	K = 0

	SCAN
		K = K + 1
		PreviewString = [Loading record ] + TRANSFORM(K) + [/] + cRecCount
	    WAIT WINDOW PreviewString NOWAIT
	    Cmd        = .BuildInsertCommand( ALIAS() )
		cExpr      = Cmd
		ExprLen    = LEN(cExpr)
		NumChunks  =   1
		MaxLen     = 254
		IF ExprLen > MaxLen
		   NumChunks = INT ( ExprLen / MaxLen ) + 1
		   DIMENSION Strings(NumChunks)
		   FOR I = 1 TO Numchunks
		       nStart = ( (I-1) * MaxLen ) + 1
		       Strings(I) = SUBSTR ( cExpr + SPACE(254), nStart, MaxLen )
		       Strings(I) = TRIM   ( Strings(I) )
		   ENDFOR
		ENDIF
		IF Numchunks = 1
		   cmd = cExpr
		   lr  = SQLExec ( .Handle, cExpr )
		   IF lr < 0
		     AERROR(_Err)
	         .StoreError ( ALIAS(), RECNO(), Cmd, _Err(2) )
		   ENDIF
		 ELSE	&& too long to be called in a single statement; break into chunks and store in an array
		   cExpr2 = [SQLEXEC( .Handle, ]
		   FOR I  = 1 TO NumChunks
			   cExpr2 = cExpr2 + [Strings(] + TRANSFORM(I) + [)+]
		   ENDFOR
		   cExpr2 = LEFT(cExpr2, LEN(cExpr2)-1) + [ )]
		   Cmd    = cExpr2

	   	   lr = &cExpr2.  && Call SQLEXEC

	   	   IF lr < 0
	   	      AERROR ( _Err )
			  Usual = "[Microsoft][ODBC SQL Server Driver][SQL Server]"
			  _Err(2) = STRTRAN ( _err(2), Usual, [] )
			  .StoreError ( ALIAS(), RECNO(), [INSERT], _Err(2) )
	   	   ENDIF
		ENDIF
	ENDSCAN
	WAIT CLEAR
	USE

ENDWITH

ENDPROC

The CreateTable method creates the database. I use VarChar for all character fields, SmallDateTime for dates, Bit for logical fields, Money for FoxPro Currency fields (Y), and Text for Memo fields. I also use my data dictionary to find those fields that the user has designated as Currency fields and create that data type in the CREATE TABLE statement. Note also that Double fields have a larger width in SQL Server tables. Finally, General fields in FoxPro are beyond the scope of this article, since their handling is way, way different from what .NET and SQL do with such objects, so we just leave them out, both while creating the table and generating the INSERT statement.

PROCEDURE CreateTable

	LOCAL J
	Cmd = "CREATE TABLE [" + ALIAS() + "] ( "
	AFIELDS(laFlds)
	FOR J = 1 TO ALEN(laFlds,1)

	    IF laFlds(J,2) = [G]
	       LOOP
	    ENDIF

	    FldName = laFlds(J,1)
	    Cmd = Cmd + m.FldName + [ ] 

	    DO CASE
	       CASE laFlds(J,2) $ [CV]
	            Cmd = Cmd + [VarChar(] + TRANSFORM(laFlds(J,3)) + [)  NOT NULL DEFAULT '', ]
	       CASE laFlds(J,2) = [I]
	            Cmd = Cmd + [Integer  NOT NULL DEFAULT 0, ]
	       CASE laFlds(J,2) = [M]
	            Cmd = Cmd + [Text     NOT NULL DEFAULT '', ]
	       CASE laFlds(J,2) = [N]
	            N = TRANSFORM(laFlds(J,3))
	            D = TRANSFORM(laFlds(J,4))

	* If the field is marked as a Currency type in the Data Dictionary, use that instead
	            =SEEK ( PADR ( ALIAS(), 10 ) + PADR( FldName, 10 ), [DATADICT] )
	            IF DATADICT.Currency
	               Cmd = Cmd + [Money    NOT NULL DEFAULT 0, ]
	              ELSE
	               Cmd = Cmd + [Numeric(] + N + [,] + D + [)  NOT NULL DEFAULT 0, ]
	            ENDIF

	       CASE laFlds(J,2) = [B]		&& Double is B... go figure
	            N = TRANSFORM(laFlds(J,3)*2)
	            D = TRANSFORM(laFlds(J,4))
	            Cmd = Cmd + [Numeric(] + N + [,] + D + [)  NOT NULL DEFAULT 0, ]
	       CASE laFlds(J,2) $ [TD]
	            Cmd = Cmd + [SmallDateTime  NOT NULL DEFAULT '', ]
	       CASE laFlds(J,2) = [L]
	            Cmd = Cmd + [Bit      NOT NULL DEFAULT 0, ]
	       CASE laFlds(J,2) = [Y]
	            Cmd = Cmd + [Money    NOT NULL DEFAULT 0, ]

	    ENDCASE

	ENDFOR

	Cmd = Cmd + [ UID Integer IDENTITY(1,1) PRIMARY KEY )]  && Add an Identity column

*	_ClipText = Cmd  && In case you want to paste the statement into Query Analyzer

	lr = 0
	TRY
	   lr = SQLEXEC( .Handle, Cmd )
	   IF lr < 0
	      AERROR(l)
	      MESSAGEBOX( Cmd, 16, l(3) )
	   ENDIF
	CATCH TO Ex
	   AERROR(L)
	   MESSAGEBOX( [Error:] + ex.Message + CHR(13) + L(3) + CHR(13) + Cmd, 16, [Error])
	ENDTRY
ENDPROC

The BuildInsert command creates the INSERT statement, using the FoxPro FIELD() function to determine the data type of each column, and converting the data in the record to the appropriate type. Dates are enclosed in single quotes in SQL Server, as are strings. The UID field that I added is not included in the INSERT statement, since it's an AUTOINC column. Neither are any General fields, as mentioned above. The "?" in front of the column name causes FoxPro to use the data, properly formatted, in place of the column name variable - sort of like macro expansion. (An example generated statement would be INSERT INTO USERS ([UserName],[Password]) values ( ?UserName, ?Password ).

PROCEDURE BuildInsertCommand
	LPARAMETERS m.pTable
	LOCAL I,	m.Cmd,	m.Cmd1,	m.Fld
	m.Cmd 			= []
	m.Cmd1			= []
	FOR I = 1 TO FCOUNT()
		m.Fld = UPPER(FIELD(I))
		IF UPPER(m.Fld) = [UID] OR VARTYPE ( &Fld. ) = [G]	&& skip GENERAL fields
			LOOP
		ENDIF
		IF NOT EMPTY(m.Cmd)
			m.Cmd	= m.Cmd  + [,]
			m.Cmd1	= m.Cmd1 + [,]
		ENDIF
		m.Cmd 	= m.Cmd  + "[" + m.Fld + "]"	&& in case the column name is a reserved word
		m.Cmd1	= m.Cmd1 + "?" + m.Fld			&& Precede the variable name with "?"
	ENDFOR
	RETURN "INSERT INTO [" + m.pTable + "] (" + m.Cmd + ") VALUES (" + m.Cmd1 + ")"
ENDPROC

Finally, any errors that occur during storage (null values, invalid dates that FoxPro let you store but SQL won't, etc) are inserted into SQLERRLOG, so that you can look at them later and fix them before re-running the program. The SQL tables are dropped, created anew and reloaded each time, so don't do this with production data!

PROCEDURE StoreError
	LPARAMETERS pTable, pRecNo, pCmd, pErrMsg
	SaveAlias = ALIAS()
	IF NOT USED     (  [SQLERRLOG] )
	   SELECT 0
	   IF NOT FILE  (  [SQLERRLOG.DBF] )
	       CREATE TABLE SQLERRLOG ( TableName Char(12), RECNUM Integer, InsCmd Memo, ErrMsg char(200) )
	      ELSE
	       USE          SQLERRLOG IN 0
	   ENDIF
	ENDIF
	INSERT INTO         SQLErrLog VALUES ( pTable, pRecNo, pCmd, pErrMsg )
	THISFORM.WriteLog(pErrMsg)
	SELECT ( Savealias )
ENDPROC


PROCEDURE WriteLog
	PARAMETERS pcMess
	PRIVATE lnHandle, lcFile
	lnHandle = 0
	lcFile = []
	lcFile = SYS(5) + CURDIR() + [Errors.LOG]
	STRTOFILE( pcMEss, lcFile, 1 )
	RETURN
ENDPROC

Caveat Programmer

SQL has a number of reserved words, which I won't list here because there is a boatload of them. Double quotes or square brackets are used to delineate column and table names that contain blanks or might be reserved words - as in a column named [COUNT]. If your tables' names are reserved words, or if your tables contain column names that are reserved words, you can either enclose the names in square brackets every single time you use them, or just bite the bullet and change them before uploading. Bite the bullet. I've tried to coddle my customers by doing what they wanted, and we were both sorry. You're better off changing them before you convert the data.

Conclusion

This utility will move your DBF data to SQL quickly, and will add the UID column and NOT NULL and default constraints that will eliminate other headaches down the line. It will also convert your CHAR fields to VARCHAR, which saves space and makes comparisons work more like they do in FoxPro. You've still got a ways to go, but this is a good start.

qqq


Copyright(C) Pinter Consulting, 2012Tel: +1 (650) 464-6924
Automated conversion between C# and VB by Visible C#/Visible VB from Tangible Software Solutions