<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Joe,</p>
<p>I'm hardly a FPC/SQLdb expert, but I am somewhat familiar with
SQL databases and SQLite in particular.</p>
<p>Your problem is exactly what is expected.</p>
<p>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;".</p>
<p>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.]</p>
<p>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).</p>
<p>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.</p>
<p>Just remove all the transaction bits of your code, and it should
work fine.</p>
<p>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]. <br>
[1] <a class="moz-txt-link-freetext" href="https://sqlite.org/pragma.html#pragma_journal_mode">https://sqlite.org/pragma.html#pragma_journal_mode</a><br>
[2] <a class="moz-txt-link-freetext" href="https://sqlite.org/pragma.html#pragma_temp_store">https://sqlite.org/pragma.html#pragma_temp_store</a><br>
[3] <a class="moz-txt-link-freetext" href="https://sqlite.org/pragma.html#pragma_cache_size">https://sqlite.org/pragma.html#pragma_cache_size</a><br>
</p>
<p>(and if you do care about data safety, don't do any of those!)</p>
<p>(except the cache... you can still do the cache.) :)<br>
</p>
<p><br>
</p>
<p>Cheers,<br>
Ryan<br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">On 2020/06/30 00:25, Special via
lazarus wrote:<br>
</div>
<blockquote type="cite"
cite="mid:af27e156-891c-b933-2e9e-cf804bb87cf8@joepgen.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div class="moz-text-flowed" style="font-family: -moz-fixed;
font-size: 13px;" lang="x-unicode">Hi, <br>
<br>
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. <br>
<br>
Why this exception? How to fix? Is it maybe a bug in Lazarus
2.0.8 or in SQLdb? <br>
<br>
Code: <br>
<br>
procedure TForm1.TestButtonClick(Sender: TObject); <br>
var XConnection: TSQLite3Connection; <br>
XTransaction: TSQLTransaction; <br>
XDatasource: TDataSource; <br>
XQuery: TSQLQuery; <br>
begin <br>
XConnection:= TSQLite3Connection.Create(Form1); <br>
XTransaction:= TSQLTransaction.Create(Form1); <br>
XDatasource:= TDataSource.Create(Form1); <br>
XQuery:= TSQLQuery.Create(Form1); <br>
XQuery.DataBase:= XConnection; <br>
XQuery.Transaction:= XTransaction; <br>
XDatasource.DataSet:= XQuery; <br>
XTransaction.DataBase:= XConnection; <br>
XConnection.Transaction:= XTransaction; <br>
XConnection.DatabaseName:= ExtractFilePath
(Application.ExeName) + 'D.sqlite'; <br>
XQuery.SQL.Text:= 'PRAGMA synchronous=OFF'; <br>
try <br>
XQuery.ExecSQL; <br>
XTransaction.Commit; <br>
except <br>
on e: Exception do <br>
ShowMessage ('Exception "' + e.Message + '"') <br>
end; <br>
XQuery.Free; <br>
XDatasource.Free; <br>
XTransaction.Free; <br>
XConnection.Free; <br>
end; <br>
<br>
Regards -- Joe <br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
</blockquote>
</body>
</html>