SQLite+Foreign Key+Cascade
lazarus / 16-Databases

----------------------------------------------------------------------
Vingadero · 2011-12-09 18:40

Hello all

I'm using Lazarus 0.9.30.2, SQLite3Connection, SQLTransaction and
SQLQuery.

So I have a table master and a table detail, linked with an Foreign
Key with Cascade for On Delete and On Update.

When I delete a master record from my Lazarus app, it wont delete its
detail records.

If I go and do the same process on "SQLite Expert" or any other
manager... all detail records are deleted..

Any help?
Both "SQLite Expert" and my app are using the same sqlite3.dll.

thx in advance

----------------------------------------------------------------------
rayanAyar · 2011-12-10 03:55

Do you enable FK ?:
http://www.sqlite.org/foreignkeys.html#fk_enable

It must be done on every connection to Sqlite DB.

----------------------------------------------------------------------
Vingadero · 2011-12-10 05:06

Hello thx for answer...

I tryied to enable the fks by:

Code: [Select]    SQLQuery.SQL.Text := 'PRAGMA foreign_keys = ON;';
    SQLQuery.ExecSQL;
    SQLQuery.SQL.Text := 'PRAGMA foreign_keys;';
    SQLQuery.Open;
    ShowMessage(SQLQuery.fields[0].asString);
Right after SQLite3Connection.Connected := True;
But its returns "0"

At SQLite Expert, the "PRAGMA foreign_keys;" returns 1... do you know
how to enable this at Lazarus?

thx!

----------------------------------------------------------------------
ludob · 2011-12-10 09:04

The problem is in the last paragraph of the sqlite do:
QuoteIt is not possible to enable or disable foreign key constraints
in the middle of a multi-statement transaction (when SQLite is not in
autocommit mode). Attempting to do so does not return an error; it
simply has no effect. TSQLite3Connection starts a transaction before
the first query is sent to the database, even in case of a
ExecuteDirect. As a result it isn't possible to set PRAGMA
foreign_keys. I'll make a bug report and a patch.

----------------------------------------------------------------------
ludob · 2011-12-10 09:38

Reported in http://bugs.freepascal.org/view.php?id=20865.

Attached you'll find the modified sqlite3conn.pp that you can simply
drop in your projects directory.
Add
Code: [Select]foreign_keys=onto the TSQLite3Connection.Params in the
object inspector or in the code before connecting to enable FK
support.

----------------------------------------------------------------------
Vingadero · 2011-12-10 17:16

Thx ludob!
Thx for the patch.

I got "sqlite3conn.pp(200,34) Error: identifier idents no member
"AsFMTBCD"" when I put the pp file on my project.
I'm using the Lazarus-0.9.31-34078-fpc-2.4.4-20111210-win32.exe from
2011-12-10

But dont worry, i'll wait for the next version.

Thx for help!

----------------------------------------------------------------------
ludob · 2011-12-10 18:38

Yes, sqldb from 2.4.4 would be too old.
You could also apply the patch (2 lines) to the old version of
sqlite3conn.pp. The diff is attached to the bug report mentioned
earlier.

----------------------------------------------------------------------
Vingadero · 2011-12-10 18:58

Quote from: ludob on December 10, 2011, 07:38:10 pmYes, sqldb from
2.4.4 would be too old.
You could also apply the patch (2 lines) to the old version of
sqlite3conn.pp. The diff is attached to the bug report mentioned
earlier.

I see.. better goona start using Lazrarus w FPC 2.5.1 then..
thx again!