Weird TSQLite3Connection problem
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2016-01-29 17:01

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TDataModule1.DataModuleCreate(Sender: TObject);begin  // Open
database  DbConnection.Connected := False;  DbConnection.DatabaseName
:= SysUtils.ExtractFilePath(ParamStr(0)) + 'GliderMaintenance.db'; 
DbConnection.Connected := True;   // Open all tables 
qryCertificatenLog.Active := True; 

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)

----------------------------------------------------------------------
bylaardt · 2016-01-29 17:49

seem like a 'BEGIN TRANSACTION' without 'COMMIT' ou 'ROLLBACK'.

----------------------------------------------------------------------
JanRoza · 2016-01-29 18:15

Changed procedure to:

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   // Open
database  DbConnection.Connected := False;  DbConnection.DatabaseName
:= SysUtils.ExtractFilePath(ParamStr(0)) + 'GliderMaintenance.db'; 
DbConnection.Connected := True;   if DbConnection.Transaction.Active =
False  then DbConnection.Transaction.StartTransaction;   // Open all
tables  qryCertificatenLog.Active := True; 
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.

----------------------------------------------------------------------
bylaardt · 2016-01-29 18:31

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 · 2016-01-29 18:38

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.

----------------------------------------------------------------------
Jurassic Pork · 2016-01-30 01:16

hello,
have you tried :
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 //  
qryCertificatenLog.Close;// Open all tables  qryCertificatenLog.Active
:= True; 
Friendly, J.P

----------------------------------------------------------------------
JanRoza · 2016-01-30 22:54

I tried, the close is done without error but after that the error
message is shown again.
So 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
qryCertificatenLog.Active := True; still causes the error.

----------------------------------------------------------------------
Jurassic Pork · 2016-01-30 23:27

hello,
what are your used database components for your project and their
properties. Or put your source code in attachment.

----------------------------------------------------------------------
JanRoza · 2016-01-30 23:37

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!

----------------------------------------------------------------------
Jurassic Pork · 2016-01-31 00:30

hello,
i have added your datamodule unit in a project. I have no error  on :
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
qryCertificatenLog.Active := True;
have you tried your empty database ? have you the error with it ?

My config :  Windows 7 Lazarus 32 bits  1.6RC1

----------------------------------------------------------------------
JanRoza · 2016-01-31 00:43

Tried it with the same result

----------------------------------------------------------------------
JanRoza · 2016-01-31 00:55

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.

----------------------------------------------------------------------
Jurassic Pork · 2016-01-31 01:08

hello,
can you tried the project in attachments ?

----------------------------------------------------------------------
JanRoza · 2016-01-31 11:57

Unfortunately same result

----------------------------------------------------------------------
Jurassic Pork · 2016-01-31 12:10

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.

----------------------------------------------------------------------
JanRoza · 2016-01-31 12:32

YES!!
Your hint about sqlite3.dll version did it.
In my Lazarus folder I had version 3.8.9 and in my stand-alone test
folder was a newer version (maybe 64-bit whereas the project is
32-bit).
After replacing the dll the exe file works.
Man, something so simple and yet so hard to find! 
Thanks for your time, you really put me on my way again.

----------------------------------------------------------------------
zoltanleo · 2021-08-04 06:04

I will try to leave this message for those who also encounter a
similar error.

My project used writing and reading datasets. I set the following
options for these datasets:
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
qryMasterSelect.Options:= Options + [sqoAutoCommit,
sqoKeepOpenOnCommit];
I was getting the same error when I tried to programmatically close
the DB connection after connect/disconnect more than 1 time. It didn't
depend on the bitness of the library used sqlite3.

As a result, I found a workaround. I began to programmatically
create/destroy SQLite3Connection during connection/disconnection,
respectively.

Perhaps my experience will be useful to someone.