Need help in record lock

[97itr217]

IDE: BDS2006
MyDAC: v5.55.0.39
Windows: XP sp 2
MySQL: v5.1.41 community

Let say I have a Depts and Employees table. I also have a view vEmpRec created in MySQL which has Employees left join Depts on idDepts (since new employee may not have a dept yet).

On my DataModule, I have a TMyQuery which queries this view as in

Code: Select all

SELECT * FROM vEmpRec;

The LockMode property is set to lmPessimistic.

On my employees form, I have a CRDBGrid to show rows from the above query. When I double click an employee, the code will do MyQuery->Edit() so that I should have the record in Employees table locked. But I discovered that both Depts and Employees table were locked, which is a problem because I cannot have another employee locked under the same dept.

I have tried setting UpdatingTable of TMyQuery to Employees but MyDAC doesn't like it, saying unknown table (guess it was because my select SQL statement does not have this table) when my project is run. So I put vEmpRec instead, but problem persists (both tables are locked).

I have also tried to put Lock SQL statement as:

Code: Select all

SELECT * FROM vEmpRec WHERE idEmployees = :Old_idEmployees FOR UPDATE;

into TMyQuery. TMyQuery accepts the statement and saves okay, but "forgets" it after closing and reopen. No matter what I did, the lock sql statement doesn't stay.


After many trial-and-error, I need to insert a dummy join in the select SQL to get only the row in Employees locked:

Code: Select all

SELECT * FROM Employees JOIN vEmpRec ON Employees.idEmployees = vEmpRec.idEmployees;

Is this what it supposes to be?
Any other ways to code my select SQL so that I don't need to do the dummy join?

[Dimon]

I have also tried to put Lock SQL statement as:

Code: Select all

SELECT * FROM vEmpRec WHERE idEmployees = :Old_idEmployees FOR UPDATE;

into TMyQuery. TMyQuery accepts the statement and saves okay, but "forgets" it after closing and reopen. No matter what I did, the lock sql statement doesn't stay.

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.

[97itr217]

So is it true that for the time being, the work around is to put the dummy JOIN into the select SQL?

[Dimon]

Yes, it's right.