Page 1 of 1
SQLite : best way to share database ?
Posted: Wed 04 Mar 2015 15:08
by 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.
Re: SQLite : best way to share database ?
Posted: Fri 06 Mar 2015 10:49
by 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.
Re: SQLite : best way to share database ?
Posted: Thu 12 Mar 2015 19:57
by swierzbicki
I'll try to reproduce my error and send you an sample (i'm using SQLite Spy).
Re: SQLite : best way to share database ?
Posted: Fri 13 Mar 2015 06:15
by AlexP
You can send your example to alexp*devart*com .