Lock Tables Error

[BarryW]

Hi Devart Team,

I am developing a multi-user app and in one instance, I am getting the next ID from a master id table. In that routine, I am locking the table as I have the table component on the screen. I am also calling a function within that routine to ensure the next id is not already in use, by doing a SELECT query on another table where it will be used. However, in doing the select query, I have no locking in place - it is simply a read SELECT statement. When I add the "LOCK TABLES tablename READ" line to the sql, and later "UNLOCK TABLES tablename", I get an error that says "Table is READ locked. Unable to do update".

My first question is why do I nead a read lock to do a simple select statement?

Here is my code example:

Code: Select all

function TForm1.GetNextID: Integer;
var NSID: Integer;
begin

  //Set NSID to a 0.
  NSID := 0;

  //Open the table directly to get the next id value.
  try
    //Open the table.
    IDTbl.Open;
    //Lock the table so no one else can read or write for the moment.
    IDTbl.LockTable(ltWrite);
    //Get the current value - there is only 1 record and increment it by one.
    NSID := IDTbl.FieldByName('id').AsInteger+1;
    //Check to ensure the user has not previously specified the next id.
    while NOT CheckIDAvail(IntToStr(NSID)) do Inc(NSID);
    //Put the table in edit mode.
    IDTbl.Edit;
    //Put the value back into the table.
    IDTbl.FieldByName('id').AsInteger := NSID;
    //Post the value.
    IDTbl.Post;
    //Unlock the table.
    IDTbl.UnLockTable;
    //Close the table.
    IDTbl.Close;

    except on Exception do begin

      //Close the table.
      IDTbl.Close;

    end;

  end;

  //Return NSID.
  Result := NSID;

end;

// Here us the function that is called to check the availability of the ID.

function CheckIDAvail(SID: String): Boolean;
var SIDQ: TMyQuery;
    RV: Boolean;
begin

  //This function is designed to query the table to see if
  //the SID is available.

  //Set RV to True.
  RV := True;

  //Create the query component.
  SIDQ := TMyQuery.Create(nil);
  SIDQ.Connection := userConn;

  //Close, clear and populate the query.
  SIDQ.Close;
  SIDQ.SQL.Clear;
  //This next line causes grief!
//  SIDQ.SQL.Add('LOCK TABLES tablename READ;');
  SIDQ.SQL.Add('SELECT prodid '
                 +'FROM tablename '
                 +'WHERE id='+SID+';');

  //Open the query.
  SIDQ.Open;

  //Check to see if there are any records - if so, set RV to False.
  if SIDQ.RecordCount > 0 then RV := False;

  //Unlock the table.
  //These lines do not execute.
//  SIDQ.Close;
//  SIDQ.SQL.Clear;
//  SIDQ.SQL.Add('UNLOCK TABLES tablename;');
//  SIDQ.Open;

  //Close the query and free the component.
  SIDQ.Close;
  SIDQ.Free;

  //Return RV.
  Result := RV;

end;

I used similar code in another routine and it worked previously, but now it does not work either.

Any help would be greatly appreciated.

Thanks

Barry

[BarryW]

I worked through the issue more and resolved the problem.

I corrected the SQL strings in the second function as follows:

Code: Select all

    function CheckIDAvail(SID: String): Boolean;
    var SIDQ: TMyQuery;
        RV: Boolean;
    begin

      //This function is designed to query the table to see if
      //the SID is available.

      //Set RV to True.
      RV := True;

      //Create the query component.
      SIDQ := TMyQuery.Create(nil);
      SIDQ.Connection := userConn;

      //Close, clear and populate the query.
      SIDQ.Close;
      SIDQ.SQL.Clear;
      //This next line causes grief!
      SIDQ.SQL.Add('LOCK TABLES tablename READ;');
      SIDQ.SQL.Add('SELECT prodid '
                     +'FROM tablename '
                     +'WHERE id='+SID+';');
      SIDQ.SQL.Add('UNLOCK TABLES;');

      //Open the query.
      SIDQ.Open;

      //Check to see if there are any records - if so, set RV to False.
      if SIDQ.RecordCount > 0 then RV := False;

      //Close the query and free the component.
      SIDQ.Close;
      SIDQ.Free;

      //Return RV.
      Result := RV;

    end;

All is good.

Barry