Tim
I went through a major conversion of one of my apps from .dbf to Sql Server .. and I decided to incorporate the update into my distributed executable.
First thing I had to do was to determine the trigger that would occur or not occur each time the application was started .. meaning, find some Sql table that is central to your application .. like your Users table .. if the Sql users table has no records .. then that would trigger the Sql update routine .. ( this assumes that your sql database and tables have already been created by a DBA with the proper security and authorized user accounts )
Here is a sample of the conversion code .. Hope this gives you some ideas what you can do on your own.
Rick Lipkin
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM AGENCY where agency = '"+xAGENCY+"'"
TRY
oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening AGENCY table" )
IF cAUTH = 'Y'
oDLG:End()
ENDIF
RETURN(.F.)
END TRY
IF oRS:eof
SAYING := "There are no AGENCY Records for "+xAGENCY+CHR(10)
SAYING += "Would you like to IMPORT a Dataset ?"+CHR(10)
IF MsgYesNo(saying )
nNUM := 0
IF cAUTH = 'Y'
cSAY := "Creating Dataset AGENCY "+str(Nnum)
oSay:ReFresh()
SysReFresh()
ENDIF
IF .not. FILE( cDEFA+"\UTILITY.DBF" )
SAYING := "The Import file "+ cDEFA+"\UTILITY.DBF"+CHR(10)
SAYING += "Could not be found .. Aborting"+CHR(10)
MsgAlert(SAYING)
oRs:Close()
IF cAUTH = 'Y'
oDLG:End()
ENDIF
RETURN(.F.)
ENDIF
oRs:CLose()
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM AGENCY"
TRY
oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening AGENCY table" )
IF cAUTH = 'Y'
oDLG:End()
ENDIF
RETURN(.F.)
END TRY
SELECT 1
USE UTILITY via "DBFCDX" EXCL
GO TOP
DO WHILE .not. EOF()
IF DELETED()
SELECT UTILITY
SKIP
LOOP
ENDIF
cEID := _GenEID( oRS, 1 )
oRs:AddNew()
oRs:Fields( "ageneid"):Value := cEID
oRs:Fields( "agency"):Value := upper(utility->agency)
oRs:Fields( "owner" ):Value := UPPER(utility->owner)
oRs:Fields( "address" ):Value := UPPER(utility->address)
oRs:Fields( "city" ):Value := UPPER(utility->city)
oRs:Fields( "state" ):Value := UPPER(utility->state)
oRs:Fields( "zip" ):Value := utility->zip
oRs:Fields( "last_program"):Value := "100"
oRs:Fields( "last_vend"):Value := utility->last_vend
oRs:Fields( "last_po" ):Value := utility->last_po
oRs:Fields( "contact"):Value := " "
oRs:Fields( "phone" ):Value := utility->phone
oRs:Fields( "finance" ):Value := upper(utility->finance)
oRs:Fields( "fin_addr" ):Value := upper(utility->fin_addr )
oRs:Fields( "fin_city" ):Value := upper(utility->fin_city )
oRs:Fields( "fin_state" ):Value := upper(utility->fin_state)
oRs:Fields( "fin_zip" ):Value := utility->fin_zip
oRs:Fields( "shoprate" ):Value := utility->shoprate
oRs:Fields( "logtype" ):Value := utility->logtype
oRs:Fields( "lextract" ):Value := if(empty(utility->lextract), ctod(""), utility->lextract)
oRs:Fields( "comm1" ):Value := utility->comm1
oRs:Fields( "comm2" ):Value := utility->comm2
oRs:Fields( "comm3" ):Value := utility->comm3
oRs:Update()
nNUM++
IF cAUTH = 'Y'
cSAY := "Creating Dataset AGENCY "+str(Nnum)
oSay:ReFresh()
SysReFresh()
ENDIF
SELECT UTILITY
EXIT // only allow one agency record to be created
ENDDO
CLOSE UTILITY
ENDIF
ENDIF
