Need help in record lock
Posted: Thu 03 Dec 2009 21:54
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
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:
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:
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?
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;
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;
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;
Any other ways to code my select SQL so that I don't need to do the dummy join?