Hi all
I wonder how to treat SQL Server transactions with VFP9SP2 as front end and MS SQLServer as back end.
Specifically in a father /child scenario (invoices)
.
I have tried two approaches: cursor adapters and spt, but have many doubts.
1) in the case of CAs, I do the following
sqlexec(nHandle,'begin transaction')
SQLSetProp(nHandle,'Transactions',2) && set transactions to manual mode
then insert into the cursor adapter (father table ) and tableupdate the CA
obtain primary key from father table
then insert all records into the cursor adapter for the child table and tableupdate the CA
If OK
SQLCommit(nHandle)
Else
SQLRollback(nHandle)
endif
SQLSetProp(nHandle,'Transactions',1) && set transactions back to automatic mode
2) in the case of spt
begin transaction and set them to manual
insert records directly into father table
obtain pk
then insert records into child table
and commit or rollback the transactions, setting them back to automatic
In case there is an error at any point during the transaction, my doubtz are:
Does vfp really pick up the error as returned by the sql server and therefore sends the command to rollback the transaction?
And foremost, is it necessary to set the transactions to manual to begin with or just leaving them in automatic mode is enough?
IOW, if I leave the transactions in automatic mode, does the sql server automatically roll them back in case of an error?
I am very confused with this issue and will greatly appreciate your help
Rafael Copquin
rcopquin@fibertel.com.ar
Rafael Copquin
Treasurer - Microsoft Users Group of Argentina (MUG)
www.mug.org.ar