Transactions in a VFP Client/Server Application
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
Transactions in a VFP Client/Server Application
Hector J. Correa
We all know how important transactions are in the database world. We also know that VFP provides an excellent support for transactions. However, what happens when your VFP application uses another DBMS, like SQL Server, to store its information? Do you still need transactions? Yes, you do. Do you need VFP transactions or SQL Server transactions? You need both of them! Hector Correa shows why.
Generally speaking, a transaction is a program unit that accesses a database. During execution, the transaction can retrieve and possibly update data. A database management system, like VFP, has the responsibility of executing a transaction so that it's both atomic and correct. To be atomic, a transaction must either execute to completion or not execute at all.
The use of transactions allows you to transform your database from one consistent state to another. A database is in a consistent state if it satisfies all of its integrity constraints. During a transaction, the database might be inconsistent. However, if the integrity constraints aren't satisfied at the end of a transaction, VFP must abort the transaction and leave the database in a state as if the transaction had not been executed.
Typical examples of transactions include bank transfers (withdraw $500 from checking, deposit $500 to savings) and invoicing systems (add five items to an invoice, remove five items from inventory).
The advantages of using transactions are uncountable, but the basic idea is that transactions make your life much easier because you now have a powerful, VFP built-in mechanism to help keep your database in a consistent state.
Now, let's talk about a typical scenario in real-world applications. What happens when your VFP application uses another database management system, like SQL Server, to store its data? SQL Server supports transactions as well. Do you need SQL Server transactions? The answer is yes. Should you use SQL Server transactions instead of VFP transactions? No, you need to use both of them. Let's see why.
Testing scenario
In this article, I'll use VFP remote views to access and update a SQL Server database. For the sake of simplicity, I'll use two imaginary VFP remote views (rv_MyView and rv_MyOtherView) to access two imaginary tables (MyTable and MyOtherTable) in SQL Server. Figure 1 shows how this scenario is organized.
.jpg){kind=link}
I tested all of the samples shown in this article with SQL Server and MSDE. However, if you plan to use another DBMS as your back end, you might need to perform a few adjustments to it.
Included in the accompanying Download file, you'll find more examples that use remote views to access and update the Northwind database that comes with SQL Server 7.
The world without transactions
For the most part, transactions are a mechanism to ensure correctness in a database. In a perfect world, you wouldn't need transactions. The following code updates data in the back end and doesn't use transactions:
Now, because we don't live in a perfect world, let's see what will happen if the update to rv_MyView succeeds, but the update to rv_MyOtherView fails.
The first TableUpdate() will instruct VFP to issue an INSERT/UPDATE command to SQL Server (for example, UPDATE myTable SET balance = 400 WHERE myTablePK = 'abc'). Once that command has been submitted to the back end, the back end processes it and tells VFP the command was successfully executed. The second TableUpdate() failed—one reasoning for this is that the back end couldn't process requested commands because the record that I'm trying to update has been deleted by another user.
How should I handle this? I'd already updated MyTable and I don't want to keep that change if the MyOtherTable doesn't get updated. Of course, I could write code to save original state of MyTable and then restore it if something goes wrong. But hey, that's why we have transaction capabilities in any decent database management system. Let's use them!
Transaction commands
Both VFP and SQL Server provide similar capabilities for transaction management. Table 1 summarizes the equivalent commands.
Table 1. The VFP and SQL Server transaction commands.
| VFP command/function | SQL Server equivalent (T-SQL) |
| Begin Transaction |
Begin Transaction Set Implicit_Transactions On |
| End Transaction | Commit |
| Rollback | Rollback |
| TnxLevel() | @@TranCount |
Although both DBMS provide similar capabilities for transaction management, there are some subtle differences of which you need to be aware.
The first difference is that VFP provides an End Transactioncommand, while SQL Server provides a Commit command.
Another discrepancy is found with the Rollback command when using nested transactions. In VFP, Rollback reverts changes made to the current transaction only (that is, you need one command for each nested transaction). Whereas in SQL Server, Rollback reverts changes in all nested transactions up to the outer transaction (so, you need just one Rollback command regardless of how many nested transactions you might have).
There's also a difference in the number of nested transactions allowed. VFP allows nested transactions up to five levels deep, while SQL Server doesn't have a level limit of the "deepness" of nested transactions.
Using VFP transactions
The use of VFP transactions when working with remote views is straightforward. The following code shows you how to do it:
When I issue a VFP begin transaction command, I'm asking VFP to start logging every change to VFP cursors. At the end, I'll accept all of those changes by issuing a VFP end transaction command, or I'll reject them all by issuing a VFP rollback command.
Let's say that I have the same scenario here as in my previous example: update to MyTable succeeds, but the update to MyOtherTable fails. When this happens, lEverythingOK will be false, and thus, I'll rollback my changes to revert everything to its original state. This is all true, but let's take a look at what I mean by original state.
My two views had some changes pending on them before I issued VFP begin transaction. When I issued begin transaction, I asked VFP to keep an eye on everything that happens. Then, when I updated rv_MyView**,** VFP sent those changes to MyTable in the back end and marked rv_MyView as updated. Next, my call to update rv_MyOtherView failed because the back end couldn't process changes to MyOtherTable. At the end, I rolled back the transaction, and thus, VFP marked rv_MyView as not updated (that is, its original state.)
Now, let's take a closer look at what TableUpdate() does when updating a remote view. TableUpdate() sends the appropriate SQL statement (INSERT, UPDATE or DELETE) to the back end and, upon success, marks the VFP cursor as updated. That's right, TableUpdate() actually sends the update to the back end.
You might be wondering what happened to the changes to rv_MyView. We know for sure that they were sent to the back end and that the back end accepted them. Were those changes reverted when I rolled back the VFP transaction? No, they weren't! VFP marked rv_MyView as not updated, but it never told the back end that it needs to forget about the changes in MyTable.
In other words, with the use of VFP transactions, VFP saves and restores original state in VFP cursors, but not in back-end tables!
Using SQL Server transactions
As you can see, when you're using another database management system, like SQL Server, you need a mechanism to handle transactions in the back end, in addition to the transaction capabilities that VFP provides. Fortunately, most DBMSs build in this capability.
When you want to start a transaction on the back end, all you have to do is send a command to the server to do this job. There are basically two ways to accomplish this, and both of them involve SQL pass-through functions.
The first method to start a transaction on the server is to use VFP SQLSetProp() functions. The following code shows how:
Although it isn't very intuitive, the call to the SQLSetProp() function is the one that actually starts a transaction on SQL Server. Setting the 'Transactions' property to DB_TRANSMANUAL indicates to the SQL Server that transactions will be handled manually. That is, the programmer will issue an explicit rollback or commit to indicate the end of a transaction. When you issue this call, VFP sends a SET IMPLICIT_TRANSACTIONS ON command to SQL Server. This command is sort of a smarter version of BEGIN TRANSACTION that delays the beginning of the transaction until it's really required (for example, when an UPDATE command is detected).
There's another non-intuitive call that you must do when using VFP built-in functions to handle transactions on the server. You should ensure that you set the transactions mode back to its original state (most likely DB_MANUAL) once you're finished with your transaction. Neglecting this step could produce major problems in your applications (for example, locking issues).
Instead of using those non-intuitive calls and guessing what VFP is asking the server to do, I prefer sending the transaction commands to the back end myself. The following code shows this approach:
The preceding code goes down to the metal and explicitly issues T-SQL commands to start and end the transaction on the back end. I've found this approach easier to read than using VFP built-in functions.
Now, let's see what happens with this code if rv_View is successfully updated and rv_MyOtherView fails. My first call to SQLExec() starts a transaction in the back end. That is, I'm now asking the back end to keep track of all that happens to the back-end tables. Then, TableUpdate() sends changes to rv_MyView to the back end and marks the cursor as updated. Next, my second update fails because the back end couldn't process changes to MyOtherTable. At the end, I ask the back end to roll back any changes performed to the back-end tables.
The best of both worlds
The following example shows how my code to process transactions looks when using both VFP and SQL Server transactions:
As you can see in this last code example, processing transactions in a VFP client/server application is pretty straightforward. As a golden rule you should keep in mind that you're dealing with two (not one) database management systems, and thus your code must ensure that both DBMSs are aware of your actions with the underlying database. Once you've done that, ensuring correctness in your database is a piece of cake… even if you live in an imperfect world.
To find out more about FoxTalkand Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the September 2000 issue of FoxTalk. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. FoxTalkis an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.