SQLite : best way to share database ?

[swierzbicki]

Hello,

I want to share my SQLite DB with other software. My software update table every 500ms. I've set SQLite specific options :

BusyTimeOut to 10 000 ms (this is huge).
EnableSharedCache and ReadUncommited to true.

I have also set this on my OnAfterConnect event :

Code: Select all

if TUniConnection(Sender).ProviderName = 'SQLite' then
   TUniConnection(Sender).ExecSQL('Pragma locking_mode = NORMAL;')

But I'm still getting a "Database is locked" error message when I try to access my DB with any SQLite DB Manager.

[AlexP]

Hello,

We couldn't reproduce the described case. The code below write a new value to the database each 500ms. When opening the same database in SQLite Expert, no errors occur. Please specify the database manager you are using.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils, Uni, SQLiteUniProvider, Windows;

var
  UniConnection: TUniConnection;
  UniTable: TUniTable;

begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ConnectString := 'Provider Name=SQLite;Database=D:\test.db3;';
    UniConnection.SpecificOptions.Values['BusyTimeout'] := '10000';
    UniConnection.SpecificOptions.Values['EnableSharedCache'] := 'true';
    UniConnection.SpecificOptions.Values['ReadUncommitted'] := 'true';
    UniConnection.SpecificOptions.Values['ForceCreateDatabase'] := 'true';
    UniConnection.Connect;
    UniConnection.ExecSQL('CREATE TABLE IF NOT EXISTS T_TEST(F_ID INTEGER PRIMARY KEY, F_TEST INTEGER)');
    UniConnection.ExecSQL('DELETE FROM T_TEST');
    UniConnection.ExecSQL('INSERT INTO T_TEST(F_ID) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)');
    UniTable := TUniTable.Create(nil);
    try
      UniTable.Connection := UniConnection;
      UniTable.TableName := 'T_TEST';
      UniTable.Open;
      while True do begin
        Sleep(500);
        UniTable.Edit;
        UniTable.Fields[1].AsInteger := GetTickCount;
        UniTable.Post;
        UniTable.Next;
        if UniTable.Eof then
          UniTable.First;
      end;
    finally
      UniTable.Free;
    end;
  finally
    UniConnection.Free;
    Readln;
  end;

end.

[swierzbicki]

I'll try to reproduce my error and send you an sample (i'm using SQLite Spy).

[AlexP]

You can send your example to alexp*devart*com .