SQLite3 & WAL Journal Mode
lazarus / 16-Databases

----------------------------------------------------------------------
runs · 2015-10-08 08:56

I made "SqliteConnection.ExecuteDirect("PRAGMA journal_mode=WAL"); "
before any SQL SELECT, at the beginning of all, and I get the error:
"Error: cannot change into wal mode from within a transaction"

Accordingly this post:
http://sqlite.1065341.n5.nabble.com/setting-journal-mode-while-in-a-
transaction-may-behave-strangely-td77334.html
this is a normal behaviour.

So, is there a way to enter into WAL mode with Lazarus standard db
components? Has anyone able to? I try to put the above sentence into
BeforeConection & AfterConnnection events but it does not work.

----------------------------------------------------------------------
LacaK · 2015-10-09 05:30

Executing any SQL statement automatically starts transaction.

In FPC 3.0 you can play with TSQLTransaction.Options :=
stoUseImplicit;

Or you can use SqliteConnection.Handle and directly pass it to
sqlite3_exec() api call.

----------------------------------------------------------------------
rvk · 2015-10-09 08:05

There is another PRAGMA command that suffers from this, which need to
be set before anything else. That's "PRAGMA foreign_keys = on". If you
look at the source you'll see there is a provision created that if you
set foreign_keys=1 in the params the DoInternalConnect of
TSQLite3Connection does this for you directly after the connect before
any transaction is started.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSQLite3Connection.DoInternalConnect;var  filename: ansistring;begin 
Inherited;  if DatabaseName = '' then   
DatabaseError(SErrNoDatabaseName,self);  InitializeSQLite;  filename
:= DatabaseName; 
checkerror(sqlite3_open(PAnsiChar(filename),@fhandle));  if
(Length(Password)>0) and assigned(sqlite3_key) then    checkerror(sqli
te3_key(fhandle,PChar(Password),StrLen(PChar(Password))));  if
Params.IndexOfName('foreign_keys') <> -1 then    execsql('PRAGMA
foreign_keys =  '+Params.Values['foreign_keys']);end;
The AfterConnect-event is just a tad too late for this. But if you
create your own TSQLite3Connection.DoInternalConnect and set your
PRAGMA it should work correctly.

Set this above your TForm1 declaration:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)  protected 
  procedure DoInternalConnect; override;  end; type  { TForm1 } 
TForm1 = class(TForm)  //...
And this below the implementation:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
implementation procedure TSQLite3Connection.DoInternalConnect;begin 
inherited;  execsql('PRAGMA journal_mode=WAL');end;
Now when the connection is set your PRAGMA will be set before anything
else.

Maybe, in the furture, the maintainers of TSQLite3Connection could
create a generic PRAGMA option (for params) to be set in the DoConnect
(and not just for the foreign_keys).

----------------------------------------------------------------------
runs · 2015-10-09 14:36

Thanks. It works!

----------------------------------------------------------------------
fpcoder · 2015-10-09 15:57

A while ago I used cascading deletes with SQLite. It needs
foreign_keys enabled, which is off by default. Going to the Object
Inspector and putting 'foreign_keys=on' to the Params property works.
Also in this case?

After that incident I added it to the wiki:
http://wiki.lazarus.freepascal.org/MasterDetail#Delete_all_Detail_reco
rds_if_Master_record_is_deleted

----------------------------------------------------------------------
rvk · 2015-10-09 22:24

Quote from: kapibara on October 09, 2015, 05:57:00 pmA while ago I
used cascading deletes with SQLite. It needs foreign_keys enabled,
which is off by default. Going to the Object Inspector and putting
'foreign_keys=on' to the Params property works. Also in this case?
As you can see in my post I showed the code of
TSQLite3Connection.DoInternalConnect. The developers made a special
provision for "PRAGMA foreign_keys=on". But they ONLY made it for
"foreign_keys". runs (OP) needed to do "PRAGMA  journal_mode=WAL".
Because the developers of TSQLite3Connection haven't taken any other
PRAGMA settings (other then foreign_keys=on) into account you can't
set the journal_mode with the connection-params.

Hence my example of the small "hack" to override DoInternalConnect and
do it there (because you already saw you can't do it with
ExecuteDirect and OnAfterConnect is too late).

Until someone changes TSQLite3Connection and makes all PRAGMA-settings
possible via connection.params the only way to set these PRAGMAs is
via this "hack" (which works fine).

----------------------------------------------------------------------
sky_khan · 2015-10-09 23:15

a possible workaround:

SqliteConnection.ExecuteDirect("COMMIT;PRAGMA journal_mode=WAL;BEGIN")

----------------------------------------------------------------------
rvk · 2015-10-10 00:20

Quote from: sky_khan on October 10, 2015, 01:15:15 ama possible
workaround:
SqliteConnection.ExecuteDirect("COMMIT;PRAGMA journal_mode=WAL;BEGIN")
That was a nice idea.
But, unfortunately, ExecuteDirect doesn't execute scripts (as far as I
know). It executes one statement and here are 3 statements used.

I tested it like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin 
SQLite3Connection1.Connected:=true; 
SQLite3Connection1.ExecuteDirect('COMMIT;PRAGMA
journal_mode=WAL;BEGIN');  with TSQLQuery.Create(nil) do  begin   
Database := SQLite3Connection1;    SQL.Text := 'PRAGMA
journal_mode;';    Open;    ShowMessage('PRAGMA journal_mode = ' +
fields[0].AsString);    Free;  end;end;It still showed
Code: [Select]PRAGMA journal_mode = delete(and I got an error exiting
the test-project)

So, we're still stuck with my little "hack"

----------------------------------------------------------------------
sky_khan · 2015-10-10 07:41

Well then, lets do it properly this time 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses 
 sqlite3dyn; procedure TForm1.ExecSQL3(Conn: TSQLite3Connection; const
aSQL: string);var  SL3Handle:Pointer;  err_msg : PChar;  msg :
string;begin  SL3Handle:=Conn.Handle;  if
sqlite3_exec(SL3Handle,@aSQL[1],nil,nil,@err_msg) <> SQLITE_OK then 
begin    if err_msg<>nil then    begin      msg:=err_msg;     
sqlite3_free(err_msg);    end;    raise Exception.Create('Query
failed: '+ msg);  end;end; procedure TForm1.Button1Click(Sender:
TObject);begin  SQLite3Connection1.Connected:=true; 
ExecSQL3(SQLite3Connection1,'PRAGMA journal_mode=WAL');  with
TSQLQuery.Create(nil) do  begin    Database := SQLite3Connection1;   
SQL.Text := 'PRAGMA journal_mode;';    Open;    ShowMessage('PRAGMA
journal_mode = ' + fields[0].AsString);    Free;  end;end;

----------------------------------------------------------------------
rvk · 2015-10-10 07:58

Quote from: sky_khan on October 10, 2015, 09:41:48 amWell then, lets
do it properly this time 
Yep, that works much better 

Although one small remark. I wouldn't make it a function of TForm1.
It's not needed and works just fine as a standalone function. That way
you could make this a global function in your program (in a utils-unit
for example):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
ExecSQL3(Conn: TSQLite3Connection; const aSQL: string);var//...