Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

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.

  • + Share This
  • 🔖 Save To Your Account