[SOLVED] Database Locked Exception

[1HuntnMan]

Okay thanks all, finally figured out the SQL way of thinking. So, from everyone's advice I have a working app but just for the main table Contacts. Now, I don't need separate Connections or Transactions for each table in a SQLite database. That was my think, kind of like TDBf thinking.
So, for the other 3 tables which are lookup tables for each Contact record, I just need a Query and DataSource for each table in the database, correct? So, just define separate Queries, Query.SQL.Text for the other 3 tables. A LookUpCombo has the DataSource for the Contacts table and the lookup part is the DataSource for the States/Prov table pointing to the States/Prov query.
I also like the advice when closing the mainform, using the OnCloseQuery event to tidy up and close the query(s), commit any transactions and close the connection.

[1HuntnMan]

Okay, this app was compiling and running without the other 3 lookup tables so added the Queries for those and made sure the DataSource's were in their proper places in the LookUpCombo's for States, Countries and Categories. But now I'm getting a new error when I 'Run' but it compiles without error. See the attachment, "Database not Assigned". I tried to step thru but couldn't figure it out. Here's the Create and Show procedures:

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

1. procedure TFrmCntksMain.FormCreate(Sender: TObject);
2. //-> Create the connection to the ContactsDB sqlite3 database ...
3. begin
4.   ConnectCntks:= TSQLite3Connection.Create(Nil);
5.   ConnectCntks.DatabaseName:= Application.Location + 'ContactsDB.sqlite3';
6.   //-> Create a transaction ...
7.   ConnectCntks.Connected:= True;
8.   TransCntks:= TSQLTransaction.Create(ConnectCntks);
9.   //-> Point to the database instance ...
10.   TransCntks.DataBase:= ConnectCntks;
11.   TransCntks.Action:= caCommit;
12.   TransCntks.Active:= True;
13.   //-> Now open the database ...
14.   //ConnectCntks.Connected:= True;
15.   QryCntks.Transaction:= TransCntks;
16.   QryCntks.Options:= [sqoAutoApplyUpdates, sqoAutoCommit];
17.   QryCntks.DataBase:= ConnectCntks;
18.   //QryCntks:= TSQLQuery.Create(ConnectCntks);
19.   //QryCntks.TransCntks:= True;
20.   //-> Select CONTACTS Table query...
21.   DSrcCntks.DataSet:= QryCntks;
22.   QryCntks.SQL.Text:=
23.    'SELECT * FROM CONTACTS ContactsDB ORDER BY LASTNAME, FIRSTNAME, MI';
24.   QryCntks.UpdateSQL.Text:=
25.    'UPDATE CONTACTS SET CNTKID=:CNTKID, DATESTAMP=:DATESTAMP, LASTNAME=:LASTNAME, '+
26.    'FIRSTNAME=:FIRSTNAME, MI=:MI, CATEGORY=:CATEGORY, DOB=:DOB, COMPANY=:COMPANY, '+
27.    'TITLE=:TITLE, SPOUSE=:SPOUSE, ADDRESS=:ADDRESS, ADDRESS2=:ADDRESS2, '+
28.    'CITYTOWN=:CITYTOWN, COUNTY=:COUNTY, STATEPROV=:STATEPROV, POSTALCODE=:POSTALCODE, '+
29.    'COUNTRY=:COUNTRY, CELLNO=:CELLNO, MAINPHONE=:MAINPHONE, PHONE=:PHONE, '+
30.    'FAX=:FAX, EMAIL=:EMAIL, EMAIL2=:EMAIL2, WEBURL=:WEBURL, NOTES=:NOTES '+
31.    'WHERE CNTKID=:OLD_CNTKID';
32.   //-> Select STATES Table query...
33.   DSrcStates.DataSet:= QryStates;
34.   QryStates.SQL.Text:= 'SELECT * FROM STPROVCODES ContactsDB ORDER BY STPROVCODE';
35.   QryStates.UpdateSQL.Text:=
36.    'UPDATE STPROVCODES SET STPROVCODE=:STPROVCODE, STPROVNAME=:STPROVCODE, '+
37.    'STPROVISO=:STPROVISO WHERE STPROVCODE=:OLD_STPROVCODE';
38.   //-> Select COUNTRIES Table query...
39.   DSrcCntries.DataSet:= QryCntries;
40.   QryCntries.SQL.Text:= 'SELECT * FROM COUNTRIES ContactsDB ORDER BY CODE';
41.   QryCntries.UpdateSQL.Text:= 'UPDATE COUNTRIES SET CODE=:CODE, COUNTRY=:COUNTRY'+
42.    'WHERE CODE=:OLD_CODE';
43.   //-> Select CATEGORIES Table query...
44.   DSrcCat.DataSet:= QryCat;
45.   QryCat.SQL.Text:= 'SELECT * FROM CATEGORIES ContactsDB ORDER BY CATID';
46.   QryCat.UpdateSQL.Text:= 'UPDATE CATEGORIES SET CATID=:CATID, CATEGORY=:CATEGORY'+
47.    'WHERE CATID=:OLD_CATID';
48.   //-> Clear/Assign index items to the CmboBxSelIndex component...
49.   CmboBxSelIndex.Items.Clear;
50.   CmboBxSelIndex.Items.Add('Name (L,F,M)');
51.   CmboBxSelIndex.Items.Add('Company');
52.   CmboBxSelIndex.Items.Add('Category');
53.   CmboBxSelIndex.Items.Add('City/Town');
54.   CmboBxSelIndex.Items.Add('State/Province');
55.   CmboBxSelIndex.Items.Add('Country');
56. end;
57.  
58. procedure TFrmCntksMain.FormShow(Sender: TObject);
59. begin
60.   BitBtnSaveCntk.Enabled:= False;
61.   BitBtnCancel.Enabled:= False;
62.   //-> Open Contacts tables queries
63.   QryCntks.Open;
64.   QryStates.Open;
65.   QryCntries.Open;
66.   QryCat.Open;
67.   EditTTLCntks.ReadOnly:= False;
68.   IntRecCount:= DSrcCntks.DataSet.RecordCount;
69.   EditTTLCntks.Text:= IntToStr(IntRecCount); //-> Show total Contacts...
70.   EditTTLCntks.ReadOnly:= True;
71. end;
72.  

[cdbc]

Hi
No wonder... I only see this one:

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

1. QryCntks.DataBase:= ConnectCntks;

Where are the others?!?
Regards Benny

[CharlyTango]

Just for your information: qualified help can only be given if you know WHERE the error message occurs.
In other words, at which line of code. This requires that you have debugged the code at least once up to the error line.

Just showing an error message without a reference would get you more than one reprimand in other forums.

Back to the topic: My guess goes in the same direction as @cdbc that the error message occurred when opening.

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

1. QryStates.Open;

And the error message is meaningful. The TSQLQuery QryStates is missing the assignment of the database connection via which it should fetch the data.
So something like

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

1. QryStates.DataBase:= ConnectCntks ;;
2. QryCntries.DataBase:= ConnectCntks;
3. QryCat.DataBase:= ConnectCntks;

Every TSQLQuery and also every other SQL component needs an associated database connection. In your case, this is ConnectCntks. All SQL components connect to the database via the same database connection, but you also have to assign it.

[1HuntnMan]

Yes, I will do that from now on but figured it out by looking at my code and using debug to figure out that I had forgotten to assign the DSrcStates, DSrcCntries and DSrcCategories to the proper Queries, i.e. DSrcStates.DataSet:= QryStates; and also the other 2. I had QryStates, Cntries and Categories assigned to their corresponding datasource but the DBLookUpCombos weren't active because the DSrc components weren't assigned to the proper Queries.

Next and probably last question for awhile. I've tested the main form, adding records, deleting and editing, etc. All appears to be working properly.  But, I have 3 other forms that are launched from the mainform's menu File - Open - States/Provinces, Countries and Categories. These 3 forms are for just maintaining the States, Countries and Categories tables.  Each form is just a DBNavigator, DbGrid and a Close btn.  The forms are in the Uses of the main form and each form only has a DataSource component -  DataSet assigned to the main forms Query, i.e. FrmCntksMain.QryCat. Same setup on the other 2 forms. I can open and edit, delete and add new records but when I close the form the database is closed on everything, even the main form.

Can anyone advise how to approach this correctly?
 

[CharlyTango]

why your application closes the database connection cannot be estimated without knowing the code.
As i mentioned before: My crystal ball is currently being serviced

I have provided you with an example in which you can see all the techniques I have mentioned in my posts, including a way to make your master data maintenance

https://github.com/CharlyTango/SQLite_simple

[1HuntnMan]

Charley, thanks for the demo. I made some changes and this test app for learning SQLite3 runs just fine except when I save a new record. It saves but then closes the database??? Below is the code for the main part of the Main program as far as saving a new record. It saves but closes the database. I've attempted to step thru but too many forms pop-up to figure out why the database is closing.

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

1. var
2.   FrmCntksMain: TFrmCntksMain;
3.   Keys: TStringList;
4.   IntRecCount: Integer;
5.   ConnectCntks : TSQLite3Connection;
6.   TransCntks   : TSQLTransaction;
7.   QryCntks     : TSQLQuery;
8.   QryStates    : TSQLQuery;
9.   QryCntries   : TSQLQuery;
10.   QryCat       : TSQLQuery;
11.  
12. implementation
13.  
14. {$R *.lfm}
15.  
16. { TFrmCntksMain }
17.  
18. procedure TFrmCntksMain.FormCreate(Sender: TObject);
19. begin
20.   SetupDB;
21.   ConnectCntks.Open; //-> Open the Contacts Database...
22.   //-> Clear/Assign index items to the CmboBxSelIndex component...
23.   CmboBxSelIndex.Items.Clear;
24.   CmboBxSelIndex.Items.Add('Name (L,F,M)');
25.   CmboBxSelIndex.Items.Add('Company');
26.   CmboBxSelIndex.Items.Add('Category');
27.   CmboBxSelIndex.Items.Add('City/Town');
28.   CmboBxSelIndex.Items.Add('State/Province');
29.   CmboBxSelIndex.Items.Add('Country');
30. end;
31.  
32. procedure TFrmCntksMain.SetupDB;
33. var
34.   CntksDB : string = 'ContactsDB.sqlite3';
35. begin
36.   //-> Create the connection to the ContactsDB sqlite3 database ...
37.   CntksDB:= Application.Location + CntksDB;
38.   if not FileExists(CntksDB) then begin
39.     showmessage('Contacts Database file '+CntksDB+', is missing!');
40.     exit;
41.   end;
42.   ConnectCntks:= TSQLite3Connection.Create(Nil);
43.   ConnectCntks.DatabaseName:= CntksDB;
44.   //-> Create the transaction for the connection ...
45.   { Next line Error??? Error: identifier idents no member "TSQLTransaction"
46.     TransCntks.TSQLTransaction.Create(ConnectCntks); }
47.   TransCntks.DataBase:= ConnectCntks;
48.   TransCntks.Action:= caCommit;
49.   TransCntks.Active:= True;
50.   //-> Now Query/Open/Connect to the ContactsDB database ...
51.   ConnectCntks.Connected:= True;
52.   QryCntks.Transaction:= TransCntks;
53.   QryCntks.Options:= [sqoAutoApplyUpdates,sqoAutoCommit];
54.   QryCntks.DataBase:= ConnectCntks;
55. end;
56.  
57. procedure TFrmCntksMain.FormShow(Sender: TObject);
58. begin
59.   //-> Create/Select the CONTACTS Table query...
60.   QryCntks.SQL.Text:=
61.    'SELECT * FROM CONTACTS ContactsDB ORDER BY LASTNAME, FIRSTNAME, MI';
62.   QryCntks.UpdateSQL.Text:=
63.    'UPDATE CONTACTS SET CNTKID=:CNTKID, DATESTAMP=:DATESTAMP, LASTNAME=:LASTNAME, '+
64.    'FIRSTNAME=:FIRSTNAME, MI=:MI, CATEGORY=:CATEGORY, DOB=:DOB, COMPANY=:COMPANY, '+
65.    'TITLE=:TITLE, SPOUSE=:SPOUSE, ADDRESS=:ADDRESS, ADDRESS2=:ADDRESS2, '+
66.    'CITYTOWN=:CITYTOWN, COUNTY=:COUNTY, STATEPROV=:STATEPROV, POSTALCODE=:POSTALCODE, '+
67.    'COUNTRY=:COUNTRY, CELLNO=:CELLNO, MAINPHONE=:MAINPHONE, PHONE=:PHONE, '+
68.    'FAX=:FAX, EMAIL=:EMAIL, EMAIL2=:EMAIL2, WEBURL=:WEBURL, NOTES=:NOTES '+
69.    'WHERE CNTKID=:OLD_CNTKID';
70.   DSrcCntks.DataSet:= QryCntks;
71.   //-> Create/Select the STATES Table query...
72.   QryStates.DataBase:= ConnectCntks;
73.   QryStates.Options:= [sqoAutoApplyUpdates, sqoAutoCommit];
74.   QryStates.SQL.Text:= 'SELECT * FROM STPROVCODES ContactsDB ORDER BY STPROVCODE';
75.   QryStates.UpdateSQL.Text:=
76.    'UPDATE STPROVCODES SET STPROVCODE=:STPROVCODE, STPROVNAME=:STPROVCODE, '+
77.    'STPROVISO=:STPROVISO WHERE STPROVCODE=:OLD_STPROVCODE';
78.   DSrcStates.DataSet:= QryStates;
79.   //-> Create/Select the COUNTRIES Table query...
80.   QryCntries.DataBase:= ConnectCntks;
81.   QryCntries.Options:= [sqoAutoApplyUpdates, sqoAutoCommit];
82.   QryCntries.SQL.Text:= 'SELECT * FROM COUNTRIES ContactsDB ORDER BY CODE';
83.   QryCntries.UpdateSQL.Text:= 'UPDATE COUNTRIES SET CODE=:CODE, COUNTRY=:COUNTRY'+
84.    'WHERE CODE=:OLD_CODE';
85.   DSrcCntries.DataSet:= QryCntries;
86.   //-> Create/Select the CATEGORIES Table query...
87.   QryCat.DataBase:= ConnectCntks;
88.   QryCat.Options:= [sqoAutoApplyUpdates, sqoAutoCommit];
89.   QryCat.SQL.Text:= 'SELECT * FROM CATEGORIES ContactsDB ORDER BY CATID';
90.   QryCat.UpdateSQL.Text:= 'UPDATE CATEGORIES SET CATID=:CATID, CATEGORY=:CATEGORY'+
91.    'WHERE CATID=:OLD_CATID';
92.   DSrcCat.DataSet:= QryCat;
93.   //-> Open all Contacts Database table queries...
94.   QryCntks.Open;
95.   QryStates.Open;
96.   QryCntries.Open;
97.   QryCat.Open;
98.   //-> Show total Contacts...
99.   EditTTLCntks.ReadOnly:= False;
100.   IntRecCount:= DSrcCntks.DataSet.RecordCount;
101.   EditTTLCntks.Text:= IntToStr(IntRecCount);
102.   EditTTLCntks.ReadOnly:= True;
103.   //-> Disable Save/Cancel buttons...
104.   BitBtnSaveCntk.Enabled:= False;
105.   BitBtnCancel.Enabled:= False;
106. end;
107.  
108. procedure TFrmCntksMain.BitBtnSaveCntkClick(Sender: TObject);
109. begin
110.   if DSrcCntks.DataSet.Modified then
111.     DSrcCntks.DataSet.Post;
112. end;
113.  

[CharlyTango]

At first glance, I see SELECT * in the queries, which in many cases leads to problems because then the estimation of the TSQLQuery what the primary key could be does not work.
I have select * in my example because I only display the data in the grid but do not update it.

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

1. IntRecCount:= DSrcCntks.DataSet.RecordCount;

does not give a correct value until all the data in the SELECT has actually been fetched. This is not always the case because the components and the SQL server (in your case SQLite) do not transfer all data but only those that are currently displayed. If scrolled, the data is reloaded.
This means that a RecordCount only makes sense if all data has been loaded.

IMO, after a

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

1. DSrcCntks.DataSet.Post;

a

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

1. DSrcCntks.DataSet.ApplyUpdates;

should follow to send the data safely to the server.

Your programme still looks the same as before, so you have not implemented any of our recommendations. I also don't see any logging of the connection.
I cannot determine from this tiny piece of code why your programme is terminating the database connection.

Without the source of a working test programme including the database, I am out of this topic

[1HuntnMan]

Okay, I'll use your example and implement a logging. I don't use the DBGrid other than just scrolling thru the records. I designed this test app. to edit/view/add records via the form of DBEdits, 3 DBLookUpCombos for States/Provinces, Countries and Categories. My test data is only 20 records. The primary key is just the ContactID: Primary Key, ASC, Autoincrement. The first test I did was a SQLite3 database with just one table, States and tested by importing data from a TDBf table with all the states of US, Mexico, Canada and Germany. It works fine, it's just a DBNavigator and a DBGrid. I don't think this is the issue of SELECTING all the records in the table and the DBGrid. I'm going to totally rewrite this from scratch and add your example to create a log. Then maybe I'll be able to figure it out. Be back later and let everyone know ... Also, I'm going to disable updating in the DBGrid, it's just allows scrolling and viewing anyway, my daughter likes it but it's not used for editing, etc. Her database TDbf has 102 records in the Contacts table, mine has 72. That's not a large table. This test only has 20 test records.

[1HuntnMan]

Added ApplyUpdates to the OnClick SaveBtn. It saves:

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

1. procedure TFrmCntksMain.BitBtnSaveCntkClick(Sender: TObject);
2. begin
3.   if DSrcCntks.DataSet.Modified then
4.     try
5.       DSrcCntks.DataSet.Post; <- Saves
6.     finally
7.       QryCntks.ApplyUpdates; <- Errors here???
8.     end;
9. end;
10.  

Error Finally: Operation cannot be performed on an inactive dataset.
So, now as suspected, it's posted and saved to the table but the DataSet isn't Active.
Thanks

[CharlyTango]

and where the hell can I download this test application?

Nobody can tell you exactly what the problem is just from your description. We can speculate forever, but that can't help anyone and certainly not you

[paweld]

If you set AutoAppluUpdates to True in the options ( https://forum.lazarus.freepascal.org/index.php/topic,71164.msg555204.html#msg555204 ), then you don't need to add a line:

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

1. DSrcCntks.DataSet.ApplyUpdates;

[egsuh]

I haven't read all the posts carefully. But with following question,

I can open and edit, delete and add new records but when I close the form the database is closed on everything, even the main form.

When you commit or rollback a transaction, all datasets (ie. queries) using that transaction are CLOSED.

If you want to commit or rollback transaction while keeping datasets open, use COMMITRETAINING or ROLLBACKRETAINING instead.

BTW, why don't you simply drop components on the form or datamodule, and fill in their properties, and create codes? I know that advanced programmers prefer to define them themselves, but you are not advanced programmer at least in this area. First make your whole program work in inserting, editing, and deleting any record. You are stuck at this stage. After that, consider custom-creating and fine-tuning SQLite3Connection, TSQLTransaction, etc. Too many things are discussed together, from Locked database, closing one form closing all other datasets, to posting and applying updates. Each one are separate subject.

[1HuntnMan]

Want to thank everyone for your input.  I think I finally figured out the correct way to design a small SQLite database and make it work. I've been testing and so far not errors.  Also, the 3 maintenance forms for States, Countries and Categories were just a DBNav, DBGrid and a Close Btn.  Fixed that and removed the Qry, DSrc, etc. components and in the implementation of the main frm added a Uses of the those 3 units. Then added the main unit to Uses in those three units Uses in their interface. So, no Qry, Trans, DSrc on those 3 small forms. Just pointed their DBNavs, DBGrids back to the mainforms DSrcCAT, DSrcCntry, DSrcStats. Again, thanks, moving on to my other larger app to convert from TDbf -> SQLite3!

[CharlyTango]

apparently you don't want to learn from our recommendations.
It is a good programming skill to separate the data access from the graphical elements.
It also makes sense (as long as there are not too many on a form) to place the TSQLQuery and TDatasource directly on the maintenance form.
The TSQLiteConnection and the TTransaction belong in a separate data module, which is made known to the forms via a uses clause.

no matter
I am finally out