Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Nilson Rishi
  Where is Nilson Rishi?
 Bangkok
 Thailand
 Nilson Rishi
 To: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 Tags
Subject: RE: how to lock a table,row in SQL server from VFP
Thread ID: 104068 Message ID: 104150 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Saturday, August 12, 2006 5:21:05 AM         
   


Andy,

> About the only time I use it(serializable level) is when I have to generate sequential numbers for things. Obviously you do not want two users to get the same number!

Is it like such a case?
There is a table current_number_4_files with file_identifier, current_file_number as columns. Now a user needs to create a new file such as purchase order, he/she must refer to this table to find the value in the field current_file_number, suppose it is 2006012121, for the ro with file_identifier being 'PO', for purchase order, in order to increase it by 1 to get a new purchase order number.
After an implicit transaction is called with SQLSETPROP(gnhdl,'Transactions',DB_TRANSMANUL),the system should issue SQLExec(gnhdl,"SELECT current_file_number FROM current_number_4_files WHERE file_identifier = 'PO' WITH (SERIALIZABLE)",'myfilenumber')
The "WITH (SERIALIZABLE)" will ensure the table current_number_4_files will be exclusively locked until the end of transaction, so that it is ensured no other users could access its field current_file_number until the new number has been created and the field gets updated.
And in this case, a Read Repeatable isolation level for the table is NOT enough, since other users could still read the old field value and thus, makes it possible for a duplicate purchase order number.
Am I right? Please give more comments.


>> SQLEXEC() is a FUNCTION and so it returns a value! It actually gives you back "1" if the command succeeds, and "-1" if it fails. If you get a -1 then you can use AERROR() to determine the SQL Error that caused the code to fail - you can use something like this:
>
lnRes = SQLExec(lnhdl,"UPDATE CUSTOMER SET last_name='ABC'")
> IF lnRes < 1
>   AERROR( laErr )
>   MESSAGEBOX( laErr[3], 16, "SQL Server Error" )
> ENDIF


So I could use SQLRollback to roll back any transaction whenever lnRes < 1. There might be many kinds of reasons for a non-1 lnRes, update conflict is just one among many. Then I could use AERROR() to detect the reasons for update failure and make users have options to do accordingly.
LOCAL ARRAY laErr[1]
laErr[1]='00'
AERROR( laErr )
DO CASE
CASE laErr[1]=1585 && a update conflict occured, such an error has its number stored in the 1st column of array
lnResp=MESSAGEBOX('Another user has changed the data. The old data is '+cOldData+', the new data is '+cNewData+'.'+chr(13)+chr(10)+"If you want to overwrite the data, please click YES, otherwise, click No.",4+64,"UPDATE CONFLICT OCCURED")
IF lnResp=6 && YES clicked
TABLEUPDATE(.T.,.T.)
ELSE
SQLRollback(gnhdl)
ENDIF

CASE laErr[5]=1222 && a non--1 lock_timeout occured, error number is in the 5th column
ENDCASE

There are 2 problems with the code above.
1) The TABLEUPDATE(.T.,.T.)issued after "IF lnResp=6 && YES clicked" is ok for a remote view. But what is the equivalent code for SPT? Or there is no way to do a second attempt to overwrite data changes after a update conflict occurs in SPT?
2) There is still chance for lock_timeout error for TABLEUPDATE(.T.,.T.), so I have to use AERROR() to detect it again after TABLEUPDATE(.T.,.T.) and again? Is it a choice up to me? To make it easy, I prefer to use SQLRollback immedately after the first TABLEUPDATE(.T.,.F.) or SQLExec() returns .F. or <1 respectively. It is a lazy way, but good for a programmer, yes?

Regards,
Nilson

ENTIRE THREAD

how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 3:20:15 AM
RE: how to lock a table,row in SQL server from VFP Posted by Ken Murphy @ 8/11/2006 4:47:44 AM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 5:07:11 AM
RE: how to lock a table,row in SQL server from VFP Posted by Andy Kramek @ 8/11/2006 11:39:06 AM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 4:31:02 PM
RE: how to lock a table,row in SQL server from VFP Posted by Andy Kramek @ 8/11/2006 7:42:52 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/12/2006 5:21:05 AM
RE: how to lock a table,row in SQL server from VFP Posted by Andy Kramek @ 8/12/2006 1:38:26 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/12/2006 5:39:49 PM
RE: how to lock a table,row in SQL server from VFP Posted by Andy Kramek @ 8/12/2006 9:55:28 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/13/2006 5:14:59 AM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 4:47:07 PM
RE: how to lock a table,row in SQL server from VFP Posted by Martin Sellwood @ 8/11/2006 11:55:11 AM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 2:22:22 PM
RE: how to lock a table,row in SQL server from VFP Posted by tushar @ 8/11/2006 2:37:23 PM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 3:55:07 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 5:15:22 PM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 5:52:49 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 6:15:35 PM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 8:09:02 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/12/2006 5:31:24 AM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/12/2006 9:28:20 AM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/12/2006 5:44:43 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 4:44:27 PM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 4:52:50 PM
RE: how to lock a table,row in SQL server from VFP Posted by Martin Sellwood @ 8/11/2006 5:04:19 PM
RE: how to lock a table,row in SQL server from VFP Posted by Dexter Carlit @ 8/11/2006 5:18:03 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 5:47:53 PM
RE: how to lock a table,row in SQL server from VFP Posted by Nilson Rishi @ 8/11/2006 5:41:32 PM