Recent

Author Topic: weird sqlite error "database schema has changed"  (Read 839 times)

tt

  • Jr. Member
  • **
  • Posts: 79
weird sqlite error "database schema has changed"
« on: May 16, 2022, 10:28:11 am »
Hello everyone,

I am observing a behaviour I do not understand in sqlite, I post here to know if somebody has similar situations, maybe is a intrinsic sqlite error not related to FP.

I need to create a table to temporary store the result of a query and I DO NOT use TEMP tables (and I won't use them).

I follow this procedure

1. Open a connection C
2. Open a transaction T on C
3. (using C and T) create the table TB to persist the result of the query
4. (using C and T) Execute a insert into ... select in order to populate TB
5. (using C and T) Execute a query to read data from TB
6. Drop table TB
7. Commit T
8. Close C

The problem is the following:

Case1: if in 3 and 6 I use "CREATE IF NOT EXISTS"/"DROP IF EXISTS" I get a "database schema has changed error" when I finally drop the table in step 6

Case2: if in 3 and 6 I use the simple "CREATE"/"DROP" I do not get any error.

Why IF NOT EXISTS/IF EXIST generate such an error?


Code: Pascal  [Select][+][-]
  1. function LoadWorkCyclesAndDiametersInInterval(
  2.   logger: TLogger;
  3.   aliasName: string;
  4.   machineId: Integer;
  5.   startDate: TDateTime;
  6.   endDate: TDateTime;
  7.   workCyclesAndDiametersInIntervalDiameters: TObjectList;
  8.   workCyclesAndDiametersInIntervalWorkCycles: TObjectList;
  9.   workCyclesAndDiametersInInterval: TObjectList;
  10.   out errorMessage: string): Boolean;
  11.  
  12.   procedure DoLoadWorkCyclesAndDiametersInInterval(connection: TMEPConnection; transaction: TSQLTransaction);
  13.   var
  14.     names, sqls: TMEPDatabaseIOStrings;
  15.     queries: TQueries;
  16.     q: TSQLQuery;
  17.     summary_item: TWorkCycleAndDiameter;
  18.     workcycle: TWorkCycle;
  19.     diameter: TDiameter;
  20.   begin
  21.     names := TMEPDatabaseIOStrings.Create();
  22.     names.Add('ctt');
  23.     names.Add('dtt');
  24.     names.Add('ttp');
  25.     names.Add('workcycles');
  26.     names.Add('diameters');
  27.     names.Add('summary');
  28.  
  29.     sqls := TMEPDatabaseIOStrings.Create();
  30.     sqls.Add('create table if not exists LWCADII'
  31.              + ' ('
  32.              + '    customerNo varchar(255),'
  33.              + '    scheduleNo varchar(255),'
  34.              + '    jobOrderNo varchar(255),'
  35.              + '    min_start_date datetime, '
  36.              + '    max_end_date datetime'
  37.              + ' )');
  38.     sqls.Add('drop table if exists LWCADII');
  39.  
  40.     sqls.Add(' insert into LWCADII (customerNo, scheduleNo, jobOrderNo, min_start_date, max_end_date)'
  41.              + '  select          '
  42.              + '    customerNo    '
  43.              + '  , scheduleNo    '
  44.              + '  , jobOrderNo    '
  45.              + '  , min(startDate)' // it might happen by mistake that same c/s/j is used for multiple diameter
  46.              + '  , max(endDate)  '
  47.              + ' from '
  48.              + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name
  49.              + ' where'
  50.              + ' refMachine = :refMachine'
  51.              + '   and (:startDate <= startDate)'
  52.              + '   and (endDate < :endDate)'
  53.              + ' group by customerNo, scheduleNo, jobOrderNo'
  54.              + ' order by startDate, customerNo, scheduleNo, jobOrderNo');
  55.  
  56.     sqls.Add('select              '
  57.              + '  customerNo      '
  58.              + ', scheduleNo      '
  59.              + ', jobOrderNo      '
  60.              + ', min_start_date  ' // it might happen by mistake that same c/s/j is used for multiple diameter
  61.              + ', max_end_date    '
  62.              + ' from LWCADII');
  63.  
  64.     sqls.Add('select distinct '
  65.              + '  materialDiameter                                         '
  66.              + ' from '
  67.              + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name
  68.              + ' where'
  69.              + ' refMachine = :refMachine'
  70.              + '   and (:startDate <= startDate)'
  71.              + '   and (endDate < :endDate)'
  72.              + ' order by materialDiameter');
  73.  
  74.     sqls.Add('select '
  75.              + '  customerNo                                                        '
  76.              + ', scheduleNo                                                        '
  77.              + ', jobOrderNo                                                        '
  78.              + ', materialDiameter                                                  '
  79.              + ', sum(totalPiecesExpected)                 as actual_expected_count '
  80.              + ', sum(totalPiecesProduced)                 as actual_produced_count '
  81.              + ', sum(totalWeight)                         as actual_weight         '
  82.              + ', sum(totalEnergyWh)                       as total_energy_Wh       '
  83.              + ', sum(totalElapsedSeconds)                 as total_elapsed_seconds '
  84.              + ', sum(totalPiecesProduced * productLength) as total_length_mm       '
  85.              + ' from '
  86.              + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name
  87.              + ' where'
  88.              + ' refMachine = :refMachine'
  89.              + '   and (:startDate <= startDate)'
  90.              + '   and (endDate < :endDate)'
  91.              + ' group by customerNo, scheduleNo, jobOrderNo, materialDiameter'
  92.              + ' order by customerNo, scheduleNo, jobOrderNo, materialDiameter');
  93.  
  94.     queries := connection.QueriesCreate(names, sqls, transaction);
  95.     try
  96.       try
  97.         q := queries.Queries['dtt']; // make sure table is dropped
  98.         q.ExecSQL();
  99.       except
  100.         // if did not exist then ok
  101.       end;
  102.  
  103.       q := queries.Queries['ctt']; // make sure table is created
  104.       q.ExecSQL();
  105.  
  106.       q := queries.Queries['ttp']; // populated temporary table
  107.       q.ParamByName('refMachine').AsInteger := machineId;
  108.       q.ParamByName('startDate').AsDateTime := startDate;
  109.       q.ParamByName('endDate').AsDateTime   := endDate  ;
  110.       q.ExecSQL();
  111.  
  112.       {%REGION 'Load workcycles'}
  113.       q := queries.Queries['workcycles'];
  114.       q.Open();
  115.       while(not q.EOF) do
  116.       begin
  117.         workcycle := TWorkCycle.Create(
  118.                         q.FieldByName('customerNo'      ).AsString
  119.                       , q.FieldByName('scheduleNo'      ).AsString
  120.                       , q.FieldByName('jobOrderNo'      ).AsString
  121.                       , q.FieldByName('min_start_date'  ).AsDateTime
  122.                       , q.FieldByName('max_end_date'    ).AsDateTime);
  123.         workCyclesAndDiametersInIntervalWorkCycles.Add(workcycle);
  124.  
  125.         q.Next();
  126.       end;
  127.       q.Close();
  128.       {%ENDREGION}
  129.  
  130.       {%REGION 'Load diameters'}
  131.       q := queries.Queries['diameters'];
  132.       q.ParamByName('refMachine').AsInteger := machineId;
  133.       q.ParamByName('startDate').AsDateTime := startDate;
  134.       q.ParamByName('endDate').AsDateTime   := endDate  ;
  135.       q.Open();
  136.       while(not q.EOF) do
  137.       begin
  138.         diameter := TDiameter.Create(q.FieldByName('materialDiameter').AsFloat);
  139.         workCyclesAndDiametersInIntervalDiameters.Add(diameter);
  140.  
  141.         q.Next();
  142.       end;
  143.       q.Close();
  144.       {%ENDREGION}
  145.  
  146.       {%REGION 'Load summary'}
  147.       q := queries.Queries['summary'];
  148.       q.ParamByName('refMachine').AsInteger := machineId;
  149.       q.ParamByName('startDate').AsDateTime := startDate;
  150.       q.ParamByName('endDate').AsDateTime   := endDate  ;
  151.       q.Open();
  152.       while(not q.EOF) do
  153.       begin
  154.         summary_item := TWorkCycleAndDiameter.Create(
  155.                             q.FieldByName('customerNo').AsString
  156.                           , q.FieldByName('scheduleNo').AsString
  157.                           , q.FieldByName('jobOrderNo').AsString
  158.                           , q.FieldByName('materialDiameter').AsFloat
  159.                           , q.FieldByName('actual_produced_count'  ).AsInteger
  160.                           , q.FieldByName('actual_weight'          ).AsFloat
  161.                           , q.FieldByName('total_energy_Wh'        ).AsFloat
  162.                           , q.FieldByName('total_length_mm'        ).AsFloat / 1000
  163.                           , q.FieldByName('total_elapsed_seconds'  ).AsFloat);
  164.         workCyclesAndDiametersInInterval.Add(summary_item);
  165.  
  166.         q.Next();
  167.       end;
  168.       q.Close();
  169.       {%ENDREGION}
  170.  
  171.       q := queries.Queries['dtt']; // make sure table is deleted befor exit
  172.       q.ExecSQL();
  173.     finally
  174.       FreeAndNil(names);
  175.       FreeAndNil(sqls);
  176.       FreeAndNil(queries);
  177.     end;
  178.   end;
  179.  
  180. var
  181.   connection: TMEPConnection;
  182.   transaction: TSQLTransaction;
  183. begin
  184.   Result := False;
  185.   errorMessage := '';
  186.  
  187.   connection := TMEPConnection.Create();
  188.   connection.AliasName := aliasName;
  189.   try
  190.     connection.ConnectionOpen();
  191.     transaction := connection.TransactionCreate();
  192.     try
  193.       transaction.StartTransaction();
  194.       try
  195.  
  196.         DoLoadWorkCyclesAndDiametersInInterval(connection, transaction);
  197.  
  198.         transaction.Commit();
  199.  
  200.         Result := True;
  201.       except
  202.         on E: Exception do
  203.         begin
  204.           transaction.Rollback();
  205.  
  206.           errorMessage := logger.LogExceptionErrorS(E, 'Unexpected error while loading work cycles and diameters in interval');
  207.         end;
  208.       end;
  209.     finally
  210.       FreeAndNil(transaction);
  211.     end;
  212.   finally
  213.     FreeAndNil(connection);
  214.   end;
  215. end;
  216.  

« Last Edit: May 16, 2022, 10:37:19 am by tt »

rvk

  • Hero Member
  • *****
  • Posts: 4949
Re: weird sqlite error "database schema has changed"
« Reply #1 on: May 16, 2022, 10:59:08 am »
I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).

You could try to move 3 (create) to before 2 (opening the transaction) and 6 (drop) to after 7 (commit).

So:
1. Open a connection C
2. (using C and T) create the table TB to persist the result of the query
3. Open a transaction T on C
4. (using C and T) Execute a insert into ... select in order to populate TB
5. (using C and T) Execute a query to read data from TB
6. Commit T
7. Drop table TB
8. Close C

Does that work? (not sure if the components can execute sql without an active transaction though).

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: weird sqlite error "database schema has changed"
« Reply #2 on: May 16, 2022, 11:58:32 am »
I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).
No.
For SQLite it's legal to wrap CREATE TABLE/DROP TABLE in a Transaction
Meaning: As long as the Transaction is not Committed, a "created" Table doesn't "exist" for the DROP and vice versa

At a guess: IF (NOT) EXISTS is basically a SELECT on "sqlite_master", and since the Transaction is not committed.....
Would have to check source-code of SQLite
« Last Edit: May 16, 2022, 12:07:17 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

tt

  • Jr. Member
  • **
  • Posts: 79
Re: weird sqlite error "database schema has changed"
« Reply #3 on: May 16, 2022, 12:24:18 pm »
I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).
No.
For SQLite it's legal to wrap CREATE TABLE/DROP TABLE in a Transaction
Meaning: As long as the Transaction is not Committed, a "created" Table doesn't "exist" for the DROP and vice versa

At a guess: IF (NOT) EXISTS is basically a SELECT on "sqlite_master", and since the Transaction is not committed.....
Would have to check source-code of SQLite

Thanks.
Just to add few bits more: opening the command line sqlite3 it does not show errors (and I also checked in the middle of create and drop, the table was actually created).

tt@debian:~$ sqlite3 databasetest
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> begin transaction;
sqlite> create table if not exists XXXX (id integer);
sqlite> drop table if exists XXXX;
sqlite> commit;
sqlite> .q





Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: weird sqlite error "database schema has changed"
« Reply #4 on: May 16, 2022, 12:45:40 pm »
Just found something else: Do you use prepared Statements?
https://stackoverflow.com/questions/41347346/causes-of-sqlite-general-error-17-database-schema-has-changed
Quote
Attempting to execute a prepared statement defined before the schema change will result in a SQLite General error: 17 database schema has changed error.

EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited

EDIT2: As far as i could find out: If you prepare a Statement, it kinda saves a "schema_version" internally against which the statement was prepared.
If you then change the schema (and Creating/dropping tables is changing the schema) you get a KABOOM, when trying to execute said statement
« Last Edit: May 16, 2022, 01:04:14 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 4949
Re: weird sqlite error "database schema has changed"
« Reply #5 on: May 16, 2022, 01:16:03 pm »
EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited
That's why I said I thought DDL statements like CREATE and DROP are done outside the transaction (and if they are in sqlite_master even before commit that is correct).

@tt, can you test this by beginning a transaction, create the table and then doing a rollback. Is the table after that still there?
If the table is still there, that's proof the CREATE is done outside the transaction directly on the database schema.
« Last Edit: May 16, 2022, 01:20:04 pm by rvk »

tt

  • Jr. Member
  • **
  • Posts: 79
Re: weird sqlite error "database schema has changed"
« Reply #6 on: May 16, 2022, 01:25:51 pm »
Just found something else: Do you use prepared Statements?
https://stackoverflow.com/questions/41347346/causes-of-sqlite-general-error-17-database-schema-has-changed
Quote
Attempting to execute a prepared statement defined before the schema change will result in a SQLite General error: 17 database schema has changed error.

EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited

EDIT2: As far as i could find out: If you prepare a Statement, it kinda saves a "schema_version" internally against which the statement was prepared.
If you then change the schema (and Creating/dropping tables is changing the schema) you get a KABOOM, when trying to execute said statement


Sorry, I initially thought that this explanation was fine to me, but then I realized that I am still confused.

The call

Code: Pascal  [Select][+][-]
  1.     queries := connection.QueriesCreate(names, sqls, transaction);
  2.  

creates all the TSQLQueries described in statements before that, and puts them in a sort of map, to ease later usage and don't create the queries in the middle of the logic.

So yes, I create the queries in advance (even if I am not sure that this means using prepared statements).

But still, why if I create/drop the table using the the if not exists/if exists clauses I get a "database schema has changed" error while if I do not use the cite clauses I dont get any error?

All in all:
1. as the support table does not exists before I create it,  putting if not exists or nothing is equivalent
2. as the support table exists before I drop it, putting if exists or nothing is equivalent
3. And, in both cases:
3.1 I am using statements somehow created before
3.2 I am changing the schema

So I still don't undertstand what is wrong with if not exists/if exists clauses.

Anyway that for the effort in helping me.
« Last Edit: May 16, 2022, 01:32:57 pm by tt »

tt

  • Jr. Member
  • **
  • Posts: 79
Re: weird sqlite error "database schema has changed"
« Reply #7 on: May 16, 2022, 01:35:53 pm »
EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited
That's why I said I thought DDL statements like CREATE and DROP are done outside the transaction (and if they are in sqlite_master even before commit that is correct).

@tt, can you test this by beginning a transaction, create the table and then doing a rollback. Is the table after that still there?
If the table is still there, that's proof the CREATE is done outside the transaction directly on the database schema.

Thank you rvk.
I have tried doing that before writing the post, from within the sqlite3 client:


Code: Text  [Select][+][-]
  1. tt@debian:~$ rm databasetest
  2. tt@debian:~$ sqlite3 databasetest
  3. SQLite version 3.27.2 2019-02-25 16:06:06
  4. Enter ".help" for usage hints.
  5. sqlite> begin transaction;
  6. sqlite> create table if not exists XXXX (id integer);
  7. sqlite> .q
  8. tt@debian:~$ sqlite3 databasetest
  9. SQLite version 3.27.2 2019-02-25 16:06:06
  10. Enter ".help" for usage hints.
  11. sqlite> .schema
  12. sqlite> .q
  13.  
  14.  
  15.  
  16. tt@debian:~$ rm databasetest
  17. tt@debian:~$ sqlite3 databasetest
  18. SQLite version 3.27.2 2019-02-25 16:06:06
  19. Enter ".help" for usage hints.
  20. sqlite> begin transaction;
  21. sqlite> create table if not exists XXXX (id integer);
  22. sqlite> commit;
  23. sqlite> .q
  24. tt@debian:~$ sqlite3 databasetest
  25. SQLite version 3.27.2 2019-02-25 16:06:06
  26. Enter ".help" for usage hints.
  27. sqlite> .schema
  28. CREATE TABLE XXXX (id integer);
  29. sqlite> .q
  30.  

As you can see, if I start the transaction, unless I do the commit before exiting, table is not created.
« Last Edit: May 16, 2022, 01:38:48 pm by tt »

rvk

  • Hero Member
  • *****
  • Posts: 4949
Re: weird sqlite error "database schema has changed"
« Reply #8 on: May 16, 2022, 01:40:58 pm »
Ok. Then somehow the CREATE is rolled back in the transaction.
Not as I thought...

Code: [Select]
SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> begin transaction;
sqlite> .schema
sqlite> create table ABC (id integer);
sqlite> .schema
CREATE TABLE ABC (id integer);
sqlite> rollback;
sqlite> .schema
sqlite> .q

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: weird sqlite error "database schema has changed"
« Reply #9 on: May 16, 2022, 01:42:55 pm »
Thank you rvk.
I have tried doing that before writing the post, from within the sqlite3 client:


Code: Text  [Select][+][-]
  1. tt@debian:~$ rm databasetest
  2. tt@debian:~$ sqlite3 databasetest
  3. SQLite version 3.27.2 2019-02-25 16:06:06
  4. Enter ".help" for usage hints.
  5. sqlite> begin transaction;
  6. sqlite> create table if not exists XXXX (id integer);
  7. sqlite> .q
  8. tt@debian:~$ sqlite3 databasetest
  9. SQLite version 3.27.2 2019-02-25 16:06:06
  10. Enter ".help" for usage hints.
  11. sqlite> .schema
  12. sqlite> .q
  13.  
  14.  
  15.  
  16. tt@debian:~$ rm databasetest
  17. tt@debian:~$ sqlite3 databasetest
  18. SQLite version 3.27.2 2019-02-25 16:06:06
  19. Enter ".help" for usage hints.
  20. sqlite> begin transaction;
  21. sqlite> create table if not exists XXXX (id integer);
  22. sqlite> commit;
  23. sqlite> .q
  24. tt@debian:~$ sqlite3 databasetest
  25. SQLite version 3.27.2 2019-02-25 16:06:06
  26. Enter ".help" for usage hints.
  27. sqlite> .schema
  28. CREATE TABLE XXXX (id integer);
  29. sqlite> .q
  30.  

As you can see, if I start the transaction, unless I do the commit before exiting, table is not created.
Not correct.
If you start a Transaction, but quit before committing you execute an implicit Rollback.
As long as you're still inside your transaction, the table is in sqlite_master
Try:
Code: Text  [Select][+][-]
  1. tt@debian:~$ rm databasetest
  2. tt@debian:~$ sqlite3 databasetest
  3. SQLite version 3.27.2 2019-02-25 16:06:06
  4. Enter ".help" for usage hints.
  5. sqlite> begin transaction;
  6. sqlite> create table if not exists XXXX (id integer);
  7. sqlite> .schema  //--> THIS ONE!
  8. sqlite> SELECT * FROM sqlite_master WHERE name='XXX'  //--> OR THIS ONE
  9. sqlite> PRAGMA table_info('XXX')  //--> OR THIS ONE
  10. sqlite> .q
  11.  

EDIT: But i agree with tt: The error is not making any sense. IF (NOT) EXIST should work irrespective if the Transaction has been committed or not.
Just did a quick test to CREATE TABLE IF NOT EXISTS inside a Transaction: Works without error
« Last Edit: May 16, 2022, 01:49:45 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 4949
Re: weird sqlite error "database schema has changed"
« Reply #10 on: May 16, 2022, 01:46:10 pm »
O, wow,
Why is this different on Windows ??

Code: [Select]
C:\Users\Rik\Desktop\sqlite>sqlite3 databasetest
SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
sqlite> begin transaction;
sqlite> create table if not exists XXXX (id integer);
sqlite> .schema
CREATE TABLE XXXX (id integer);
sqlite> .q

Here I do have the .schema after create.

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: weird sqlite error "database schema has changed"
« Reply #11 on: May 16, 2022, 02:16:40 pm »
Hmmm.......last post --> https://forum.lazarus.freepascal.org/index.php?topic=47824.0

@tt
Could you try to explicitely close your query-object after you "ExecSQL" each of your statements?
btw: Why are you dropping twice?

EDIT: On a sidenote: Why are you even Creating/Dropping the table?
if it's a regular occurance that you have to populate that table, then keep it permanently, just do a "DELETE FROM LWCADII" at the start of the Procedure (or at the end)
« Last Edit: May 16, 2022, 02:20:47 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 4949
Re: weird sqlite error "database schema has changed"
« Reply #12 on: May 16, 2022, 02:48:49 pm »
This works. So could you show more code from your TMEPConnection and especially TMEPConnection.QueriesCreate.

Or just simplify your code to a workable example where you still get that error.
(you can also expand on the example below until you get the error)

Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   SQLite3Conn, SQLDB;
  5.  
  6. var
  7.   conn: TSQLite3Connection;
  8.   tran: TSQLTransaction;
  9.   sql: TSQLQuery;
  10.  
  11. begin
  12.   conn := TSQLite3Connection.Create(nil);
  13.   tran := TSQLTransaction.Create(conn);
  14.  
  15.   try
  16.  
  17.     conn.Transaction := tran;
  18.     conn.DatabaseName:= 'databasetest';
  19.  
  20.     // 1. Open a connection C
  21.     conn.Open;
  22.  
  23.     // 2. Open a transaction T on C
  24.     tran.StartTransaction;
  25.  
  26.     sql := TSQLQuery.Create(conn);
  27.     sql.DataBase := conn;
  28.     sql.Transaction := tran;
  29.  
  30.     // 3. (using C and T) create the table TB to persist the result of the query
  31.     sql.SQL.Text := 'create table if not exists XXXX (id integer);';
  32.     sql.ExecSql;
  33.  
  34.     // 4. (using C and T) Execute a insert into ... select in order to populate TB
  35.     sql.SQL.Text := 'insert into XXXX values(5);';
  36.     sql.ExecSql;
  37.  
  38.     // 5. (using C and T) Execute a query to read data from TB
  39.     sql.SQL.Text := 'select * from XXXX';
  40.     sql.Open;
  41.     Writeln('This works: ', sql.FieldByName('id').AsInteger);
  42.     sql.Close;
  43.  
  44.     // 6. Drop table TB
  45.     sql.SQL.Text := 'drop table if exists XXXX;';
  46.     sql.ExecSql;
  47.  
  48.     // 7. Commit T
  49.     tran.Commit;
  50.  
  51.     // 8. Close C
  52.     sql.Close;
  53.  
  54.     writeln('Press enter');
  55.     readln;
  56.  
  57.   finally
  58.  
  59.     conn.Free;
  60.  
  61.   end;
  62.  
  63. end.

tt

  • Jr. Member
  • **
  • Posts: 79
Re: weird sqlite error "database schema has changed"
« Reply #13 on: May 16, 2022, 03:31:24 pm »
This works. So could you show more code from your TMEPConnection and especially TMEPConnection.QueriesCreate.

Or just simplify your code to a workable example where you still get that error.
(you can also expand on the example below until you get the error)

Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   SQLite3Conn, SQLDB;
  5.  
  6. var
  7.   conn: TSQLite3Connection;
  8.   tran: TSQLTransaction;
  9.   sql: TSQLQuery;
  10.  
  11. begin
  12.   conn := TSQLite3Connection.Create(nil);
  13.   tran := TSQLTransaction.Create(conn);
  14.  
  15.   try
  16.  
  17.     conn.Transaction := tran;
  18.     conn.DatabaseName:= 'databasetest';
  19.  
  20.     // 1. Open a connection C
  21.     conn.Open;
  22.  
  23.     // 2. Open a transaction T on C
  24.     tran.StartTransaction;
  25.  
  26.     sql := TSQLQuery.Create(conn);
  27.     sql.DataBase := conn;
  28.     sql.Transaction := tran;
  29.  
  30.     // 3. (using C and T) create the table TB to persist the result of the query
  31.     sql.SQL.Text := 'create table if not exists XXXX (id integer);';
  32.     sql.ExecSql;
  33.  
  34.     // 4. (using C and T) Execute a insert into ... select in order to populate TB
  35.     sql.SQL.Text := 'insert into XXXX values(5);';
  36.     sql.ExecSql;
  37.  
  38.     // 5. (using C and T) Execute a query to read data from TB
  39.     sql.SQL.Text := 'select * from XXXX';
  40.     sql.Open;
  41.     Writeln('This works: ', sql.FieldByName('id').AsInteger);
  42.     sql.Close;
  43.  
  44.     // 6. Drop table TB
  45.     sql.SQL.Text := 'drop table if exists XXXX;';
  46.     sql.ExecSql;
  47.  
  48.     // 7. Commit T
  49.     tran.Commit;
  50.  
  51.     // 8. Close C
  52.     sql.Close;
  53.  
  54.     writeln('Press enter');
  55.     readln;
  56.  
  57.   finally
  58.  
  59.     conn.Free;
  60.  
  61.   end;
  62.  
  63. end.


DO NOT LAUGH!  :)

Code: Pascal  [Select][+][-]
  1. unit MEPDatabaseIO;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils
  9.   , sqldb
  10.   , fgl;
  11.  
  12. const
  13.   DefaultAliasesFileName: string = 'MEP-dbaliases.ini';
  14.  
  15.  
  16. type
  17.  
  18.   TConnectionParams = specialize TFPGMap<string, string>;
  19.  
  20.   TQueriesMap = specialize TFPGMap<string, TSQLQuery>;
  21.  
  22.   TMEPDatabaseIOStrings = specialize TFPGList<string>; // This is used to create sets of queries easily
  23.  
  24.   { TQueries }
  25.  
  26.   TQueries = class(TObject)
  27.     public
  28.       Queries: TQueriesMap;
  29.       Transaction: TSQLTransaction;
  30.       constructor Create(); reintroduce;
  31.       destructor Destroy(); override;
  32.   end;
  33.  
  34.   TMEPDatabaseType = (
  35.       dtUnknown
  36.     , dtSQLite
  37.     , dtPostgreSQL
  38.     );
  39.  
  40. {$REGION 'MEP Connection'}
  41. type
  42.  
  43.   { TMEPConnection }
  44.  
  45.   TMEPConnection = class(TObject)
  46.     private
  47.       fAliasesFilePath: string;
  48.       fAliasName: string;
  49.       fInMemory: Boolean;
  50.       fConnectionParameters: TConnectionParams;
  51.       fConnection: TSQLConnection;
  52.       _imposeSQLiteFilename : Boolean;
  53.       _imposedSQLiteFilename : string;
  54.       procedure InnerCreate(paliasesFilePath: string; paliasName: string);
  55.  
  56.       procedure TryReloadAliasParameters();
  57.  
  58.       procedure SetAliasesFilePath(value: string);
  59.       procedure SetAliasName(value: string);
  60.       procedure SetInMemory(value: Boolean);
  61.       function GetDatabaseType: TMEPDatabaseType;
  62.     public
  63.       property AliasesFilePath: string read fAliasesFilePath write SetAliasesFilePath;
  64.       property AliasName: string read fAliasName write SetAliasName;
  65.       property InMemory: Boolean read fInMemory write SetInMemory;
  66.       property DatabaseType: TMEPDatabaseType read GetDatabaseType;
  67.  
  68.       constructor Create(); reintroduce; overload;
  69.       constructor Create(paliasName: string); reintroduce; overload;
  70.       constructor Create(paliasesFilePath: string; paliasName: string); reintroduce; overload;
  71.       destructor Destroy(); override;
  72.  
  73.       procedure AssignSQLiteParameters(basePath: string; databaseName: string);
  74.      {procedure AssignPostgresqlParameters(????);}
  75.  
  76.       procedure ReloadAliasParameters();
  77.  
  78.       procedure ConnectionCreate();
  79.       procedure ConnectionOpen();
  80.  
  81.       function TransactionCreate(): TSQLTransaction;
  82.  
  83.       function QueryCreate(transaction: TSQLTransaction): TSQLQuery; overload;
  84.       function QueryCreate(sql: string; transaction: TSQLTransaction): TSQLQuery; overload;
  85.  
  86.       procedure QueryCreate(var transaction: TSQLTransaction; var query: TSQLQuery); overload;
  87.       procedure QueryCreate(sql: string; var transaction: TSQLTransaction; var query: TSQLQuery); overload;
  88.  
  89.       function QueriesCreate(transaction: TSQLTransaction): TQueries; overload;
  90.       { using SysUtils.TStringArray explicitely because of a conflict with sqlite3conn.TStringArray }
  91.       function QueriesCreate(names: SysUtils.TStringArray; sqls: SysUtils.TStringArray; transaction: TSQLTransaction): TQueries; overload;
  92.       function QueriesCreate(names: TMEPDatabaseIOStrings; sqls: TMEPDatabaseIOStrings; transaction: TSQLTransaction): TQueries; overload;
  93.  
  94.       procedure QueriesCreate(var transaction: TSQLTransaction; var queries: TQueries); overload;
  95.       procedure QueriesCreate(names: SysUtils.TStringArray; sqls: SysUtils.TStringArray; var transaction: TSQLTransaction; var queries: TQueries); overload;
  96.       procedure QueriesCreate(names: TMEPDatabaseIOStrings; sqls: TMEPDatabaseIOStrings; var transaction: TSQLTransaction; var queries: TQueries); overload;
  97.  
  98.       function GetActualSQLiteFilename(): string;
  99.       procedure ImposeSQLiteFilename(filename: string);
  100.  
  101.       function RunBackup(targetConnection: TMEPConnection; var ErrorMessage: string): Boolean;
  102.       procedure DeleteDatabase();
  103.   end;
  104.  
  105. {$ENDREGION}
  106.  
  107.  
  108. implementation
  109.  
  110. uses
  111.  
  112.   IniFiles
  113.  
  114.   , db
  115.   , sqlite3conn
  116.   , sqlite3backup
  117.   , pqconnection
  118.   , streamex
  119.  
  120.   , MEPWorkingPaths
  121.   ;
  122.  
  123.  
  124. {$REGION TQueries}
  125.  
  126. constructor TQueries.Create();
  127. begin
  128.   inherited Create();
  129.  
  130.   Queries := TQueriesMap.Create();
  131. end;
  132.  
  133. destructor TQueries.Destroy();
  134. var
  135.   i: Integer;
  136. begin
  137.   for i := 0 to Queries.Count -1 do
  138.   begin
  139.     Queries.Data[i].Destroy();
  140.   end;
  141.   Queries.Destroy();
  142.  
  143.   inherited Destroy();
  144. end;
  145.  
  146. {$ENDREGION}
  147.  
  148. { TMEPConnection }
  149.  
  150. const
  151.  
  152.   SQLIteInMemoryDBAlias = ':memory:';
  153.  
  154. constructor TMEPConnection.Create();
  155. begin
  156.   inherited Create();
  157.  
  158.   InnerCreate(MEPWorkingPaths.DBPersistentDirectory + PathDelim + DefaultAliasesFileName, '');
  159.  
  160.   // User has not supplied the alias name, it is not possible to load anything
  161. end;
  162. constructor TMEPConnection.Create(paliasName: string);
  163. begin
  164.   inherited Create();
  165.  
  166.   InnerCreate(MEPWorkingPaths.DBPersistentDirectory + PathDelim + DefaultAliasesFileName, paliasName);
  167.  
  168.   // As user has explicitely indicated the alias name it must fail on error
  169.   ReloadAliasParameters();
  170. end;
  171. constructor TMEPConnection.Create(paliasesFilePath: string; paliasName: string);
  172. begin
  173.   inherited Create();
  174.  
  175.   InnerCreate(paliasesFilePath, paliasName);
  176.  
  177.   // As user has explicitely indicated the alias name it must fail on error
  178.   ReloadAliasParameters();
  179. end;
  180. procedure TMEPConnection.InnerCreate(paliasesFilePath: string; paliasName: string);
  181. begin
  182.   fAliasesFilePath := paliasesFilePath;
  183.   fAliasName := paliasName;
  184.   fInMemory := False;
  185.   fConnectionParameters := TConnectionParams.Create();
  186.   fConnection := nil;
  187.   _imposeSQLiteFilename := False;
  188.   _imposedSQLiteFilename := '';
  189. end;
  190.  
  191. destructor TMEPConnection.Destroy();
  192. begin
  193.   FreeAndNil(fConnectionParameters);
  194.   FreeAndNil(fConnection);
  195.  
  196.   inherited Destroy();
  197. end;
  198.  
  199. procedure TMEPConnection.SetAliasesFilePath(value: string);
  200. begin
  201.   fAliasesFilePath := value;
  202.  
  203.   // It might be not all necessary params are set
  204.   TryReloadAliasParameters();
  205. end;
  206. procedure TMEPConnection.SetAliasName(value: string);
  207. begin
  208.   fAliasName := value;
  209.  
  210.   // It might be not all necessary params are set
  211.   TryReloadAliasParameters();
  212. end;
  213.  
  214. procedure TMEPConnection.SetInMemory(value: Boolean);
  215. begin
  216.   fInMemory := value;
  217.  
  218.   // It might be not all necessary params are set
  219.   TryReloadAliasParameters();
  220. end;
  221.  
  222.  
  223. procedure TMEPConnection.AssignSQLiteParameters(basePath: string; databaseName: string);
  224. begin
  225.  
  226.   if (fInMemory) then
  227.   begin
  228.     fConnectionParameters.Clear();
  229.     fConnectionParameters.Add('DatabaseType', 'sqlite');
  230.  
  231.     // In memory db
  232.     Exit();
  233.   end;
  234.  
  235.   fConnectionParameters.Clear();
  236.  
  237.   fConnectionParameters.Add('BasePath'    , basePath    );
  238.   fConnectionParameters.Add('DatabaseName', databaseName);
  239. end;
  240. procedure TMEPConnection.ReloadAliasParameters();
  241. var
  242.   aliasesIni: TIniFile;
  243.   aliasIniLines: TStringList;
  244.   stringarray: TStringArray;
  245.   i: Integer;
  246. begin
  247.   if (fInMemory) then
  248.   begin
  249.     fConnectionParameters.Clear();
  250.     fConnectionParameters.Add('DatabaseType', 'sqlite');
  251.  
  252.     // In memory db
  253.     Exit();
  254.   end;
  255.  
  256.   fConnectionParameters.Clear();
  257.  
  258.   aliasesIni := TIniFile.Create(fAliasesFilePath);
  259.   aliasIniLines := TStringList.Create();
  260.   try
  261.     if aliasesIni.SectionExists(fAliasName) then
  262.     begin
  263.       try
  264.         aliasesIni.ReadSectionValues(fAliasName, aliasIniLines);
  265.         for i := 0 to aliasIniLines.Count - 1 do begin
  266.           stringarray := aliasIniLines[i].Split('=');
  267.           try
  268.             fConnectionParameters.Add(stringarray[0], stringarray[1]);
  269.           finally
  270.             stringarray := nil;
  271.           end;
  272.         end;
  273.       except
  274.         on E: Exception do begin
  275.           raise Exception.Create(
  276.               Format('Unexpected error while reading alises file in "%s" (Message: "%s")',
  277.               [fAliasesFilePath, E.Message]));
  278.         end;
  279.       end;
  280.     end;
  281.   finally
  282.     FreeAndNil(aliasIniLines);
  283.     FreeAndNil(aliasesIni);
  284.   end;
  285. end;
  286.  
  287.  
  288. procedure TMEPConnection.TryReloadAliasParameters();
  289. begin
  290.   try
  291.     ReloadAliasParameters();
  292.   except
  293.     // It is allowed to fail silently
  294.   end;
  295. end;
  296.  
  297. function TMEPConnection.GetDatabaseType: TMEPDatabaseType;
  298. begin
  299.   case fConnectionParameters['DatabaseType'] of
  300.   'sqlite'    :
  301.     begin
  302.       Result := TMEPDatabaseType.dtSQLite;
  303.     end;
  304.   'postgresql':
  305.     begin
  306.       Result := TMEPDatabaseType.dtPostgreSQL;
  307.     end;
  308.   else
  309.     begin
  310.       Result := TMEPDatabaseType.dtUnknown;
  311.     end;
  312.   end;
  313.  
  314. end;
  315.  
  316. procedure TMEPConnection.ConnectionCreate();
  317.  
  318.   function GetSQLConnection(): TSQLConnection;
  319.  
  320.     function GetSQLConnection_SQLite(): TSQLConnection;
  321.     const
  322.       sqlite_time_dynamic_indicator: string = '<time-dynamic>';
  323.     var
  324.       BasePath: string;
  325.       DatabaseName: string;
  326.     begin
  327.       if (fInMemory) then
  328.       begin
  329.         Result := TSQLite3Connection.Create(nil);
  330.         Result.DatabaseName := SQLIteInMemoryDBAlias;
  331.         Exit();
  332.       end;
  333.  
  334.       if (fConnectionParameters.IndexOf('BasePath') = -1) then begin
  335.         BasePath := '.';
  336.       end else
  337.       begin
  338.         BasePath := fConnectionParameters['BasePath'];
  339.       end;
  340.       if (fConnectionParameters.IndexOf('DatabaseName') = -1) then begin
  341.         DatabaseName := 'db_sqlite';
  342.       end else
  343.       begin
  344.         DatabaseName := fConnectionParameters['DatabaseName'];
  345.         if (DatabaseName.Contains(sqlite_time_dynamic_indicator)) then
  346.         begin
  347.           DatabaseName := DatabaseName.Replace(sqlite_time_dynamic_indicator, FormatDateTime('YYYYMMDDHHmmss', SysUtils.Now()));
  348.         end;
  349.       end;
  350.  
  351.       Result := TSQLite3Connection.Create(nil);
  352.       if (_imposeSQLiteFilename) then
  353.       begin
  354.         Result.DatabaseName := _imposedSQLiteFilename;
  355.       end
  356.       else begin
  357.         Result.DatabaseName := BasePath + PathDelim + DatabaseName;
  358.       end;
  359.     end;
  360.     function GetSQLConnection_PostgreqSQL(): TSQLConnection;
  361.     var
  362.       Hostname: string;
  363.       Port: string;
  364.       DatabaseName: string;
  365.       DatabaseUsername: string;
  366.       DatabasePassword: string;
  367.     begin
  368.       if (fConnectionParameters.IndexOf('Hostname')         = -1) then begin Hostname         := 'locahost';          end else begin Hostname         := fConnectionParameters['Hostname'          ]; end;
  369.       if (fConnectionParameters.IndexOf('Port')             = -1) then begin Port             := '5432';              end else begin Port             := fConnectionParameters['Port'              ]; end;
  370.       if (fConnectionParameters.IndexOf('DatabaseName')     = -1) then begin DatabaseName     := 'pg_db';             end else begin DatabaseName     := fConnectionParameters['DatabaseName'      ]; end;
  371.       if (fConnectionParameters.IndexOf('DatabaseUsername') = -1) then begin DatabaseUsername := 'pg_user';           end else begin DatabaseUsername := fConnectionParameters['DatabaseUsername'  ]; end;
  372.       if (fConnectionParameters.IndexOf('DatabasePassword') = -1) then begin DatabasePassword := 'pg_password';       end else begin DatabasePassword := fConnectionParameters['DatabasePassword'  ]; end;
  373.  
  374.       Result := TPQConnection.Create(nil);
  375.       Result.HostName := Hostname;
  376.       Result.Params.Add(Format('port=%d', [Port]));
  377.       Result.DatabaseName := DatabaseName;
  378.       Result.UserName := DatabaseUsername;
  379.       Result.Password := DatabasePassword;
  380.     end;
  381.  
  382.   begin
  383.     if (fConnectionParameters.IndexOf('DatabaseType') = -1) then begin
  384.       raise Exception.Create(Format('Parameter "DatabaseType" not found in "%s" for alias "%s"', [fAliasesFilePath, fAliasName]));
  385.     end else
  386.     begin
  387.       case fConnectionParameters['DatabaseType'] of
  388.       'sqlite'    : begin Result := GetSQLConnection_SQLite     (); end;
  389.       'postgresql': begin Result := GetSQLConnection_PostgreqSQL(); end;
  390.       else raise Exception.Create(Format('Parameter "DatabaseType" value "%s" is not known in "%s" for alias "%s"', [fConnectionParameters['DatabaseType'], fAliasesFilePath, fAliasName]));
  391.       end;
  392.     end;
  393.   end;
  394.  
  395. begin
  396.   fConnection := GetSQLConnection();
  397. end;
  398. procedure TMEPConnection.ConnectionOpen();
  399. var
  400.   transaction: TSQLTransaction;
  401.   q: TSQLQuery;
  402. begin
  403.   ConnectionCreate();
  404.  
  405.   fConnection.Connected := True;
  406.  
  407.   transaction := TransactionCreate();
  408.   try
  409.  
  410.     transaction.StartTransaction();
  411.  
  412.     try
  413.       q := QueryCreate('PRAGMA foreign_keys = ON', transaction);
  414.       try
  415.         q.ExecSQL();
  416.       finally
  417.         FreeAndNil(q);
  418.       end;
  419.  
  420.       transaction.Commit();
  421.     except
  422.       transaction.Rollback();
  423.     end;
  424.  
  425.   finally
  426.     FreeAndNil(transaction);
  427.   end;
  428.  
  429.  
  430. end;
  431.  
  432.  
  433. function TMEPConnection.TransactionCreate(): TSQLTransaction;
  434. begin
  435.   Result := TSQLTransaction.Create(nil);
  436.   Result.Options:=[stoExplicitStart];
  437.  
  438.   Result.DataBase := fConnection;
  439. end;
  440.  
  441. function TMEPConnection.QueryCreate(transaction: TSQLTransaction): TSQLQuery;
  442. begin
  443.   Result := TSQLQuery.Create(nil);
  444.  
  445.   Result.Transaction := transaction;
  446. end;
  447. function TMEPConnection.QueryCreate(sql: string; transaction: TSQLTransaction): TSQLQuery;
  448. begin
  449.   Result := QueryCreate(transaction);
  450.  
  451.   Result.SQL.Add(sql);
  452. end;
  453.  
  454. procedure TMEPConnection.QueryCreate(var transaction: TSQLTransaction; var query: TSQLQuery);
  455. begin
  456.   transaction := TransactionCreate();
  457.  
  458.   query := QueryCreate(transaction);
  459. end;
  460. procedure TMEPConnection.QueryCreate(sql:string; var transaction: TSQLTransaction; var query: TSQLQuery);
  461. begin
  462.   QueryCreate(transaction, query);
  463.  
  464.   query.SQL.Add(sql);
  465. end;
  466.  
  467. function TMEPConnection.QueriesCreate(transaction: TSQLTransaction): TQueries;
  468. begin
  469.   Result := TQueries.Create();
  470.  
  471.   Result.Transaction := transaction;
  472. end;
  473.  
  474. function TMEPConnection.QueriesCreate(names: SysUtils.TStringArray; sqls: SysUtils.TStringArray; transaction: TSQLTransaction): TQueries;
  475. var
  476.   i: Integer;
  477. begin
  478.   Result := QueriesCreate(transaction);
  479.  
  480.   for i := 0 to Length(names) - 1 do
  481.   begin
  482.     Result.Queries.Add(names[i], QueryCreate(sqls[i], Result.Transaction));
  483.   end;
  484. end;
  485.  
  486. function TMEPConnection.QueriesCreate(names: TMEPDatabaseIOStrings; sqls: TMEPDatabaseIOStrings; transaction: TSQLTransaction): TQueries;
  487. var
  488.   i: Integer;
  489. begin
  490.   Result := QueriesCreate(transaction);
  491.  
  492.   for i := 0 to names.Count - 1 do
  493.   begin
  494.     Result.Queries.Add(names[i], QueryCreate(sqls[i], Result.Transaction));
  495.   end;
  496. end;
  497.  
  498. procedure TMEPConnection.QueriesCreate(var transaction: TSQLTransaction; var queries: TQueries);
  499. begin
  500.   transaction := TransactionCreate();
  501.  
  502.   queries := QueriesCreate(transaction);
  503. end;
  504.  
  505. procedure TMEPConnection.QueriesCreate(names: SysUtils.TStringArray; sqls: SysUtils.TStringArray; var transaction: TSQLTransaction; var queries: TQueries);
  506. begin
  507.   transaction := TransactionCreate();
  508.  
  509.   QueriesCreate(names, sqls, transaction, queries);
  510. end;
  511.  
  512. procedure TMEPConnection.QueriesCreate(names: TMEPDatabaseIOStrings; sqls: TMEPDatabaseIOStrings; var transaction: TSQLTransaction; var queries: TQueries);
  513. begin
  514.   transaction := TransactionCreate();
  515.  
  516.   QueriesCreate(names, sqls, transaction, queries);
  517. end;
  518.  
  519. function TMEPConnection.GetActualSQLiteFilename(): string;
  520. begin
  521.   if (fConnection  is TSQLite3Connection) then
  522.   begin
  523.     Result := TSQLite3Connection(fConnection).DatabaseName;
  524.   end
  525.   else begin
  526.     Result := '';
  527.   end;
  528. end;
  529.  
  530. procedure TMEPConnection.ImposeSQLiteFilename(filename: string);
  531. begin
  532.   _imposeSQLiteFilename := True;
  533.   _imposedSQLiteFilename := filename;
  534. end;
  535.  
  536.  
  537. function TMEPConnection.RunBackup(targetConnection: TMEPConnection; var ErrorMessage: string): Boolean;
  538. var
  539.   backup: TSQLite3Backup;
  540. begin
  541.   backup := TSQLite3Backup.Create();
  542.   try
  543.     if (not backup.Backup(TSQLite3Connection(fConnection), TSQLite3Connection(targetConnection.fConnection), {LockUntilFinished}True)) then begin
  544.       ErrorMessage := backup.ErrorMessage;
  545.       Result := False;
  546.     end
  547.     else begin
  548.       Result := True;
  549.     end;
  550.   finally
  551.     FreeAndNil(backup);
  552.   end;
  553. end;
  554.  
  555. procedure TMEPConnection.DeleteDatabase();
  556. var
  557.   fileHandle: File;
  558. begin
  559.   if (fConnection = nil) then
  560.   begin
  561.     raise Exception.Create('Cannot delete a database without a connection');
  562.   end;
  563.  
  564.   if (fConnection.Connected) then
  565.   begin
  566.     raise Exception.Create('Cannot delete a database while a connection is opened');
  567.   end;
  568.  
  569.   System.Assign(fileHandle, fConnection.DatabaseName);
  570.   System.Erase(fileHandle);
  571. end;
  572.  
  573. {
  574. Usage:
  575.  
  576. var
  577.   connection: TMEPConnection;
  578.   transation: TSQLTransation;
  579.   queries: TQueries;
  580. begin
  581.  
  582.   conn := TMEPConnection.Create(); // or Create('dbalias1'); or Create('fullpath to db-aliases file', 'dbalias1');
  583.  
  584.   conn.AliasName := 'myalias'; //unless it was set on creation with dedicated methods
  585.   try
  586.     transation := conn.TransactionCreate();
  587.     queries := conn.QueriesCreate(['q1', 'q2'], ['select * from table1', 'select * from table2'], transaction);
  588.     try
  589.       transaction.StartTransation();
  590.       try
  591.  
  592.         queries['q1'].Open();
  593.         queries['q2'].Open();
  594.  
  595.         queries['q1'].Close();
  596.         queries['q2'].Close();
  597.  
  598.         transaction.Commit();
  599.       except
  600.         transaction.Rollback();
  601.       end;
  602.     finally
  603.       FreeAndNil(transation);
  604.     end;
  605.   finally
  606.  
  607.     FreeAndNil(conn);
  608.   end;
  609.  
  610.  
  611. end;
  612.  
  613. }
  614.  
  615. end.
  616.  


tt

  • Jr. Member
  • **
  • Posts: 79
Re: weird sqlite error "database schema has changed"
« Reply #14 on: May 16, 2022, 03:47:55 pm »
This works. So could you show more code from your TMEPConnection and especially TMEPConnection.QueriesCreate.

Or just simplify your code to a workable example where you still get that error.
(you can also expand on the example below until you get the error)

Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   SQLite3Conn, SQLDB;
  5.  
  6. var
  7.   conn: TSQLite3Connection;
  8.   tran: TSQLTransaction;
  9.   sql: TSQLQuery;
  10.  
  11. begin
  12.   conn := TSQLite3Connection.Create(nil);
  13.   tran := TSQLTransaction.Create(conn);
  14.  
  15.   try
  16.  
  17.     conn.Transaction := tran;
  18.     conn.DatabaseName:= 'databasetest';
  19.  
  20.     // 1. Open a connection C
  21.     conn.Open;
  22.  
  23.     // 2. Open a transaction T on C
  24.     tran.StartTransaction;
  25.  
  26.     sql := TSQLQuery.Create(conn);
  27.     sql.DataBase := conn;
  28.     sql.Transaction := tran;
  29.  
  30.     // 3. (using C and T) create the table TB to persist the result of the query
  31.     sql.SQL.Text := 'create table if not exists XXXX (id integer);';
  32.     sql.ExecSql;
  33.  
  34.     // 4. (using C and T) Execute a insert into ... select in order to populate TB
  35.     sql.SQL.Text := 'insert into XXXX values(5);';
  36.     sql.ExecSql;
  37.  
  38.     // 5. (using C and T) Execute a query to read data from TB
  39.     sql.SQL.Text := 'select * from XXXX';
  40.     sql.Open;
  41.     Writeln('This works: ', sql.FieldByName('id').AsInteger);
  42.     sql.Close;
  43.  
  44.     // 6. Drop table TB
  45.     sql.SQL.Text := 'drop table if exists XXXX;';
  46.     sql.ExecSql;
  47.  
  48.     // 7. Commit T
  49.     tran.Commit;
  50.  
  51.     // 8. Close C
  52.     sql.Close;
  53.  
  54.     writeln('Press enter');
  55.     readln;
  56.  
  57.   finally
  58.  
  59.     conn.Free;
  60.  
  61.   end;
  62.  
  63. end.

Anyway yes, I rewrite the test similarly to what you have done, but using the small libraries I use to see if I can obtain a version that does not trigger the error when using the "IF (NOT) EXISTS".
« Last Edit: May 16, 2022, 04:14:43 pm by tt »

 

TinyPortal © 2005-2018