Recent

Author Topic: [SOLVED] SQLite: Dropping a temp table? (database schema has changed)  (Read 2059 times)

Hansaplast

  • Hero Member
  • *****
  • Posts: 674
  • Tweaking4All.com
    • Tweaking4All

I'm using TSQLQuery and a SQLite 3 database.
Under macOS I keep getting the error that "SQLite3Connection : database schema has changed." after I drop a temporary table (which is not used after that).
What is the correct way to handle this, or is this a bug?


Some more info:
macOS Catalina 10.15.2, Lazarus 2.1.0 r62379M FPC 3.3.1 x86_64-darwin-cocoa (beta).


I'm using the following to set the correct dylib (this used to be not needed, but with Catalina this seems required? Any advice on doing this better is very much appreciated).


Code: Pascal  [Select][+][-]
  1. SQLiteLibraryName:='/usr/lib/libsqlite3.dylib';


In code I set the SQL of a TSSQLQuery to drop the table. SQLTransaction is active.


Code: Pascal  [Select][+][-]
  1. DROP TABLE IF EXISTS MyTempTable;


And execute it with "ExecSQL" followed by a "SQLTransaction.Commit;".
I've tried starting a transaction (SQLTransaction.StartTransaction) and ending a transaction (SQLTransaction.EndTransaction), but that didn't work other.


Dropping the table works, but anything after that produces the "Data schema has changed" error.


Under Windows and Linux this does work just fine. Just under macOS this causes an error.
Could this be related to the library (dylib) or the fact that I'm using FPC 3.3.1 (SVN) ?


I'm not the most knowledgeable person when it comes to using SQLite, and couldn't find anything, concerning this issue, in the Wiki.
« Last Edit: December 28, 2019, 01:16:56 pm by Hansaplast »

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: SQLite: How to handle dropping a temp table? (database schema has changed)
« Reply #1 on: December 18, 2019, 05:24:40 pm »
This is how I do it:
Code: Pascal  [Select][+][-]
  1. Connect_RefereeDB: TSQLite3Connection;
  2. Trans_RefereeDB: TSQLTransaction;
  3.  
  4. .....
  5. .....
  6.  
  7. Connect_RefereeDB.ExecuteDirect('DROP VIEW IF EXISTS qry_Temp_Uitbetalen');
  8. Trans_RefereeDB.Commit;
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

Hansaplast

  • Hero Member
  • *****
  • Posts: 674
  • Tweaking4All.com
    • Tweaking4All
Re: SQLite: How to handle dropping a temp table? (database schema has changed)
« Reply #2 on: December 19, 2019, 12:14:03 pm »
Thanks madref!
Awesome tip, but unfortunately it gives the same error ...  :(
I will however use it in other scenarios ... Seems easier than ExecSQL.

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: SQLite: How to handle dropping a temp table? (database schema has changed)
« Reply #3 on: December 19, 2019, 12:25:32 pm »
Then there is something else wrong...
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

Hansaplast

  • Hero Member
  • *****
  • Posts: 674
  • Tweaking4All.com
    • Tweaking4All
[SOLVED] SQLite: Dropping a temp table? (database schema has changed)
« Reply #4 on: December 28, 2019, 01:16:25 pm »
OK, I found the "issue", after several days, so I thought I'd post it here.


I have one particular TSQLQuery and its only purpose is to update a record (UPDATE statement).
I've defined an SQL statement, with parameters, prepared and all, and execute it using ExecSQL.
For some reason, or maybe because I didn't understand ExecSQL correctly, the dataset remained "open".
Doing an explicit "Close" before and after using this Query seems to fix it (!).


Note: I could not find anything in my code that accesses or opens this query, except for code in the failing function, where I do an additional close after ExecSQL.
I know just slapping a "close" before and after the query is most certainly not the right way to do it ... but I'm honestly out of ideas, and this seems to work, even though it makes no sense to me haha.

 

TinyPortal © 2005-2018