- Why Three-Tier?
- Creating the SQL Database and Loading Your Tables
- Writing the Sample Application
- The Form Template
- A Search Form Template
- The Data Tier
- What's Next?
This chapter is from the book
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.