Forum > Databases

weird sqlite error "database schema has changed"

<< < (3/4) > >>

rvk:
O, wow,
Why is this different on Windows ??


--- Code: ---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
--- End code ---

Here I do have the .schema after create.

Zvoni:
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:
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.

tt:

--- Quote from: rvk 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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.
--- End quote ---


DO NOT LAUGH!  :)


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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. 

tt:

--- Quote from: rvk 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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.
--- End quote ---

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".

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version