Dorin,
Thanks for the code but it's not me who needs it :)
PS: Using ? you don't need type conversions for date/datetime and memo. See my first reply.
Cetin

>Hi
>Pardon me for jumpig in
>I needed a "quick and dirty" solutions to transfer our tables (structure and data) from DBF to Firebird SQL. I wrote a procedure for this. Check it, if you translate column types, maybe you can use it. I've commented DROP TABLE statement execution, uncomment it if you need it.
>
>

>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
>
>

>
>
>>>Cetin, greetings again
>>>
>>>quick question about
>>>
>>>>SQLExec(lnHandle,'insert into SomeTable (textField) values (?myTemp.myMemo)')
>>>>
>>>
>>>whats the best syntax for inserting records with a few fields? and how would that differ if, say, the records had 100 fields.
>>>
>>>I'm trying to insert a bunch of records using
>>>
>>>for n=1 to reccount()
>>>scatter memvar
>>>result=SQLEXEC(gnhandle,'insert into mytable (field1,field2,field3 etc) values (m.field1,m.field2,m.field3 etc),'')
>>>endfor
>>>
>>>the "result" is coming back as minus 1 so obviously I've got something wrong.
>>>
>>>In any case, is there a more efficient way? Particularly for tables with large numbers of columns that looks horrendous!
>>>
>>>Thanks
>>>
>>>Harry
>>
>>Harry,
>>Prefixing each column insert value with ? would do but a hard and long way :)
>>Instead select from source table, insert into it as if it was a local table using other xbase commands as well like append from, gather etc and update.
>>IOW you could make an SQLExec() result SPT cursor updatable. I hardly can understand what I'm saying in plain English so a code would show better I think :)
>>
>>

>>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'
>>

>>
>>PS: Few points here :
>>1) For authors table was very light sample uses 'select * from ...'. For a large table you might use 'select * from myTable where myID = ?m.ID'. Or when you need only the structure 'where 1=2'.
>>
>>2) I only showed modify. You might insert as well. Here authors ID is not autogenerated by backend as I remember. If you set that part (either backend generates or you send) you could insert as well. Notice that which fields are allowed to be modified are specified in UpdatableFieldList. UpdatenameList supports it for matching the fields from local to backend.
>>Tip : You would get these codes creating a temporary remote view in a temporary dbc maybe, then run home()+'tools\gendbc\gendbc' to get programmatic version.
>>
>>3) It might be easier to start with remote views.
>>4) VFP8 enhances all these via cursoradapter.
>>
>>5) Whenever your SQLExec returns a failure code (negative) check with aerror. It supplies information about why it might failed (ie: xx field doesn't accept null, yy field breaks a constraint).
>>
>>

>>*..
>>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.')

>>
>>
>>If a bunch of records are a great bunch of records then consider SQL server's bulk operations too (like reading bulk records from a text file).
>>Cetin