>PROCEDURE dbf2fb >PARAMETERS tcTable, tnConnHnd, tlAddData >*parameters: tcTable table name or full path >* tnConnHnd valid connection handle to Firebird database returned by SQLConnect() >* tlAddData transfer table data? >_t1=SECONDS() >LOCAL lcCreateTableSQL, lcCreateIndexSQL, lcDropTableSQL, lcInsertDataSQL, ; > lcVFPTypes, lcFBTypes, lcOptions, lnFields, lnNDX >LOCAL ARRAY laVFPTypes[9], laFBTypes[9], laOptions[9] > >USE (tcTable) > >*types translation table >lcVFPTypes = 'C^D^L^M^N^F^I^B^Y^T' >lcFBTypes = 'CHAR^DATE^SMALLINT^BLOB SUB_TYPE TEXT^NUMERIC^FLOAT^INTEGER^DOUBLE PRECISION^NUMERIC^TIMESTAMP' >lcOptions = 'C^ ^ ^ ^N^N^ ^ ^N^ ^ ' >lnFields = AFIELDS(laFlds) >ALINES(laVFPTypes,lcVFPTypes,.t.,'^') >ALINES(laFBTypes,lcFBTypes,.t.,'^') >ALINES(laOptions,lcOptions,.t.,'^') >tcTable = ALIAS() >*build SQL statements >lcDropTableSQL = [DROP TABLE ]+tcTable > >SET TEXTMERGE on >SET TEXTMERGE TO memvar lcCreateTableSQL NOSHOW > >\\CREATE TABLE <<tcTable>> ( >FOR i=1 TO lnFields > IF i>1 > \\, > ENDIF > lnFieldTypeRange = ASCAN(laVFPTypes, laFlds[i,2]) > IF !'getkey'$LOWER(laFlds[i,9]) > \\<<laFlds[i,1]>> <<laFBTypes[lnFieldTypeRange]>> > ELSE > \\<<laFlds[i,1]>> BIGINT > ENDIF > IF laOptions[lnFieldTypeRange] = 'N' > \\(<<laFlds[i,3]>>,<<laFlds[i,4]>>) > ENDIF > IF laOptions[lnFieldTypeRange] = 'C' > \\(<<laFlds[i,3]>>) > ENDIF > IF laFlds[i,5] = .f. and !(laFlds[i,2] = 'D' OR laFlds[i,2]='T') > \\ NOT NULL > ENDIF >NEXT >\\) >SET TEXTMERGE to >*SQLEXEC(tnConnHnd,lcDropTableSQL) >IF SQLEXEC(tnConnHnd,lcCreateTableSQL) < 0 > AERROR(laError) > DISPLAY MEMORY LIKE laError > ?lcCreateTableSql >ENDIF >SQLCOMMIT(tnConnHnd) >*build and execute SQL statements to add data if third parameter is .T. >lcMemoConversion = '' >IF tlAddData=.t. > lnRecordsProcessed=0 > SET TEXTMERGE on > SET TEXTMERGE TO memvar lcInsertDataSQL NOSHOW > \\INSERT INTO <<tcTable>> ( > FOR i=1 TO lnFields > IF i>1 > \\, > ENDIF > \\<<laFlds[i,1]>> > NEXT > \\) VALUES ( > FOR i=1 TO lnFields > IF i>1 > \\, > ENDIF > \\?m.<<laFlds[i,1]>> > NEXT > \\) > SET TEXTMERGE to > SQLPREPARE(tnConnHnd,lcInsertDataSql) > SCAN > SCATTER MEMVAR > lnRecordsProcessed = lnRecordsProcessed + 1 > FOR i=1 TO lnFields > lcVarName = 'm.'+laFlds[i,1] > DO case > CASE TYPE(laFlds[i,1]) = 'L' &&logical type, need conversion > &lcVarName = IIF(EVALUATE(laFlds[i,1]) = .T.,1,0) > CASE TYPE(laFlds[i,1]) = 'D' AND EMPTY(EVALUATE(laFlds[i,1])) &&empty date, need conversion > &lcVarName = '1900-01-01' > CASE TYPE(laFlds[i,1]) = 'T' AND EMPTY(EVALUATE(laFlds[i,1])) &&empty datetime, need conversion > &lcVarName = '1900-01-01 00:00:00' > CASE TYPE(laFlds[i,1]) = 'M' AND EMPTY(EVALUATE(laFlds[i,1])) > &lcVarName = '' > ENDCASE > NEXT > IF SQLEXEC(tnConnHnd) < 1 > CLEAR > AERROR(xx) > DISPLAY MEMORY LIKE xx > ?'insert error' > SUSPEND > ENDIF > IF MOD(lnRecordsProcessed,100) = 0 > SET MESSAGE to STR(lnRecordsProcessed) > endif > ENDSCAN >ENDIF >CLEAR >? >?'Data transferred in :', SECONDS()-_t1 , 'seconds' > >*build and execute SQL statements to create indexes >lnNDX = ATAGINFO(laIndexes) >FOR i=1 TO lnNDX > lcIndexName = ALIAS()+'_'+laIndexes[i,1] > IF laIndexes[i,2] = 'PRIMARY' > lcCreateIndexSQL = [ALTER TABLE ]+tcTable+[ ADD PRIMARY KEY (]+laIndexes[i,3]+[)] > ELSE > lcCreateIndexSQL = [CREATE INDEX ]+tcTable+[_]+ALLTRIM(STR(I))+[ ON ]+tcTable+[ (]+LaIndexes[i,3]+[)] > ENDIF > ?lcCreateIndexSQL > > SQLEXEC(tnConnHnd,lcCreateIndexSQL) > SQLCOMMIT(tnConnHnd) >NEXT > >RETURN > >>
>>lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+; >> 'SERVER=servername;DATABASE=pubs;Trusted_Connection=Yes') >> >>SQLExec(lnHandle,'select * from dbo.authors','v_authors') >> >>CursorSetProp('KeyFieldList','au_id','v_authors') >>CursorSetProp('WhereType',1,'v_authors') >>CursorSetProp('Tables','authors','v_authors') >> >>CursorSetProp("UpdateNameList", ; >> "au_id authors.au_id,"+; >> "au_lname authors.au_lname,"+; >> "au_fname authors.au_fname,"+; >> "contract authors.contract",'V_authors') >> >>CursorSetProp('UpdatableFieldList','au_fname,au_lname,contract','v_authors') >>CursorSetProp('SendUpdates',.T.,'v_authors') >>CursorSetProp('Buffering',5,'v_authors') >>Browse title 'Modify records' && modify >>Tableupdate(2,.T.,'v_authors') >> >>SQLExec(lnHandle,'select * from dbo.authors','afterupdate') >>SQLDisconnect(lnHandle) >>Select afterupdate >>Browse 'After update' >>>>
>>*.. >>mySQLExec(lnHandle,'insert ...') >> >> >>Function mySQLExec >>Lparameters tnHandle, tcSQL, tcCursorName >>tcCursorName = iif(empty(tcCursorName),'',tcCursorName) >>If SQLExec(tnHandle,tcSQL,tcCursorName) < 0 >> Do errHand with tcSQL >>Endif >> >>Function errHand >>Lparameters tcSQL >>lcError=tcSQL+chr(13) >>Aerror(arrCheck) >>For ix=1 to 7 >> lcError = lcError+trans( arrCheck [ix])+ chr(13) >>Endfor >>Messagebox(lcError,0,'Error def.')>>