SQLCommit(), SQLRollback()

These functions control updates to a remote server when you’re working with manual transactions. They’re the SQL Pass Through equivalents to END TRANSACTION and ROLLBACK.

Usage

nSuccess = SQLCommit( nConnectionHandle )
nSuccess = SQLRollback( nConnectionHandle )

By default, SQL Pass Through (see “Your Server Will Be With You in a Moment”) uses automatic transaction handling, which wraps each individual update in a transaction and sends it along. You can choose to manage your own transactions by setting the connection’s Transactions property to 2 (for Manual) using SQLSetProp(). If you do so, then SQLCommit() actually passes updates to the server while SQLRollback() cancels the queued updates.

Doing things this way means updating is a two-step process. TableUpdate() commits the changes locally and queues them for the server, but they don’t actually get sent until you issue SQLCommit().

From our tests, it appears that SQLRollback() can leave your local cursors out of synch with the original remote data. This makes sense because you’ve committed the changes locally, but then reverted them at the server level. It’s too late to revert them locally, but a call to REFRESH() for each cursor affected seems to solve that problem.

SQLCommit() and SQLRollback() are generic ODBC calls you can use with any ODBC driver that supports them. That’s good if you are working in an environment with multiple flavors of back-end servers. If, however, you’re in a shop with only one server, check to see if the server can support manual transactions internally. We’ve found some benefits to using nothing but SQLExec() commands to control the beginning, end or rollback of transactions on the server side. This way, you can leave the connection’s Transaction property set to Automatic and use it to send one-shot, atomic, transaction-wrapped updates, but also send your own “Start,” “End” or “Rollback” commands as needed—a far more flexible model.

Example

* You can run this code from the Command Window
* to see how this stuff works.
* Our "Northwinds" datasource is
* described in "Your Server Will Be With You in a Moment"

nHandle = SQLConnect("Northwinds")
? SQLSetProp(nHandle, "Transactions", 2)
? SQLExec(nHandle, "SELECT * FROM Customers")
* make the cursor updatable
? CursorSetProp("Tables", "customers")
? CursorSetProp("KeyFieldList", "customerid")
? CursorSetProp("UpdatableFieldList", "companyname")
? CursorSetProp("SendUpdates", .t.)
? CursorSetProp("UpdateNameList", ;
                "customerid customers.customerid, ;
                companyname customers.companyname")
* Now change something
GO 4
REPLACE CompanyName WITH "Around the Horn and Back"
* Update locally
? TableUpdate()
* Go over to Access and note that the record hasn't been changed
? SQLCommit(nHandle)
* Now the table in Access should reflect the change
* Clean up
? SQLDisconnect(nHandle)

See Also

CursorSetProp(), SQLConnect(), SQLDisconnect(), SQLExec(), SQLSetProp(), TableUpdate()