Hi,
Is there a recommended method to perform row/record lock in Pervasive SQL?
I'm working on an application that has the database on the server and is accessed by 4 desktops and 2 PDA (Pocket PC 2003 SE). I cannot find any Compact Framework libraries and desperately need to use it as I will having more projects based solely on PDAs.

All the desktops and PDAs will be required to lock a job while a staff is entering information for the job so that other staffs will not edit the same job.

I'm currently using a custom Socket implementation to access the data on the server. There is a boolean column to indicate if the job is locked. Problem is if anything happens and the application terminates the lock flag will not be reset. The staff will then need to contact a senior staff to unlock the job.

What should I do? The customer will be adding more PDAs and the custom implementation might not be able to cope. Also they will be adding more staffs, so it's not feasible to get the senior staff to unlock it everytime something happens.

Any help would be appreciated.

It depends a lot on the implementation of your custom socket server.

First, it may not be necessary to lock each job record the entire time it's being accessed. Pervasive uses passive concurrency, which in a nutshell, only attempts a lock on a record when it is updated, not when it is read. If the Microkernel detects a conflict between the updates of two users, it returns a status 80 to the second, at which time the application can re-read the record and apply the second user's changes and re-update.

If you need stronger concurrency control than that, you can have the socket server read the record with one of the several available lock biases. There are several options available: Single wait record lock, single no-wait record lock, multiple wait record lock, multiple no-wait record lock. These are all fully documented in the SDK documentation. One problem I see is if the PDA application terminates while it has a record locked, your socket server is still going to have to release the read lock on the record somehow. Might be safer to just go with the passive concurrency. It works fine, it's just a different way of looking at things.

You haven't mentioned whether the socket server is using the Btrieve Transactional API or SQL. If you're using SQL, then passive concurrency is also the default in that case because passive concurrency is implemented at the microkernel level. You can implement implicit record locking in SQL by adding the "WITH UPDATE" clause to the SELECT.

Last, can't you use JDBC on the PDAs?

Wayne Freeman
www.analyticabiz.com

Thanks for the hints. I never knew so many options existed. I'm pretty new to programming, so I will take some time to check it out.
Using .NET framework was one of the key requirements. I'm using the .NET libraries via SQL.

Thanks again for the tips. Cheers.