Recent

Author Topic: SQLite+Foreign Key+Cascade  (Read 9342 times)

Vingadero

  • New Member
  • *
  • Posts: 42
SQLite+Foreign Key+Cascade
« on: December 09, 2011, 07:40:58 pm »
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

  • New member
  • *
  • Posts: 7
Re: SQLite+Foreign Key+Cascade
« Reply #1 on: December 10, 2011, 04:55:57 am »
Do you enable FK ?:
http://www.sqlite.org/foreignkeys.html#fk_enable

It must be done on every connection to Sqlite DB.

Vingadero

  • New Member
  • *
  • Posts: 42
Re: SQLite+Foreign Key+Cascade
« Reply #2 on: December 10, 2011, 06:06:02 am »
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

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite+Foreign Key+Cascade
« Reply #3 on: December 10, 2011, 10:04:42 am »
The problem is in the last paragraph of the sqlite do:
Quote
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

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite+Foreign Key+Cascade
« Reply #4 on: December 10, 2011, 10:38:24 am »
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

  • New Member
  • *
  • Posts: 42
Re: SQLite+Foreign Key+Cascade
« Reply #5 on: December 10, 2011, 06:16:48 pm »
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

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite+Foreign Key+Cascade
« Reply #6 on: December 10, 2011, 07:38:10 pm »
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

  • New Member
  • *
  • Posts: 42
Re: SQLite+Foreign Key+Cascade
« Reply #7 on: December 10, 2011, 07:58:13 pm »
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!

 

TinyPortal © 2005-2018