weird sqlite error "database schema has changed"
lazarus / 16-Databases

----------------------------------------------------------------------
Чебурашка · 2022-05-16 08:28

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
LoadWorkCyclesAndDiametersInInterval(  logger: TLogger;  aliasName:
string;  machineId: Integer;  startDate: TDateTime;  endDate:
TDateTime;  workCyclesAndDiametersInIntervalDiameters: TObjectList; 
workCyclesAndDiametersInIntervalWorkCycles: TObjectList; 
workCyclesAndDiametersInInterval: TObjectList;  out errorMessage:
string): Boolean;   procedure
DoLoadWorkCyclesAndDiametersInInterval(connection: TMEPConnection;
transaction: TSQLTransaction);  var    names, sqls:
TMEPDatabaseIOStrings;    queries: TQueries;    q: TSQLQuery;   
summary_item: TWorkCycleAndDiameter;    workcycle: TWorkCycle;   
diameter: TDiameter;  begin    names :=
TMEPDatabaseIOStrings.Create();    names.Add('ctt');   
names.Add('dtt');    names.Add('ttp');    names.Add('workcycles');   
names.Add('diameters');    names.Add('summary');     sqls :=
TMEPDatabaseIOStrings.Create();    sqls.Add('create table if not
exists LWCADII'             + ' ('             + '    customerNo
varchar(255),'             + '    scheduleNo varchar(255),'          
 + '    jobOrderNo varchar(255),'             + '    min_start_date
datetime, '             + '    max_end_date datetime'             + '
)');    sqls.Add('drop table if exists LWCADII');     sqls.Add('
insert into LWCADII (customerNo, scheduleNo, jobOrderNo,
min_start_date, max_end_date)'             + '  select          '    
       + '    customerNo    '             + '  , scheduleNo    '     
      + '  , jobOrderNo    '             + '  , min(startDate)' // it
might happen by mistake that same c/s/j is used for multiple diameter 
           + '  , max(endDate)  '             + ' from '             +
DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_Production
Batches].Name             + ' where'             + ' refMachine =
:refMachine'             + '   and (:startDate <= startDate)'        
   + '   and (endDate < :endDate)'             + ' group by
customerNo, scheduleNo, jobOrderNo'             + ' order by
startDate, customerNo, scheduleNo, jobOrderNo');     sqls.Add('select
            '             + '  customerNo      '             + ',
scheduleNo      '             + ', jobOrderNo      '             + ',
min_start_date  ' // it might happen by mistake that same c/s/j is
used for multiple diameter             + ', max_end_date    '        
   + ' from LWCADII');     sqls.Add('select distinct '             + '
 materialDiameter                                         '          
 + ' from '             + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103
_ReportingCache_ProductionBatches].Name             + ' where'       
    + ' refMachine = :refMachine'             + '   and (:startDate <=
startDate)'             + '   and (endDate < :endDate)'             +
' order by materialDiameter');     sqls.Add('select '             + '
 customerNo                                                        ' 
          + ', scheduleNo                                            
          '             + ', jobOrderNo                              
                        '             + ', materialDiameter          
                                      '             + ',
sum(totalPiecesExpected)                 as actual_expected_count '  
         + ', sum(totalPiecesProduced)                 as
actual_produced_count '             + ', sum(totalWeight)            
           as actual_weight         '             + ',
sum(totalEnergyWh)                       as total_energy_Wh       '  
         + ', sum(totalElapsedSeconds)                 as
total_elapsed_seconds '             + ', sum(totalPiecesProduced *
productLength) as total_length_mm       '             + ' from '     
      + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_Pr
oductionBatches].Name             + ' where'             + '
refMachine = :refMachine'             + '   and (:startDate <=
startDate)'             + '   and (endDate < :endDate)'             +
' group by customerNo, scheduleNo, jobOrderNo, materialDiameter'     
      + ' order by customerNo, scheduleNo, jobOrderNo,
materialDiameter');     queries := connection.QueriesCreate(names,
sqls, transaction);    try      try        q :=
queries.Queries['dtt']; // make sure table is dropped       
q.ExecSQL();      except        // if did not exist then ok     
end;       q := queries.Queries['ctt']; // make sure table is created 
    q.ExecSQL();       q := queries.Queries['ttp']; // populated
temporary table      q.ParamByName('refMachine').AsInteger :=
machineId;      q.ParamByName('startDate').AsDateTime := startDate;  
  q.ParamByName('endDate').AsDateTime   := endDate  ;     
q.ExecSQL();       {%REGION 'Load workcycles'}      q :=
queries.Queries['workcycles'];      q.Open();      while(not q.EOF)
do      begin        workcycle := TWorkCycle.Create(                 
     q.FieldByName('customerNo'      ).AsString                      ,
q.FieldByName('scheduleNo'      ).AsString                      ,
q.FieldByName('jobOrderNo'      ).AsString                      ,
q.FieldByName('min_start_date'  ).AsDateTime                      ,
q.FieldByName('max_end_date'    ).AsDateTime);       
workCyclesAndDiametersInIntervalWorkCycles.Add(workcycle);        
q.Next();      end;      q.Close();      {%ENDREGION}       {%REGION
'Load diameters'}      q := queries.Queries['diameters'];     
q.ParamByName('refMachine').AsInteger := machineId;     
q.ParamByName('startDate').AsDateTime := startDate;     
q.ParamByName('endDate').AsDateTime   := endDate  ;      q.Open();   
 while(not q.EOF) do      begin        diameter :=
TDiameter.Create(q.FieldByName('materialDiameter').AsFloat);       
workCyclesAndDiametersInIntervalDiameters.Add(diameter);        
q.Next();      end;      q.Close();      {%ENDREGION}       {%REGION
'Load summary'}      q := queries.Queries['summary'];     
q.ParamByName('refMachine').AsInteger := machineId;     
q.ParamByName('startDate').AsDateTime := startDate;     
q.ParamByName('endDate').AsDateTime   := endDate  ;      q.Open();   
 while(not q.EOF) do      begin        summary_item :=
TWorkCycleAndDiameter.Create(                           
q.FieldByName('customerNo').AsString                          ,
q.FieldByName('scheduleNo').AsString                          ,
q.FieldByName('jobOrderNo').AsString                          ,
q.FieldByName('materialDiameter').AsFloat                          ,
q.FieldByName('actual_produced_count'  ).AsInteger                   
     , q.FieldByName('actual_weight'          ).AsFloat              
          , q.FieldByName('total_energy_Wh'        ).AsFloat         
               , q.FieldByName('total_length_mm'        ).AsFloat /
1000                          , q.FieldByName('total_elapsed_seconds'
 ).AsFloat);       
workCyclesAndDiametersInInterval.Add(summary_item);         q.Next(); 
    end;      q.Close();      {%ENDREGION}       q :=
queries.Queries['dtt']; // make sure table is deleted befor exit     
q.ExecSQL();    finally      FreeAndNil(names);     
FreeAndNil(sqls);      FreeAndNil(queries);    end;  end; var 
connection: TMEPConnection;  transaction: TSQLTransaction;begin 
Result := False;  errorMessage := '';   connection :=
TMEPConnection.Create();  connection.AliasName := aliasName;  try   
connection.ConnectionOpen();    transaction :=
connection.TransactionCreate();    try     
transaction.StartTransaction();      try        
DoLoadWorkCyclesAndDiametersInInterval(connection, transaction);     
  transaction.Commit();         Result := True;      except        on
E: Exception do        begin          transaction.Rollback();        
 errorMessage := logger.LogExceptionErrorS(E, 'Unexpected error while
loading work cycles and diameters in interval');        end;     
end;    finally      FreeAndNil(transaction);    end;  finally   
FreeAndNil(connection);  end;end;

----------------------------------------------------------------------
rvk · 2022-05-16 08:59

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 · 2022-05-16 09:58

Quote from: rvk on May 16, 2022, 10:59:08 amI'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

----------------------------------------------------------------------
Чебурашка · 2022-05-16 10:24

Quote from: Zvoni on May 16, 2022, 11:58:32 amQuote from: rvk on May
16, 2022, 10:59:08 amI'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 · 2022-05-16 10:45

Just found something else: Do you use prepared Statements?
https://stackoverflow.com/questions/41347346/causes-of-sqlite-general-
error-17-database-schema-has-changed
QuoteAttempting 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

----------------------------------------------------------------------
rvk · 2022-05-16 11:16

Quote from: Zvoni on May 16, 2022, 12:45:40 pmEDIT: 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.

----------------------------------------------------------------------
Чебурашка · 2022-05-16 11:25

Quote from: Zvoni on May 16, 2022, 12:45:40 pmJust found something
else: Do you use prepared Statements?
https://stackoverflow.com/questions/41347346/causes-of-sqlite-general-
error-17-database-schema-has-changed
QuoteAttempting 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     queries
:= connection.QueriesCreate(names, sqls, transaction); 
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.

----------------------------------------------------------------------
Чебурашка · 2022-05-16 11:35

Quote from: rvk on May 16, 2022, 01:16:03 pmQuote from: Zvoni on May
16, 2022, 12:45:40 pmEDIT: 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite
version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage
hints.sqlite> begin transaction;sqlite> create table if not exists
XXXX (id integer);sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite
version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage
hints.sqlite> .schemasqlite> .q   tt@debian:~$ rm databasetest
tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25
16:06:06Enter ".help" for usage hints.sqlite> begin
transaction;sqlite> create table if not exists XXXX (id
integer);sqlite> commit;sqlite> .qtt@debian:~$ sqlite3
databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for
usage hints.sqlite> .schemaCREATE TABLE XXXX (id integer);sqlite> .q 
As you can see, if I start the transaction, unless I do the commit
before exiting, table is not created.

----------------------------------------------------------------------
rvk · 2022-05-16 11:40

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 · 2022-05-16 11:42

Quote from: tt on May 16, 2022, 01:35:53 pmThank you rvk.
I have tried doing that before writing the post, from within the
sqlite3 client:


Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite
version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage
hints.sqlite> begin transaction;sqlite> create table if not exists
XXXX (id integer);sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite
version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage
hints.sqlite> .schemasqlite> .q   tt@debian:~$ rm databasetest
tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25
16:06:06Enter ".help" for usage hints.sqlite> begin
transaction;sqlite> create table if not exists XXXX (id
integer);sqlite> commit;sqlite> .qtt@debian:~$ sqlite3
databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for
usage hints.sqlite> .schemaCREATE TABLE XXXX (id integer);sqlite> .q 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite
version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage
hints.sqlite> begin transaction;sqlite> create table if not exists
XXXX (id integer);sqlite> .schema  //--> THIS ONE!sqlite> SELECT *
FROM sqlite_master WHERE name='XXX'  //--> OR THIS ONEsqlite> PRAGMA
table_info('XXX')  //--> OR THIS ONEsqlite> .q 
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

----------------------------------------------------------------------
rvk · 2022-05-16 11:46

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 · 2022-05-16 12:16

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)

----------------------------------------------------------------------
rvk · 2022-05-16 12:48

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  SQLite3Conn, SQLDB; var  conn: TSQLite3Connection; 
tran: TSQLTransaction;  sql: TSQLQuery; begin  conn :=
TSQLite3Connection.Create(nil);  tran :=
TSQLTransaction.Create(conn);   try     conn.Transaction := tran;   
conn.DatabaseName:= 'databasetest';     // 1. Open a connection C   
conn.Open;     // 2. Open a transaction T on C   
tran.StartTransaction;     sql := TSQLQuery.Create(conn);   
sql.DataBase := conn;    sql.Transaction := tran;     // 3. (using C
and T) create the table TB to persist the result of the query   
sql.SQL.Text := 'create table if not exists XXXX (id integer);';   
sql.ExecSql;     // 4. (using C and T) Execute a insert into ...
select in order to populate TB    sql.SQL.Text := 'insert into XXXX
values(5);';    sql.ExecSql;     // 5. (using C and T) Execute a query
to read data from TB    sql.SQL.Text := 'select * from XXXX';   
sql.Open;    Writeln('This works: ',
sql.FieldByName('id').AsInteger);    sql.Close;     // 6. Drop table
TB    sql.SQL.Text := 'drop table if exists XXXX;';    sql.ExecSql;  
 // 7. Commit T    tran.Commit;     // 8. Close C    sql.Close;    
writeln('Press enter');    readln;   finally     conn.Free;  
end; end.

----------------------------------------------------------------------
Чебурашка · 2022-05-16 13:31

Quote from: rvk on May 16, 2022, 02:48:49 pmThis 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  SQLite3Conn, SQLDB; var  conn: TSQLite3Connection; 
tran: TSQLTransaction;  sql: TSQLQuery; begin  conn :=
TSQLite3Connection.Create(nil);  tran :=
TSQLTransaction.Create(conn);   try     conn.Transaction := tran;   
conn.DatabaseName:= 'databasetest';     // 1. Open a connection C   
conn.Open;     // 2. Open a transaction T on C   
tran.StartTransaction;     sql := TSQLQuery.Create(conn);   
sql.DataBase := conn;    sql.Transaction := tran;     // 3. (using C
and T) create the table TB to persist the result of the query   
sql.SQL.Text := 'create table if not exists XXXX (id integer);';   
sql.ExecSql;     // 4. (using C and T) Execute a insert into ...
select in order to populate TB    sql.SQL.Text := 'insert into XXXX
values(5);';    sql.ExecSql;     // 5. (using C and T) Execute a query
to read data from TB    sql.SQL.Text := 'select * from XXXX';   
sql.Open;    Writeln('This works: ',
sql.FieldByName('id').AsInteger);    sql.Close;     // 6. Drop table
TB    sql.SQL.Text := 'drop table if exists XXXX;';    sql.ExecSql;  
 // 7. Commit T    tran.Commit;     // 8. Close C    sql.Close;    
writeln('Press enter');    readln;   finally     conn.Free;  
end; end.

DO NOT LAUGH! 

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 unit
MEPDatabaseIO; {$mode objfpc}{$H+} interface uses  Classes, SysUtils 
, sqldb  , fgl; const  DefaultAliasesFileName: string = 'MEP-
dbaliases.ini';  type   TConnectionParams = specialize TFPGMap<string,
string>;   TQueriesMap = specialize TFPGMap<string, TSQLQuery>;  
TMEPDatabaseIOStrings = specialize TFPGList<string>; // This is used
to create sets of queries easily   { TQueries }   TQueries =
class(TObject)    public      Queries: TQueriesMap;      Transaction:
TSQLTransaction;      constructor Create(); reintroduce;     
destructor Destroy(); override;  end;   TMEPDatabaseType = (     
dtUnknown    , dtSQLite    , dtPostgreSQL    ); {$REGION 'MEP
Connection'}type   { TMEPConnection }   TMEPConnection =
class(TObject)    private      fAliasesFilePath: string;     
fAliasName: string;      fInMemory: Boolean;     
fConnectionParameters: TConnectionParams;      fConnection:
TSQLConnection;      _imposeSQLiteFilename : Boolean;     
_imposedSQLiteFilename : string;      procedure
InnerCreate(paliasesFilePath: string; paliasName: string);      
procedure TryReloadAliasParameters();       procedure
SetAliasesFilePath(value: string);      procedure SetAliasName(value:
string);      procedure SetInMemory(value: Boolean);      function
GetDatabaseType: TMEPDatabaseType;    public      property
AliasesFilePath: string read fAliasesFilePath write
SetAliasesFilePath;      property AliasName: string read fAliasName
write SetAliasName;      property InMemory: Boolean read fInMemory
write SetInMemory;      property DatabaseType: TMEPDatabaseType read
GetDatabaseType;       constructor Create(); reintroduce; overload;  
  constructor Create(paliasName: string); reintroduce; overload;     
constructor Create(paliasesFilePath: string; paliasName: string);
reintroduce; overload;      destructor Destroy(); override;      
procedure AssignSQLiteParameters(basePath: string; databaseName:
string);     {procedure AssignPostgresqlParameters(????);}      
procedure ReloadAliasParameters();       procedure
ConnectionCreate();      procedure ConnectionOpen();       function
TransactionCreate(): TSQLTransaction;       function
QueryCreate(transaction: TSQLTransaction): TSQLQuery; overload;     
function QueryCreate(sql: string; transaction: TSQLTransaction):
TSQLQuery; overload;       procedure QueryCreate(var transaction:
TSQLTransaction; var query: TSQLQuery); overload;      procedure
QueryCreate(sql: string; var transaction: TSQLTransaction; var query:
TSQLQuery); overload;       function QueriesCreate(transaction:
TSQLTransaction): TQueries; overload;      { using
SysUtils.TStringArray explicitely because of a conflict with
sqlite3conn.TStringArray }      function QueriesCreate(names:
SysUtils.TStringArray; sqls: SysUtils.TStringArray; transaction:
TSQLTransaction): TQueries; overload;      function
QueriesCreate(names: TMEPDatabaseIOStrings; sqls:
TMEPDatabaseIOStrings; transaction: TSQLTransaction): TQueries;
overload;       procedure QueriesCreate(var transaction:
TSQLTransaction; var queries: TQueries); overload;      procedure
QueriesCreate(names: SysUtils.TStringArray; sqls:
SysUtils.TStringArray; var transaction: TSQLTransaction; var queries:
TQueries); overload;      procedure QueriesCreate(names:
TMEPDatabaseIOStrings; sqls: TMEPDatabaseIOStrings; var transaction:
TSQLTransaction; var queries: TQueries); overload;       function
GetActualSQLiteFilename(): string;      procedure
ImposeSQLiteFilename(filename: string);       function
RunBackup(targetConnection: TMEPConnection; var ErrorMessage: string):
Boolean;      procedure DeleteDatabase(); 
end; {$ENDREGION}  implementation uses   IniFiles   , db  ,
sqlite3conn  , sqlite3backup  , pqconnection  , streamex   ,
MEPWorkingPaths  ;  {$REGION TQueries} constructor
TQueries.Create();begin  inherited Create();   Queries :=
TQueriesMap.Create();end; destructor TQueries.Destroy();var  i:
Integer;begin  for i := 0 to Queries.Count -1 do  begin   
Queries.Data[i].Destroy();  end;  Queries.Destroy();   inherited
Destroy();end; {$ENDREGION} { TMEPConnection } const  
SQLIteInMemoryDBAlias = ':memory:'; constructor
TMEPConnection.Create();begin  inherited Create();  
InnerCreate(MEPWorkingPaths.DBPersistentDirectory + PathDelim +
DefaultAliasesFileName, '');   // User has not supplied the alias
name, it is not possible to load anythingend;constructor
TMEPConnection.Create(paliasName: string);begin  inherited Create();  
InnerCreate(MEPWorkingPaths.DBPersistentDirectory + PathDelim +
DefaultAliasesFileName, paliasName);   // As user has explicitely
indicated the alias name it must fail on error 
ReloadAliasParameters();end;constructor
TMEPConnection.Create(paliasesFilePath: string; paliasName:
string);begin  inherited Create();   InnerCreate(paliasesFilePath,
paliasName);   // As user has explicitely indicated the alias name it
must fail on error  ReloadAliasParameters();end;procedure
TMEPConnection.InnerCreate(paliasesFilePath: string; paliasName:
string);begin  fAliasesFilePath := paliasesFilePath;  fAliasName :=
paliasName;  fInMemory := False;  fConnectionParameters :=
TConnectionParams.Create();  fConnection := nil; 
_imposeSQLiteFilename := False;  _imposedSQLiteFilename :=
'';end; destructor TMEPConnection.Destroy();begin 
FreeAndNil(fConnectionParameters);  FreeAndNil(fConnection);  
inherited Destroy();end; procedure
TMEPConnection.SetAliasesFilePath(value: string);begin 
fAliasesFilePath := value;   // It might be not all necessary params
are set  TryReloadAliasParameters();end;procedure
TMEPConnection.SetAliasName(value: string);begin  fAliasName :=
value;   // It might be not all necessary params are set 
TryReloadAliasParameters();end; procedure
TMEPConnection.SetInMemory(value: Boolean);begin  fInMemory :=
value;   // It might be not all necessary params are set 
TryReloadAliasParameters();end;  procedure
TMEPConnection.AssignSQLiteParameters(basePath: string; databaseName:
string);begin   if (fInMemory) then  begin   
fConnectionParameters.Clear();   
fConnectionParameters.Add('DatabaseType', 'sqlite');     // In memory
db    Exit();  end;   fConnectionParameters.Clear();  
fConnectionParameters.Add('BasePath'    , basePath    ); 
fConnectionParameters.Add('DatabaseName', databaseName);end;procedure
TMEPConnection.ReloadAliasParameters();var  aliasesIni: TIniFile; 
aliasIniLines: TStringList;  stringarray: TStringArray;  i:
Integer;begin  if (fInMemory) then  begin   
fConnectionParameters.Clear();   
fConnectionParameters.Add('DatabaseType', 'sqlite');     // In memory
db    Exit();  end;   fConnectionParameters.Clear();   aliasesIni :=
TIniFile.Create(fAliasesFilePath);  aliasIniLines :=
TStringList.Create();  try    if aliasesIni.SectionExists(fAliasName)
then    begin      try        aliasesIni.ReadSectionValues(fAliasName,
aliasIniLines);        for i := 0 to aliasIniLines.Count - 1 do begin 
        stringarray := aliasIniLines[i].Split('=');          try     
     fConnectionParameters.Add(stringarray[0], stringarray[1]);      
  finally            stringarray := nil;          end;        end;   
 except        on E: Exception do begin          raise
Exception.Create(              Format('Unexpected error while reading
alises file in "%s" (Message: "%s")',              [fAliasesFilePath,
E.Message]));        end;      end;    end;  finally   
FreeAndNil(aliasIniLines);    FreeAndNil(aliasesIni); 
end;end;  procedure TMEPConnection.TryReloadAliasParameters();begin 
try    ReloadAliasParameters();  except    // It is allowed to fail
silently  end;end; function TMEPConnection.GetDatabaseType:
TMEPDatabaseType;begin  case fConnectionParameters['DatabaseType'] of 
'sqlite'    :    begin      Result := TMEPDatabaseType.dtSQLite;   
end;  'postgresql':    begin      Result :=
TMEPDatabaseType.dtPostgreSQL;    end;  else    begin      Result :=
TMEPDatabaseType.dtUnknown;    end;  end; end; procedure
TMEPConnection.ConnectionCreate();   function GetSQLConnection():
TSQLConnection;     function GetSQLConnection_SQLite():
TSQLConnection;    const      sqlite_time_dynamic_indicator: string =
'<time-dynamic>';    var      BasePath: string;      DatabaseName:
string;    begin      if (fInMemory) then      begin        Result :=
TSQLite3Connection.Create(nil);        Result.DatabaseName :=
SQLIteInMemoryDBAlias;        Exit();      end;       if
(fConnectionParameters.IndexOf('BasePath') = -1) then begin       
BasePath := '.';      end else      begin        BasePath :=
fConnectionParameters['BasePath'];      end;      if
(fConnectionParameters.IndexOf('DatabaseName') = -1) then begin      
DatabaseName := 'db_sqlite';      end else      begin       
DatabaseName := fConnectionParameters['DatabaseName'];        if
(DatabaseName.Contains(sqlite_time_dynamic_indicator)) then       
begin          DatabaseName :=
DatabaseName.Replace(sqlite_time_dynamic_indicator,
FormatDateTime('YYYYMMDDHHmmss', SysUtils.Now()));        end;     
end;       Result := TSQLite3Connection.Create(nil);      if
(_imposeSQLiteFilename) then      begin        Result.DatabaseName :=
_imposedSQLiteFilename;      end      else begin       
Result.DatabaseName := BasePath + PathDelim + DatabaseName;      end; 
  end;    function GetSQLConnection_PostgreqSQL(): TSQLConnection;   
var      Hostname: string;      Port: string;      DatabaseName:
string;      DatabaseUsername: string;      DatabasePassword: string; 
  begin      if (fConnectionParameters.IndexOf('Hostname')         =
-1) then begin Hostname         := 'locahost';          end else begin
Hostname         := fConnectionParameters['Hostname'          ]; end; 
    if (fConnectionParameters.IndexOf('Port')             = -1) then
begin Port             := '5432';              end else begin Port   
        := fConnectionParameters['Port'              ]; end;      if
(fConnectionParameters.IndexOf('DatabaseName')     = -1) then begin
DatabaseName     := 'pg_db';             end else begin DatabaseName 
  := fConnectionParameters['DatabaseName'      ]; end;      if
(fConnectionParameters.IndexOf('DatabaseUsername') = -1) then begin
DatabaseUsername := 'pg_user';           end else begin
DatabaseUsername := fConnectionParameters['DatabaseUsername'  ]; end; 
    if (fConnectionParameters.IndexOf('DatabasePassword') = -1) then
begin DatabasePassword := 'pg_password';       end else begin
DatabasePassword := fConnectionParameters['DatabasePassword'  ];
end;       Result := TPQConnection.Create(nil);      Result.HostName
:= Hostname;      Result.Params.Add(Format('port=%d', [Port]));     
Result.DatabaseName := DatabaseName;      Result.UserName :=
DatabaseUsername;      Result.Password := DatabasePassword;    end;  
begin    if (fConnectionParameters.IndexOf('DatabaseType') = -1) then
begin      raise Exception.Create(Format('Parameter "DatabaseType" not
found in "%s" for alias "%s"', [fAliasesFilePath, fAliasName]));   
end else    begin      case fConnectionParameters['DatabaseType'] of 
   'sqlite'    : begin Result := GetSQLConnection_SQLite     (); end; 
    'postgresql': begin Result := GetSQLConnection_PostgreqSQL();
end;      else raise Exception.Create(Format('Parameter "DatabaseType"
value "%s" is not known in "%s" for alias "%s"',
[fConnectionParameters['DatabaseType'], fAliasesFilePath,
fAliasName]));      end;    end;  end; begin  fConnection :=
GetSQLConnection();end;procedure TMEPConnection.ConnectionOpen();var 
transaction: TSQLTransaction;  q: TSQLQuery;begin 
ConnectionCreate();   fConnection.Connected := True;   transaction :=
TransactionCreate();  try     transaction.StartTransaction();     try 
    q := QueryCreate('PRAGMA foreign_keys = ON', transaction);     
try        q.ExecSQL();      finally        FreeAndNil(q);      end;  
    transaction.Commit();    except      transaction.Rollback();   
end;   finally    FreeAndNil(transaction);  end;  end;  function
TMEPConnection.TransactionCreate(): TSQLTransaction;begin  Result :=
TSQLTransaction.Create(nil);  Result.Options:=[stoExplicitStart];  
Result.DataBase := fConnection;end; function
TMEPConnection.QueryCreate(transaction: TSQLTransaction):
TSQLQuery;begin  Result := TSQLQuery.Create(nil);   Result.Transaction
:= transaction;end;function TMEPConnection.QueryCreate(sql: string;
transaction: TSQLTransaction): TSQLQuery;begin  Result :=
QueryCreate(transaction);   Result.SQL.Add(sql);end; procedure
TMEPConnection.QueryCreate(var transaction: TSQLTransaction; var
query: TSQLQuery);begin  transaction := TransactionCreate();   query
:= QueryCreate(transaction);end;procedure
TMEPConnection.QueryCreate(sql:string; var transaction:
TSQLTransaction; var query: TSQLQuery);begin  QueryCreate(transaction,
query);   query.SQL.Add(sql);end; function
TMEPConnection.QueriesCreate(transaction: TSQLTransaction):
TQueries;begin  Result := TQueries.Create();   Result.Transaction :=
transaction;end; function TMEPConnection.QueriesCreate(names:
SysUtils.TStringArray; sqls: SysUtils.TStringArray; transaction:
TSQLTransaction): TQueries;var  i: Integer;begin  Result :=
QueriesCreate(transaction);   for i := 0 to Length(names) - 1 do 
begin    Result.Queries.Add(names[i], QueryCreate(sqls[i],
Result.Transaction));  end;end; function
TMEPConnection.QueriesCreate(names: TMEPDatabaseIOStrings; sqls:
TMEPDatabaseIOStrings; transaction: TSQLTransaction): TQueries;var  i:
Integer;begin  Result := QueriesCreate(transaction);   for i := 0 to
names.Count - 1 do  begin    Result.Queries.Add(names[i],
QueryCreate(sqls[i], Result.Transaction));  end;end; procedure
TMEPConnection.QueriesCreate(var transaction: TSQLTransaction; var
queries: TQueries);begin  transaction := TransactionCreate();  
queries := QueriesCreate(transaction);end; procedure
TMEPConnection.QueriesCreate(names: SysUtils.TStringArray; sqls:
SysUtils.TStringArray; var transaction: TSQLTransaction; var queries:
TQueries);begin  transaction := TransactionCreate();  
QueriesCreate(names, sqls, transaction, queries);end; procedure
TMEPConnection.QueriesCreate(names: TMEPDatabaseIOStrings; sqls:
TMEPDatabaseIOStrings; var transaction: TSQLTransaction; var queries:
TQueries);begin  transaction := TransactionCreate();  
QueriesCreate(names, sqls, transaction, queries);end; function
TMEPConnection.GetActualSQLiteFilename(): string;begin  if
(fConnection  is TSQLite3Connection) then  begin    Result :=
TSQLite3Connection(fConnection).DatabaseName;  end  else begin   
Result := '';  end;end; procedure
TMEPConnection.ImposeSQLiteFilename(filename: string);begin 
_imposeSQLiteFilename := True;  _imposedSQLiteFilename :=
filename;end;  function TMEPConnection.RunBackup(targetConnection:
TMEPConnection; var ErrorMessage: string): Boolean;var  backup:
TSQLite3Backup;begin  backup := TSQLite3Backup.Create();  try    if
(not backup.Backup(TSQLite3Connection(fConnection),
TSQLite3Connection(targetConnection.fConnection),
{LockUntilFinished}True)) then begin      ErrorMessage :=
backup.ErrorMessage;      Result := False;    end    else begin     
Result := True;    end;  finally    FreeAndNil(backup); 
end;end; procedure TMEPConnection.DeleteDatabase();var  fileHandle:
File;begin  if (fConnection = nil) then  begin    raise
Exception.Create('Cannot delete a database without a connection'); 
end;   if (fConnection.Connected) then  begin    raise
Exception.Create('Cannot delete a database while a connection is
opened');  end;   System.Assign(fileHandle,
fConnection.DatabaseName);  System.Erase(fileHandle);end; {Usage: var 
connection: TMEPConnection;  transation: TSQLTransation;  queries:
TQueries;begin   conn := TMEPConnection.Create(); // or
Create('dbalias1'); or Create('fullpath to db-aliases file',
'dbalias1');   conn.AliasName := 'myalias'; //unless it was set on
creation with dedicated methods  try    transation :=
conn.TransactionCreate();    queries := conn.QueriesCreate(['q1',
'q2'], ['select * from table1', 'select * from table2'],
transaction);    try      transaction.StartTransation();      try    
   queries['q1'].Open();        queries['q2'].Open();        
queries['q1'].Close();        queries['q2'].Close();        
transaction.Commit();      except        transaction.Rollback();     
end;    finally      FreeAndNil(transation);    end;  finally    
FreeAndNil(conn);  end;  end; } end.

----------------------------------------------------------------------
Чебурашка · 2022-05-16 13:47

Quote from: rvk on May 16, 2022, 02:48:49 pmThis 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  SQLite3Conn, SQLDB; var  conn: TSQLite3Connection; 
tran: TSQLTransaction;  sql: TSQLQuery; begin  conn :=
TSQLite3Connection.Create(nil);  tran :=
TSQLTransaction.Create(conn);   try     conn.Transaction := tran;   
conn.DatabaseName:= 'databasetest';     // 1. Open a connection C   
conn.Open;     // 2. Open a transaction T on C   
tran.StartTransaction;     sql := TSQLQuery.Create(conn);   
sql.DataBase := conn;    sql.Transaction := tran;     // 3. (using C
and T) create the table TB to persist the result of the query   
sql.SQL.Text := 'create table if not exists XXXX (id integer);';   
sql.ExecSql;     // 4. (using C and T) Execute a insert into ...
select in order to populate TB    sql.SQL.Text := 'insert into XXXX
values(5);';    sql.ExecSql;     // 5. (using C and T) Execute a query
to read data from TB    sql.SQL.Text := 'select * from XXXX';   
sql.Open;    Writeln('This works: ',
sql.FieldByName('id').AsInteger);    sql.Close;     // 6. Drop table
TB    sql.SQL.Text := 'drop table if exists XXXX;';    sql.ExecSql;  
 // 7. Commit T    tran.Commit;     // 8. Close C    sql.Close;    
writeln('Press enter');    readln;   finally     conn.Free;  
end; 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".

----------------------------------------------------------------------
Zvoni · 2022-05-16 14:24

reg. your code above
IIRC, you shouldn't call "Destroy" directly, but use the "Free"-Method
(and i see some "FreeAndNil"'s in your code)