Unidac SQLITE locking_mode

[Dunkelmann]

Hello DevArt Team,

I am trying to make an exclusive connection to a SQlite File with

query.SQL.Add('PRAGMA locking_mode = EXCLUSIVE');
query.Execute;
The query is executed without error, but the database is still accessible from other computer.

Connection Parameters:

UniConnectionMA.Options.DisconnectedMode:=false;
UniConnectionMA.SpecificOptions.Add('Direct=True');
UniConnectionMA.SpecificOptions.Add('ForceCreateDatabase=true');
UniConnectionMA.SpecificOptions.Add('ForeignKeys=true');
UniConnectionMA.Connected := true;


Connection is in Direct Mode.
Unidac 4.6.11 for XE3, Licensed.

Can you show me how to open a SQLITE Database exclusively ?

Thanks!

MV

[AlexP]

Hello,

PRAGMA locking_mode works as follows: when setting the EXCLUSIVE value and reading from a database, another connection can open this database for reading; when making any changes, the database is completely locked. The following sample demonstrates this behaviour:

Code: Select all

var
  LiteConnectionLock: TliteConnection;
  LiteConnection: TliteConnection;
  LiteQuery: TLiteQuery;
begin
  LiteConnectionLock := TliteConnection.Create(nil);
  try
    LiteConnectionLock.Options.ForceCreateDatabase := True;
    LiteConnectionLock.Database := 'test.db3';
    LiteConnectionLock.Connect;
    LiteConnectionLock.ExecSQL('CREATE TABLE IF NOT EXISTS T_TEST(F_ID INTEGER, F_TEXT VARCHAR2(20))');
    LiteConnectionLock.ExecSQL('DELETE FROM T_TEST');
    LiteConnectionLock.ExecSQL('INSERT INTO T_TEST VALUES(1,''test1'')');
    LiteConnectionLock.ExecSQL('INSERT INTO T_TEST VALUES(2,''test2'')');
    LiteConnectionLock.ExecSQL('INSERT INTO T_TEST VALUES(3,''test2'')');
    LiteConnectionLock.ExecSQL('PRAGMA locking_mode = EXCLUSIVE');
    LiteQuery := TLiteQuery.Create(nil);
    try
      LiteQuery.Connection := LiteConnectionLock;
      LiteQuery.SQL.Text := 'SELECT * FROM T_TEST';
      LiteQuery.FetchAll := True;
      LiteQuery.Open;
      LiteConnection := TliteConnection.Create(nil);
      try
        LiteConnection.Database := 'test.db3';
        try
          LiteConnection.Connect;
          LiteConnection.ExecSQL('Select * from T_TEST');
          LiteConnection.ExecSQL('UPDATE T_TEST SET F_ID = 4 WHERE F_ID = 1');
        except
          on E:ESQLiteError do
          begin
            case E.ErrorCode of
              5: ShowMessage('Correct Message: ' + e.Message);
              else ShowMessage('Incorrect Message: ' + e.Message);
            end;
            LiteConnection.Close;
          end;
        end;
        LiteQuery.Close;

        LiteQuery.Open;
        LiteQuery.Edit;
        LiteQuery.Fields[0].AsInteger := 4;
        LiteQuery.Post;
        try
          LiteConnection.Connect;
        except
          on E:ESQLiteError do
            case E.ErrorCode of
              5: ShowMessage('Correct Message: ' + e.Message);
              else ShowMessage('Incorrect Message: ' + e.Message);
            end;
        end;

      finally
        LiteConnection.Free;
      end;
    finally
      LiteQuery.Free;
    end;
  finally
    LiteConnectionLock.Free;
  end;
end;

[Dunkelmann]

Ah, now I understand!!



Thanks!

[AlexP]

Hello,

If you have any other questions, feel free to contact us.