Forum > Databases
[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?
--- Quote ---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.
--- End quote ---
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 [+][-]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";}};} ---procedure TLiteDb.RunSQL(const aStatement: string); { writing: insert, update & delete }begin if not fTrans.Active then fTrans.StartTransaction; try fQuery.Close; fQuery.SQL.Text:= aStatement; fQuery.Prepare; fQuery.ExecSQL; fQuery.Close; finally fTrans.Commit; end; end; 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 [+][-]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";}};} ---procedure TLiteDb.RunSQL(const aStatement: string); { writing: insert, update & delete }begin if not fTrans.Active then fTrans.StartTransaction; try fExec.Close; // is this nescesary, fExec is only used here, for writing??? fExec.SQL.Text:= aStatement; // class decl-> fExec: TSQLQuery; fExec.ExecSQL; finally fTrans.Commit; end; // does this 'close' the query???end; 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
Navigation
[0] Message Index
[#] Next page