Converting a Project from TDbf to SQLite [SOLVED]

[TRon]

I never activate database connections in Lazarus. This only causes problems in the long term if you forget to reset everything.

This was the initial message that I wanted to get across 

When working with a team and/or big projects you are bound to be confronted with the situation that someone somewhere changed or forget to set a property at design time. Hunting for those can be a PITA. When done with code it enables to locate such issue in the proverbial 5 seconds.

I can take credit for the compliment from Thaddy (thank you for that) but in reality it boils down to wise-up through shame of running into these kind of pitfalls.

I agree with your explanation on system wide access @CharlyTango, I already hinted to using a datamodule especially when there are many database related forms. In my answer I tried to let 1HuntnMan walk first as he was unable to get (even) a single query setup and run as intended.

And @1HuntnMan don't feel awkward about it. We all have been there and in case you were able to learn something from the answer I provided then I consider that a win. You're more than welcome. I wrote my answer the way I did (instead of just posting a working result) in the hopes someone else reading it is able to learn from it as well and be able to locate the pitfalls that you've ran into. In case you require further clarification for my post or require additional assistance with converting your project then feel free to let that know.

By the way... 'select * from' is more than a bad habit. After 30 years of programming frontends --> Don't ever do that

Yeah I know, another one of those wonderful pitfalls 

[Zvoni]

By the way... 'select * from' is more than a bad habit. After 30 years of programming frontends --> Don't ever do that

Praise the Lord.....

A case from real life:
The programmer used "SELECT * FROM" through out his code. It worked well (since the column-"Selection" happened in the Frontend).
Later on came the requirement to add a BLOB-Field (Images!!!!).
Well, quickly adding the column wasn't difficult, nor adding the code for displaying the image, when a row was selected.
The issue was, the phone didn't stop ringing in the IT-Department: "Why in blazes is the Network so slow?"

Have a guess, why it was so slow.....

[1HuntnMan]

Still working thru this. Quick question, in reading thru multiple docs online for SQLite3, several documents and documents are referring to "Using TSQLite3Dataset components to access SQLite databases.  I don't see a TSQLite3Dataset in the SQLdb in Lazarus???
What are they referring to? The only DataSource I know is in Data Access.

[1HuntnMan]

Never mind on the TSQLite3Dataset ... thanks. Just using the TDataSource to connect components.

[1HuntnMan]

Okay TRon et. al.,
  I'm back to my main application which is a Photographer's Mgt. System. The SQLite3 Database file is PMSDB. It's located in a folder just below my app with all source code called PMS.  The database (PMSDB.sqlite) contains 18 tables and 42 indexes in a folder just below the app called PMS DB. The code here is kind of what you explained but I was just working with and separate project with just the countries maintenance form which I didn't need to do because it's made it confusing to me, esp. the Database vs. just needing to update data in one table in the complete database. I guess a folder in TDbf might be the database in sql thinking and all the dbf tables are separate files where in SqLite all the tables and indices are in one file, i.e. the Database.
So, my components Connection, Transaction, Query I'm going to locate to datamodule or the like once I totally understand just accessing the database and or table or tables within say Clients Mgt, Sales Orders, Counties Maint., etc. Below, from reading I feel this is a round-robin. I'm understanding to connect to the database but not just opening and updating just the Countries table?????

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

1. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
2. begin
3.   SetupDB;
4. end;
5.  
6. procedure TFrmCntriesMaint.SetupDB;
7. var
8.   PMS_DB : string = 'PMS DB\PMSDB.sqlite';
9. begin
10.   //-> Establish connection to the PMSDB.sqlite database.
11.   PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)+ PMS_DB;
12.   DBCntriesConnect.PMS_DB:= DBCntriesConnect;
13.   //-> A transaction is required for the connection.
14.   DBTransCntries.PMS_DB:= DBCntriesConnect;
15.   //-> Open the database.
16.   DBCntriesConnect.Open;
17.   //-> Setup the Query
18.   DBCntriesQry.DataBase:= DBCntriesConnect;
19.   DBCntriesQry.Transaction:= DBTransCntries;
20.   //-> Can setup your data source next but done design time
21. end;
22.  
23. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
24. begin
25.   DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';
26.   DBCntriesQry.Open;
27.   Screen.Cursor:= crDefault;
28. end;
29.  
30. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
31. begin
32.   try
33.     DBCntriesQry.Close;
34.   finally
35.     DBCntriesConnect.Close(True);
36.   end;
37.   FrmCntriesMaint.Close;
38.   {FrmCntriesMaint.Free; Don't need this according to TRon!}
39. end;

[TRon]

I guess a folder in TDbf might be the database in sql thinking and all the dbf tables are separate files where in SqLite all the tables and indices are in one file, i.e. the Database.

If that helps you understand better then yes, you could make that analogy.

So, my components Connection, Transaction, Query I'm going to locate to datamodule or the like once I totally understand just accessing the database and or table or tables within say Clients Mgt, Sales Orders, Counties Maint., etc. Below, from reading I feel this is a round-robin.

It isn't really so I wonder how or what gave you that idea/feeling. Could you elaborate on how you were able to come to that feeling ?

I'm understanding to connect to the database but not just opening and updating just the Countries table?????

Once a query is "active" (e.g. executed correctly) the countries table /is/ open (sort of, it actually is the query), well at least with these particular SQL statement(s) in the query.

Because the initial countries example that was shared also used a navigator, the navigator component takes care of the editing and posting.

Please have another look at the first example from this reference documentation. Can you spot the SQL statement of the query and the query being opened with Q.open ? You should be able to recognize the EOF and Next methods that are used later on because those are the same for a dbf table/dataset. In a similar fashion the results that match the query can be edited, modified and posted (the things that a navigator component does behind the scenes).

If you want to access another table, certain fields inside a table or make a query that combines different tables then either modify the current query or create another one, set the SQL statement to match the criteria, open the query and do to the data what you want to do to it (the latter is a bit with a grain of salt because things somewhat needs to cohere with the rest of the flow of your code/GUI).

The only real difference in comparison when using traditional DBF tables is a transaction. There is no need for a deep analyses of a transaction but you have to grasp that depending on how things are setup that a transaction can keep track of whatever was applied to a query and only performs all the (previous) actions when instructed to do so (with a commit). In a similar manner a transaction can be used to rollback previously performed actions (f.e. in case there is a duplicate in one table and all the other related modifications that where done should then be reverted). Again, depending on how the transaction was setup/configured.

Note that in light of the posted countries example that such a form could perfectly well create and destroy the query on the fly and as such does not has to reside in a datamodule. But that has more to to with a (personal) design decision rather than anything else. Whatever works best for the use case or floats the boat 

[egsuh]

I don't see a TSQLite3Dataset in the SQLdb in Lazarus???

It's on Data Access tab. It's similar to TTable component of Delphi (actually trying to combine both TTable and TQuery). So, it might be more familiar to you if you have worked with TDbf,

Whether you use TSQLQuery or TSQLite3DataSet, TDataSource is necessary to connect them to Data controls,  e.g. TDBGrid, TDBEdit, etc.

The connections are:

SQLite3DataBase, Transaction --> SQLQuery or SQLite3DataSet --> DataSource --> Data controls (DBGrid, DBEdit, etc.)

[1HuntnMan]

Okay, TRon and egsug, I'll go thru comments. But, went back and made corrections and think this should work, compiles with no errors but errors on execution, see the attached. Below is the source code to the form that just has a TDBNavigator and a TDBGrid.

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

1. unit CountriesMaint;
2. {Program....: CountriesMaint.pas
3.  Author.....: Donald King
4.  Date.......: 20 Nov. 2023
5.  Copyright..: Copyright(c) 2023-2025 NewFound Photo Art, Inc.
6.  On the Web.: https://www.NewFoundPhotoArt.com
7.  Last Update: 03/09/2025}
8.  
9.  
10. {$mode ObjFPC}{$H+}
11.  
12. interface
13.  
14. uses
15.   Classes, SysUtils, DB, SQLDB, SQLite3Conn, Forms, Controls, Graphics,
16.   Dialogs, ExtCtrls, DBGrids, DBCtrls, Buttons;
17.  
18. type
19.  
20.   { TFrmCntriesMaint }
21.  
22.   TFrmCntriesMaint = class(TForm)
23.     DSCntries: TDataSource;
24.     DBGridCntries: TDBGrid;
25.     DBNavCntries: TDBNavigator;
26.     PnlCntryMaintTop: TPanel;
27.     SpdBtnCloseCntries: TSpeedButton;
28.     DBCntriesConnect: TSQLite3Connection;
29.     DBCntriesQry: TSQLQuery;
30.     DBTransCntries: TSQLTransaction;
31.     procedure FormCreate(Sender: TObject);
32.     procedure FormShow(Sender: TObject);
33.     procedure SpdBtnCloseCntriesClick(Sender: TObject);
34.   private
35.     procedure SetupDB;
36.   public
37.   end;
38.  
39. var
40.   FrmCntriesMaint: TFrmCntriesMaint;
41.  
42.  
43. implementation
44.  
45. {$R *.lfm}
46.  
47. { TFrmCntriesMaint }
48.  
49. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
50. begin
51.   //DBCntriesConnect:= TSQLite3Connection.Create(nil);
52.   SetupDB;
53. end;
54.  
55. procedure TFrmCntriesMaint.SetupDB;
56. var
57.   PMS_DB : string = 'PMSDB.sqlite';
58. begin
59.   //-> Establish connection to the PMSDB.sqlite database.
60.   PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)) + PMS_DB;
61.   DBCntriesConnect.DatabaseName:= PMS_DB;
62.   //-> A transaction is required for the connection.
63.   DBTransCntries.DataBase:= DBCntriesConnect;
64.   //-> Open the database.
65.   DBCntriesConnect.Open;
66.   //-> Setup the Query
67.   DBCntriesQry.DataBase:= DBCntriesConnect;
68.   DBCntriesQry.Transaction:= DBTransCntries;
69.   //-> Data source done design time, form component
70. end;
71.  
72. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
73. begin
74.   DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';
75.   DBtransCntries.Active:= True;
76.   DBCntriesQry.Active:= True;
77.   Screen.Cursor:= crDefault;
78. end;
79.  
80. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
81. begin
82.   try
83.     DBCntriesQry.Close;
84.   finally
85.     DBTransCntries.Active:= False;
86.     DBCntriesConnect.Connected:= False;
87.   end;
88.   FrmCntriesMaint.Close;
89.   {FrmCntriesMaint.Free; Don't need this according to TRon!}
90. end;
91.  
92. end.
93. { EoF: CountriesMaint.pas }
94.  

[TRon]

There might be several causes for this to happen:
- form was not closed using the speedbutton but form opened again.
- application has multiple connections connected to the same database (make sure to have one connection to a database per application)
- The database is also opened inside an(other) application or the other application that did is either still running with the database open or did not properly closed itself (and "hangs" in the background with the database still open)

I am personally not a big fan of setting the active property to true/false. Open and Close methods are more clear to me imho (I can never remember what setting active property actually does behind the scenes (also with the other components) and what I need to do myself). It might very well be that setting both the transaction and query to active actually tries to do the same thing (and chokes on the database already having been opened).

BTW:

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

1. {FrmCntriesMaint.Free; Don't need this according to TRon!}
2.  

It is not that I told so but the fact that you call for the destruction of the form while the code running is still inside that form. If one does not create the form manually at runtime then also not destroy it at runtime (at least not inside itself). To make another analogy in case you would do that: you are tearing down the house (and wait for it to finish) while closing the door. The door is not present anymore to actually close it 

edit PS:
Have you ever worked with the Lazarus debugger ? If you are not sure where a program fails you can run it through the debugger. Whenever an error pops-up the debugger allows to break the program and (normally) shows the source-code on the code-line that causes the error. In case that error is located somewhere outside your own code (but instead somewhere inside the LCL-code or perhaps even FPC code) then you can set a break-point inside your code instead and let the debugger run through your code source-line by source-line until the executed line generates an error (which then will pop-up) so that you are able to locate what code is responsible for generating such an error.

If the above sound to daring then use simple write to either the console or a memo so that it is possible to keep track which code was still executed correctly and round and about where it failed. It is then possible to narrow it down by adding additional writes.

[egsuh]

First of All, there is TSQLite3dataSet, with which you can open a table. If you assign filename and tablename and set it active, you will see the content of the table on DBGrid etc., if looked on. But it's not related with TSQLite3Connection.

I have made a simple test program. No problem in clicking checkboxes. But once there was a problem --- I mistakenly clicked "AutoCalcFields" of SQLQuery1 at design time, and then when I tried to set active/not active SQLQuery1 and connect/disconnect of SQLite3Connection1, it showed "access violation" error messages but still operated correctly.

I cannot attach my sample db file, as it's too large. I think you can try with your own database file.

[CharlyTango]

but errors on execution

The error message is quite clear -- some process occupies the (database-) file.

There might be several causes for this to happen:

I completely agree with TRon on this.
Be aware that SQLite is a single user database which means only one connection at a time is allowed.
The usual suspects in such a case are listed in TRons answer and just repeated from a different angle

-- most often Lazarus itself with an active database connection in the GUI (which the programmer has forgotten to close before starting the application.

As to the "opened in DB Browser at the same time" thing:
Someone above mentioned that in "standard"-way you block yourself: That's correct.

-- another Programm which occupies the file (eg a database manager like "DB Browser for SQLite") or has just crashed with open database file.

-- the application itself if you open a separate database connection in each form and use this forms at the same time. So that two connections are competing for the database file at the same time.

There is usually no need to have more than one database connection to a SQLite SQL server.

[Zvoni]

As to the "opened in DB Browser at the same time" thing:
Someone above mentioned that in "standard"-way you block yourself: That's correct.

-- another Programm which occupies the file (eg a database manager like "DB Browser for SQLite") or has just crashed with open database file.

-- the application itself if you open a separate database connection in each form and use this forms at the same time. So that two connections are competing for the database file at the same time.

There is usually no need to have more than one database connection to a SQLite SQL server.

I don't remember, if it's enough to use Journal_mode=WAL in DB Browser, or if you need to fire off the Pragma from your Pascal-Code.
Open your Database in DB Browser, switch to Tab "Pragmas", and set Journal-Mode to WAL, then try your code again

After establishing connection, fire off a "PRAGMA journal_mode=WAL" (ExecuteDirect or whatever it's called).
Though, i think i remember there are issues with fireing off PRAGMA-Calls directly

[1HuntnMan]

EGSuh,
   Where do you get the TSQLite3dataSet? I searched for that a few days ago but I thought it was something you had to buy.
   Anyway, I have been stepping thru the code and it's bombing out in the FormShow procedure when the query is opened. I tried to F7 thru but Laz is opening a lot of files and stepping thru a lot of code so don't know exactly what line in Laz where it's bombing with an access.

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

1. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
2. begin
3.   //DBCntriesConnect:= TSQLite3Connection.Create(nil);
4.   SetupDB;
5. end;
6.  
7. procedure TFrmCntriesMaint.SetupDB;
8. var
9.   PMS_DB : string = 'PMSDB.sqlite';
10. begin
11.   //-> Establish connection to the PMSDB.sqlite database.
12.   PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)) + PMS_DB;
13.   DBCntriesConnect.DatabaseName:= PMS_DB;
14.   //-> A transaction is required for the connection.
15.   DBTransCntries.DataBase:= DBCntriesConnect;
16.   //-> Open the database.
17.   DBCntriesConnect.Open;
18.   //-> Setup the Query
19.   DBCntriesQry.DataBase:= DBCntriesConnect;
20.   DBCntriesQry.Transaction:= DBTransCntries;
21.   //-> Data source done design time, form component
22. end;
23.  
24. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
25. begin
26.   DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';
27.   //DBtransCntries.Active:= True;
28.   DBCntriesQry.Open;  <---- Errors at this line!!!
29.   Screen.Cursor:= crDefault;
30. end;
31.  
32. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
33. begin
34.   try
35.     DBCntriesQry.Close;
36.   finally
37.     //DBTransCntries.Active:= False;
38.     //DBCntriesConnect.Connected:= False;
39.     DBCntriesConnect.Close(True);
40.   end;
41.   FrmCntriesMaint.Close;
42.   {FrmCntriesMaint.Free; Don't need this according to TRon!}
43. end;
44.  

Also, the complete project is still dBase Level 7 tables except for just one form which the user can maintain the Countries lookup table that's used when they are setting up Clients and/or Contacts address info. The main form is lauched when you run the PMS.exe file with no database open at all.  The main form is just menus and speed buttons for access to Clients, Contacts, References, Appointments, Proposals, Sales Orders, Invoicing, etc. No tables in the database are opened at all until you click a speed-button and go into a new form, i.e. Clients Mgt. Then, just the Clients table is opened for editing, deleting and inserting new Clients for example.  The way the system is working now, the only menu, no speed-buttons, you can click is File->Maintenance->PMS Lookup Tables->Countries. If I select Countries vs. States, Job Orders Status, etc. There are 8 lookup tables used by this app. Most are just tables with 2 fields.

If you can make a suggestion on this small form countries which is SQLite3 by itself, the next line to execute you can see is just returning the cursor to normal.

I'm moving on to convert another lookup table, i.e. States/Provinces which only has one table, 2 fields.

[TRon]

The error that was shown before doesn't really match the line in which the error occurs (at least not with the information we currently have)

Before opening the query and setting the SQL text, check if the query is active. If it is then close it. If that still raises the same error then it is most likely that the database is in fact in use by something else.

Are you sure the name of the table is literally COUNTRIES ? (f.i. the example that was shown before actually had the table name  COUNTRIESDB). (fwiw yes, I am aware that the presented error should be different then).

Before I forget, SQL statements always end with a ";" token.

Straws ... grasping 

[1HuntnMan]

TRon, when you stated that SQL statements always end with a semi-colon are you referring to the line:

DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';

If so, then it should be: DBCntriesQry.SQL.Text:= 'select * from COUNTRIES;';
correct?
Also, earlier EGSUH mentioned a SQLiteDataSet??? Is there a SQLite3 dataset and datasource? I thought I was reading online that mentioned a SQLite3 datasource?

Thanks for your help and everyone. I'll figure this out soon.