[SOLVED]Error on closing connection to SQLite3 db

[cdbc]

Hi
Suddenly I've been getting an error that I don't really understand...:

TSQLite3Connection : unable to close due to unfinalized statements
   or unfinished backups.
                               [Abort] [Ok]

It happens after a write in a transaction (which is commited), then I want to close the connection -> Squiiiick to a halt with that message.

No backups going on, whatsoever.

Has anybody seen this before or does someone have a clue?!?
Even better, knows how to fix it 
Regards Benny

[rvk]

Did you close all open queries (and prepared statements) as well as open BLOB handles etc. before closing the connection?

Applications must finalize all prepared statements and close all BLOB handles associated with the sqlite3 object prior to attempting to close the object. If sqlite3_close() is called on a database connection that still has outstanding prepared statements or BLOB handles, then it returns SQLITE_BUSY.

And what version of SQLite and Lazarus are your using?

[cdbc]

Hi
@rvk:
Lazarus version: 2.2.2
Fpc version: 3.2.2
SQLite3 version: 3.39.1

The code that executes is this:

Code: Pascal  [Select][+][-]

1. procedure TLiteDb.RunSQL(const aStatement: string); { writing: insert, update & delete }
2. begin
3.   if not fTrans.Active then fTrans.StartTransaction;
4.   try
5.     fQuery.Close;
6.     fQuery.SQL.Text:= aStatement;
7.     fQuery.Prepare;
8.     fQuery.ExecSQL;
9.     fQuery.Close;
10.   finally fTrans.Commit; end;  
11. end;
12.  

After this, when I close the connection, it goes belly-up...
Regards Benny

[rvk]

Some remarks...

*) Why do a prepare when you have only one ExecSQL?
You do a prepare if you have a parametrized query and you execute the same SQL multiple times.
Otherwise (when the SQL is different each time) prepare isn't needed (and is done internally).
So remove line 7 (fQuery.Prepare)

*) Why do you close fQuery before doing ExecSQL?
Is this a fQuery which is used elsewhere too?
If it is used with fQuery.Open you indeed need to close the query but in that case I would use a separate query for ExecSQL.

If it is not used with a fQuery.Open somewhere, you shouldn't have a need to call fQuery.Close.

*) You do a fQuery.Close after a fQuery.ExecSQL. But ExecSQL is for executing an UPDATE/DELETE statement. Not for SELECT.
So use fQuery.Open and fQuery.Close with SELECT statement (because they return a dataset)
Use fQuery.ExecSQL with UPDATE and DELETE (and without fQuery.Close) because that doesn't return a dataset !!

I'm not sure why these combinations would lead to a crash but you might want to fix them just the same and see if it works better.

If it still crashes we'll need to see some more code (where is fQuery used, with what code, what is aStatement etc).

[cdbc]

Hi
@rvk:
Thank you, i've taken all of your advice and implemented them:

Code: Pascal  [Select][+][-]

1. procedure TLiteDb.RunSQL(const aStatement: string); { writing: insert, update & delete }
2. begin
3.   if not fTrans.Active then fTrans.StartTransaction;
4.   try
5.     fExec.Close; // is this nescesary, fExec is only used here, for writing???
6.     fExec.SQL.Text:= aStatement; // class decl->  fExec: TSQLQuery;
7.     fExec.ExecSQL;
8.   finally fTrans.Commit; end; // does this 'close' the query???
9. end;
10.  

It works like a charm. I've now got a dedicated query (fExec) for insert, update and delete, and one only for select. I've put in a couple of questions for you, if you don't mind 
Regards Benny

[rvk]

It works like a charm. I've now got a dedicated query (fExec) for insert, update and delete, and one only for select. I've put in a couple of questions for you, if you don't mind 

To answer your questions.
No, it's not needed to do fExec.Close if it's only used there.
As stated in the previous post, an UPDATE, INSERT and DELETE don't produce a dataset.
So in that case you shouldn't use fExec.Open and fExec.Close.

The fTrans.Commit doesn't close the query because the fExec.ExecSQL doesn't need closing.
Closing is only something you do on a dataset (after opening).
And since ExecSQL (with UPDATE, INSERT and DELETE) doesn't return anything, there is no need for closing.
(BTW there is an exception on this when you are including RETURNING when INSERTing but that's a separate case which you don't use here)

The fTrans.Commit should also only be done when there is a transaction.

So:

Code: Pascal  [Select][+][-]

1. procedure TLiteDb.RunSQL(const aStatement: string); { writing: insert, update & delete }
2. begin
3.   if not fTrans.Active then fTrans.StartTransaction;
4.   try
5.     fExec.SQL.Text:= aStatement;
6.     fExec.ExecSQL;
7.   finally
8.     if fTrans.Active then fTrans.Commit; { or fTrans.CommitRetaining; }
9.   end;
10. end;

BTW. If the fTrans is the same as used in the TConnection and other TSQLQuery for SELECT, it might be needed to use fTrans.CommitRetaining;
Because it could be that when the transaction is closed that the other TSQLQuery is closed automatically.
(When a transaction is committed ALL connected queries are usually also closed, but that could depend on the database and other components used).

If the fTrans is a different transaction, the other TSQLQuery for SELECT could need a Close/Open cycle before they see the changes made by this ExecSQL.

[cdbc]

Hi
Cool thank you.
I've got the app setup like this:

Gui  <------->  Business Object  <------->  Datalayer

The gui only talks to the Business Object and so does the datalayer.
The Business Object takes care of business and the gui has no idea from where the data comes, it just observes and shows you the data. The datalayer just "put this", and "get that" after orders from Business Object, it has no idea what the data are used for. This makes it easy to change datalayer without changing the application. If you want to store data in textfiles, just write an "engine" that conforms with the datalayer api.
And yes the bom has a deltaqueue for cached updates, kinda like TClientDataset in Delphi, only this works on the hole bom and not just one dataset 

Thank you for your time and help, I very much apreciate that.
Regards Benny