Converting a Project from TDbf to SQLite [SOLVED]

[Zvoni]

You know, just following this thread from its inception to now, a feeling gets more and more pronounced:
Are you trying to code with sqlite the same way you did with DBF?
Because if you do, then it's no surprise you're struggling so much....

[TRon]

@Zvoni: Yes, I have the same vibe and got that vibe fairly quick from the start of the discussion. Indeed, that is where all the struggling for TS comes from. I do not know how to properly reset that mind-set from dbf to sql (tbh it has been such a long time since I last used classic dbf tables that I can't remember).

One thing is clear, TS does not seem to grasp that a SQL query is in fact a dataset and can be treated as such (if one wished to do so) and that sql statements are able to collect many data from many tables depending on whatever condition necessary.

Not a problem, we all started somewhere, but the sqldb tutorial does cover most topics (though perhaps not the most user-friendly introduction to SQL)

[CharlyTango]

it has been such a long time since I last used classic dbf tables that I can't remember).

It's a switch from a record-based thinking and positioning database pointer in a table to am more independent mass-data-thinking

[CharlyTango]

Forgive me, but I don't understand the problem.
This contact form is a very common one and not even particularly complex. I can tell you what it would look like for me.

In a data module is the complete access logic to the SQLite database. Including possible logging, reading the access data from a separate configuration file (e.g. an INI file) and other useful things that you need.

In the form, there is a separate duo of TDataset and TSQLQuery for each SQL query that is to exist in parallel with other queries.
In the FormActivate event, I assign the database connection to the TSQLQueries as well as the SELECT Statements

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

1. SQLQueryContacts.Database:=Datamodule_SQConnect.SQLiteConnection1;
2. SQLQueryContacts.SQL.Text:='SELECT......';
3. SQLQueryContacts.Open;
4.  

[CharlyTango]

Pictures to previous post

[Zvoni]

Not a problem, we all started somewhere, but the sqldb tutorial does cover most topics (though perhaps not the most user-friendly introduction to SQL)

it's neither the job of sqldb nor of the FreePascal-Wiki to "teach" SQL.
There are enough tutorials out there.
Nevermind, that SQL itself is not dependant on which programming language you use.
It's only dependant on which Database-System you use, and even there the only differences are DBMS-specific "dialects" for specific stuff.
SQL is pretty much "standardized"

it has been such a long time since I last used classic dbf tables that I can't remember).

It's a switch from a record-based thinking and positioning database pointer in a table to am more independent mass-data-thinking

I'd compare it more to:

DBF:
you have three boxes: 1st has bolts in different sizes, 2nd has nuts in different sizes, 3rd has washers in different sizes
You pick a bolt from box 1, then you look into box 2 and 3 and search for corresponding nut and washer

SQLite (or any modern DBMS):
you have one box: bolts, nuts, washers in different sizes in there in no particular order
You tell sqlite: "Oy, i need a Bolt Size 6 with corresponding nut and washer"

Bottom line: With SQL you tell the Database what you want across all "categories", in DBF it's a "manual" job you have to do yourself

Yeah, not the best analogy, but *shrug*

[TRon]

It's a switch from a record-based thinking and positioning database pointer in a table to am more independent mass-data-thinking

True though I think I prefer Zvoni's analogy a bit better but it seems that the issue is how do you make that clear to someone who is (still) in that dbf mind-set.

I mean, looking back at it dbf tables feel outdated and nearly impractical to work with even back then when you ran into all kinds of limitations that required tedious workarounds which SQL is capable to solve in a heartbeat (I am not an SQL expert either but am able to get by and still am amazed at some of the ingenious queries that are sometimes posted on the forums).

it's neither the job of sqldb nor of the FreePascal-Wiki to "teach" SQL.
...

Fair enough, though I believe in this particular case it seems more about how things interact (GUI/SQL(DB)) in order to be able to convert a project from DBF tables to SQL.


The use of SQLDataset, although perhaps familiar to TS, isn't particularly helpful in making that conversion (though at first sight it probably seems like a good idea in order to be able to do a quick conversion).

Although you have to start somewhere I also am not very positive about learning about SQL(DB) by converting a project that uses DBF tables. imho that shouldn't be a first experience with SQL and its components. Better would be to get familiar with how SQL works and based on the gained knowledge improve (rewrite) the existing project. Something with burned bridges 

[Zvoni]

Although you have to start somewhere I also am not very positive about learning about SQL(DB) by converting a project that uses DBF tables. imho that shouldn't be a first experience with SQL and its components. Better would be to get familiar with how SQL works and based on the gained knowledge improve (rewrite) the existing project. Something with burned bridges 

Exactly my thoughts.
To make matters worse is TS' approach to use those blasted "visual" components (don't get me wrong: They do have their uses), them probably "hiding" anything to do with SQL
(Think connecting a DBGrid to a Datasource, the Datasource itself connected to a Table instead of a SQL-Statement)

[egsuh]

If you have a program with DBF, SQLite3DataSet must be the same as using DBF.  I think it's good idea to first set up with SQLite3Dataset, instead of TSQLQuery.

With SQLite3DataSet, at the object inspector,

1.  Fill in Filename with full path, like c:\mydata\.....db.
2.  Type in TableName.
3.   Test it by clicking Active. It should turn to (True).
4.  Type in PrimaryKey name, or IndexFieldNames. In IndexFieldNames, field names are separated by semi-colon. Like "ID;OrderID;Name". (You may do this later).

5.  Drop a datasource. In the datasource, set the dataset to your sqlite3dataset.
6.  Now drop a DBGrid. Set DBGrid's datasource to the datasource at 5.
     You can set many DBcontrols (e.g. DBEdit) to the same datasource.

Now select SQLite3DataSet again, and switch between Active/not active. You'll see that the contents are shown and disappear in the DBGrid, not at runtime, but at design time.

If you need to open four tables at the same time, you need four datasets (whether they are SQLite3DataSet or SQLQuery).

[Zvoni]

If you have a program with DBF, SQLite3DataSet must be the same as using DBF.  I think it's good idea to first set up with SQLite3Dataset, instead of TSQLQuery.

With SQLite3DataSet, at the object inspector,

1.  Fill in Filename with full path, like c:\mydata\.....db.
2.  Type in TableName.
3.   Test it by clicking Active. It should turn to (True).
4.  Type in PrimaryKey name, or IndexFieldNames. In IndexFieldNames, field names are separated by semi-colon. Like "ID;OrderID;Name". (You may do this later).

5.  Drop a datasource. In the datasource, set the dataset to your sqlite3dataset.
6.  Now drop a DBGrid. Set DBGrid's datasource to the datasource at 5.
     You can set many DBcontrols (e.g. DBEdit) to the same datasource.

Now select SQLite3DataSet again, and switch between Active/not active. You'll see that the contents are shown and disappear in the DBGrid, not at runtime, but at design time.

If you need to open four tables at the same time, you need four datasets (whether they are SQLite3DataSet or SQLQuery).

A big NO!
That's like trying to maintain a Tesla-Car, wondering why there is no Fuel-Tank like you're used to from your 69 Corvette
Both are cars.....

[egsuh]

First make a running app with sqlitedataset and then replacing them with sqlquery one by one could be an approach.

[TRon]

Yes egsuh but it is imho the wrong way of learning SQL.

And fwiw I also agree with Zvoni about the visual component crap. Nice for a demonstration but it has no business in real world applications nor is it suitable to introduce someone to the topic.

Each and everytime I see 'code' that opens a databse at form creation my skin crawls (let alone the database is already opened when streaming the components, see also the nice C:\blah/blah file on my Linux machine when used your example (which otherwise is nice)).

2 cents (or perhaps even less)

[egsuh]

Learning SQL is not the purpose. Running application is the first target. That's what Rapid Application Development tool intends to do.

[TRon]

This is a perfectly good example of the Achilles' heel of RAD. But I get it, we are never going to see eye to eye on that topic. It is ok 

[1HuntnMan]

Okay, ya'll are all correct, I had 2 apps working just fine with dbf. But, learning from the Lazarus site, a lot of folks recommending to change to SQLite3. I'm doing that but all the online docs do not explain just how to setup a simple app in the design very well, not even getting to the code. I quit with my larger project - the Photographer's Mgt. System because a lot to convert. I have a Contact's Mgt. little project I did last year when I was learning Lasarus. So, learning mostly what EGSUG recommended, finally have the SQLite3DataSet installed. Here's what I've done so far starting with the lpr, the dm and only one form, the CntksMainFrm. You can tell about the forms by just looking at the code:

Contactsmgt.lpr

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

1. program ContactsMgt;
2. {Program....: ContactsMgt.lpr
3.  Author.....: Donald King
4.  Date.......: 30 Nov. 2023
5.  Copyright..: Copyright(c) 2024 NewFound Photo Art, Inc.
6.  On the Web.: https://www.NewFoundPhotoArt.com
7.  Last Update: 04/02/2025}
8.  
9. {$mode objfpc}{$H+}
10.  
11. uses
12.   {$IFDEF UNIX}
13.   cthreads,
14.   {$ENDIF}
15.   {$IFDEF HASAMIGA}
16.   athreads,
17.   {$ENDIF}
18.   Interfaces, // this includes the LCL widgetset
19.   Forms, datetimectrls, printer4lazarus, sqlite3laz, lhelpcontrolpkg,
20.   { Main Unit added below ... }
21.   CntksDM, CntksMain;
22.  
23. {$R *.res}
24.  
25. begin
26.   RequireDerivedFormResource:=True;
27.   Application.Title:='ContactsMgt';
28.   Application.Scaled:=True;
29.   Application.Initialize;
30.   Application.CreateForm(TDMCntks, DMCntks);
31.   Application.CreateForm(TFrmCntksMain, FrmCntksMain);
32.   Application.Run;
33.   { If the compile errors at the end statement below, Contacts Mgt.
34.     is running and in memory! Shut it down and then compile/run!}
35. end.
36.  

Now the datamodule: CntksDM

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

1. unit CntksDM;
2.  
3. {$mode ObjFPC}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, SQLite3Conn, SQLDB, Db;
9.  
10. type
11.  
12.   { TDMCntks }
13.  
14.   TDMCntks = class(TDataModule)
15.     ConnectCntksDB : TSQLite3Connection;
16.     QueryCntksDB   : TSQLQuery;
17.     TransCntksDB   : TSQLTransaction;
18.     procedure DataModuleCreate(Sender: TObject);
19.   private
20.  
21.   public
22.  
23.   end;
24.  
25. var
26.   DMCntks: TDMCntks;
27.   CntksDB: string;
28.  
29. implementation
30.  
31. {$R *.lfm}
32.  
33. { TDMCntks }
34.  
35. procedure TDMCntks.DataModuleCreate(Sender: TObject);
36. begin
37.   ConnectCntksDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + ContactsDB.sqlite3);
38.   //-> Create a transactions...
39.   TransCntksDB:= TSQLTransaction.Create(ConnectCntksDB);
40.   //-> Point to the database instance...
41.   TransCntksDB.DataBase:= ConnectCntksDB;
42.   //-> Open the Contacts database...
43.   ConnectCntksDB.Open;
44.   //-> Point to the database and transaction...
45.   QueryCntksDB.DataBase:= ConnectCntksDB;
46.   QueryCntksDB.Transaction:= TransCntks;
47. end;
48.  
49. end.
50.  

Now the main form, CntksMain.pas but just important stuff, I have a TSQLite3DataSet for each table; Contacts, States, Countries and Categories. States, Countries and Categories are just for DBLookupCombos. Each TSQLite3DataSet has a corresponding TDataSource.

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

1. unit CntksMain;
2. { Program....: CntksMain.pas
3.   Author.....: Donald King
4.   Date.......: 30 Nov. 2024
5.   Copyright..: Copyright(c) 2024, 2025 NewFound Photo Art, Inc.
6.   On the Web.: https://www.NewFoundPhotoArt.com
7.   Last Update: 01/04/2025 }
8.  
9. {$mode objfpc}{$H+}
10.  
11. interface
12.  
13. uses
14.   Classes, SysUtils, Dbf, DB, SQLite3Conn, SQLDB, SQLite3DS, DBGrids, DBCtrls,
15.   Forms, Controls, Graphics, Dialogs, ExtCtrls, Buttons, StdCtrls, Menus,
16.   ExtDlgs, ActnList, MaskEdit, LR_DBSet, LR_Class, JvDBLookup, JvDBSearchEdit,
17.   SynHighlighterHTML, PrintersDlgs, jdblabeleddateedit,
18.   { Custom units added below... }
19.   CatMaint, CntriesMaint, StatesProvMaint, AboutCntkMgt, CntksMgtLicense,
20.   Rebuildtables, Windows, LCLIntf, CntksDM;
21.  
22. Then skip down to past private, public ...
23. var
24.   FrmCntksMain: TFrmCntksMain;
25.   Keys: TStringList;
26.   IntRecCount: Integer;
27.  
28. procedure TFrmCntksMain.FormCreate(Sender: TObject);
29. begin
30.   //-> Add items to the CmboBxSelIndex component
31.   CmboBxSelIndex.Items.Clear;
32.   CmboBxSelIndex.Items.Add('Name (L,F,M)');
33.   CmboBxSelIndex.Items.Add('Company');
34.   CmboBxSelIndex.Items.Add('Category');
35.   CmboBxSelIndex.Items.Add('State/Province');
36.   CmboBxSelIndex.Items.Add('Country');
37.   CmboBxSelIndex.Items.Add('City, State, Country');
38. end;
39.  
40. procedure TFrmCntksMain.FormShow(Sender: TObject);
41. begin
42.   SQLite3DSCntks.IndexFieldNames:= 'CNTKNAME';
43.   //SQLite3DSCntks.Open;
44.   SQLite3DSStates.IndexFieldNames:= 'STPROVCODE';
45.   //SQLite3DSStates.Open;
46.   SQLite3DSCntries.IndexFieldNames:= 'CODE';
47.   //SQLite3DSCntries.Open;
48.   SQLite3DSCategories.IndexFieldNames:= 'CATEGORIES';
49.   //SQLite3DSCategories.Open;
50.   DSStates.DataSet.FieldByName('STPROVCODE').DisplayWidth:= 2;
51.   DSStates.DataSet.FieldByName('STPROVNAME').DisplayWidth:= 12;
52.   DSCntries.DataSet.FieldByName('CODE').DisplayWidth:= 2;
53.   DSCntries.DataSet.FieldByName('COUNTRY').DisplayWidth:= 10;
54.   DSCategories.DataSet.FieldByName('CATID').DisplayWidth:= 2;
55.   DSCategories.DataSet.FieldByName('CATEGORY').DisplayWidth:= 25;
56.   BitBtnSaveCntk.Enabled:= False;
57.   BitBtnCancel.Enabled:= False;
58.   EditTTLCntks.ReadOnly:= False;
59.   IntRecCount:= DbfCntks.ExactRecordCount;
60.   EditTTLCntks.Text:= IntToStr(IntRecCount); //-> Show total contacts...
61.   EditTTLCntks.ReadOnly:= True;
62. end;
63.  

The rest of the code is just bitbtnSave, Cancel, AfterCancel, AfterDelete, and selecting/changing the Index. The SQL I put the the SQLite3DataSets just to be able to get it to work and see some kind of success.
What do ya'll think.