On Thu, Jul 29, 2010 at 2:40 PM, Rafael Copquin <[email protected]> wrote:
> 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
---------------
You are expecting VFP to act as the watchdog for your transaction and
in this case is to not th best thing to consider.
If I were you I would generate stored procedures for your CRUD code
and have VFP call them.
How many line items are you dealing with? 5, 20, 50, 100+?
In theory you could have params for the header and fill them in. All
line items are in a single string element and are parsed in the SP at
execution time. You put your own line terminator in there and you can
have a varchar(8000) char in size. That is a lot of line items.
Now in the SP you can create your transaction and call a line item
insert SP till all rows are done. I don't trust VFP to be able to
recall all transactions done outside of itself. Once again maybe the
CA does a great job and no need to worry?
Are you dates date time, or just strings? SQL Server dates and VFP
dates are not the same. If the cursor adapter fixes that great.
> 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
>
>
>
[excessive quoting removed by server]
_______________________________________________
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.
