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