>When I have 2 or more cursors (CursorAdapter) that need to be updated, and the database can be either VFP or SQL, can the
>Transactions co-exist. Mainly I want to be sure that the BEGIN TRANSACTION and END TRANSACTION will not have an adverse affect
>when using SQL Server. Here is a simplified case:
>
>The variable lSqlServer is set to .T. for SQL and .F. for VFP
>
>
>*-- There are two cursors (for the sake of demo) working in this procedure c_table1 and c_table2
>
>*-- This one is for VFP database
>BEGIN TRANSACTION
>
>if lSqlServer
> *-- This one is for SQL Server database
> nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 2)
>endif
>
>
>lok = tableupdate( 0, .T., c_table1 )
>if !lOk
> aerror( aErrorInfo )
> *-- store error description is some properties
>endif
>
>if lok
> lok = tableupdate( 0, .T., c_table1 )
> if !lOk
> aerror( aErrorInfo )
> *-- store error description is some properties
> endif
>endif
>
>*-- Roll back changes if one of the tableupdates fails
>if !lOk
> *-- Roll back changes to the cursors
> ROLLBACK
> tablerevert(.T., c_table1 )
> tablerevert(.T., c_table2 )
>
> if lSqlServer
> *-- Roll back changes to the SQL tables
> SQLROLLBACK(oApp.conn_handle)
> endif
>
>else
>
> *-- Commit changes for VFP data
> END TRANSACTION
>
> if lSqlServer
> *-- Commit changes for SQL Server database
> SQLCOMMIT ( oApp.conn_handle )
> endif
>
>endif
>
>
>Do you see any problem(s) with above code?
For the TRANSACTIONS part I don't see any obvious problem, but in code optimization, I do:
*-- There are two cursors (for the sake of demo) working in this procedure c_table1 and c_table2
*-- This one is for VFP database
BEGIN TRANSACTION
if lSqlServer
*-- This one is for SQL Server database
nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 2)
endif
lok = tableupdate( 0, .T., c_table1 ) AND tableupdate( 0, .T., c_table2 )
if NOT lOk
aerror( aErrorInfo )
*-- store error description in some properties
*-- Roll back changes to the cursors
ROLLBACK
tablerevert(.T., c_table1 )
tablerevert(.T., c_table2 )
if lSqlServer
*-- Roll back changes to the SQL tables
SQLROLLBACK( oApp.conn_handle )
endif
else
*-- Commit changes for VFP data
END TRANSACTION
if lSqlServer
*-- Commit changes for SQL Server database
SQLCOMMIT ( oApp.conn_handle )
endif
endif
You mention that you use CursorAdapters, you may need to set their UseTransactions property.
From the help file:
The CursorAdapter object relies on ADO and ODBC APIs to manage transactions. If you ignore API recommendations and send specific transaction commands directly to an ADO or ODBC backend, you may want to set the UseTransactions property to False (.F.) to prevent undesirable transaction interaction.
Regards.-
Fernando D. Bozzo
Madrid / Spain