Recent

Author Topic: Weird TSQLite3Connection problem  (Read 4793 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Weird TSQLite3Connection problem
« on: January 29, 2016, 06:01:31 pm »
My program uses a datamodule which contains the connection, the transaction and all queries and in the datamodule oncreate procedure I have the following code:

Code: Pascal  [Select]
  1. procedure TDataModule1.DataModuleCreate(Sender: TObject);
  2. begin
  3.   // Open database
  4.   DbConnection.Connected := False;
  5.   DbConnection.DatabaseName := SysUtils.ExtractFilePath(ParamStr(0)) + 'GliderMaintenance.db';
  6.   DbConnection.Connected := True;
  7.  
  8.   // Open all tables
  9.   qryCertificatenLog.Active := True;
  10.  


Running my program in the Lazarus IDE and running the program in the development folder gives no problem but as soon as I copy the program, the database and the SQLite dll to a different folder and run it there it give an error message "dbconnection: Unable to close due to unfinalized statements or unfinished backups" (see attached screenshot).
This error occurs when executing the statement in line 9.
The weird thing is that the error says that dbconnection wants to close whereas nowhere there is a close statement. It should stay connected and the query should be activated.
What am I doing wrong? Why does it run in its source folder and give an error when running elsewhere?
Any advise would be more than welcome.
(running Lazarus 1.6RC2 / FPC 3.0.0)
« Last Edit: January 29, 2016, 06:34:16 pm by JanRoza »
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

bylaardt

  • Sr. Member
  • ****
  • Posts: 303
Re: Weird TSQLite3Connection problem
« Reply #1 on: January 29, 2016, 06:49:17 pm »
seem like a 'BEGIN TRANSACTION' without 'COMMIT' ou 'ROLLBACK'.

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #2 on: January 29, 2016, 07:15:42 pm »
Changed procedure to:

Code: Pascal  [Select]
  1.   // Open database
  2.   DbConnection.Connected := False;
  3.   DbConnection.DatabaseName := SysUtils.ExtractFilePath(ParamStr(0)) + 'GliderMaintenance.db';
  4.   DbConnection.Connected := True;
  5.  
  6.   if DbConnection.Transaction.Active = False
  7.   then DbConnection.Transaction.StartTransaction;
  8.  
  9.   // Open all tables
  10.   qryCertificatenLog.Active := True;
  11.  

But this makes no difference.
@bylaardt: there is nothing to commit or rollback yet as this is on program start, the first thing opening is the datamodule. It also doesn't explain why it runs in the IDE and in the projects own source folder but not outside it. In the end it may be some small stupid mistake but for now it beats me.  :'(
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

bylaardt

  • Sr. Member
  • ****
  • Posts: 303
Re: Weird TSQLite3Connection problem
« Reply #3 on: January 29, 2016, 07:31:14 pm »
can you open this db file with another app ( http://sqlitebrowser.org/  or sqlite3 on console ) and use select/insert instructions in all tables?

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #4 on: January 29, 2016, 07:38:01 pm »
Yes no problem, I use SQLite Expert Personal for this and the database opens perfectly.
Changing table structures and inserting and deleting records wok as well.
Somehow the clue must have to do with the fact that IDE and exe file in source folder do work but outside that folder the exe file does not work.
Normally having sqlite3.dll and the database in the program folder should be enough to run the program, but something now prevents that.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Weird TSQLite3Connection problem
« Reply #5 on: January 30, 2016, 02:16:42 am »
hello,
have you tried :
Code: Pascal  [Select]
  1. //
  2.   qryCertificatenLog.Close;
  3. // Open all tables
  4.   qryCertificatenLog.Active := True;
  5.  

Friendly, J.P
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #6 on: January 30, 2016, 11:54:54 pm »
I tried, the close is done without error but after that the error message is shown again.
So
Code: Pascal  [Select]
  1. qryCertificatenLog.Active := True;
still causes the error.
 
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Weird TSQLite3Connection problem
« Reply #7 on: January 31, 2016, 12:27:16 am »
hello,
what are your used database components for your project and their properties. Or put your source code in attachment.
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #8 on: January 31, 2016, 12:37:24 am »
I'm using SQLdb with TSQLite3Connection, TSQLTransaction and TSQLQuery.
Since it's a big project I've included only the datamodule and an empty database in the attachment.
Sending the filled database is not possible as it exceeds the maximum attachment size.
You can't do much with those items but give the datamodule a thorough look to see if you can spot anything.
Thanks!
« Last Edit: January 31, 2016, 12:39:04 am by JanRoza »
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Weird TSQLite3Connection problem
« Reply #9 on: January 31, 2016, 01:30:53 am »
hello,
i have added your datamodule unit in a project. I have no error  on :
Code: Pascal  [Select]
  1. qryCertificatenLog.Active := True;

have you tried your empty database ? have you the error with it ?

My config :  Windows 7 Lazarus 32 bits  1.6RC1   
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #10 on: January 31, 2016, 01:43:00 am »
Tried it with the same result  >:(
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #11 on: January 31, 2016, 01:55:13 am »
Funny thing is if I place the exe file in my source folder it works okay, but putting it in a different folder (together with the database and the sqlite.dll) it gives the error.
I cannot explain why it does open the connection but cannot activate the queries, though this test shows it has to do with the location somehow.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Weird TSQLite3Connection problem
« Reply #12 on: January 31, 2016, 02:08:15 am »
hello,
can you tried the project in attachments ?
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

JanRoza

  • Hero Member
  • *****
  • Posts: 535
    • http://www.silentwings.nl
Re: Weird TSQLite3Connection problem
« Reply #13 on: January 31, 2016, 12:57:58 pm »
Unfortunately same result  %)
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Weird TSQLite3Connection problem
« Reply #14 on: January 31, 2016, 01:10:14 pm »
hello,
strange  %)
try to find all the sqlite3.dll (or so) on your system (particularly in system32 or syswow64  folders if windows O.S). What is your O.S ? what is the version of your dll. If windows try to check the PATH variable.
« Last Edit: January 31, 2016, 01:12:25 pm by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko