puzzled by this exception

[scasparz]

Hi,

Am latest Lazarus + SQLite3 on Linux Mint 21.1
Am trying to modify the SQL property of a TSQLQuery at run time, but I get an unexpected exception at line (19).

Please consider the code:

Code: Pascal  [Select][+][-]

1. PROCEDURE   tFrmAppointments.FSetFilterEnum(aValue: tFilterEnums);
2. BEGIN { tFrmAppointments.FSetFilterEnum }
3.  
4.     IF QAppointments.Active
5.       THEN QAppointments.Close;
6.  
7.     IF dbT.Active
8.       THEN dbT.Rollback;
9.  
10.     FFilterEnum := aValue;
11.     CASE FFilterEnum OF
12.       dsNonFired             : QAppointments.SQL.Text := 'SELECT * FROM TAppointments WHERE FFIRED = FALSE ORDER BY FFIREAT ASC'; { dsNonFired }
13.       dsFiredOnly            : QAppointments.SQL.Text := 'SELECT * FROM TAppointments WHERE FFIRED = TRUE ORDER BY FFIREAT DESC'; { dsFiredOnly }
14.       dsPerpetualOnly        : QAppointments.SQL.Text := 'SELECT * FROM TAppointments WHERE (FPERPETUAL = TRUE) AND (FFIRED = FALSE) ORDER BY FFIREAT DESC'; { dsPerpetualOnly }
15.       dsAll                  : QAppointments.SQL.Text := 'SELECT * FROM TAppointments ORDER BY FId ASC'; { dsAll }
16.     END; { case }
17.  
18.     // dbT.StartTransaction;
19.     QAppointments.Open; // CRASH POINT WITH EXCEPTION <<'ESQLDatabaseError with message db:Table "TAppointments" already exists>>
20.     FInitializeDBGrid;
21.  
22. END; { tFrmAppointments.FSetFilterEnum }
23.  

Of course table TAppointments exits, query is not trying to recreate it.
For the love of God, what am I doing wrong?


regards
s

[Zvoni]

Does the exception occur for all 4 enum-values? or for only one specific?
any "visual" DB-components accessing the db "directly"?

Because i agree: Can't see anything wrong, either, provided all 3 objects (Connection, Transaction, Query) are connected to each other properly

EDIT: The only thing "fishy" to me is the Rollback in line 8.
Is it possible you have a CREATE TABLE somewhere, which is pending?
Maybe change that Create Table statement to

Code: SQL  [Select][+][-]

1. CREATE TABLE IF NOT EXISTS TAppointments.......

That way it would be a no-op if the table already exists, and no you wouldn't lose any data already in that table.

Remember: CREATE TABLE is a Transactional Statement in SQLite
https://www.sqlite.org/lang_transaction.html

a write transaction is started by statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively "write statements")

[Khrys]

Does your  case  statement handle all variants of  TFilterEnums?
The  SQL  property isn't explicitly updated in the  else  case, so you're most likely calling  Open()  on the previous (stale) CREATE statement.

[Zvoni]

Does your  case  statement handle all variants of  TFilterEnums?
The  SQL  property isn't explicitly updated in the  else  case, so you're most likely calling  Open()  on the previous (stale) CREATE statement.

Good call. Didn't think about an eventual "otherwise/else" for an unhandled Enum-Value

[scasparz]

To begin with, thank you both for your contributions.

Now:
There is no fifth value for the tFilterEnums type, hence I can see no need for an 'otherwise/else'. Exception is raised when control passes from any of the four case clauses.
Also have ensured that the creation of the tAppointments table has been committed explicitly long before. I do not prefer autocommit.

Still seems a kind of a mystery to me. Have no other issues with SQLite. It looks robust to me, at least to the extend I have used it.


kind regards
s

[rvk]

Still seems a kind of a mystery to me. Have no other issues with SQLite. It looks robust to me, at least to the extend I have used it.

Just for the fun of it... Put the following line above the .Open. and the what the result is.

Code: Pascal  [Select][+][-]

1. Showmessage(QAppointments.SQL.Text);

[Khrys]

There is no fifth value for the tFilterEnums type, hence I can see no need for an 'otherwise/else'.

Keep in mind that enums may contain invalid values when casting from integers, so depending on how  aValue  is obtained I still wouldn't rule out the possibility.


You could log the statements that actually get executed by SQLite3 like this (low-level library calls):

Code: Pascal  [Select][+][-]

1. uses
2.   SQLite3Dyn;
3.  
4. procedure TraceCallback(User: Pointer; SQL: PChar); cdecl;
5. begin
6.   WriteLn(PChar(SQL));
7. end;
8.  
9. begin
10.   // Right after connection is initialized
11.   sqlite3_trace(Connection.Handle, @TraceCallback, Nil);
12. end.

(TSQLConnection  can also do generic logging, but I'm more familiar with this approach.  sqlite3_trace  is deprecated in favor of  sqlite3_trace_v2, but as far as I know  SQLite3Dyn  doesn't declare the latter, and the former is sufficient for simply looking at the executed statements anyways.)

[Zvoni]

Agree with rvk and Khrys:
Look at the statement just before you "open" it
And between your line 10 and 11 (Before you enter into the Case Of) i would insert a
"QAppointments.SQL.Clear;" just for the heck of it

[Zvoni]

(TSQLConnection  can also do generic logging, but I'm more familiar with this approach.  sqlite3_trace  is deprecated in favor of  sqlite3_trace_v2, but as far as I know  SQLite3Dyn  doesn't declare the latter, and the former is sufficient for simply looking at the executed statements anyways.)

that's because sqlite3.inc is AFAIK still at SQLite3 version 3.14, and sqlite3_trace_v2 came afterwards
https://www.sqlite.org/c3ref/trace_v2.html

Code: Pascal  [Select][+][-]

1. Const
2.   SQLITE_TRACE_STMT       = $01;
3.   SQLITE_TRACE_PROFILE    = $02;
4.   SQLITE_TRACE_ROW        = $04;
5.   SQLITE_TRACE_CLOSE      = $08;
6.  
7. Type
8.   xCallback = function(t: cunsigned; c: Pointer; p:Pointer; x:Pointer):cint;cdecl;
9. {$IFDEF S}function{$ELSE}var{$ENDIF}sqlite3_trace_v2{$IFDEF D}: function{$ENDIF}(db: psqlite3; umask:cunsigned; cb: xCallback; user: pointer): cint; cdecl;{$IFDEF S}external Sqlite3Lib;{$ENDIF}

EDIT: Just found out, that sqlite3_trace_v2 was introduced in sqlite source at v3.14, sooo....... No idea why our guys didn't use it in their inc-file

[scasparz]

Again thanks everyone for their contributions. No solution found, am trying to bypass the issue by changing application to the extend I can afford.


kind regards
s