Hi Joe,
I'm hardly a FPC/SQLdb expert, but I am somewhat familiar with SQL
databases and SQLite in particular.
Your problem is exactly what is expected.
Basically you are trying to execute SQL code in SQLite that changes the
underlying DB engine to change the journalling mode (or at least, a
component of it, namely "synchronous") while you have already started a
transaction "XConnection.Transaction:= XTransaction;".
Now in SQLite, since it is a file-based DB engine, it needs to be able
to lock files (with standard OS file-locking semantics) during
reading/writing as needed. It is able to provide full ACID transactions
by using "Journals" of various types that funtion in various ways. The
best is to use WAL journal mode, but the fastest would be DELETE mode,
etc. It aslo has the "synchronous" pragama switch that controls HOW the
underlying engine confirms writes to actual hardware via the current VFS
(virtual file system) in use. [Think of a VFS like a driver for EXT4,
NTFS, whatever you are using.]
With "PRAGMA synchronous = OFF" you are telling the the VFS to stop
FSYNCing after writes on your file system. i.e. to not care whether your
data has been comitted to the physical hardware, but return immediately
after writing and go on with the next thing. This is a nice hack to make
it very fast, but it does cause a measure of risk for a power-cut right
at the point between sending the write, and accepting it is completed
when it really isn't comitted to hardware, will mean data corruption
(detected when you next start up).
This is called "SAFETY LEVEL" and for obvious reasons, you cannot change
how the safety mechanism acts while already inside a transaction which
started on a certain safety level. You have to perform this swicth
BEFORE starting that transaction.
Just remove all the transaction bits of your code, and it should work fine.
By the way, if you don't care about safety at all, also make sure the
"PRAGMA journal_mode = DELETE;"[1] is called (also before any
transaction), set the temp-store to memory[2] and set the cache nice and
high[3].
[1] https://sqlite.org/pragma.html#pragma_journal_mode
[2] https://sqlite.org/pragma.html#pragma_temp_store
[3] https://sqlite.org/pragma.html#pragma_cache_size
(and if you do care about data safety, don't do any of those!)
(except the cache... you can still do the cache.) :)
Cheers,
Ryan
On 2020/06/30 00:25, Special via lazarus wrote:
Hi,
I am using Lazarus 2.0.8, package SQLdb, under Win 10 (64). The
following procedure raises an exception with message
"TSQLite3Connection: Safety level may not be changed inside a
transaction". This is strange, since I think there is no active
transition in the moment when the exeption is raised.
Why this exception? How to fix? Is it maybe a bug in Lazarus 2.0.8 or
in SQLdb?
Code:
procedure TForm1.TestButtonClick(Sender: TObject);
var XConnection: TSQLite3Connection;
XTransaction: TSQLTransaction;
XDatasource: TDataSource;
XQuery: TSQLQuery;
begin
XConnection:= TSQLite3Connection.Create(Form1);
XTransaction:= TSQLTransaction.Create(Form1);
XDatasource:= TDataSource.Create(Form1);
XQuery:= TSQLQuery.Create(Form1);
XQuery.DataBase:= XConnection;
XQuery.Transaction:= XTransaction;
XDatasource.DataSet:= XQuery;
XTransaction.DataBase:= XConnection;
XConnection.Transaction:= XTransaction;
XConnection.DatabaseName:= ExtractFilePath (Application.ExeName) +
'D.sqlite';
XQuery.SQL.Text:= 'PRAGMA synchronous=OFF';
try
XQuery.ExecSQL;
XTransaction.Commit;
except
on e: Exception do
ShowMessage ('Exception "' + e.Message + '"')
end;
XQuery.Free;
XDatasource.Free;
XTransaction.Free;
XConnection.Free;
end;
Regards -- Joe
--
_______________________________________________
lazarus mailing list
lazarus@lists.lazarus-ide.org
https://lists.lazarus-ide.org/listinfo/lazarus
