Converting a Project from TDbf to SQLite [SOLVED]

[TRon]

If so, then it should be: DBCntriesQry.SQL.Text:= 'select * from COUNTRIES;';
correct?

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?

They inside packages sqlite3laz and/or sqlitelaz (menu->package->install/uninstall packages ... -> Available for installation (list at right)). Ergo they are required to be (manually) installed before appearing in the component list.

[egsuh]

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.

You can find it at DataAccess tab on component palette. The last component. See the attached image.

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

1.  DBCntriesConnect.Open;
2.   //-> Setup the Query
3.   DBCntriesQry.DataBase:= DBCntriesConnect;
4.   DBCntriesQry.Transaction:= DBTransCntries;

I think you may try :

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

1.  DBCntriesConnect.Connected := True;
2.   DBCntriesConnect.Transaction:= DBTransCntries;
3.   DBCntriesQry.DataBase:= DBCntriesConnect;

It's strange that if you cannot open a database, then the error should occur at:

      DBCntriesConnect.Connected := True;

But I found the positions that raise exceptions are not always correct. 

So, I think you may try to connect the database at design time as shown in another image, by 1) directly selecting the database file at object inspector, and then try to set connected to true. If this succeeds, then problem is likely to be located in your own program. If the error message occurs here, the db file is likely to be locked up by other process.

[1HuntnMan]

Okay, EGSuh, TRon, et. al.
Success on the small Countries only form/pas.  This morning on my main application, I decided to use a Data Module.  Reading online a bit just wanted to run this by all of you if my thinking is correct. From reading, I have the data module: DMPMS.  I put the 3 SQL components: PMSDBConnect, PMSSQLiteTrans and PMSSQLiteQry on it. The Uses just has Classes, SysUtils, SQLite3Conn, SQLDB, DB;
I added under public: PMS_DB: string;
Under var: DmPMSDataBase : TDmPMSDataBase;

I added a procedure, see below, do I even need to do this for the DMPMS.

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

1. procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);
2. begin
3.   { Declare the PMS Database for the PMS application. }
4.   PMSDBConnect.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
5.   PMSDBConnect:= TSQLite3Connection.Create(nil);
6. end;
7.  

Also, I added the DMPMS in the uses for every procedure that's access the database, i.e. Appointments, ClientsMgt, CntksMgt, Invoicing, etc. The only component in my thinking is putting a Dataset for each table on these procedure - forms.

Is my thinking/approach correct?

[1HuntnMan]

I forgot this:  In the program PMS I added the DMPMS to the beginning of the units, i.e.

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

1.  { The PMS units are listed below ... }
2.   DMPMS, PMSMain, datetimectrls, printer4lazarus, lazcontrols;
3.  

then the begin..end is like so creating the data module first then the main form for the application, i.e.

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

1. begin
2.   RequireDerivedFormResource:=True;
3.   Application.Title:= 'Photographers Management System';
4.   Application.Scaled:=True;
5.   Application.Initialize;
6.   Application.CreateForm(TDmPMSDataBase, DmPMSDataBase);
7.   Application.CreateForm(TFrmPMSMain, FrmPMSMain);
8.  
9.   Application.Run;
10.   { If the compile errors at the end statement below, the PMS
11.     application is running and in memory while you are in Lazarus!
12.     Shut down the PMS running outside of Lazarus and then compile/run! }
13. end.
14. { EoF: PMS.lpr }
15.  
16.  

[1HuntnMan]

Okay, moved on with converting my complete TDbf PMS system to SQLite3. I setup a data module and just some feedback it I have this setup properly, but probably not. Because I am thinking if it should be global to the complete app. if it's created and in memory before the main form is created. Any advice, thanks in advance...
Source below and screenshot attached. Maybe I don't need the On Create in the Data Module at all???

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

1. unit DMPMS;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, SQLite3Conn, SQLDB, DB;
9.  
10. type
11.  
12.   { TDmPMSDataBase }
13.  
14.   TDmPMSDataBase = class(TDataModule)
15.     ConnectPMSDB: TSQLite3Connection;
16.     QueryPMSDB:   TSQLQuery;
17.     TransPMSDB:   TSQLTransaction;
18.     procedure DataModuleCreate(Sender: TObject);
19.   private
20.  
21.   public
22.     //->PMS_DB: string;
23.   end;
24.  
25. var
26.   DmPMSDataBase: TDmPMSDataBase;
27.  
28. implementation
29.  
30. {$R *.lfm}
31.  
32. { TDmPMSDataBase }
33.  
34. procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);
35. begin
36.   ConnectPMSDB:= TSQLite3Connection.Create(nil);
37.   { Connect to the PMS Database for the PMS application. }
38.   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
39.   //-> Create a transaction... THIS NEXT LINE Compiler warning????
40.   //TransPMSDB.DataBase.Create(ConnectPMSDB);
41.   //-> Point to the database instance...
42.   TransPMSDB.DataBase:= ConnectPMSDB;
43.   //-> Open the database...
44.   ConnectPMSDB.Open;
45. end;
46.  
47. end.
48.  

[cdbc]

Hi
Hmmm, the datamodule creates the components on own creation via streaming... So maybe:

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

1. procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);
2. begin
3.   { Connect to the PMS Database for the PMS application. }
4.   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
5.   //-> Point to the database instance...
6.   TransPMSDB.DataBase:= ConnectPMSDB;
7.   //-> Open the database...
8.   ConnectPMSDB.Open;
9. end;

The above should now correctly connect the transaction to the database...
Regards Benny

edit: Totally forgot, we're working with a datamodule here, which creates the components...

[egsuh]

As cdbc pointed out,

   PMSDBConnect:= TSQLite3Connection.Create(nil);

is not necessary, as you have dropped the component on the datamodule already. Creating and destroying components are managed by "owners". In this case, "ConnectPMSDB" is owned by TDmPMSDataBase. So ConnectPMSDB is created and destroyed when TDmPMSDataBase is created and destroyed.

The order in "uses" clause is generally not important, unless you are overwriting something.

The creation order is important. "Used" modules (whether they are datamodule or form, etc.) should be created before "using" modules are created, whether they are connected in design time or created by your program. 

You have the option to put TSQLQuery for each table on the form that edits the specific table, or put them on the datamodule. I prefer to put them on forms (plus datasource and data controls), but this is just personal choice.

[1HuntnMan]

Makes sense, so the only line of code I need in the DataModule is in the Create:

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

1.   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
2.  

Also, in the PMS.lpr which on compile on the complete app creates the PMS.exe. The PMS.lpr I designed Forms, DMPMS (datamodule), PMSMain (the opening form for the app which has no database usage, it's just Menu and SpeedBtns for the user to open Units for Clients Mgt, Invoices, etc. As far as order, the first unit listed is the DMPMS (datamodule), PMSMain (main for accessing the rest of the units), printer4lazarus, lazcontrols, etc. Next, in the LPR is the Application.Title, scaled, Initialize and then Application.CreateForm(TDmPMSDataBase, DmPMSDataBase)-datamodule first, then CreateForm(TFrmPMSMain, FrmPMSMain)

So, my understanding cdbc, the DataModule has on it's form with the ConnectPMSDB(TSQLite3Connection), the TransPMSDB(TSQLTransaction) and the QueryPMSDB(TSQLQuery) components on it. The Connect has it's Transaction, the Trans has it's ConnectPMSDB and the Query has it's ConnectPMSDB and it's Trans(TransPMSDB).

The only line of code I need in the DataModule OnCreate event is:

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

1. ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';
2.  

because that is assigned to the Database item in ConnectPMSDB.
So, in the PMS.lpr the datamodule and the main form is created after and the datamodule is in memory with the database connected.

As the user clicks on Clients Mgt. all I need to do is have a procedure for Setup to create a query QueryPMSDB to create the connection to the database, QueryPMSDB.database:= the previous ConnectPMSDB and then the transaction. Then on the FormShow, QueryPMSDB.SQL.Text:= 'SELECT * FROM Clients; the Clients table.

If this is the correct order of things, then I can start working on each unit as far as connecting, trans and query the table(s) in each unit called by the PMSMain unit.

[egsuh]

      ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';

Even this, you can assign directly at object inspector of data module. I think you'd better start with "no code" first, without OnCreate event hander for the datamodule. So assign directly the file at object inspector at design time.

Assuming "no codes", you can change the forms' creation time and order at menu "Project>Project Options>Forms".  You will see available forms and auto-created forms. And you can change the auto-created orders there. Auto-created forms will change the content of .lpr file.

Assuming that datamodule has been created, the mainform had better be created at last theoretically if all forms are to be created at the beginning of the application, but the order doesn't matter unless other forms are called at creation.

Let's see a simple example.

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

1. unit uMainform;
2.  
3. uses usubform1, usubform2, usubform3;
4.  
5. type
6.  
7. Tmainform = class
8.     ..
9. end;
10.  
11. var
12.    MainForm : TMainForm;
13.  
14. implementation
15.  
16. procedure TMainForm.OnCreate(Sender:TComponent);
17. begin
18.     SubForm1.SQLquery1.Open;
19.     SubForm2.SQLquery1.Open;
20.     SubForm3.SQLquery1.Open;
21. end;
22.  
23. -----------------------------------------------
24. unit subform1;
25.  
26. type
27.    TSubForm1 = class
28.           ..
29.    end;
30.  
31. ------------------------------------------------
32. unit subform2;
33.  
34. type
35.    TSubForm2 = class
36.           ..
37. ------------------------------------------------
38. unit subform3;
39.  
40. type
41.    TSubForm3 = class
42.           ..
43.  

If all forms are created automatically, all sub-forms should have been created at the time of mainform's creation.

Or, you may manually create other forms yourself.

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

1. procedure TMainForm.OnCreate(Sender:TComponent);
2. begin
3.     SubForm1 := TSubForm1.Create(self);  // by owner=self, you don't have to worry about destruction
4.     SubForm2 := TSubForm1.Create(self);
5.     SubForm3 := TSubForm1.Create(selfl);
6.  
7.     SubForm1.SQLquery1.Open;
8.     SubForm2.SQLquery1.Open;
9.     SubForm3.SQLquery1.Open;
10. end;

As your application gets complex, you'll come to prefer to create forms yourself. But at the beginning I recommend to use auto-creation (I know you are not novice. Just you seem not quite familiar with component-approach).

[1HuntnMan]

Okay, I'm not doing this correctly with what I've been reading in several online documents and examples.
The data module is created at the beginning before the main form. So I'm assuming it's in memory. So, next if it compiles without error, which it didn't, lots of errors the first compile like the DM wasn't in memory. I started first on a Countries management form, it's just a maintenance on one table out of the database to start out simple. Below is the source for this Countries.pas unit. Any reference to the SQL components on the Data Module is an error which I thought was in memory:

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

1.  
2. TDmPMSDataBase = class(TDataModule)
3.     ConnectPMSDB: TSQLite3Connection;
4.     QueryPMSDB:   TSQLQuery;
5.     TransPMSDB:   TSQLTransaction;
6.     procedure DataModuleCreate(Sender: TObject);
7. etc.
8.  

 So, my brain isn't right. The countries.pas and form are accessed from the main unit: PMSMain.  Below is the CountriesMaint.pas with all the errors which it doesn't know anything from the PMSDM data module in memory but it's in the Uses:

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/16/2025}
8.  
9.  
10. {$mode ObjFPC}{$H+}
11.  
12. interface
13.  
14. uses
15.   Classes, SysUtils, Forms, DB, SQLDB, SQLite3Conn, memds, Controls,
16.   Graphics,  Dialogs, ExtCtrls, DBGrids, DBCtrls, Buttons, DMPMS;
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.     procedure FormCreate(Sender: TObject);
29.     procedure FormShow(Sender: TObject);
30.     procedure SpdBtnCloseCntriesClick(Sender: TObject);
31.   private
32.     procedure SetupDB;
33.   public
34.  
35.   end;
36.  
37. var
38.   FrmCntriesMaint: TFrmCntriesMaint;
39.  
40.  
41. implementation
42.  
43. {$R *.lfm}
44.  
45. { TFrmCntriesMaint }
46.  
47. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
48. begin
49.   SetupDB;
50. end;
51.  
52. procedure TFrmCntriesMaint.SetupDB;
53. begin
54.   //-> Create a transaction... ERROR identifier not found
55.   TransPMSDB:= TSQLTransaction.Create(ConnectPMSDB);
56.   //-> Point to the database instance... ERROR identifier not found
57.   TransPMSDB.Database:= ConnectPMSDB;
58.   //-> Open the PMS database... ERROR identifier not found
59.   ConnectPMSDB.Open;
60.   //-> Create a query to return data... ERROR identifier not found
61.   QueryPMSDB:= TSQLQuery.Create(ConnectPMSDB);
62.   //-> Point to the database and transaction... ERROR identifiers not found
63.   QueryPMSDB.Database:= ConnectPMSDB;
64.   QueryPMSDB.Transaction:= TransPMSDB;
65. end;
66.  
67. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
68. begin
69.   //-> Set the SQL select statement... ERROR identifier not found
70.   QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES';
71.   //-> Now use the standard TDataset methods... ERROR identifier not found
72.   QueryPMSDB.Open; // ERROR identifier not found
73.   Screen.Cursor:= crDefault;
74. end;
75.  
76. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
77. begin
78.   try
79.     ConnectPMSDB.Close; // ERROR identifier not found
80.   finally
81.     TransPMSDB.Close; // ERROR identifier not found
82.   end;
83.   FrmCntriesMaint.Close;
84.   {FrmCntriesMaint.Free; Don't need this according to TRon!}
85. end;
86.  
87. end.
88. { EoF: CountriesMaint.pas }
89.  

[TRon]

I make one example:

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

1. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
2. begin
3.   DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES';
4.   DmPMSDataBase.QueryPMSDB.Open;
5.   Screen.Cursor:= crDefault;
6. end;
7.  

If working with a sql database there is (usually) one connection and one transaction. There be only one set of them that are to be used during the lifetime of an application. They are to be setup/configured once (also during the lifetime of an application). There is no reason to create and/or destroy them at runtime. The only things that is in a normal flow is to close the connection and (re)open the existing one (for instance because wanting to load in a different database).

So whatever is done in the create event of TFrmCntriesMaint should be done in the datamodule instead (*). Which was the whole reason to stuff that into a subroutine as that is easier to copy-paste and to change it so that it is not invoked at creation time of the forms/datamodules because that is nigh impossible to debug.

(*) but because now a datamodule is in place on which the components where placed at designtime there is no need to create the components as they already exist. So only set the properties.

[1HuntnMan]

Okay TRon, et.al., geeze-luezze! I have 4 forms working with SQLite!  The only form was the Countries form and no errors but the whole DBGrid is displaying Memo in the country abrev. column and the country name column.  You mentioned that before but I have no idea why because 3 of these lookup maintenance tables, States/Provinces, PayStatus, JobTypes are displaying the test data from the SQLite database. Hmmm Well, just going to delete the grid and add a new one. Like I did when you helped me with just a single table app. that just was the countries table.

[TRon]

Okay TRon, et.al., geeze-luezze! I have 4 forms working with SQLite!

Good and congratz ! 

The only form was the Countries form and no errors but the whole DBGrid is displaying Memo in the country abrev. column and the country name column.  You mentioned that before but I have no idea why because 3 of these lookup maintenance tables, States/Provinces, PayStatus, JobTypes are displaying the test data from the SQLite database.

There are several causes/fixes (or a mixture thereof). See also here

[egsuh]

Hello,
I made a very simple program to test SQLite with minimal typing of codes.
There are
 - datamodule, which has TSQLite3Connection and TSQLTransaction
 - mainform, which has mainform. By clicking menu, each sub-form will be shown.
 - form1 : Artists form.
 - form2 : Album form.

Each of form1 and form2 has DB-related components, TSQLQuery, TDataSource, TDBGrid and TDBNavigator. When each form is shown, its SQLQuery is opened.

I tried to set SQLQueries ACTIVE at design time. This is OK when you first develop the application, but it DOES NOT RE-OPEN the .LFM file afterwards. Lazarus itself is not responding.  Should we regard this as a bug? Not a bug in itself, but something needs to be done. I opened ufartist.lfm and ufalbum.lfm with plain text editor and removed Active=true of each sqlquery, and then was able to open the Lazarus project.

I'll attach database file at next.

[egsuh]

Chinook db file.. which was provided by DBeaver.