[SOLVED] Database Locked Exception

[1HuntnMan]

Anyone know why this occurs. This is an application I converted from TDbf to SQLite3. Testing, I can add 1 record or 4 or 5, doesn't matter, if I close the form I get an EDatabaseError with message: database is locked.

If I relaunch the app all newly added records are lost. To save a record, I'm just using the DBNav->Post. Closing the application I'm just Closing the main form.

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

1. procedure TFrmCntksMain.MnuFileExitClick(Sender: TObject);
2. begin
3.   Close;
4. end;
5.  

Also when the mainform is closed I was Form.Free but commented that out but makes no difference. It's as if adding records, posting, they aren't really being committed to the SQLite3 database.

[1HuntnMan]

I figured it out from ChatGPT. I haven't seen this in any of the documentation on line such as SQLdb Tutorial's 0-4, but this is what you do to correctly close an application when you have active connections, queries, transactions:

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

1. procedure TFrmCntksMain.SpdBtnCloseClick(Sender: TObject);
2. begin
3.   if QryCntks.Active then
4.     QryCntks.Close;
5.   if TransCntks.Active then
6.     begin
7.       try
8.         TransCntks.Commit;
9.       except
10.         on E: Exception do
11.         begin
12.           //-> Handle if rollback is required...
13.           TransCntks.Rollback;
14.         end;
15.       end;
16.     end;
17.   if ConnectCntks.Connected then
18.     Close;
19. end;
20.  

But, not finished, need to close all connections, queries and transactions before closing the application. I have 3 other tables connected.

[paweld]

In order for the changes to save to the database you must commit the transaction before closing the connection.
You can do this by calling:

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

1. SQLTransaction1.Commit;

before closing the connection.
But you can also set auto-commit transactions in the TSQLQuery options:

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

1. SQLQuery1.Options := [sqoAutoApplyUpdates, sqoAutoCommit];  

[1HuntnMan]

PAWeld, thanks! I have the commit's in my code and that's working now but getting an exception everytime close the app after testing adding records, but at least it's saving all my testing/transactions.  See the attached ScrnShot. I'm going to run the app and step thru right when I close the app to see where it's burbing with the Access Violation.

[cdbc]

Hi
Do you have more <Connection>s to the database?!?
If so, that could be what's locking the database...
The correct way is to have ONE connection and then share that among components...
Regards Benny

[1HuntnMan]

cdbc, et. al.
Here's the way I'm closing the app.

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

1. procedure TFrmCntksMain.SpdBtnCloseClick(Sender: TObject);
2. begin
3.   //-> Close the CONTACTS table
4.   if QryCntks.Active then
5.     QryCntks.Close;
6.     if TransCntks.Active then
7.       begin
8.         try
9.           TransCntks.Commit;
10.         except on E: Exception do
11.           begin
12.             //-> Handle if rollback is required...
13.             TransCntks.Rollback;
14.             TransCntks.Active:= False;
15.           end;
16.       end;
17.     end;
18.     if ConnectCntks.Connected then
19.       Close;
20.   //-> Close the STPROVCODES table
21.   if QryStates.Active then
22.     QryStates.Close;
23.   if TransStates.Active then
24.     begin
25.       try
26.         TransStates.Commit;
27.       except on E: Exception do
28.         begin
29.           //-> Handle if rollback is required...
30.           TransStates.Rollback;
31.           TransStates.Active:= False;
32.         end;
33.       end;
34.     end;
35.   if ConnectStates.Connected then
36.     Close;
37.   //-> Close CATEGORIES table
38.   if QryCat.Active then
39.     QryCat.Close;
40.   if TransCat.Active then
41.     begin
42.       try
43.         TransCat.Commit;
44.       except on E: Exception do
45.         begin
46.           // Handle if rollback is required...
47.           TransCat.Rollback;
48.           TransCat.Active:= False;
49.         end;
50.       end;
51.     end;
52.   if ConnectCat.Connected then
53.     Close;
54.   //-> Close COUNTRIES table
55.   if QryCntries.Active then
56.     QryCntries.Close;
57.   if TransCntries.Active then
58.     begin
59.       try
60.         TransCntries.Commit;
61.       except on E: Exception do
62.         begin
63.           //-> Handle if rollback is required...
64.           TransCntries.Rollback;
65.           TransCntries.Active:= False;
66.         end;
67.       end;
68.     end;
69.   if ConnectCntries.Connected then
70.     Close;
71.   FrmCntksMain.Close;
72. end;
73.  

When the FrmCntksMain.FormClose is executed, I'm FrmCntksMain.Free the popping up a Quote of the Day in a randomingly generated MessageDlg.

I don't get the Database Locked Exception until just after the Quote of the Day MessageDlg is closed. But at that time, every table should be closed. Should I also close the Database file itself, I haven't read any examples showing closing the database file itself.

Also, the app allows to update the countries, categories and states/provinces tables which can be edit from the main form with File-Open-Categories, Countries, States. Those 3 forms just have a DataSource with the DataSet property pointing back to the main form.Qry. The mainform uses those 3 procedure. I can go to any of those 3 forms and update and save records but when I exit the main form, any updates aren't saving in those 3 tables nor are any updates saving from the main contacts table.

Do you see anything wrong with the way I'm exiting?

[CharlyTango]

But you can also set auto-commit transactions in the TSQLQuery options:

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

1. SQLQuery1.Options := [sqoAutoApplyUpdates, sqoAutoCommit];  

IMO there is no need to have seperate transaction Objects for each Query. A single Transaction Object connected to the connection object will suffice. So you get rid of any lines dealing with transactions except a last one that closes the transaction object before closing the connection object.

Where do you store your connection Object including the connections transaction?
Mainform or Data Module?

Do you know which strings are sendt and received to and from your database?
Have you implemented a log file feature where you can see this?

some kind of code example by aircode:

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

1.    
2. //put this in your datamodules initializing procedure
3.  
4. SQLConn.OnLog:=@DoSQLLog;    
5. SQLConn.LogEvents:=LogAllEventsExtra;  //either LogAllEvents or LogAllEventsExtra
6.  
7.  
8.  
9. procedure TctSQLconnect.DoSQLLog(Sender: TSQLConnection; EventType: TDBEventType;
10.   const Msg: String);
11. var
12.   f:TextFile;
13.   sFileName:string;
14.   LogString:string;
15. begin
16.  
17.   if not FbLogSQL then exit;
18.  
19.   case EventType of
20.     detCustom: LogString:='Custom';
21.     detPrepare: LogString:='Prepare';
22.     detExecute: LogString:='Execute';
23.     detFetch: LogString:='Fetch';
24.     detCommit: LogString:='Commit';
25.     detRollBack: LogString:='RollBack';
26.     detParamValue: LogString:='ParamValue';
27.     detActualSQL: LogString:='ActualSQL';
28.   else ;
29.     LogString:='Unknown';
30.   end;
31.  
32.   Logstring:=Logstring+' '+ Msg;
33.  
34.   WriteToSQLLog(Logstring); //write log to file immediately
35.  
36. end;

maybe you get a trace where the database locks.
Usually a SQLite database is locked when another open access to the SQLite database occupies the access. That can be internal (when another/second SQL connection object tries to manipulate data while the primary connection object is not yet closed) or external (if some kind of "DB Browser for SQLite" or "Heidi" blocks the database)

BTW: a common closing of SQLQueries in one procedure of the main form is obsolete for me.
In my mind every form takes care of opening and closing all components it needs. Including database components as TSQLQuery and TDatasource.
In the Event OnFormClose or even better OnCloseQuery you can do all this and tidy up your form. This way all work is done with am mere close of the each form.
Even when closing the application with several open forms this strategy closes all open database components and even the connection securely.

[silvercoder70]

In your code I notice the following ...

  if ConnectStates.Connected then
    Close;

shouldn't that read ...

  if ConnectStates.Connected then
    ConnectStates.Close;

there are a couple of places where I notice that. 

[paweld]

And above all, for one database (one file) use only one connection (as @cdbc mentioned). And in the attached code you can see that you are using a separate connection for each table - this cannot be the case because the connections will block each other.

[1HuntnMan]

Wow, great! Thought I was on the right track, thanks all for the knowledge, back to the drawing board. Think I'm going to move the one Connect, one Transaction, Queries to a DataModule just so the main form of this test app for learning SQLite isn't cluttered with the Connects, Trans, Qrys and DSrcs for every table.

I'll be back maybe later, probably didn't word this properly, need to study this a bit while I'm redoing eveything, thanks!

This app has one table for managing the Contacts, the other three tables are just for DBLookupCombo's so the user can lookup a state/province, country and a category for the contact. 3 other forms are just for building/maintaining these 3 lookup tables. Thanks...

[cdbc]

Hi
To make it clear >> ONE << connection per database!!!
Regards Benny

[1HuntnMan]

Okay, started all over, basically. Dumped all the code that was accessing the tables separately. See below the code snipet that won't compile with this first line:

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

1. Connect:= TSQLiteConnection.Create(Nil);
2.  

If I comment out that line, I get a compile but the next line bombs:

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

1. procedure TFrmCntksMain.FormCreate(Sender: TObject);
2. //-> Create the connection to the ContactsDB sqlite3 database ...
3. begin
4.   //Connect:= TSQLiteConnection.Create(Nil);
5.   Connect.DatabaseName:= Application.Location + 'ContactsDB.sqlite3';{ This errors when running: 'External
6.                                  ACCESS VIOLATION' }
7.   Connect.Connected:= True;
8.   //-> Create a transaction ...
9.   Trans.DataBase:= Connect;
10.   Trans.Action:= caCommit;
11.   Trans.Active:= True;
12.   Query.Transaction:= Trans;
13.   Query.Options:= [sqoAutoApplyUpdates, sqoAutoCommit];
14.   DSrcCntks.DataSet:= Query;
15.   Query.SQL.Text:=
16.    'SELECT * FROM CONTACTS ContactsDB ORDER BY LASTNAME, FIRSTNAME, MI';
17.   Query.UpdateSQL.Text:=
18.    'UPDATE CONTACTS SET CNTKID=:CNTKID, DATESTAMP=:DATESTAMP, LASTNAME=:LASTNAME, '+
19.    'FIRSTNAME=:FIRSTNAME, MI=:MI, CATEGORY=:CATEGORY, DOB=:DOB, COMPANY=:COMPANY, '+
20.    'TITLE=:TITLE, SPOUSE=:SPOUSE, ADDRESS=:ADDRESS, ADDRESS2=:ADDRESS2, '+
21.    'CITYTOWN=:CITYTOWN, COUNTY=:COUNTY, STATEPROV=:STATEPROV, POSTALCODE=:POSTALCODE, '+
22.    'COUNTRY=:COUNTRY, CELLNO=:CELLNO, MAINPHONE=:MAINPHONE, PHONE=:PHONE, '+
23.    'FAX=:FAX, EMAIL=:EMAIL, EMAIL2=:EMAIL2, WEBURL=:WEBURL, NOTES=:NOTES '+
24.    'WHERE CNTKID=:OLD_CNTKID';
25. end;
26.  
27. procedure TFrmCntksMain.FormShow(Sender: TObject);
28. begin
29.   BitBtnSaveCntk.Enabled:= False;
30.   BitBtnCancel.Enabled:= False;
31.   //--->
32.   Query.Open;
33. ...
34.  

[cdbc]

Hi
If you would call it its correct type-name (TSQLite3Connection), I think it should compile 
Tsk, tsk, tsk...
Regards Benny

[Handoko]

If I am allowed, I would give a piece of advice:
Move all database related code into a separate module.

After years of writing code, I learn that code becomes harder to manage if we don't separate them properly. If the program only contains 1 or 2 forms then it is okay to put all the code in the form. But if there are many forms accessing same thing, in this case database, it will lead to spaghetti code.
https://en.wikipedia.org/wiki/Spaghetti_code

Most of OP problems, can be minimized if the non-GUI code separated properly from the forms. The code become more readable, less places for bugs to hide. If done properly, you can see clearly there is no reason to have more than 1 connection, as pointed out by cdbc.

Let forms do what they intended to do. Collecting information from users and showing information to users.

[1HuntnMan]

cdbc, thanks, that fixed that error.
Handoko, trying to get there. I have a working app, a Photographer's Mgt. app that I wrote a few years ago but uses TDbf. I has 18 tables, i.e. Dbf files. But, my goal is to convert it to SQLite3 but just learning by designing a simple Contact Mgt. app which only has 4 tables in the database. The main table is Contacts and the other 3 are just lookup tables for States/Provinces, Countries and Categories. Now, I'm getting an error at the Trans.Database:= Connect.  (External ACCESS VIOLATION AT LINE 187 Trans.DataBase:= Connect;). I'll figure that out here in a bit probably... thanks