SQLite+Foreign Key+Cascade

[Vingadero]

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]

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

It must be done on every connection to Sqlite DB.

[Vingadero]

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]

The problem is in the last paragraph of the sqlite do:

It 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]

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]

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]

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]

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.

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