Microsoft KB Archive/115838

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
 * Microsoft FoxPro Connectivity Kit, versions 2.5, 2.6

-

SUMMARY
This article describes how to use the FoxPro Connectivity Kit to prevent other users from updating a table on which you have executed an SQL SELECT statement before you UPDATE the table with any needed changes. This locking strategy is known as "pessimistic locking."

MORE INFORMATION
In Transact-SQL in SQL Server for Windows NT, the SELECT statement can use the HOLDLOCK keyword to hold a shared lock that it has set until a transaction has been completed instead of releasing the lock as soon as the required table is no longer needed.

You can accomplish this with the Connectivity Kit by using a routine similar to the one shown below.

  * CKPLOCK.PRG * Pessimistic Locking on SQL Server with the CK  * Assumes pass of a valid connection handle or connection handle = 1 * Uses 'pubs' database

PARAMETERS dbHdle

IF TYPE('dbHdle') = "L" STORE 1 TO dbHdle ENDIF

STORE 0 TO lnResult

* Use pubs database lnResult = DBExec(dbHdle, "use pubs")

* Set Transaction Mode to manual (required for this to work) lnResult = DBSetOpt(dbHdle, "Transact", 2)

* SELECT a rowset to update and lock the table until transaction * is completed. Note that SELECT without HOLDLOCK will not normally * cause the table to be locked. lnResult = DBExec(   dbHdle, "SELECT * FROM sales HOLDLOCK WHERE sales.stor_id = '7131' ")

* FoxPro program would normally perform any necessary data changes * locally at this point. WAIT WINDOW "Table is now locked on server ... " TIMEOUT 5

* Write changed data back lnResult = DBExec(   dbHdle,"UPDATE sales SET sales.qty =25 WHERE sales.stor_id='7131' ;      AND sales.ord_num = 'P3087a' ")

* Commit results lnResult = DBTransact(dbHdle, "Commit")

* Set Transaction Mode back to Automatic lnResult = DBSetOpt(dbHdle, "Transact", 1)

WAIT WINDOW ; "Table is now available for updating by another user on server ... " ; TIMEOUT 2