Page 1 of 1
Unidac SQLITE locking_mode
Posted: Thu 06 Jun 2013 13:14
by 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
Re: Unidac SQLITE locking_mode
Posted: Fri 07 Jun 2013 08:58
by 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;
Re: Unidac SQLITE locking_mode
Posted: Sat 08 Jun 2013 05:09
by Dunkelmann
Ah, now I understand!!
Thanks!
Re: Unidac SQLITE locking_mode
Posted: Mon 10 Jun 2013 07:00
by AlexP
Hello,
If you have any other questions, feel free to contact us.