Delphi Xe5 firedac Database locked error with SQLite database

Question

I am trying to create a simple object to handle all my database related functions. I have a functions to return a dataset or to execute a command. Now when i call this from my program I am able to fetch records using Execute_Dataset and it works fine but when i do an changes and execute a command by calling Execute_Command i get an error "database is locked" when the commit transaction is called. I have tried everything that i could be it still happens. Can someone put some light into what i am doing wrong and how i can prevent this from happening.

  function TConnectionManager.Execute_Dataset(const ASql: string; const AParams:
      array of variant; out VDataset: TDataset; const ATrn_Name: string): Boolean;
  var
    lTrn: TFDTransaction;
    lQry: TFDQuery;
  begin
    Result := True;
    lTrn:= TFDTransaction.Create (Self);
    try
      lTrn.Connection := FConnection;
      lTrn.StartTransaction;
      lQry := TFDQuery.Create (Self);
      lQry.Connection := FConnection;
      lQry.Transaction := lTrn;
      try
        if Length (AParams) > 0
        then lQry.Open (ASql, AParams)
        else lQry.Open (ASql);
        VDataset := lQry;
        Result := True;
        { Commit transaction if started within the procedure }
        lTrn.Commit;
      except
        on e:Exception
        do begin
           { Rollback transaction if started within the procedure }
           lTrn.Rollback;
           lQry.DisposeOf;
           //log
           raise;
        end;
      end;
    finally
      lTrn.DisposeOf;
    end;
  end;



 procedure TConnectionManager.Execute_Command(const ASql: string; const AParams:
      array of variant; const ATrn_Name: string);
  var
    lTrn: TFDTransaction;
    lQry: TFDQuery;
  begin
    lTrn:= TFDTransaction.Create (Self);
    try
      lTrn.Connection := FConnection;
      lTrn.StartTransaction;
      lQry := TFDQuery.Create (Self);
      lQry.Connection := FConnection;
      lQry.Transaction := lTrn;
      try
        { Execute command }
        if Length (AParams) > 0
        then lQry.ExecSQL (ASql, AParams)
        else lQry.ExecSQL (ASql);
        { Commit transaction if started within the procedure }
        lTrn.Commit;
      except
        on e:Exception
        do begin
           { Rollback transaction if started within the procedure }
           lTrn.Rollback;
           //log
           raise;
        end;
      end;
    finally
      lQry.DisposeOf;
      lTrn.DisposeOf;
    end;
  end;

Thanks

The connection component is something that I create within the object but instead if I drop a connection component on to my form and pass it into the object it works fine. This says that there is some property that is not set which is causing the problem. Is there any other properties that should be set on the connection component. — Razal K.A, Apr 09 '14 at 23:43
Put a connection on your form, set it up so that it works properly. Then right-click on your form, choose `View as text` from the context menu, and search for the connection. You can see what the property values are on the component from the resulting text. Copy them into the clipboard. Then right-click the text, choose `View as form`, and paste the clipboard content into a comment in your code. You can then set those same properties in your code (and know what the values are you should assign to those properties). — Ken White, Apr 09 '14 at 23:59
I have tried that and the created connection has all the same values set which is nothing more than the driver name and the database file path. — Razal K.A, Apr 10 '14 at 00:50
Sorry. Too much missing info, then. What is the purpose of the `ATrn_Name` parameter, which you never use? What is the content of `ASql` when the exception is raised? What was the last operation prior to the exception (what was done last before `Exec_Command` raises the exception)? If the same setup works when done with a component on the form, what does the debugger tell you when you step through the code? There are a lot of missing details here. — Ken White, Apr 10 '14 at 01:03
Hi, I wanted to create and maintain different transaction and ATrn_Name will tell which transaction to use but i have discarded this option and am creating a transaction for each operation as in the code. As for the flow I connect to the database call the execute_reader with the select statement to get the data from a table which will be filled into a list displayed on the screen. Now when i try to add a new record i call the execute_command with the insert statement. The error happens at the point where the transaction is committed (lTrn.commit) in the execute_command method. — Razal K.A, Apr 10 '14 at 01:56
Execute_Dataset seems a suspicious to me: you return an active (opened) dataset, but its underlying transaction is destroyed on exit. I'd say that common approach is to make transaction active for all the time the dataset is opened — pf1957, Apr 10 '14 at 08:33

score 3 · Answer 1 · edited Mar 16 '15 at 09:20

Try setting the Connection's properties SharedCache to 'False' and LockingMode to 'Normal'.

The default value for the connection's locking mode is 'exclusive' which can cause this problem. You can do this by right clicking on your Connection-Component (on the form) and choosing ConnectionEditor (I'm not quite sure, if that's the right English word for it, but it should be called something like that) and then setting these values.

Alternatively you can set these properties in sourcecode:

connection.Params.Add('SharedCache=False');
connection.Params.Add('LockingMode=Normal');

I'm not sure that this is the best way to solve this problem. There might be a better solution to this.

score 0 · Answer 2 · answered Mar 07 '19 at 12:44

0

Because other connection exist. Check connection component in datamodule MyFDConnection.Connected:=False;

answered Mar 07 '19 at 12:44

Cfon

235
2
9

score 0 · Answer 3 · edited Aug 16 '19 at 12:18

0

Sharedcache = false is indeed the best way to solve this problem. The components already support designtime connections.

edited Aug 16 '19 at 12:18

marc_s

732,580
175
1,330
1,459

answered Aug 16 '19 at 10:43

TeoS

1

Delphi Xe5 firedac Database locked error with SQLite database

3 Answers3