It's me again with my client/server doubts.
Those of you with experience in SQL Server (or MYSQL) please advise me.
I'm connecting through ODBC
If I use two cursor adapters, one for the invoice header (curHeader) and
the other one for the invoice details (curDetails), how should I code
the save method?
I developed the code below, but am not sure whether this is the right
way to go, especially the use of manual transactions with cursor adapters.
All the server operations (connection, save, obtention of identity keys,
etc.) are handled by the oDataSQL object, instantiated in the
dataenvironment of the form
Local cCatalog,cCmd,lOK
Local cSendCmd,lBatchMode,nResults,nOK
Local nHandle
nHandle = thisform.oDataSQL.nHandle
nOK = 0
cCatalog = 'mytestdatabase'
cCmd = [use ]+cCatalog+[;go]
thisform.oDataSQL.send(cCmd) && performs a SQLEXEC command
thisform.dDate = Date()
#DEFINE DB_TRANSAUTO 1
#DEFINE DB_TRANSMANUAL 2
SQLSetProp(nHandle, "TRANSACTIONS", DB_TRANSMANUAL)
BEGIN TRANSACTION && vfp transaction for the cursor adapters
thisform.oDataSQL.send('BEGIN TRANSACTION' ) && SQL Server transaction
command
Select curHeader
Scatter name oTotals blank
RemoveProperty(oTotals,'idtotals')
With oTotals
.invdate = thisform.dDate
.account = '9999'
.doctype = 'INV'
.docnum = thisform.cInvNum
.vat = thisform.nVAT
.net = thisform.nNet
.total = thisform.nTotal
EndWith
** insert the record into the updatable cursor adapter
Insert into curHeader from name oTotals
** the following method issues a tableupdate command and then a sqlexec
call to scope_identity
** to get the last identity key generated in the header table
thisform.nPK = thisform.oDataSQL.savecursoradapter("curHeader")
lOK = Iif(thisform.nPK > 0,.t.,.f.)
If lOK = .t.
Select curDetails && the invoice details are filled by the user in
a grid
replace all curDetails.invdate with this.dDate,;
curDetails.doctype with 'INV',;
curDetails.docnum with thisform.cInvNum,;
curDetails.idtotales with thisform.nPK
lOK = thisform.oDataSQL.save('curDetails') && sends a tableupdate
command
EndIf
If lOK = .t.
thisform.oDataSQL.send(,'IF @@TRANCOUNT > 0 COMMIT' ) && SQL Server command
END TRANSACTION && VFP command
MessageBox('Save operation succeded')
Else
thisform.oDataSQL.send(,'IF @@TRANCOUNT > 0 ROLLBACK' ) && SQL Server
command
ROLLBACK && VFP command
MessageBox('Save operation failed')
endif
SQLSetProp(nHandle, "TRANSACTIONS", DB_TRANSAUTO)
My question is: is the above code correct or is there a better way to
handle transactions in SQL Server?
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.
