CODE Pathway Gateway
   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


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")

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:


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


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


	FOR K = 1 TO .List2.ListCount

		Alias = .List2.List(K)

	    .List2.ListIndex = K

	    SELECT 4
	    USE ( .List2.List(K) )


	    USE IN 4


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

	   BROWSE TITLE [Errors encountered: ]




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


	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

		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) )
		IF Numchunks = 1
		   cmd = cExpr
		   lr  = SQLExec ( .Handle, cExpr )
		   IF lr < 0
	         .StoreError ( ALIAS(), RECNO(), Cmd, _Err(2) )
		 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) + [)+]
		   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) )



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.


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

	    IF laFlds(J,2) = [G]

	    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, ]
	               Cmd = Cmd + [Numeric(] + N + [,] + D + [)  NOT NULL DEFAULT 0, ]

	       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, ]



	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
	   lr = SQLEXEC( .Handle, Cmd )
	   IF lr < 0
	      MESSAGEBOX( Cmd, 16, l(3) )
	   MESSAGEBOX( [Error:] + ex.Message + CHR(13) + L(3) + CHR(13) + Cmd, 16, [Error])

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
	LOCAL I,	m.Cmd,	m.Cmd1,	m.Fld
	m.Cmd 			= []
	m.Cmd1			= []
		m.Fld = UPPER(FIELD(I))
		IF UPPER(m.Fld) = [UID] OR VARTYPE ( &Fld. ) = [G]	&& skip GENERAL fields
			m.Cmd	= m.Cmd  + [,]
			m.Cmd1	= m.Cmd1 + [,]
		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 "?"
	RETURN "INSERT INTO [" + m.pTable + "] (" + m.Cmd + ") VALUES (" + m.Cmd1 + ")"

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!

	LPARAMETERS pTable, pRecNo, pCmd, pErrMsg
	SaveAlias = ALIAS()
	   SELECT 0
	       CREATE TABLE SQLERRLOG ( TableName Char(12), RECNUM Integer, InsCmd Memo, ErrMsg char(200) )
	       USE          SQLERRLOG IN 0
	INSERT INTO         SQLErrLog VALUES ( pTable, pRecNo, pCmd, pErrMsg )
	SELECT ( Savealias )

	PRIVATE lnHandle, lcFile
	lnHandle = 0
	lcFile = []
	lcFile = SYS(5) + CURDIR() + [Errors.LOG]
	STRTOFILE( pcMEss, lcFile, 1 )

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.


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.


Copyright(C) Pinter Consulting, 2018Tel: +1 (650) 464-6924