Thanks Vladimir, that helps a lot.
>>Hi All
>>
>>I am unclear about using manual transactions. Do I need to set manual transactions for each transaction set or do I only need to do this when I create the connection? If I set manual transactions at the start of each transaction then do I need to also send a begin transaction as well?
>>
>>Thanks for your help.
>Hello James
>There are 2 different things, conserning transaction
>1) to set manual transaction in sqlsetprop
>2) to use begin transaction in the code of sqlexec
>Let speak about about first variant first
>If you don't use it, all commands sent to server will be automatically commited
>If you set sqlsetprop(con,'transaction',2), you have to send sqlcommit(con)
>to commit commands. You can commit few commands, not one
>For example, I use this to exelerate dbf=server import.
>I use sqlcommit(con) after few sets of insert commands. It works more fast,
>because autocommit does not work after each insert
>It is your choice to change autocommit/manual mode for any opened connection
>or don't do it
>Now let tell about 2
>Usually you use begin transaction only in
>autocommit mode. But sql commands will be fixed after you send to server
>sqlexec(con,'commit')
>I did found that using 1+2 (manual sqlsetprop(con,'transaction',2) and sending sqlexec(con,'begin transaction') for Oracle can lead to unpredicted
>results
>So, I use or 1 or 2
>Best wishes, Vladimir
>The sample of 1
>*!* #include FOXPRO.h
>hConn = SQLConnect("odbcpubs", "sa", "")
>lnResult = SQLSetProp(hConn, "TRANSACTIONS", 2)
>
>*!* lnResult = SQLCommit(hConn)
>*!* If something did go wrong, the transaction can be rolled back and all operations reversed
>*!* with the SQLRollback() function:
>*!* lnResult = SQLRollback(hConn)
>
>*!* #include FOXPRO.h
>…
>lnResult = SQLSetProp(hConn, "TRANSACTIONS", 2)
>lnResult = SQLExec(hConn, ;
> "UPDATE account " + ;
> "SET balance = balance – 100 " + ;
> "WHERE ac_num = 14356" + ;
> ";" + ;
> "UPDATE account " + ;
> "SET balance = balance + 100 " + ;
> "WHERE ac_num = 45249")
>If (lnResult != 1)
> Sqlrollback(hConn)
>*-- Relay error message to the user
>Else
> Sqlcommit(hConn)
>Endif
>SQLDISCONNECT(hConn)
>the sample of 2
>****transactions
>hConn = SQLConnect("odbcpubs", "sa", "")
>SQLExec(hConn,'begin transaction')
>lnResult1 = SQLExec(hConn, ;
> "UPDATE account " + ;
> "SET balance = balance – 100 " + ;
> "WHERE ac_num = 14356")
>lnResult2 = SQLExec(hConn, ;
> "UPDATE account " + ;
> "SET balance = balance + 100 " + ;
> "WHERE ac_num = 45249")
>
>lnResult3 = SQLExec(hConn, ;
> "UPDATE account " + ;
> "SET balance = balance – 100 " + ;
> "WHERE ac_num = 14356" + ;
> ";" + ;
> "UPDATE account " + ;
> "SET balance = balance + 100 " + ;
> "WHERE ac_num = 45249")
>If lnResult1>1 And lnResult2>1 And lnResult3>1
> ?SQLExec(hConn,'commit')
>Else
> ?SQLExec(hConn,'rollback')
>Endif
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only