Creating the SQL Database and Loading Your Tables

We're ready to build the SQL database and load the data. The easy way is to open the Enterprise Manager and create the MDF and LDF files yourself, estimating the initial size of each. Or you can just use a CREATE DATABASE command and let SQL start you out with about half a megabyte each. You can specify where to put these files, but the default, which is probably Program Files\Microsoft SQL Server\MSSQL\Data , is usually best. You can also use three lines of code in the command window to create the database:

 Handle = SQLStringConnect ( ;       "driver={SQL Server};server=(local);database=Master;pwd=sa;uid=;") Result = SQLExec ( Handle, "CREATE DATABASE MYDATABASE" ) SQLDisconnect(0) 

I also strongly urge you to create a userID and password that takes you straight to your database. Again, you can do this in Enterprise Manager or in code, but doing so visually is easier. Use the Security tab to add a login, giving (for now) full rights to the userID. Let the DBAs worry about fine-tuning security. They want to keep people out; we want to let them in.

As you saw earlier, whenever you want to send a command to SQL Server, you'll need a handle, which is always a positive integer, for example, 1, 2, 3, and so on.

The following connection string gets you a handle:

 Handle = SQLStringConnect ( ;     "driver={SQL Server};server=(local);database=MyDatabase;pwd=sa;uid=;") 

To close any and all open handles, use SQLDisconnect(0) .

Now we're ready. The program shown in Listing 3.2 will ask you where your DBFs are and load them to the named database on SQL Server.

Listing 3.2. LoadSQLTables.PRG

 * Purpose....: Creates a duplicate of each DBF *            : from your data directory in SQL Server *              and copies the DBF's records to the SQL table. *              The program puts brackets around named reserved words. *  If you get an error indicating illegal use of a reserved word, add it here: SET TALK OFF CLEAR CLOSE ALL SET STRICTDATE TO 0 SET SAFETY OFF SET EXCLUSIVE ON SET DATE AMERICAN SET CONFIRM ON ConnStr = [Driver={SQL Server};Server=(local);UID=sa;PWD=;Database=MyDatabase;] Handle = SQLSTRINGCONNECT( ConnStr ) IF Handle < 1    MESSAGEBOX( "Unable to connect to SQL" + CHR(13) + ConnStr, 16 )    RETURN ENDIF ReservedWords = ;  [,DESC,DATE,RESERVED,PRINT,ID,VIEW,BY,DEFAULT,CURRENT,KEY,ORDER,CHECK,FROM,TO,] DataPath = GETDIR("Where are your DBFs?") IF LASTKEY() = 27  && Escape was pressed    RETURN ENDIF IF NOT EMPTY  ( DataPath )    SET PATH TO &DataPath ENDIF ADIR( laDBFS, ( DataPath + [*.DBF] ) ) ASORT(laDBFS,1) * Load each of the tables to SQL FOR I = 1 TO ALEN(laDBFS,1)     USE ( laDBFS(I,1))     _VFP.Caption = "Loading " + ALIAS()     LoadOneTable() ENDFOR SQLDISCONNECT(0) _VFP.Caption = [Done] PROCEDURE  LoadOneTable  LOCAL I cRecCount = TRANSFORM(RECCOUNT()) cmd  = [DROP TABLE ] + ALIAS() SQLEXEC( Handle, Cmd ) * Skip tables we don't want to load IF ALIAS() $ [COREMETA/DBCXREG/SDTMETA/SDTUSER/FOXUSER/] * skip system tables, add yours here.    ? [Skipping ] + ALIAS()    RETURN ENDIF CreateTable()      && see below SCAN     WAIT WINDOW [Loading record ] + TRANSFORM(RECNO()) + [/] + cRecCount NOWAIT     Cmd  = [INSERT INTO ] + ALIAS() + [ VALUES ( ]     FOR I = 1 TO FCOUNT()         fld  = FIELD(I)         IF TYPE(Fld) = [G]            LOOP         ENDIF         dta  = &Fld         typ  = VARTYPE(dta)         cdta = ALLTRIM(TRANSFORM(dta))         cdta = CHRTRAN ( cdta, CHR(39),CHR(146) )   && remove any single quotes         DO CASE            CASE Typ $ [CM]                 Cmd = Cmd + ['] + cDta + ['] + [, ]            CASE Typ $ [IN]                 Cmd = Cmd +       cDta       + [, ]            CASE Typ = [D]                 IF cDta = [/  /]                    cDta = []                 ENDIF                 Cmd = Cmd + ['] + cDta + ['] + [, ]            CASE Typ = [T]                 IF cDta = [/  /]                    cDta = []                 ENDIF                 Cmd = Cmd + ['] + cDta + ['] + [, ]            CASE Typ = [L]                 Cmd = Cmd + IIF('F'$cdta,[0],[1]) + [, ]            CASE Typ $ [Y]                 Cmd = Cmd         ENDCASE     ENDFOR     Cmd = LEFT(Cmd,LEN(cmd)-2) + [ )]     lr = SQLEXEC( Handle, Cmd )     IF lr < 0        ? [Error: ] + Cmd        SUSPEND     ENDIF ENDSCAN WAIT CLEAR 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)     IF [,] + FldName + [,] $ ReservedWords        FldName = "[" + FldName + "]"     ENDIF     Cmd = Cmd + FldName + [ ]     DO CASE        CASE laFlds(J,2) = [C]             Cmd = Cmd + [Char(] + 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))             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, ]     ENDCASE ENDFOR Cmd = LEFT(Cmd,LEN(cmd)-2) + [ )] lr = SQLEXEC( Handle, Cmd ) IF lr < 0    _ClipText = Cmd    ? [Couldn't create table ] + ALIAS()    MESSAGEBOX( Cmd )    SUSPEND ENDIF ? [Created ] + ALIAS() ENDPROC 

For each DBF that's not in the "Skip These Tables" list at the top of the LoadOneTable routine, the program issues a DROP TABLE " Name " command, followed by a CREATE TABLE command, which it builds. It then scans all records in the DBF and creates and executes an INSERT statement for each record. Users are often amazed at how fast this loads their data. I'm not. It's FoxPro.