I want to know how to effectively handle transactions on the sql server
in the following situation:
Example: invoice header table, invoice details table, stock table
I created three updateable cursor adapters
(curInvHeader,curInvDetails,curStock) to insert records from an
invoicing application into a SQL Server Express 2008 database
#DEFINE DB_TRANSMANUAL 2
#DEFINE DB_TRANSAUTO 1
SQLSetProp(nHandle, "TRANSACTIONS", DB_TRANSMANUAL)
sqlexec(nHandle,'BEGIN TRANSACTION')
Then I insert into invoice header
lOK = TableUpdate(1,.t.,'curInvHeader')
if lOK
insert all records into invdetails
lOK = TableUpdate(1,.t.,'curInvDetails')
lf lOK
update stock for each item sold
lOK = TableUpdate(1,.t.,'curStock')
endif
endif
endif
if lOK
sqlexec(nHandle,'IF @@TRANCOUNT > 0 COMMIT')
else
sqlexec(nHandle,'IF @@TRANCOUNT > 0 ROLLBACK')
endif
SQLSetProp(this.nHandle, "TRANSACTIONS", DB_TRANSAUTO)
However, it does not work. Apparently, if one uses cursor adapters,
setting transactions to manual in the server does not allow updating the
tables.
If I do not set the transactions to manual and leave them as automatic,
it works, that is, the tables are updated.
But if there are any problems, say in the last table, the first two will
be updated but the last one will not, which defeats the purpose of using
the above construct.
What am I doing wrong?
Rafael Copquin
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
