SQL logic error when opening query (sqlite)

[Graham88]

I'm using Lazarus 2.2 on a 64 bit Windows 10 machine with the latest version of Zeos.
I'm writing a Lazarus program that inserts into a sqlite database. The table in question has an auto increment primary key plus a second column.
I'm inserting into the table via a Ztable:
DMZ.DataModule2.Zdirectory.Append;
DMZ.DataModule2.Zdirectory.FieldByName('dir_name').AsString := directory;
DMZ.DataModule2.Zdirectory.Post;
DMZ.DataModule2.Zdirectory.ApplyUpdates;
This all works fine. But then I try and follow this with a SQL query to obtain the key of the record inserted:

DMZ.DataModule2.ZQ_last_id.Open;
DMZ.DataModule2.ZQ_last_id.First;
dir_no := DMZ.DataModule2.ZQ_last_id.FieldByName('ID').AsInteger;
DMZ.DataModule2.ZQ_last_id.Close;

And I get "SQL Error: SQL logic error" on the Open
The SQL code was "SELECT LAST_INSERT_ROWID() AS ID"
I tried changing it to "SELECT MAX(DIR_NO) AS ID FROM <table> ; but it gives the same error.
I also get the error if I try and click the 'connect' checkbox for the query from within the IDE

I have another SQL which looks the table to see if the directory is in there already and this works fine.
Any help would be appreciated. Thanks in advance
Graham

[marsupilami]

Hello Graham,

could you try to create a small example program that illustrates the problem? This greatly helps in debugging.

There are some strange things though: Why do you try to get the key from the table with another query? Usually I aould assume that Zeos will try to determine the ID value hwn you post the data? What happens if you try this:

DMZ.DataModule2.Zdirectory.Append;
DMZ.DataModule2.Zdirectory.FieldByName('dir_name').AsString := directory;
DMZ.DataModule2.Zdirectory.Post;

dir_no := DMZ.DataModule2.Zdirectory.FieldByName('ID').AsInteger;

Note: This assumes that the auto increment primary key field is called "ID".

Also - why do you call ApplyUpdates? Did you enable Cached Updates?

Best regards,

Jan

[Graham88]

Hi Jan,
Well, your suggestion worked! That's something I wouldn't have thought of. Many thanks.
As to why I'm calling ApplyUpdates that's in because another forum a user was having trouble getting the LAST_INSERT_ROWID() function to work (it was always returning zero) and he discovered that he needed to call that function after Post to get the correct result.
So the program is working at the moment but I'm still very puzzled as to why I'm getting a "SQL Logic Error" when I try and connect the query I was trying to use. Try as I might I can't see anything that would cause this.
The program in it's current state is about as simple as it can be. The user enters a directory and the program recursively goes down the directory tree.
For each directory, it gets its number (if it's already in the table) or adds it if not found.
I could zip up the source and database and post them here if that would help.
Thanks again for your help.

[marsupilami]

As to why I'm calling ApplyUpdates that's in because another forum a user was having trouble getting the LAST_INSERT_ROWID() function to work (it was always returning zero) and he discovered that he needed to call that function after Post to get the correct result.

Usually that should not be necessary if one doesn't use the cache dupdates feature.

So the program is working at the moment but I'm still very puzzled as to why I'm getting a "SQL Logic Error" when I try and connect the query I was trying to use. Try as I might I can't see anything that would cause this.
The program in it's current state is about as simple as it can be. The user enters a directory and the program recursively goes down the directory tree.
For each directory, it gets its number (if it's already in the table) or adds it if not found.
I could zip up the source and database and post them here if that would help.

Well - the problem here is that the error message doesn't help much. Which Version of Zeos do you use? Do you use Zeos 8 or do you use Zeos 7.2? Zeos 8 usually tries to get better error messages from SQLite. On Zeos 7.2 one has to enable that behavior by setting a special parameter.
Without a better error message debugging thei problem doesn't make much sense...

Another option might be to think about changing from sqlite to Firebird using the embedded mode

[Graham88]

I thought I'd downloaded the latest version but it's actually 7.2.14. So this post is actually in the wrong forum. I did a quick search but I can't find an actual download link for 8.0.
The SQL logic error has now gone away! I deleted the SQL text from the query and connected (from the IDE) ok. Then I restored the original text and now connect does not give an error. Very puzzling. It looks like some interaction between the IDE and Zeos.
I will press on with program and see how I go. It's as much a learning experience as anything else.
Thanks to everyone who responded.

[marsupilami]

Zeos 8.0 currently can only be downloaded / checked out from SVN because there is no official download yet:
https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/8.0-patches/

In Zeos 7.2 you might want to add this:

Code: Select all

ZConnection.Properties.Add('ExtendedErrorMessage=true')

This should give much better error descriptions.

Best regards,

Jan