SQLite foreign_keys issue

[jjeffman]

Hello,

I am using Zeos to connect to a SQLite database ( protocol=sqlite-3 ).

According to SQLite website http://www.sqlite.org/pragma.html#pragma_foreign_keys SQLite do not enable foreign keys by default, we need to enable it on every connection running a SQL command

Code: Select all

"PRAGMA foreign_keys = ON"

I am not having success. The code bellow always pops up a message showing "0" (OFF):

Code: Select all

void __fastcall TLDB::CadConnAfterConnect(TObject *Sender)
{
  QService->SQL->BeginUpdate();
  QService->SQL->Clear();
  QService->SQL->Add("PRAGMA foreign_keys = 1");
  QService->SQL->EndUpdate();
  QService->ExecSQL();
  QService->SQL->BeginUpdate();
  QService->SQL->Clear();
  QService->SQL->Add("PRAGMA foreign_keys");
  QService->SQL->EndUpdate();
  QService->Open();
  if( !QService->Eof && !QService->FieldByName("foreign_keys")->IsNull )
    ShowMessage( QService->FieldByName("foreign_keys")->AsString );
<cut>...</cut>

Is there another way of setting up SQLite to enable foreign keys ? I am deleting parent rows and no error message are being raised.

Thanks a lot.

Kind regards.

[marsupilami]

Hello jjeffman,

trying to set the pragma from a query will not work because it cannot be set when there is a pending transaction in SQLite:

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

TZQueries always operate within a transaction.

I wrote a small patch to enable setting the pragma from the TZConnection properties. So doing ZConnection->Properties->Add("foreign_keys=1") will enable the foreign_keys pragma, whereas ZConnection->Properties->Add("foreign_keys=0") will disable the pragma. Having no foreign_keys line will leave SQLite to its default behaviour where the pragma can be set at compile time.

Could you please apply the patch and see if it solves your problems?
Best regards,

Jan

[jjeffman]

Hello marsupilami,

As a matter of fact I am not whitin a transaction, I have just connected to the SQLite database.

The TZConnection::AutoCommit is set to false and I have not started an explicit transaction, so I can not undrstand why the SQL commando does not work.

Kind regards.

[marsupilami]

Hello jjeffman,

it seems to be a Zeos decision - at least for SQLite - but when you open a connection, transaction support is started immediately. Have a look at the file ZDbcSqLite.pas. One of the last things, the procedure TZSQLiteConnection.Open does, is a call to the procedure StartTransactionSupport. Similar things happen for PostgeSQL and Firebird. I would not be surprised if it were true for all DBC drivers. So I think this has been a design decision by the Zeos developers - as soon as you open a connection, you are in a transaction.
This is why my patch modifies TZSQLiteConnection.Open to check for a parameter foreign_keys and executes the corresponding pragma before transaction support has ben started.
So - to get back to my original question - does the patch solve your problem?
Best regards,

Jan

[jjeffman]

Hello marsupilami,

I am not sure because no exception was raised when I have pressed the delete button of my application, although the record was not deleted.

Kind regards.

[jjeffman]

marsupilami,

Your patch is working fine, and the exceptions are being raised properly.

I have forgotten of compiling the Zeos components, that's why the application was not raising any exception.

Kind regards

[marsupilami]

Hello jjeffman,

thank you for the reply.
Best regards,

Jan

[EgonHugeist]

marsupilami,

Jan can you apply the patch to 6.6 and 7.1? It's testing save.

Hey enbetween we need a list for such possible parameters... (:

[marsupilami]

Hello EgonHugeist,

the patch cannot be directly applied to 7.1. But I will supply a similar patch for 7.1 in the upcoming week :-)
I have an Idea on how to support this kind of parameters in a more generic way but it will take some more time to work this out in a way that I can present here ;o)
Best regards,

Jan

[marsupilami]

Hello EgonHugeist,

I unexpectedly had some time to do the patch for Zeos 7.0. I think 7.0 should be modified too because otherwise it will not easily be possible to migrate from 6.6 to 7.0 if anybody chooses to do so.
Best regards,

Jan

[EgonHugeist]

Hello Jan,

patch applied. R2730 \testing-7.1 SVN

Thank you...

[mac_srv]

Hi,

Just for confirmation using C++Builder:

Code: Select all

ZConnection->Properties->Add("foreign_keys=1");

will activate foreign keys pragma with SQLite. But:

Code: Select all

ZConnection->Properties->Add("foreign_keys=ON");

will not.

Tested with ZeosLib 7.2.0 Beta 3609, C++Builder XE2 Pro, SQLite 3.8.11.1.

Best,

Marcelo.

[marsupilami]

Hello Marcelo,

this is strange because as far as I understand the source code, Zeos should behave exactly the opposite way. ON is not a defined value for a boolean parameter.
Hmmm - I will have to check this...
I added the expected behaviour to the driver parameters Wiki page nonetheless and added a reference to your post. See https://sourceforge.net/p/zeoslib/wiki/ ... arameters/

Thank you and best regards,

Jan

[mac_srv]

Hi Jan,

Sorry, you are right.

I was testing a "ON DELETE CASCADE" and with "foreign_keys=ON" seemed to be working, but I think I misleading something when checking it with sqlite3.exe command line tool.

I've just retested again and "foreign_keys=1" works.

On the other hand, maybe these parameters ask some standardization, e.g., the "AutoEncodeStrings=ON " when setting AutoEncodeStrings to True into Object Inspector.

Tested with ZeosLib 7.2.0 Beta 3609, C++Builder XE2 Pro, SQLite 3.8.11.1.

I'll try do delete or correct the previous comment.

Sorry.

Marcelo.

[marsupilami]

Hello Marcelo,

thank you for your feedback. I adjusted the sourceforge wiki :-)
Best regards,

Jan