puzzled by this exception
lazarus / 63-Database

----------------------------------------------------------------------
scasparz · 2025-01-05 03:20

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i PROCEDURE 
tFrmAppointments.FSetFilterEnum(aValue: tFilterEnums);BEGIN {
tFrmAppointments.FSetFilterEnum }     IF QAppointments.Active     
THEN QAppointments.Close;     IF dbT.Active      THEN dbT.Rollback;  
 FFilterEnum := aValue;    CASE FFilterEnum OF      dsNonFired       
    : QAppointments.SQL.Text := 'SELECT * FROM TAppointments WHERE
FFIRED = FALSE ORDER BY FFIREAT ASC'; { dsNonFired }      dsFiredOnly
          : QAppointments.SQL.Text := 'SELECT * FROM TAppointments
WHERE FFIRED = TRUE ORDER BY FFIREAT DESC'; { dsFiredOnly }     
dsPerpetualOnly        : QAppointments.SQL.Text := 'SELECT * FROM
TAppointments WHERE (FPERPETUAL = TRUE) AND (FFIRED = FALSE) ORDER BY
FFIREAT DESC'; { dsPerpetualOnly }      dsAll                  :
QAppointments.SQL.Text := 'SELECT * FROM TAppointments ORDER BY FId
ASC'; { dsAll }    END; { case }     // dbT.StartTransaction;   
QAppointments.Open; // CRASH POINT WITH EXCEPTION <<'ESQLDatabaseError
with message db:Table "TAppointments" already exists>>   
FInitializeDBGrid; END; { tFrmAppointments.FSetFilterEnum } 
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 · 2025-01-07 06:52

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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
Quotea write transaction is started by statements like CREATE, DELETE,
DROP, INSERT, or UPDATE (collectively "write statements")

----------------------------------------------------------------------
H₂SO₄ · 2025-01-07 14:02

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 · 2025-01-07 15:05

Quote from: Khrys on January 07, 2025, 03:02:51 pmDoes 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 · 2025-01-07 18:37

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 · 2025-01-07 18:42

Quote from: scasparz on January 07, 2025, 07:37:03 pmStill 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Showmessage(QAppointments.SQL.Text);

----------------------------------------------------------------------
H₂SO₄ · 2025-01-08 06:40

Quote from: scasparz on January 07, 2025, 07:37:03 pmThere 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses 
SQLite3Dyn; procedure TraceCallback(User: Pointer; SQL: PChar);
cdecl;begin  WriteLn(PChar(SQL));end; begin  // Right after connection
is initialized  sqlite3_trace(Connection.Handle, @TraceCallback,
Nil);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 · 2025-01-08 07:34

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 · 2025-01-08 08:22

Quote from: Khrys on January 08, 2025, 07:40:04 am(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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Const 
SQLITE_TRACE_STMT       = $01;  SQLITE_TRACE_PROFILE    = $02; 
SQLITE_TRACE_ROW        = $04;  SQLITE_TRACE_CLOSE      = $08;  Type 
xCallback = function(t: cunsigned; c: Pointer; p:Pointer;
x:Pointer):cint;cdecl;{$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 · 2025-01-09 18:49

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