Need help with table locking

[Conny]

Hi (:

I know how to do this in MySQL, but I have no clue what I'm doing wrong with UniDAC. Please help me what I have to change.

My code (shortened):

Code: Select all

procedure [...]
var
  Query: TUniQuery;
begin
  [...]
          Query := TUniQuery.Create(Self);
          Query.Connection := AppData.DatabaseConnection;	// TUniConnection object
          Query.SQL.Text := 'LOCK TABLES c_balance WRITE, c_change WRITE';
          Query.Execute;
          Query.SQL.Text := 'SELECT value FROM c_balance WHERE id = ' +
            '(SELECT max(id) FROM c_balance)';
          Query.Open;
          Query.First;

          if not Query.Eof then
            [...]
          
          Query.Close;
          
          [...]
            Query.SQL.Text := 'INSERT INTO c_balance (value, time) VALUES (:val, :time)';
            Query.ParamByName('val').AsFloat := NewVal;
            Query.ParamByName('time').AsString :=
              FormatDateTime('yyyy-mm-dd HH:NN:SS', CurrentTimestamp);
            Query.Execute;

            Query.SQL.Text := 'INSERT INTO c_change (value, time, reason) '
              + 'VALUES (:value, :time, :reason)';
            [...]
            Query.Execute;
         [...]
          Query.SQL.Text := 'UNLOCK TABLES';
          Query.Execute;
          Query.Destroy;
  [...]
end;

The error I get:

Table 'c_balance' was not locked with LOCK TABLES

The code is working without the table locking ^^

[ViktorV]

This behavior is associated with the peculiarities of the MySQL server, but not with the functionality of MyDAC.
You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias: https://dev.mysql.com/doc/refman/8.0/en ... ables.html
Therefore, to solve your problem, please try changing the code in your example to the following:

Code: Select all

  Query.SQL.Text: = 'LOCK TABLES c_balance WRITE, c_balance as c_b WRITE, c_change WRITE';
  Query.Execute;
  Query.SQL.Text: = 'SELECT value FROM c_balance WHERE id =' +
    '(SELECT max (id) FROM c_balance as c_b)';

If that doesn't work, in order to get a detailed answer, please compose a full sample demonstrating the specified behavior and send it to us through the contact form (https://devart.com/company/contactform.html), along with the script for creating database objects.

[Conny]

Oh, thank you so much, it's working!
I didn't know that! ^^''

[ViktorV]

Thank you for the interest to our product.
It is good to see that the problem has been solved.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.