SQL Server And ODBC

Databases
1

Hello everyone, in my application I have to access a SQL Server Database via ODBCDatabase. I would need to lock a record when I read it to update it later, to avoid that, in the meantime, another user accesses the same record to update it.
I have searched the forum far and wide, and also on the Internet, but I have found practically nothing that tells me if it can be done and how.

Can anyone help me?

Thank you.

Nedi

2

There are lots of ways of doing this, some more complex than others.

eg:

Create a new table that you use to maintain the locks
Check the lock table before you open the record for edit.
Create a new entry in the lock table tracking who has it locked and when they locked it.
Use the lock time to check if the lock has been orphaned due to application crash (if >10/30/60 minutes, ignore/remove lock)
When update is written, remove the record from the lock table.

3

Thank you Julian: your solution is not new for me, and I think it’s a good one.
I’m wondering if there is a more direct solution, eg an error returned by the server if I try to lock a record already locked (just as I did using Visual Basic and ADO).

If such a solution is not possible using Xojo with ODBC Database, I will adopt yours.

Thanks

4

I believe that you can control the lock level from your connexion string. You may need to handle it yourself and not rely on a plugin. Here is an interesting article on the topic.

Edit: Here is another one, a bit more technical.

5

Thank you, Louis, the articles you mention seem to be very interesting…

6

This is what my app does before connecting to the SQL Server Database:

DB = New ODBCDatabase Dim SqlStr As String ="Provider=SQLODBC;Driver={SQL Server Native Client 10.0};Server=" + DB_Host + ";Database="+DB_Name+";Trusted_Connection=yes;MARS_Connection=yes;" SqlStr = SqlStr + "InitializationString=SET TRANSACTION ISOLATION LEVEL READ COMMITTED" DB.DataSource=SqlStr

Now, when I want to update a record, how can I lock it?
I tried doing so:

[code]Dim SQL As String
SQL = "SELECT * FROM GO_Iva WHERE ivaID = " + CStr(ID)
RS_AliquoteIVA = DB.SQLSelect(SQL)

RS_AliquoteIVA.Edit // lock record
[/code]

but nothing happens!!

(RS_AliquoteIVA is a Recordset)

7

Transaction locking is different to distributed locking.

8

Hi Julian, can you explain what you mean? I have never heard about distributed locking.
Thank you

9

Its just a fancy name for what I posted above. Where as a transaction lock takes place during a transaction, distributed locking is a method of locking access to a system/data from different processes/machines.

10

I did a bit of additional reading. It would appear that most ODBC drivers support only optimistic locking. (locking only during the Update operation). That would include the MS drivers.