Hi Hector you really need the holdlock in the select statement so the select should look like this
sqlexec( n, 'select * from mytable WITH HOLDLOCK WHERE mywhere clause')
This will hold an lock in all the records that the SELECT returns.
>>Hi,
>>
>>Problem Scenario - I have a SQL database which can be accessed by a user as well as by a FoxPro program. I pull the SQL record down to foxpro(create SQL view command used), perform certain calculations, issue an SQLEXEC('update statement'). In the meantime the a user accesses the same SQL record and tries to update it. Problem Scenario explaination with an example -
>> SQL table - MyCounter, Field - NextCounter
>> Foxpro program runs - pulls down the following details : NextCounter=100
>> Foxpro program performing calculations
>> Before the foxpro program issues the update statement, a user access the
>> same table and same record => NextCounter=100; updates NextCounter=101
>> The user should not be able to update the record if the foxpro program
>> has pulled down the record for processing.
>>
>>Question - How can I implement record record locking of the SQL record?
>>
>>Neetu Kumar
>You could probably use transactions to have SQL lock the record while you have them on VFP.
>
>sqlexec( n, 'begin transaction' )
>sqlexec( n, 'select * from mytable )
>
>* perform VFP calculations here *
>
>sqlexec( n, 'update statement' )
>sqlexec( n, 'commit' )
>
>You may actually need to issue the HOLDLOCK that Alexandre mentioned inside the transaction.
Alexandre Palma
Senior Application Architect