Converting a Project from TDbf to SQLite [SOLVED] lazarus / 16-Databases ---------------------------------------------------------------------- 1HuntnMan · 2024-11-06 19:58 Has anyone accomplished this before and recommend the best way to approach this. I'm thinking to just write the code to create the database.  Then get that accomplished. Then start educating myself on changing all the forms????? I'm researching and finding: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DestTable.FieldByName(s).Value := SourceTable.FieldByName(s).Value;  I'm thinking to just create the tables one by one. Trash the test data and modify the forms for SQLite3. ---------------------------------------------------------------------- egsuh · 2024-11-07 03:19 I'd rather think of dbexport functions. The first that comes to mind is exporting existing file to csv file first, and then loading that csv file. You'd better check with TSQLiteDataSet of Lazarus. --- I think I have been importing CSV files directly to other DB formats, but need to check. ---------------------------------------------------------------------- cdbc · 2024-11-07 05:39 Hi I'd write a little utility, that takes a '.dbf' file (a table) as input param and then 1) Connect to a new 'sqlite3' database, e.g.: named like the project,    or connect to an existing database. 2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will    create a similar(copy of) table in the sqlite3 db and execute it. 3) Runs through the 'dbf' file, reads the data and insert it into the newly    created sqlite3 table. 4) Closes the database and the 'dbf' table/file ...and we're done. 5) "Rinse and repeat" for every 'dbf' table you have. This is called a /datapump/ utility. When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/  Have fun Benny ---------------------------------------------------------------------- egsuh · 2024-11-07 07:07 I found the simplest method (conceptually, at least). You can call TSQLQuery.CopyFromDataSet, and then its ApplyUpdates. You would have to CreateDataSet of SQLite tables, but I think it would be also possible to copy the structure of DBF tables. In this way you don't have to write any codes that process each field and each record by yourself. ---------------------------------------------------------------------- Zvoni · 2024-11-07 07:15 Quote from: cdbc on November 07, 2024, 06:39:27 amHi I'd write a little utility, that takes a '.dbf' file (a table) as input param and then 1) Connect to a new 'sqlite3' database, e.g.: named like the project,    or connect to an existing database. 2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will    create a similar(copy of) table in the sqlite3 db and execute it. 3) Runs through the 'dbf' file, reads the data and insert it into the newly    created sqlite3 table. 4) Closes the database and the 'dbf' table/file ...and we're done. 5) "Rinse and repeat" for every 'dbf' table you have. This is called a /datapump/ utility. When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/  Have fun Benny Be careful with this approach. We don't know anything about Parent/Child-Tables/Relations (PrimaryKey/ForeignKey) of his design. That said: Parent-Table before Child-Table Meaning: Even the order in which you "export/import" any DBF-Tables to SQLite is important. (Yes, i know there is a way in which you can ignore the order, but ... seriously???) Might even be worth to check your design/layout of the Database- Structure and rework it if neccessary EDIT: My Approach. Create your SQLite-Database with an external tool (DBeaver, DB Browser for SQlite et al). Create your Tables, Columns, Primary Keys, Foreign Keys, Constraints, Indexes whatever according to the Datatypes used for each Column. Then use the CSV-Approach mentioned by egsuh exporting the Data in the DBF-Tables (IIRC DBeaver should be able to do that, if not event directly migrating it to SQLite) and use the "Import CSV" which is available in DB Browser for SQlite. That way you would be able to catch any "malformed" original Data. Special care has to be provided for the "difficult" Datatypes, specifically DateTime ---------------------------------------------------------------------- jcmontherock · 2024-11-07 09:54 I found an export example. I do not remember where, but it seems to work. ---------------------------------------------------------------------- 1HuntnMan · 2024-11-09 17:06 Thanks all, on the way. But, will try the export example to see how that works from jcmontherock. ---------------------------------------------------------------------- 1HuntnMan · 2025-01-26 16:09 Instead of tackling my larger Photographer's Mgt. System, I designed a contact mgt. system for myself when I was first learning Lazarus for my Windows PC since Winders doesn't provide a Contacts App. like Apple.  This way I'm just converting 1 Contacts data table and 4 lookup tables. Thanks ... ---------------------------------------------------------------------- 1HuntnMan · 2025-02-13 15:30 Quote from: 1HuntnMan on January 26, 2025, 05:09:28 pmInstead of tackling my larger Photographer's Mgt. System, I designed a contact mgt. system for myself when I was first learning Lazarus for my Windows PC since Winders doesn't provide a Contacts App. like Apple.  This way I'm just converting 1 Contacts data table and 4 lookup tables. Thanks ... Just a FYI, I already built a Database Creation procedure for this Contacts app but decided to abandon converting it SQLlite3 because it's just one table with supporting lookup tables, i.e. States, Countries, Contact Types. Basically a larger app I'm working on already has a Database Schema creation procedure and took that as a separate app and converted all tables from Dbf to SqlLite3 and took each dbf file as input and connect to sqlite3 and wrote the sqlite code via TSQLiteDataSet from a dump from dbf to csv. Created sql statements that copies the structure into each sql table. Then, continue the march for the rest of the tables all in one procedure. A little more involved but it's doable... ---------------------------------------------------------------------- 1HuntnMan · 2025-02-21 20:15 I have just completed migrating a dBase Level 7 Database to SQLite. The Laz Project is still dBase Level 7. I picked out a form that is just a maintenance form for StatesProvinces.  In Lazarus the only SQL Components are the SQLdb and I just see one component which is the TSQLite3Connection. The SQLite3 Database isn't password protected.  This system is a Photographers Mgt. application and probably won't be used by more than maybe 7-8 users but mostly 1-2.  So, what is HostName, KeepConnection (T/F), OpenFlags(sofCreate, sofReadWrite, etc.) don't know what to check here except Create and ReadWrite. Options (ApplyUpdates=True) ExplicitConnection=False? Don't need UserName. Once the user launches the form then and can just Select All. It's just a DBGrid just form maintaining States, Name and ISO Code. My question is, is there a document that explains just implementing and creating forms, etc. for SQLite3? ---------------------------------------------------------------------- TRon · 2025-02-21 20:34 Quote from: 1HuntnMan on February 21, 2025, 09:15:06 pmMy question is, is there a document that explains just implementing and creating forms, etc. for SQLite3? Not a single document but a ton of wiki-pages starting with this but you are probably more interested in this by the looks of your question. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-21 20:40 Alright TRon, looking at the SQLdb Tutorial1 now. Looks like that is just what the Doctor (Tron) Ordered! Thanks ... ---------------------------------------------------------------------- TRon · 2025-02-21 20:51 As an additional advise, pleas don't try to do this on your working project in case this is your first time around the subject. It will save a lot of heartache. Better approach would be to make a small test-project that allows to follow the wiki step-by-step in order to get a better understanding on how things work. That allows for a lot of experimenting without the risk that you mess up the project that you currently are trying to convert. In case you get stuck on a certain component and/or topic then you can ask specific questions or practice some yolo-ing in case feeling adventurous ---------------------------------------------------------------------- 1HuntnMan · 2025-02-22 12:45 Yup, I was thinking about doing this on a Contacts Mgt system I wrote for my Winders PC. iPhones, iPads and the Mac come with a Contacts Mgt app but Winders doesn't. It's database TDbf just has 4 tables. Contacts, State/Provinces, Countries and Categories. The States, Countries and Categories are just lookup tables for the contacts. But, in this Photographer's Mgt. System there are a lot of tables so I was just converting one form to SQLite3 which is where the app user maintains countries. It has just one table with a TDbf DataSet and DataSource. So, reading the tutorial it appears to use a Datasource and a SQLiteConnection to get started. But, I think I'm just going to create a small project that just has a DBGrid and strickly just Countries. I've already converted the tables to SQLite tables for all the tables in both the Contacts Mgt. app and the Photography Mgt. app. with the data using DBCopier. Thanks. TDbf is alot easier for starting out like I did 6 months ago so quite the learning curve converting to Sql. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-23 16:08 TRon et. al.,    I found a couple of demo's for SQLite named Demo and Demo2.  I loaded the Demo project into Lazarus just to see how everything was setup and worked. I compiled it and ran it but it errors, see the screen shots attached.  In Laz in the SQLdb components there TSQLiteConnection.  According to the errors, I need to install the SQLite client library(dll), correct? The errors occur from the main form if you attempt to Create a SQLite Database or Load one. The demo comes with a SampleData.sqlite file. Do I need to install the SQLite Client Library (dll)? It appears so but finding online where folks are having the same issue with the 64bit dll. I searched my computer and there are 7 sqlite3.dll's on my computer. The latest one (64bit) is located under C:\Program Files\TurboTax\Individual2024\64bit. Do I need to install or do I just need to copy the sqlite3.dll from the TurboTax folder into the Demo's folder's. I'll try that but my original question is do I need to install for Lazarus or not? ---------------------------------------------------------------------- 1HuntnMan · 2025-02-23 16:12 Okay, that works... I copied the SQLite3.dll from TurboTax 64 bit folder to my demo and the demo app works. So will use that demo to design a learning project. ---------------------------------------------------------------------- TRon · 2025-02-23 16:13 Hi 1HuntnMan, If you are on windows then the easiest way (to check/verify) is to copy the dll next to your executable. In ideal circumstances the dll should be stored in your systems library folder(s) (there is a dedicated 32-bit and 64-bit folder on windows) so that you only need one copy which all programs that uses sqlite are able to locate/load. edit: our posts crossed each other ---------------------------------------------------------------------- 1HuntnMan · 2025-02-23 16:25 So my computer is Windows 11 Pro. So, need to copy the dll to both the Windows\System folder and also the Windows\System32 folder? Just checking both those folders I was surprised that the System folder is empty except for a Speech folder. Everything appears to be under System32 but my computer is Windows 11 Pro installed on a 64-bit Acer with Intel CORE i5 processor. Ummm ... ---------------------------------------------------------------------- TRon · 2025-02-23 16:38 For more information see this SO question and answers, and please ignore the ramblings of the answer about not using system folders for 3th party libraries because that is nonsense. A longer read can be found in this blogpost. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-23 20:39 Wow, like SysWOW64, huh! Well, quite the conversations. I guess the best place to put the SQLite3.dll is in the System32 and not the SysWOW64 or the System folders. I didn't see any conversations about the System folder. Just ran the demo with the SQLite3.dll moved to the System32 folder.  It works fine... So, it appears when I get back to converting my app from TDbf to SQLite3 I'll need to include the SQLite3.dll with my Inno Setup once the app is completed and tested. Thanks!!! ---------------------------------------------------------------------- Zvoni · 2025-02-24 07:22 Quote from: 1HuntnMan on February 23, 2025, 09:39:09 pmWow, like SysWOW64, huh! Well, quite the conversations. I guess the best place to put the SQLite3.dll is in the System32 and not the SysWOW64 or the System folders. I didn't see any conversations about the System folder. Just ran the demo with the SQLite3.dll moved to the System32 folder.  It works fine... So, it appears when I get back to converting my app from TDbf to SQLite3 I'll need to include the SQLite3.dll with my Inno Setup once the app is completed and tested. Thanks!!! The best place to put the sqlite3.dll is beside your EXE or in a subfolder of your program. If you need the dll machine-wide, use ProgramData, and use "sqlite3dyn.SQLiteDefaultLibrary" in your Program Only use system-folders with a certified installation-setup EDIT: And remember c:\Windows\System32 is for the 64-Bit libraries, the SysWOW64 is for the 32-Bit libs ---------------------------------------------------------------------- 1HuntnMan · 2025-02-25 20:24 Yup, reading about Microsoft creating SysWOW64 for 32-Bit libs, now Spock would say, "That's not Logical!" And Microsoft designed 64-Bit libs to go into the System32 folder, again Spock would say, "That's not Logical!" ---------------------------------------------------------------------- 1HuntnMan · 2025-02-26 16:21 Okay, starting to convert an app (Photographer's Mgt. System) from TDbf --> SQLite3. Download and reading thru multiple docs online I am starting with just one form/unit that's just a maintenance form for Countries. It's just a lookup table used thru out the app on forms such as Clients, Contacts, etc. Anyway, the maint. form is just a DBNavigator, DBGrid and a close btn. Simple to migrate but my thinking reading different docs online.  My Database has been migrated from TDbf Level 7 to SQLite3 with all the tables and indices. I used DBCopier which did the job instead of writing it all out in a prg. So, put a SQLite3Connection on the form which I named SQL3PMSDB, SQLQueryCntries, SQLTransactionCntries and a Datasource (DSCntries) on the form which was already built. Eliminated the DBf from the Uses.  All I have in the Uses is Db, SQLite3Conn and SQLDB for this form. Below is the modifications which are just 3 Procedures. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  SQLite3PMSDB:= TSQLite3Connection.Create(nil); <--Error  SQLite3PMSDB.Connected:= True;  //->SQLite3PMSDB.DatabaseName:= PMSDB;end; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  SQLQueryCntries.SQL.Text:= 'select * from COUNTRIES';  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  SQLQueryCntries.Close;  finally    SQLTransactionCntries.Active:= False;    SQLite3PMSDB.Connected:= False;  end;  FrmCntriesMaint.Close;  FrmCntriesMaint.Free;end; Connection=SQLite3PMSDB0 SQLQuery-DataBase=SQLite3PMSDB (name of the database for whole system_ SQLTransaction-Database=SQLite3PMSDB, Transaction=SQLTransactionCntries DataSource-DSCntries.DataSet=SQLQueryCntries Looking at the code snippet, can't understand the error. ---------------------------------------------------------------------- paweld · 2025-02-26 16:56 You did not specify the name of the database file, modify the code as follows Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  SQLite3PMSDB := TSQLite3Connection.Create(nil);  SQLite3PMSDB.DatabaseName := 'c:\path\to\sqlite\database\file.db';  SQLite3PMSDB.Connected := True;end; ---------------------------------------------------------------------- TRon · 2025-02-26 20:41 @1HuntnMan: Looking at the posted code I foresee a potential problem. Seems that you are approaching SQL from the same point of view as traditional dbf tables which SQLite isn't. If not the case then please ignore what I write. The code seem to suggest that every time the form is created the SQLConnection is created and every time a country was 'picked' the connection and query are closed again. The 'normal' way of handling these kind of things is to make at least the connection globally accessible (can be as a global var, can be in a datamodule, can be in a form and done at the time it is actually needed) creating and destroying the connection once during the life- time of the program. Each time a "table" from the SQL database is needed then run a query on the SQL database selecting the table. The queries can also be global or created ad-hoc and locally when required (which, if not mistaken, would be a better approach for your countries selection form). ---------------------------------------------------------------------- 1HuntnMan · 2025-02-26 21:32 I corrected the path, no errors but the form->DBGrid and DBNav are inactive as if no data was found. The path to the SQLite3PMSDB is correct, Hmmm. I have 3 SQL components on the form and a DataSource:    TSQLite3Connection named SQLite3PMSDB, TSQLQuery named SQLQueryCntries, TSQLTransaction named SQLTransactionCntries and a DataSource named DSCntries. The SQLite3PMSDB has the complete path to the SQLite3PMSDB and the Transaction has the SQLTransactionCntries. The SQLQueryCntries has the SQLite3PMSDB, the filename with the path and database, and the Transaction SQLTransactionCntries. The TSQLTransaction has the Database Name=SQLite3PMSDB and the DataSource- DataSet property has the SQLQueryCntries. What else? I corrected the code in the  FromCreate procedure for the SQLite3PMSDB.DatabaseName:= to include the complete path to the PMSDB. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  SQLite3PMSDB := TSQLite3Connection.Create(nil);  SQLite3PMSDB.DatabaseName :=  'C:\Users\DKing\Documents\Software Development\PMS Project\PMS Data\PMSDB.sqlite';  SQLite3PMSDB.Connected := True;end; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  SQLQueryCntries.SQL.Text:= 'select * from COUNTRIES';  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  SQLQueryCntries.Close;  finally    SQLTransactionCntries.Active:= False;    SQLite3PMSDB.Connected:= False;  end;  FrmCntriesMaint.Close;  FrmCntriesMaint.Free;end;  Thanks TRon, I was just attempting to get just one form to work and then tackle the rest of the units. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-26 21:35 I'm crosschecking the PMSDB opening it with DB Browser (SQLite) and I see all the tables and indexes in the database and can view the data. ---------------------------------------------------------------------- dseligo · 2025-02-26 22:32 Quote from: 1HuntnMan on February 26, 2025, 10:32:17 pmWhat else? I corrected the code in the  FromCreate procedure for the SQLite3PMSDB.DatabaseName:= to include the complete path to the You don't have SQLQueryCntries.Open; anywhere in your code. If this doesn't help, then post complete project so we don't have to guess what you have done wrong. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  SQLQueryCntries.SQL.Text:= 'select * from COUNTRIES';  SQLQueryCntries.Open;  Screen.Cursor:= crDefault;end; ---------------------------------------------------------------------- JanRoza · 2025-02-27 13:37 If you have your database in the same folder as your program then you could also use the following in your mainform.create procedure to be independent of a location: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // Database standaard in program folder  strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'yourdatabasename.db';  This way your program keeps working even when you relocate is to another location. Just my 5 cents of advice. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-28 16:16 Okay, added the Open but different error, see the attachment after I added the Open statement advised by dseligo. I spent last night going thru learning SQL from https://www.w3schools.com/sql/. That was easy and learned a bit but my issue is just getting to the point where the Database is open and accessable without Laz hiccuping.  I'm going to just start from scratch with just one form separate from this complete application running on TDbf. Also, I didn't install anything for Lazarus, just the SQLiteConnection from the SQLdb.  Reading this from a doc online called SQLite. Direct access to SQLite [You can use an easy way to connect SQLite with Lazarus. Components you are named LiteDAC. SQLite Data Access Components (LiteDAC) is a library of components that provides native connectivity to SQLite from Lazarus (and Free Pascal) on Windows, macOS, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. LiteDAC is designed for programmers to develop truly cross-platform desktop and mobile SQLite database applications with no need to deploy any additional libraries. You can download a trial version of this commercial product at Lazarus components.] Is this something you have to purchase and install? I'll be back in awhile after I create a separate app that just queries by Database for just one table. ---------------------------------------------------------------------- 1HuntnMan · 2025-02-28 16:26 JanRoza,      Thanks for that bit of knowledge, awesome! ---------------------------------------------------------------------- TRon · 2025-02-28 17:19 Quote from: 1HuntnMan on February 28, 2025, 05:16:11 pmOkay, added the Open but different error, see the attachment after I added the Open statement advised by dseligo. dseligo also mentioned something else  It is clearly to do with how the project setup the database components in the designer and how (in which order) things are invoked. That is why I personally never use the designer to setup/access databases but instead connect everything in code because that way you can not forget to set a single property (and you have to verify/check those for every database component used in a project which literally is a time waster). So, instead of providing snippets of code and an error-message please provide a full working/compilable example. QuoteI'll be back in awhile after I create a separate app that just queries by Database for just one table. It is literally a 5 minute job, see also f.e. this message And yes, I understand that it is easy to say so if you know howto but there are so many examples provided with Lazarus and FPC not to mention the wiki. PS: yes, litedac is a commercial product so payware. Never used it so no idea about specifics other than it statically links in sqlite. ---------------------------------------------------------------------- dseligo · 2025-03-01 02:54 Quote from: 1HuntnMan on February 28, 2025, 05:16:11 pmOkay, added the Open but different error, see the attachment You mentioned that you are using 'DB Browser'. If you have opened PMSDB.sqlite database in 'DB Browser' it probably won't work simultaneously with you Lazarus/FPC program. You must close 'DB Browser' prior to running your program. ---------------------------------------------------------------------- egsuh · 2025-03-01 09:11 QuoteOkay, added the Open but different error, see the attachment after I added the Open statement advised by dseligo. I experienced the same difficulty with SQLite3, which would not have happened with Firebird, etc. I think this is because SQLite is single user DB. The database should not be opened by DB Browser, nor by  Lazarus editor (i.e. at the object inspector) to be opened by "running" application itself. Even after I close all possible db connection with SQLite3 database, still I met the same error sometimes. I closed Lazarus itself and then re-opened, and then I could do it right. Not sure exactly what have caused the problem. In most cases I use Firebird. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-01 15:02 This morning I just created a simple app with one form to connect to the SQLite3 database and one form with DBNav and a DBGrid.  I don't think I have DB Browser installed??? Also, JanRosa recommended putting the line below in the main form so it's global to the app which good advice. Would this line go into the main form under VAR as a globally defined variable or just under Public? I think I tried that earlier this morning but Laz errors. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i strDBCntries:= SysUtils.ExtractFilePath(ParamStr(0) + 'Countries.db'; From what I understand, I need to add 4 components to the form with the DBNav/DBGrid.  A TSQLite3Connection, TSQLQuery, TSQLTransaction and a DataSource. I think what TRon is recommending from the link is I don't need put these on the form just define them as variables ... Back to to testing, I'll let you know in awhile, thanks everyone for your great advice??? ---------------------------------------------------------------------- TRon · 2025-03-01 15:17 Quote from: 1HuntnMan on March 01, 2025, 04:02:58 pmFrom what I understand, I need to add 4 components to the form with the DBNav/DBGrid.  A TSQLite3Connection, TSQLQuery, TSQLTransaction and a DataSource. I think what TRon is recommending from the link is I don't need put these on the form just define them as variables ... You can put those components on a form. The issue is that some of the properties of all these components need to be set. This needs to be done properly and usually in a particular order. My advise would be to not set those properties at design time but rather at runtime. Depending on the complexity of the program it is usually easier to use a 'third'' unit (usually a datamodule but it can be any unit) on which to place the non-visual database components (at design time). That way every form is able to access that datamodule unit by simply including it in the uses clause. The only thing that is perhaps confusing when using a datamodule is that the datamodule itself has to be created before accessing any components on it. The post I referred to is setting the  properties of these components manually at runtime which is why I referred to it and not so much about declaring the component variables manually/globally (while it is an option if you prefer to do it that way). PS: the issue with db related problems is that I (or anyone else) can make up any example for you but it will suffer from the same flaws that you are currently facing. You do not know where to look at/for in order to understand what might be wrong. Therefor the available demo examples probably also are not able to help you out either. You do not share any example code for us to compile and be able to have a look at (don't get me wrong, there are perfect valid reasons not to share current code if you do not want to) but at least you could try and provide a simple example of something that you do not seem to be able to accomplish. There are plenty of example databases (either accompanied with Lazarus or online) that you could refer to in a test example that you would be able to share without compromising any of your existing code-base (or at least any that we are aware of). ---------------------------------------------------------------------- JanRoza · 2025-03-01 20:07 Why not put that line in the oncreate method of your main form, that way your databasename is known as soon as your application starts. Something like: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Database.name := SysUtils.ExtractFilePath(ParamStr(0) + 'Countries.db'; ---------------------------------------------------------------------- egsuh · 2025-03-02 03:26 QuoteFrom what I understand, I need to add 4 components to the form with the DBNav/DBGrid.  A TSQLite3Connection, TSQLQuery, TSQLTransaction and a DataSource. I think what TRon is recommending from the link is I don't need put these on the form just define them as variables ... Important thing is you may have only one connection, i.e. TSQLite3Connection. Others really don't matter. You may have many of these on one form. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-02 14:23 Attached is the complete Project for this test app. Just one table in the Database-Countries. It's just an extract from a larger Project that has 21 units. Countries is just one TDbf table for a Drop-Down lookup when updating a Clients, Proposals, Sales Orders, etc. I just took the Countries maintenance unit and made this small test app to learn how to use SQLite3 before tackling the complete project which is a Photographer's Mgt. System (Clients, Contacts, Proposals, Refrences, Sales Orders, Invoicing, Photographer's Mgt., Photos Mgt, etc. My issue in learning and migrating from TDbf to SQLite3 is not the SQL code but just establish a connection, transaction, query and the Dataset. Can't get past just a small one table Database. I've actually converted all the TDBf tables to SQLite3 tables in a Database. Thanks anyone if you take this and let me know what I'm doing wrong. Just one query in it to load and view the table in a DBGrid with a Navigator based on the DataSet. ---------------------------------------------------------------------- TRon · 2025-03-02 14:54 I only had a quick glance (I am heading out the door in a short while): - code creates TSQLite3Connection. Either the form creates it automatically for you because it is placed on the  form at design time or do not place it on the form at design time and create it manually. - code assigns name of the Database to DBCEntries (which is a Datasource). It needs to be assigned to DBCntriesConnect.DatabaseName - The name of the database name on disk is "CNTRIESDB.sqlite3" not "CNTRIESDB" - Transaction database property not set to DBCntriesConnect. - Database property of the Query is not set to DBCntriesConnect - Transaction property of the query is not set to   DBCntriesTrans - The name of the table inside the database is actually "COUNTRIESDB" not "COUNTRIES" in the query - query text needs to be set before actually opening the query. I did not had a good look at the property values that were set at design time but if corrected all the mentions above in code the database/query opens and is able to be browsed by the DB Navigator. Except for one other thing and that is that all the fields in the grid are displayed as "TMEMO" (which is an entirely other issue related to field-type). ---------------------------------------------------------------------- TRon · 2025-03-02 18:39 I had managed to have a closer looksee  1. seems you manually added some entries in the form type declaration. HINT: never do that. If required to manually add components do that in a separate section of the type declaration instead. Either in the public, protected or private sections. Therefor remove the (commented) entry SQLite3COUNTRIES and the entry SQLTransactionCntries 2. remove the variable declarations DBCntriesConnect, DBCntriesTrans and DBCntriesQry that are situated right beneath the FrmCntriesMgt variable declaration. Either place the required components on the form (as was already done) or declare them as either fields (in section private, protected or public) or as you did as global variables. Never do both (don't even think about doing it threefold  ). 3. Create/add a new private procedure with proto-type "procedure SetupDB;" in the form type declaration, e.g.: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   TFrmCntriesMgt = class(TForm)    BitBtnClose: TBitBtn;    DSCntries: TDataSource;    DBGridCntries: TDBGrid;    DBNavCntries: TDBNavigator;    PnlTop: TPanel;    DBCntriesConnect: TSQLite3Connection;    DBCntriesQry: TSQLQuery;    DBCntriesTrans: TSQLTransaction;    procedure BitBtnCloseClick(Sender: TObject);    procedure FormCreate(Sender: TObject);    procedure FormShow(Sender: TObject);  private    procedure SetupDB;  public  end;  When added, position the cursor on that new procedure and press ctrl+shift+c. The method should be then automatically be added to the form implementation section. Add the following code to that implementation so that it reads: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMgt.SetupDB;var  DBName : string = 'CNTRIESDB.sqlite3';begin  // References:  // - https://www.freepascal.org/docs-html/fcl/sqldb/usingsqldb.html   // Step 1: a connection requires a (file)name (and optional credentials).  // ToDo  : make sure the file exists.  DBName := SysUtils.ExtractFilePath(ParamStr(0)) + DBName;  DBCntriesConnect.DatabaseName:= DBName;   // Step 2: A transaction require a connection.  DBCntriesTrans.DataBase := DBCntriesConnect;   // At this point the database (connection) /can/ be opened (or connected)  // We open it here just for lolz but it is better to do it after the  // form was created so that feedback can be provided (f.i when things go wrong)  DBCntriesConnect.Open;  // or alternative: DBCntriesConnect.Connected := True;   // Step 3a: Setup Query - a Query requires a database connection  DBCntriesQry.DataBase := DBCntriesConnect;   // Step 3b: Setup Query - a Query requires a transaction  DBCntriesQry.Transaction := DBCntriesTrans;   // Step 4: Setup your data sources  // This can be done at any time it does not specifically has to be step 4.  // Already (properly) done at design timeend;  4. Remove all existing code from the form create event and make a call to procedure SetupDB instead so that it reads: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMgt.FormCreate(Sender: TObject);begin  SetupDB;end;  5. Change the FormShow event implementation so that it reads: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMgt.FormShow(Sender: TObject);begin  DBCntriesQry.SQL.Text:= 'select * from COUNTRIESDB';  DBCntriesQry.Open;end;  6. Change the BitBtnCloseClick event implementation so that it reads: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMgt.BitBtnCloseClick(Sender: TObject);begin  try    DBCntriesQry.Close;  finally    DBCntriesConnect.Close(true); // alternatively: DBCntries.Connected:= False;  end;  FrmCntriesMgt.Close;  // Please do not shoot yourself in the foot  // Here the commented code literally removes the ground it stands on  // FrmCntriesMgt.Free;end;  That should do it. Again I mention, except for the fields showing TMemo in the grid. PS: Just keep in mind that the sqlite database contains all the tables so that only a single connection and transaction is required (though you could use multiple transactions if wanted). That was also one of the reasons in my previous answer to use global variables (or at least globally accessible) for at least the connection and transaction. ---------------------------------------------------------------------- Zvoni · 2025-03-03 07:32 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. 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 ---------------------------------------------------------------------- Thaddy · 2025-03-03 08:06 @TRon Impressed how you demonstrate the pitfalls of using the designtime components and object inspector as opposed to creating the components in code, in understandable language, with clear examples and consistent over multiple answers. I have never seen it explained more clearly than you did. Frankly, this is often the only way to use the lower level (connection oriented parts ) database design should be implemented. Not many people know which properties to set and in what order using the design time components and the object inspector. Creating the components in code will prevent errors. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-03 16:29 TRon, thanks loads! Like I was saying earlier, I can write and have been studying SQL as far as coding just having issues just getting started. The only code I am using just for maintaining a COUNTRIES table was 'select * from COUNTRIES'. The rest is just introducing the Database and Connecting. I agree to code it. I just copied your comments and instructions. I'll be back when I get this one little app. working and can start on converting the whole app from TDbf --> SQLite3. Thanks again!!! ---------------------------------------------------------------------- CharlyTango · 2025-03-03 23:12 Basically I agree with TRon but... ... this strategy does not solve the problem of providing application- wide database access. Yes, you can use a separate data access for each form, but this does not work with all databases (be careful with SQLite). There may also be a memory problem on the server when managing many connections, which are not normally necessary. A complete example for applications can be found here. It uses a data module and SQLDB components. This encapsulates the complete database access. The example uses SQLite, but you can use any SQL database supported by Lazarus. It also provides a logfile to log Data transferred to and from the SQL Server to ease debugging and much much more features useful for database frontend programming.. The form or frame then only contains the actual data components (TSQLQuery, TDataset and, if necessary, data-sensitive controls) The database connection is assigned to the TSQLQuery in TForm.Activate (because only then are all initialization processes of a form complete) or in a separate procedure in a frame. I never activate database connections in Lazarus. This only causes problems in the long term if you forget to reset everything. I only let Lazarus generate the main form automatically. I create all other forms (including the data module) in code. This gives me control over the necessary sequence. By the way... 'select * from' is more than a bad habit. After 30 years of programming frontends --> Don't ever do that ---------------------------------------------------------------------- TRon · 2025-03-04 00:00 Quote from: CharlyTango on March 04, 2025, 12:12:59 amI 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. QuoteBy 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 · 2025-03-04 11:37 Quote from: CharlyTango on March 04, 2025, 12:12:59 amBy 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 · 2025-03-07 22:15 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 · 2025-03-08 19:46 Never mind on the TSQLite3Dataset ... thanks. Just using the TDataSource to connect components. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-08 22:33 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  SetupDB;end; procedure TFrmCntriesMaint.SetupDB;var  PMS_DB : string = 'PMS DB\PMSDB.sqlite';begin  //-> Establish connection to the PMSDB.sqlite database.  PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)+ PMS_DB;  DBCntriesConnect.PMS_DB:= DBCntriesConnect;  //-> A transaction is required for the connection.  DBTransCntries.PMS_DB:= DBCntriesConnect;  //-> Open the database.  DBCntriesConnect.Open;  //-> Setup the Query  DBCntriesQry.DataBase:= DBCntriesConnect;  DBCntriesQry.Transaction:= DBTransCntries;  //-> Can setup your data source next but done design timeend; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';  DBCntriesQry.Open;  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  DBCntriesQry.Close;  finally    DBCntriesConnect.Close(True);  end;  FrmCntriesMaint.Close;  {FrmCntriesMaint.Free; Don't need this according to TRon!}end; ---------------------------------------------------------------------- TRon · 2025-03-09 00:15 Quote from: 1HuntnMan on March 08, 2025, 11:33:33 pmI 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. QuoteSo, 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 ? QuoteI'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 · 2025-03-09 08:50 QuoteI 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 · 2025-03-09 20:09 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit CountriesMaint;{Program....: CountriesMaint.pas Author.....: Donald King Date.......: 20 Nov. 2023 Copyright..: Copyright(c) 2023-2025 NewFound Photo Art, Inc. On the Web.: https://www.NewFoundPhotoArt.com Last Update: 03/09/2025}  {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils, DB, SQLDB, SQLite3Conn, Forms, Controls, Graphics,  Dialogs, ExtCtrls, DBGrids, DBCtrls, Buttons; type   { TFrmCntriesMaint }   TFrmCntriesMaint = class(TForm)    DSCntries: TDataSource;    DBGridCntries: TDBGrid;    DBNavCntries: TDBNavigator;    PnlCntryMaintTop: TPanel;    SpdBtnCloseCntries: TSpeedButton;    DBCntriesConnect: TSQLite3Connection;    DBCntriesQry: TSQLQuery;    DBTransCntries: TSQLTransaction;    procedure FormCreate(Sender: TObject);    procedure FormShow(Sender: TObject);    procedure SpdBtnCloseCntriesClick(Sender: TObject);  private    procedure SetupDB;  public  end; var  FrmCntriesMaint: TFrmCntriesMaint;  implementation {$R *.lfm} { TFrmCntriesMaint } procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  //DBCntriesConnect:= TSQLite3Connection.Create(nil);  SetupDB;end; procedure TFrmCntriesMaint.SetupDB;var  PMS_DB : string = 'PMSDB.sqlite';begin  //-> Establish connection to the PMSDB.sqlite database.  PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)) + PMS_DB;  DBCntriesConnect.DatabaseName:= PMS_DB;  //-> A transaction is required for the connection.  DBTransCntries.DataBase:= DBCntriesConnect;  //-> Open the database.  DBCntriesConnect.Open;  //-> Setup the Query  DBCntriesQry.DataBase:= DBCntriesConnect;  DBCntriesQry.Transaction:= DBTransCntries;  //-> Data source done design time, form componentend; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';  DBtransCntries.Active:= True;  DBCntriesQry.Active:= True;  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  DBCntriesQry.Close;  finally    DBTransCntries.Active:= False;    DBCntriesConnect.Connected:= False;  end;  FrmCntriesMaint.Close;  {FrmCntriesMaint.Free; Don't need this according to TRon!}end; end.{ EoF: CountriesMaint.pas } ---------------------------------------------------------------------- TRon · 2025-03-10 00:07 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {FrmCntriesMaint.Free; Don't need this according to TRon!} 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 · 2025-03-10 01:35 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 · 2025-03-10 08:22 Quote from: 1HuntnMan on March 09, 2025, 09:09:04 pmbut errors on execution The error message is quite clear -- some process occupies the (database-) file. Quote from: TRon on March 10, 2025, 01:07:00 amThere 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. Quote from: Zvoni on March 03, 2025, 08:32:09 amAs 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 · 2025-03-10 08:35 Quote from: CharlyTango on March 10, 2025, 09:22:25 amQuote from: Zvoni on March 03, 2025, 08:32:09 amAs 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. QuoteI 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 againAfter 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 · 2025-03-10 17:21 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  //DBCntriesConnect:= TSQLite3Connection.Create(nil);  SetupDB;end; procedure TFrmCntriesMaint.SetupDB;var  PMS_DB : string = 'PMSDB.sqlite';begin  //-> Establish connection to the PMSDB.sqlite database.  PMS_DB:= SysUtils.ExtractFilePath(ParamStr(0)) + PMS_DB;  DBCntriesConnect.DatabaseName:= PMS_DB;  //-> A transaction is required for the connection.  DBTransCntries.DataBase:= DBCntriesConnect;  //-> Open the database.  DBCntriesConnect.Open;  //-> Setup the Query  DBCntriesQry.DataBase:= DBCntriesConnect;  DBCntriesQry.Transaction:= DBTransCntries;  //-> Data source done design time, form componentend; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  DBCntriesQry.SQL.Text:= 'select * from COUNTRIES';  //DBtransCntries.Active:= True;  DBCntriesQry.Open;  <---- Errors at this line!!!  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  DBCntriesQry.Close;  finally    //DBTransCntries.Active:= False;    //DBCntriesConnect.Connected:= False;    DBCntriesConnect.Close(True);  end;  FrmCntriesMaint.Close;  {FrmCntriesMaint.Free; Don't need this according to TRon!}end;  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 · 2025-03-10 17:59 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 · 2025-03-10 21:46 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. ---------------------------------------------------------------------- TRon · 2025-03-10 23:27 Quote from: 1HuntnMan on March 10, 2025, 10:46:25 pmIf so, then it should be: DBCntriesQry.SQL.Text:= 'select * from COUNTRIES;'; correct? Correct  QuoteAlso, 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 · 2025-03-11 02:48 QuoteEGSuh,    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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  DBCntriesConnect.Open;  //-> Setup the Query  DBCntriesQry.DataBase:= DBCntriesConnect;  DBCntriesQry.Transaction:= DBTransCntries; I think you may try : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  DBCntriesConnect.Connected := True;  DBCntriesConnect.Transaction:= DBTransCntries;  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 · 2025-03-14 18:56 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);begin  { Declare the PMS Database for the PMS application. }  PMSDBConnect.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';  PMSDBConnect:= TSQLite3Connection.Create(nil);end;  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 · 2025-03-14 19:12 I forgot this:  In the program PMS I added the DMPMS to the beginning of the units, i.e. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  { The PMS units are listed below ... }  DMPMS, PMSMain, datetimectrls, printer4lazarus, lazcontrols; then the begin..end is like so creating the data module first then the main form for the application, i.e. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i begin  RequireDerivedFormResource:=True;  Application.Title:= 'Photographers Management System';  Application.Scaled:=True;  Application.Initialize;  Application.CreateForm(TDmPMSDataBase, DmPMSDataBase);  Application.CreateForm(TFrmPMSMain, FrmPMSMain);   Application.Run;  { If the compile errors at the end statement below, the PMS    application is running and in memory while you are in Lazarus!    Shut down the PMS running outside of Lazarus and then compile/run! }end.{ EoF: PMS.lpr } ---------------------------------------------------------------------- 1HuntnMan · 2025-03-15 19:18 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit DMPMS; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils, SQLite3Conn, SQLDB, DB; type   { TDmPMSDataBase }   TDmPMSDataBase = class(TDataModule)    ConnectPMSDB: TSQLite3Connection;    QueryPMSDB:   TSQLQuery;    TransPMSDB:   TSQLTransaction;    procedure DataModuleCreate(Sender: TObject);  private   public    //->PMS_DB: string;  end; var  DmPMSDataBase: TDmPMSDataBase; implementation {$R *.lfm} { TDmPMSDataBase } procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);begin  ConnectPMSDB:= TSQLite3Connection.Create(nil);  { Connect to the PMS Database for the PMS application. }  ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';  //-> Create a transaction... THIS NEXT LINE Compiler warning????  //TransPMSDB.DataBase.Create(ConnectPMSDB);  //-> Point to the database instance...  TransPMSDB.DataBase:= ConnectPMSDB;  //-> Open the database...  ConnectPMSDB.Open;end; end. ---------------------------------------------------------------------- cdbc · 2025-03-15 19:32 Hi Hmmm, the datamodule creates the components on own creation via streaming... So maybe:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDmPMSDataBase.DataModuleCreate(Sender: TObject);begin  { Connect to the PMS Database for the PMS application. }  ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite';  //-> Point to the database instance...  TransPMSDB.DataBase:= ConnectPMSDB;  //-> Open the database...  ConnectPMSDB.Open;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 · 2025-03-16 06:21 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 · 2025-03-16 16:11 Makes sense, so the only line of code I need in the DataModule is in the Create: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite'; 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ConnectPMSDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite'; 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 · 2025-03-16 16:59 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit uMainform;  uses usubform1, usubform2, usubform3;  type Tmainform = class    .. end; var   MainForm : TMainForm;  implementation procedure TMainForm.OnCreate(Sender:TComponent); begin    SubForm1.SQLquery1.Open;     SubForm2.SQLquery1.Open;     SubForm3.SQLquery1.Open; end;  -----------------------------------------------unit subform1;  type   TSubForm1 = class          ..    end;  ------------------------------------------------unit subform2;  type   TSubForm2 = class          .. ------------------------------------------------unit subform3;  type   TSubForm3 = class          .. 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TMainForm.OnCreate(Sender:TComponent); begin    SubForm1 := TSubForm1.Create(self);  // by owner=self, you don't have to worry about destruction    SubForm2 := TSubForm1.Create(self);     SubForm3 := TSubForm1.Create(selfl);      SubForm1.SQLquery1.Open;     SubForm2.SQLquery1.Open;     SubForm3.SQLquery1.Open; 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 · 2025-03-16 18:53 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  TDmPMSDataBase = class(TDataModule)    ConnectPMSDB: TSQLite3Connection;    QueryPMSDB:   TSQLQuery;    TransPMSDB:   TSQLTransaction;    procedure DataModuleCreate(Sender: TObject);etc.   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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit CountriesMaint;{Program....: CountriesMaint.pas Author.....: Donald King Date.......: 20 Nov. 2023 Copyright..: Copyright(c) 2023-2025 NewFound Photo Art, Inc. On the Web.: https://www.NewFoundPhotoArt.com Last Update: 03/16/2025}  {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils, Forms, DB, SQLDB, SQLite3Conn, memds, Controls,   Graphics,  Dialogs, ExtCtrls, DBGrids, DBCtrls, Buttons, DMPMS; type   { TFrmCntriesMaint }   TFrmCntriesMaint = class(TForm)    DSCntries: TDataSource;    DBGridCntries: TDBGrid;    DBNavCntries: TDBNavigator;    PnlCntryMaintTop: TPanel;    SpdBtnCloseCntries: TSpeedButton;    procedure FormCreate(Sender: TObject);    procedure FormShow(Sender: TObject);    procedure SpdBtnCloseCntriesClick(Sender: TObject);  private    procedure SetupDB;  public   end; var  FrmCntriesMaint: TFrmCntriesMaint;  implementation {$R *.lfm} { TFrmCntriesMaint } procedure TFrmCntriesMaint.FormCreate(Sender: TObject);begin  SetupDB;end; procedure TFrmCntriesMaint.SetupDB;begin  //-> Create a transaction... ERROR identifier not found  TransPMSDB:= TSQLTransaction.Create(ConnectPMSDB);   //-> Point to the database instance... ERROR identifier not found  TransPMSDB.Database:= ConnectPMSDB;  //-> Open the PMS database... ERROR identifier not found  ConnectPMSDB.Open;  //-> Create a query to return data... ERROR identifier not found  QueryPMSDB:= TSQLQuery.Create(ConnectPMSDB);  //-> Point to the database and transaction... ERROR identifiers not found  QueryPMSDB.Database:= ConnectPMSDB;  QueryPMSDB.Transaction:= TransPMSDB;end; procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  //-> Set the SQL select statement... ERROR identifier not found  QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES';  //-> Now use the standard TDataset methods... ERROR identifier not found  QueryPMSDB.Open; // ERROR identifier not found  Screen.Cursor:= crDefault;end; procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);begin  try  ConnectPMSDB.Close; // ERROR identifier not found  finally    TransPMSDB.Close; // ERROR identifier not found  end;  FrmCntriesMaint.Close;  {FrmCntriesMaint.Free; Don't need this according to TRon!}end; end.{ EoF: CountriesMaint.pas } ---------------------------------------------------------------------- TRon · 2025-03-16 20:04 I make one example: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMaint.FormShow(Sender: TObject);begin  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES';  DmPMSDataBase.QueryPMSDB.Open;  Screen.Cursor:= crDefault;end;  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 · 2025-03-16 21:20 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 · 2025-03-16 22:52 Quote from: 1HuntnMan on March 16, 2025, 10:20:22 pmOkay TRon, et.al., geeze-luezze! I have 4 forms working with SQLite! Good and congratz !  QuoteThe 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 · 2025-03-17 02:22 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 · 2025-03-17 02:23 Chinook db file.. which was provided by DBeaver. ---------------------------------------------------------------------- TRon · 2025-03-17 03:34 Quote from: egsuh on March 17, 2025, 03:22:19 amI made a very simple program to test SQLite with minimal typing of codes. Yeah, sublime ---------------------------------------------------------------------- egsuh · 2025-03-18 03:28 I made another example, without TSQLite3Connect. This uses only TSQLite3DataSet components. Database is the same Chinook.db file, which I uploaded before. I started this with the purpose of various tests, but as there have been many questions on SQLite3 database, like "changes are not stored", etc., I hope this example helps those who try to use SQLite. First I tried to use artist and album tables, but the album table causes problems, possibly because it has a blob field. Hope this is checked.  So I used customer and invoice tables, and name of TSQLite3DataSet for each table is tblCustomer and tblInvoice. Datasource name for each dataset is dsCustomer and dsInvoice. These two tables are master-slave relationship. Invoices are for each customer, linked by the CustomerID field. The key points here are with tblInvoice :         MasterCource : tblInvoice         MasterFields :  CustomerID         IndexFieldNames :  CustomerID When executed, the right grid, which is for tblInvoice, will show only for each employee selected at left grid, which is for tblCustomer. It's little bit weird that every customer has 7 invoices. To modify the content of invoice table here, I set following properties of tblInvoice.         PrimaryKey : InvoiceID         AutoincrementKey: true With this, when I insert a record by pressing (+) button of dbnavigator, Invoice ID was filled in automatically. But not other fields like address, country, etc. And I keyed in total amount. With this, I could modify the content at dbgrid and post, but the changes were not saved. When I go to other customers and came back, the changes were not saved. So I set         SaveOnRefresh : true         SaveOnClose: true And then I tried again... and at this time, error message popped up. InvoiceDate field should not be null. This is strange that the field is not filled in. Up to this, there were no typing of codes at all in unit file. To make this simple program working, I added following codes. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.tblInvoiceBeforePost(DataSet: TDataSet);begin    tblInvoice.FieldByName('InvoiceDate').AsDateTime:= now;    tblInvoice.fieldbyName('Billingaddress').asstring := tblCustomer.FieldByName('Address').AsString;end; Of course I can add other fields like country, city, etc. of invoice table, copying from customer table. And now, it seems to work, at least inserting and deleting. This seems quite simple way to manage relational database. I'd like to make a program to manage my own customers and contacts. ---------------------------------------------------------------------- egsuh · 2025-03-18 05:51 @1HuntnMan I think you might have transformed all dbf tables to sqlite tables already. Anyway I found an easy way. DBeaver provides the function of importing csv file into sqlite table. If table structure is not predefined, table is automatically created but I had some difficulty to setting its properties like not null field, etc. ---------------------------------------------------------------------- egsuh · 2025-03-18 07:36 It's does not seem a good idea to let any datasets open at design time. Had better to insert DataSet.Active :=true at any of FormCreate or FormActivate or FormShow. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-18 16:33 TRon, EGSuh, et.al., awesome information, thanks. Oh, keep forgetting to let you know TRon, when I created the Database for this app when I decided to change it from Dbf to SQLite3, I used DBCopier which is nifty for doing that. But, when I decided to create a Database with just one table, i.e. countries to learn how to connect, etc. and display the data and finally uploaded it for you to examine what I was doing wrong. You made it work but the DBGrid was displaying MEMO instead of the data. I had no problem with this within the main app. I finally looked back and the Countries table was created with the fields type as TEXT.  In may main app. all the text type fields are defines as VarChar(length), i.e. for Country Abbrev.VarChar(2) and Country Name VarChar(35).  Now this can be an issue if you are creating a table and you are learning with DB Browser for SQLite. If you start creating a table in a database and you add a field name and then for Type and click the drop-down it displays INTEGER, TEXT, BLOB, REAL and NUMERIC.  So, if you aren't coding the table create and use DB Browser for SQLite, you can just not use the drop-down for Type and just key-in VarChar(length) and it will execute the SQL Code accordingly.  Just thought I'd pass that on. Now, approaching the Clients form which uses 6 tables and a one-to- many relation Client to Contacts. The other tables are lookup tables: States, Countries, FirmType and Referral Type. ---------------------------------------------------------------------- wcage03 · 2025-03-18 18:25 You can likely get away with copying the dll from another location. The benefits of installing SQLite on your computer instead is that it will be available should you write another program where you want to use SQLite. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-19 15:50 egsuh, thanks good recommendation. ---------------------------------------------------------------------- JanRoza · 2025-03-19 16:41 @1Huntsman: If you want a really good database manager for SQLite give SQLiteExpert Personal a try (https://www.sqliteexpert.com). I use it for years now and can do anything I like with my SQLite databases. The Personal version is completely free. ---------------------------------------------------------------------- Zvoni · 2025-03-20 07:11 Quote from: 1HuntnMan on March 18, 2025, 05:33:32 pmTRon, EGSuh, et.al., awesome information, thanks. Oh, keep forgetting to let you know TRon, when I created the Database for this app when I decided to change it from Dbf to SQLite3, I used DBCopier which is nifty for doing that. But, when I decided to create a Database with just one table, i.e. countries to learn how to connect, etc. and display the data and finally uploaded it for you to examine what I was doing wrong. You made it work but the DBGrid was displaying MEMO instead of the data. I had no problem with this within the main app. I finally looked back and the Countries table was created with the fields type as TEXT.  In may main app. all the text type fields are defines as VarChar(length), i.e. for Country Abbrev.VarChar(2) and Country Name VarChar(35).  Now this can be an issue if you are creating a table and you are learning with DB Browser for SQLite. If you start creating a table in a database and you add a field name and then for Type and click the drop-down it displays INTEGER, TEXT, BLOB, REAL and NUMERIC.  So, if you aren't coding the table create and use DB Browser for SQLite, you can just not use the drop-down for Type and just key- in VarChar(length) and it will execute the SQL Code accordingly.  Just thought I'd pass that on. Now, approaching the Clients form which uses 6 tables and a one-to- many relation Client to Contacts. The other tables are lookup tables: States, Countries, FirmType and Referral Type. Notabene: The (MEMO)-Issue, if the Datatype of the Column is TEXT, is documented, and a search in the Forum will return a gazillion entries (and workarounds for it). That said: i've been using DB Browser for years as a "cheating"-tool I create my tables (and VIEWS, TRIGGERS, INDEXES and whatever else) in it, then i copy the CREATE-Statement from it, adjust it to suit my needs (Replace TEXT with Varchar(XX), insert a IF NOT EXISTS after CREATE TABLE etc.), and put the Statement in my Front-End-Code!! It's a simple routine at Startup: Check if DB exists: Yes/no --> If No, create it Check if tables exists: Yes/No --> If no, use the Create-Statement Run..... That way you are not dependent on DB Browser or any other Tool if something goes wrong while "on the road" EDIT: and btw: for something like "VARCHAR(50)", SQLite completely IGNORES the "(50)" Part.... There will be no difference between "VARCHAR(20)" and "VARCHAR(200)" on the SQLite-side. Not sure about the SQLite-Components/Classes in Freepascal though, since i'm not sure what of SQLite's Metadata is retrieved. e.g. You declare the column with "VARCHAR(20)", but SQLite is not going to prevent you to insert a Value longer than 20 Characters (Say a text with 100 Characters). But FreePascal's SQLite-Components MIGHT read that Metadata-Value (the "(20)"), and set the Field-Size of the Dataset to it. Have a guess what you might get back from the DB.... ---------------------------------------------------------------------- jcmontherock · 2025-03-21 10:24 As well (with SQLCpher): https://sqlitebrowser.org/ ---------------------------------------------------------------------- 1HuntnMan · 2025-03-25 21:37 I've been using DB Browser (SQLite) for viewing and checking tables during this conversion. Mostly finished, no Dbf's, all gone. But, still in a major learning curve learning SQL. I have relations on 4-5 of the forms, esp. in the Proposals, Clients, Sales Orders, Invoices.  I've been using the W3 Schools SQL Tutorial. But, I've downloaded A Beginner Guide to SQLite, SQLite Tutorials 0-4, etc. Mostly teaching you for one table, not a Database with 19 tables and 44 Indices which I'm eliminating a lot of those indexes. Looking at what ESUG wrote about TSQLite3DataSet. I'm using a lot of DataSet after connection for manipulating but learning by errors. For example, instead of this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i tblInvoice.FieldByName('InvoiceDate').AsDateTime:= now;this,procedure TFrmInvoices.JDBCurrencyAmtDueExit(Sender: TObject);begin  if DSInvces.DataSet.FieldByName('AMTDUE').IsNull then    ShowMessage('Amount Due($) must be a valid $ amount...!!!');  JDBCurrencyAmtPaid.SetFocus;end; A lot is compiling but isn't running correctly but like I said, it's a learning curve. I attempted to install  TSQLite3DataSet components but something went wrong, it just disappeared. Tomorrow I'm going to uninstall Lazarus and reinstall to see if I can get it installed. ---------------------------------------------------------------------- egsuh · 2025-03-26 09:24 If you have 19 tables, I think you'd better move to SQL. You may try some trials and errors with TSQliteDataSet, but that will not work if you have to join several tables or any table come to have large number rows, possibly over tens of thousands. To learn SQL from the start I recommend the contents in link below. It's on Interbase and Firebird will operate in the same way, but SQLite does not support stored procedure. The SQL examples are very well organized, from creating tables, through inserting/deleting/updating records, and selecting records (inc. joining tables) to trigger programming. https://docwiki.embarcadero.com/InterBase/2020/en/InterBase_Quick_Star t ---------------------------------------------------------------------- Zvoni · 2025-03-26 09:44 19 Tables? That's.... cute...... Just looking at our production Database there are over 5,000 tables (Yes... 5K).. Everything said: Yes, SQL is the way to go. Don't do that DataSet- juggling you're probably used to from your DBF-Days.... If you need help with SQL-Statements, give a shout Quote*snip*...but SQLite does not support stored procedure. ...*snip*Yes, that's one downside to SQLite, but honestly, i've never seen much use for it, but that's just me.... ---------------------------------------------------------------------- Thaddy · 2025-03-26 10:52 No, no stored procedures, neither does dBase btw, but user defined functions come close and that is encouraged and is supported. And they are easy to write in Freepascal. User defined functions can behave just like stored procedures for local databases, e.g. you can write them such that users can not modify/commit where they should not. It is a feature many people forget, wrongly interpret or simply did not know about for lack of reading documentation.... ---------------------------------------------------------------------- Zvoni · 2025-03-26 11:22 Quote from: Thaddy on March 26, 2025, 11:52:54 amNo, no stored procedures, neither does dBase btw, but user defined functions come close and that is encouraged and is supported. And they are easy to write in Freepascal. User defined functions can behave just like stored procedures for local databases, e.g. you can write them such that users can not modify/commit where they should not. It is a feature many people forget, wrongly interpret or simply did not know about for lack of reading documentation.... Behave? Yes! Are equal? Definitely No. A stored Procedure in this context is a "real" Database-Object (like a Table, View, Trigger etc.) and can be called as such, e.g "CALL MyProcedure(MyArgument)" A UDF is more along the lines of "SELECT MyOwnFunction(SomeColumn) AS Test FROM SomeTable" The prominent one in SQLite the REGEXP-operator/Function. A UDF can only ever be called/used WITHIN a SQL-Statement. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-26 16:01 Not going to switch to FireBird/SQL, sticking with SQLite3 because even though a lot of tables, not that much data. This project is for photographers. For example, how do you SELECT from let's say, 4 tables, i.e the Contacts mgt. part of the Project? Most all examples I see are SELECT from one table. So, is this correct? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntksMgt.FormShow(Sender: TObject);begin  //-> Set the SQL select statement for the CONTACTS table...  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM CONTACTS';  //-> Set the SQL select statement for the CLIENTS table...  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM CLIENTS';  //-> Set the SQL select statement for the STATES/PROVINCES table...  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM STPROVCODES';  //-> Set the SQL select statement for the COUNTRIES table...  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES';  //-> Now use the standard TDataset methods...  DmPMSDataBase.QueryPMSDB.Ope n; DSStates.DataSet.FieldByName('STPROVCODE').DisplayWidth:= 2;  DSStates.DataSet.FieldByName('STPROVNAME').DisplayWidth:= 12;//----------------------------  DSCountries.DataSet.FieldByName('CODE').DisplayWidth:= 2;  DSCountries.DataSet.FieldByName('COUNTRY').DisplayWidth:= 10;  DSClients.DataSet.FieldByName('CUSTOMER').DisplayWidth:= 12;  {...Fix this later for counting total Contacts  records with a SELECT COUNT or the like}  EditTTLContacts.ReadOnly:= False;  IntRecCount:= DSContacts.ExactRecordCount;  EditTTLContacts.Text:= IntToStr(IntRecCount);  EditTTLContacts.ReadOnly:= True;}  JvDBSrchByIndex.Text:= '';  BitBtnSaveCntk.Enabled:= False;  BitBtnCancelCntk.Enabled:= False;  Screen.Cursor:= crDefault;  DBGridCntks.SetFocus;end; This form has a DBNav, etc. on the top panel, below that is a DBGrid for panning thru the records and below the DBGrid are TDBEdits and LookupCombo's for data entry. ---------------------------------------------------------------------- TRon · 2025-03-26 21:06 Quote from: 1HuntnMan on March 26, 2025, 05:01:02 pmMost all examples I see are SELECT from one table. So, is this correct? Well, there is good news and there is bad news. The good news is that the select statements (with the exception of ending them with a semicolon) seems about right. The bad news is, and I generalize here (which is technically wrong), there can be only one select statement per query (and be active at a point in time) In case a query is only used to be active for a certain view/form and it is the only query active then such a query can be re-used by closing the query, changing the select statement and opening the query again so that it can be reused for another view/form. In case that is not possible, for example the countries-view form also contains a button that allows to view the customers in a view/form, then you can not use that same query but need to create another one. PS: in case it make it perhaps easier to understand as you seem to be familiar with it: a query /IS/ a dataset. ---------------------------------------------------------------------- egsuh · 2025-03-27 02:51 No.. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntksMgt.FormShow(Sender: TObject);begin     QueryContacts.SQL.Text:= 'SELECT * FROM CONTACTS';     QueryClients.SQL.Text:= 'SELECT * FROM CLIENTS';     QueryStprovcodes.SQL.Text:= 'SELECT * FROM STPROVCODES';     QueryCouuntries.SQL.Text:= 'SELECT * FROM COUNTRIES';      QueryContacts.open;      QueryClients.OPpen;      QueryStprovcodes.Open;      QueryCouuntries.Open;      // I think you'd better do followings with DBGrid    //  DSStates.DataSet.FieldByName('STPROVCODE').DisplayWidth:= 2;      //  DSStates.DataSet.FieldByName('STPROVNAME').DisplayWidth:= 12;        DGStates.Columns[3].DisplayFormat :=...;   // DGStates is DBGrid, and TDBGrid.columns are 0-based indexes.        DGStates.Columns[3].Width :=...;       DGStates.Columns[4].DisplayFormat :=...;       DGStates.Columns[4].Width :=...;     //---------------------------- the same as above as well.        DGCountries.Columns[0].width := 50;      // .........      // DSCountries.DataSet.FieldByName('CODE').DisplayWidth:= 2;     //  DSCountries.DataSet.FieldByName('COUNTRY').DisplayWidth:= 10;      // DSClients.DataSet.FieldByName('CUSTOMER').DisplayWidth:= 12;   {...Fix this later for counting total Contacts  records with a SELECT COUNT or the like}   // should look like:     with queryTemp do begin          SQL.Text = 'select count (*) from contacts where client_id=:cid';         Params[0].AsInteger := QueryClients.fieldByName('Client_id').AsInteger;            Open;          tcount := Fields[0].AsInteger;   // tcount is an integer variable.           Close;    end;     // And tcount is the total number of contacts for the selected client.       EditTTLContacts.ReadOnly:= False;    IntRecCount:= DSContacts.ExactRecordCount;    EditTTLContacts.Text:= IntToStr(IntRecCount);    EditTTLContacts.ReadOnly:= True;}    JvDBSrchByIndex.Text:= '';    BitBtnSaveCntk.Enabled:= False;    BitBtnCancelCntk.Enabled:= False;    Screen.Cursor:= crDefault;    DBGridCntks.SetFocus;end; Focus on datasets, like TSQLQuery, TSQLite3DataSet, etc., not DBGrid or Datasource. You do this: 1. Drop TSQLite3Connection component and TSQLTransaction component on a datamodule, DmPMSDataBase in your case.    - Set database of TSQLite3Connection to your DB file,    - set transaction to the transaction component.      try to "connect" by clicking the checkbox in the object inspector. Should be stay in "checked" state. 2. Create a form, add dmPMSDataBase in the "uses" clause of the form. Then,     - drop TSQLQuery, Tdatasource, TDBGrid, and TDBEdit, TDBLabel, TDBMemo as you need.     - Set TSQLquery.Database to dmPMSDataBase.SQLite3Connection.     - Set TDataSource.DataSet to the SQLQuery.     - Set the datasource of all the data controls (DBGrid, DBEdit, DBMemo, DBLabels, etc.) to the datasource on the form.     - Type in SQL property of TSQLQuery as "Select * from Clients"     - Try to Open it clicking "Active" at the object inspector.      If everything is OK, you should be able to see the contents of the table at the design time -- without running the application. 3. Create second form, and to the same things as step 2, using different tables. You can add more forms.    Every form will have the same names by default, SQLQuery1, DataSource1, DBGrid1, etc. , unless you rename them. Doesn't matter. 4. Once you have made a few forms, then Set all the "active" components to inactive state. SQLite3Connection, and TSQLQueries of every form.  And write some codes:       at   DataModuleCreate             SQLite3Connection.Connected := true;         at Forms' FormShow events:             SQLQuery1.Active := True; That's all.  Be careful that datamodule is created first. And run the application. Then if everything is OK, you must be able to see the contents of the first form. You can add a main form, add some menus, and when click a menu, each form will be shown. Then add more forms as necessary. This is the most basic approach. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-28 19:40 Okay, this is awesome. But this example is from the Contacts Mgt. form, called from the main form. The way I designed this the main form is for accessing Clients Mgt., Contacts Mgt., Appointments, Sales Orders, Invoices, etc. These forms have a main table as does this one Contacts Mgt., the Contacts table. The Clients table, States/Provinces table and the Countries tables are just lookups for the Contact via TJvDBLookupCombo's, i.e. selecting the Client, select the state and the country is all. So, for every table, I select the table all records, the open each one. I open it per EGSuh. Also, thanks EGSuh for the counting the contact records code. I use this on the main forms just for info. Also, the DSSTates.Columns displayformats is just for the JvDBLookupCombo. TRon helped me with that long time ago. The main DBGrid at the top is just for the user to pan thru the existing records, no data editing/inserting. They can use the roller top of their moused to roll up and down if hovering above the DBGrid. I need to get the TSQLite3DataSet installed.  Attempted to Package- Install/Uninstall packages but it disappeared in the process. It didn't appear in the SQLdb components. I'm going to uninstall Laz and reinstall it and try again. Tks, 1HuntnMan ---------------------------------------------------------------------- paweld · 2025-03-28 19:56 Quote from: 1HuntnManIt didn't appear in the SQLdb components. because it appears on the “Data Access” tab P.S. if you don't know which tab the component is on use the component finder (find component.png) ---------------------------------------------------------------------- egsuh · 2025-03-29 03:11 QuoteClients Mgt., Contacts Mgt., Appointments, Sales Orders, Invoices, If you want to like them in a way that when a clinet is selected, only appointments, sales orders, invoices, etc. of that specific client are shown, you can define Afterscroll event. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.qryClientsAfterScroll(DataSet: TDataSet);begin    with qryAppointments do begin         Close;        SQL.Text := 'select * from qryAppointments where clientid=:cid';        params[0].AsInteger := Dataset.FieldByName('ClientID').AsInteger;         Open;    end; end;         Then whenever you select a client, then only those records of appointments with that client will be show. When the databases get larger, this is better approach, ad SQLite3DataSet will make a copy of the whole table first. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-30 16:40 PAWeld et. al., Do you recommend using the SQLite3DataSet? I'm currently using just 4 components: TSQLite3Connection, TSQLTransaction, TSQLQuery and TDataSource as far as converting to SQLite3. TRon recommended and advised that I can add/install the TSQLite3DataSet from Package->Install/Uninstall Packages->Available for Installation, the sqlite3laz 0.4 or sqlitelaz0.4 package. About a week ago, I tried that but couldn't find it. PAWELD said the component should be located in the Data Access tab after the install but I didn't see it. Maybe I didn't do something correctly so uninstalled Laz and reinstalled it. Now under Available for Installation, I see 2 sqlite3 components to install:  sqlite3laz 0.4 and sqlitelaz 0.4. I assume the SQLite3DataSet is included in the sqlite3laz 0.4, correct???? ---------------------------------------------------------------------- 1HuntnMan · 2025-03-30 16:53 Okay, found it... sqlite3laz 0.4, trying to install again... ---------------------------------------------------------------------- 1HuntnMan · 2025-03-30 16:59 Or should I install the  C:\lazarus\components\sqlite\sqlite3laz.lpk ---------------------------------------------------------------------- TRon · 2025-03-30 18:45 Menu->package->install/uninstal packages Available for installation: type sql select sqlite3laz 0.4 press install selection press save and rebuild IDE Follow paweld's instructions to locate the component QuoteDo you recommend using the SQLite3DataSet Personally, no (but note that is a pure personal opinion). As stated before: a query /is/ a dataset, the only difference is that the query itself determines what becomes part of the dataset, e.g. provides much more flexibility on what data is present in the dataset. That is why I prefer it over a 'normal' dataset. You opinion might differ because you are converting existing code- base. ---------------------------------------------------------------------- 1HuntnMan · 2025-03-30 19:31 Thanks TRon and all... ---------------------------------------------------------------------- paweld · 2025-03-31 03:33 Quote from: 1HuntnMan on March 30, 2025, 06:40:51 pmPAWeld et. al., Do you recommend using the SQLite3DataSet?No QuoteI'm currently using just 4 components: TSQLite3Connection, TSQLTransaction, TSQLQuery and TDataSource as far as converting to SQLite3. And that's what I would stay with. From what I remember it's SQLite3DataSet for each occurrence opens a new connection. In my opinion, you should stay with what you described, just make sure that the connection to the database is only one for the entire application (TSQLite3Connection). ---------------------------------------------------------------------- 1HuntnMan · 2025-04-01 17:30 Okay All, as far as my maintenance forms, i.e. CountriesMaint, StatesProvMaint, PayStatus, etc. These forms work fine because easy to setup because they require only 1 table each from the DataBase, PMSDB.sqlite3. EGSUH explained how to open 4 tables from the DataBase but I'm not getting it. My DataModule has only 3 components on it: ConnectPMSDB, TransPMSDB and QueryPMSDB. Now, back to the DataModule. I can have both the ConnectPMSDB component and the TransPMSDB component on the DataModule.  But for multiple tables to be queried, SELECT * FROM Contacts, Clients, States & Countries I need to put a separate Query component on the Contacts Mgt. form, one for each DataSource correct? in order to query 4 different tables from the database on one form, i.e. Contacts Mgt. I can't do this below, correct?: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM CONTACTS';  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM CLIENTS';  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM STPROVCODES';  DmPMSDataBase.QueryPMSDB.SQL.Text:= 'SELECT * FROM COUNTRIES'; I'm going to have to have a different TSQLQuery component on the Contacts Mgt. form, not the one on the DataModule, for each Query Correct for all 4 tables to have an active query at the same time. Also, I finally got the TSQLiteDataSet installed, I had to put the SQLite3.dll into the Lazarus directory. But don't see how to use it, there's no description for any of Properties. I'll have to research to find an example. But if you can help out with just my Contacts Mgt. form, the others I can figure out. See the form attached... ---------------------------------------------------------------------- 1HuntnMan · 2025-04-01 18:27 SQLite3DataSet! Maybe that's the way because I can point it to each DataSource. Pull in the FieldDefs needed, the FileName?, Primary Key for the table and assign the SQL statement. Hmmmm ---------------------------------------------------------------------- TRon · 2025-04-02 06:34 Quote from: 1HuntnMan on April 01, 2025, 07:30:37 pm... My DataModule has only 3 components on it: ConnectPMSDB, TransPMSDB and QueryPMSDB. Now, back to the DataModule. I can have both the ConnectPMSDB component and the TransPMSDB component on the DataModule.  But for multiple tables to be queried, SELECT * FROM Contacts, Clients, States & Countries I need to put a separate Query component on the Contacts Mgt. form, one for each DataSource correct? No but with the caveat that it depends. Since not all information required to answer correctly is available I have to take a guess here but in normal situations this requires 2 queries in a s named master- detail setup (see also wiki). QuoteI can't do this below, correct?: That is correct: you can not do that. Or to be more precise, you can do that but it will not result in something that you looking for. In SQL-speak it is nonsense. QuoteI'm going to have to have a different TSQLQuery component on the Contacts Mgt. form, not the one on the DataModule, for each Query Correct for all 4 tables to have an active query at the same time. That is where the guessing part comes into play because of lacking information. Looking at the form picture that was posted it seems like a classic master-detail relationship. One query to 'browse' the contacts and depending on which contact is 'active' show the details for that contact. In the detail query you make a selct statement that 'collect's all the relevant data (read: fields from different tables) for the selected customer based on the ID of the customer. What exactly that query looks like depends on what indices are present and how the indices of the tables are linked between each other. ---------------------------------------------------------------------- Zvoni · 2025-04-02 06:36 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 · 2025-04-02 06:53 @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 · 2025-04-02 07:06 Quote from: TRon on April 02, 2025, 08:53:58 amit 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 · 2025-04-02 07:10 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQueryCon tacts.Database:=Datamodule_SQConnect.SQLiteConnection1;SQLQueryContact s.SQL.Text:='SELECT......';SQLQueryContacts.Open; ---------------------------------------------------------------------- CharlyTango · 2025-04-02 07:13 Pictures to previous post ---------------------------------------------------------------------- Zvoni · 2025-04-02 07:15 QuoteNot 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" Quote from: CharlyTango on April 02, 2025, 09:06:19 amQuote from: TRon on April 02, 2025, 08:53:58 amit 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 · 2025-04-02 08:17 Quote from: CharlyTango on April 02, 2025, 09:06:19 amIt'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). Quote from: Zvoni on April 02, 2025, 09:15:37 amit'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 · 2025-04-02 09:16 Quote from: TRon on April 02, 2025, 10:17:10 amAlthough 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 · 2025-04-02 11:25 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 · 2025-04-02 12:01 Quote from: egsuh on April 02, 2025, 01:25:42 pmIf 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 · 2025-04-02 12:59 First make a running app with sqlitedataset and then replacing them with sqlquery one by one could be an approach. ---------------------------------------------------------------------- TRon · 2025-04-02 13:18 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 · 2025-04-02 15:15 Learning SQL is not the purpose. Running application is the first target. That's what Rapid Application Development tool intends to do. ---------------------------------------------------------------------- TRon · 2025-04-02 15:47 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 · 2025-04-03 20:26 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program ContactsMgt;{Program....: ContactsMgt.lpr Author.....: Donald King Date.......: 30 Nov. 2023 Copyright..: Copyright(c) 2024 NewFound Photo Art, Inc. On the Web.: https://www.NewFoundPhotoArt.com Last Update: 04/02/2025} {$mode objfpc}{$H+} uses  {$IFDEF UNIX}  cthreads,  {$ENDIF}  {$IFDEF HASAMIGA}  athreads,  {$ENDIF}  Interfaces, // this includes the LCL widgetset  Forms, datetimectrls, printer4lazarus, sqlite3laz, lhelpcontrolpkg,  { Main Unit added below ... }  CntksDM, CntksMain; {$R *.res} begin  RequireDerivedFormResource:=True;  Application.Title:='ContactsMgt';  Application.Scaled:=True;  Application.Initialize;  Application.CreateForm(TDMCntks, DMCntks);  Application.CreateForm(TFrmCntksMain, FrmCntksMain);  Application.Run;  { If the compile errors at the end statement below, Contacts Mgt.    is running and in memory! Shut it down and then compile/run!}end.  Now the datamodule: CntksDM Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit CntksDM; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils, SQLite3Conn, SQLDB, Db; type   { TDMCntks }   TDMCntks = class(TDataModule)    ConnectCntksDB : TSQLite3Connection;    QueryCntksDB   : TSQLQuery;    TransCntksDB   : TSQLTransaction;    procedure DataModuleCreate(Sender: TObject);  private   public   end; var  DMCntks: TDMCntks;  CntksDB: string; implementation {$R *.lfm} { TDMCntks } procedure TDMCntks.DataModuleCreate(Sender: TObject);begin  ConnectCntksDB.DatabaseName:= SysUtils.ExtractFilePath(ParamStr(0)) + ContactsDB.sqlite3);  //-> Create a transactions...  TransCntksDB:= TSQLTransaction.Create(ConnectCntksDB);  //-> Point to the database instance...  TransCntksDB.DataBase:= ConnectCntksDB;  //-> Open the Contacts database...  ConnectCntksDB.Open;  //-> Point to the database and transaction...  QueryCntksDB.DataBase:= ConnectCntksDB;  QueryCntksDB.Transaction:= TransCntks;end; end. 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i unit CntksMain;{ Program....: CntksMain.pas  Author.....: Donald King  Date.......: 30 Nov. 2024  Copyright..: Copyright(c) 2024, 2025 NewFound Photo Art, Inc.  On the Web.: https://www.NewFoundPhotoArt.com  Last Update: 01/04/2025 } {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Dbf, DB, SQLite3Conn, SQLDB, SQLite3DS, DBGrids, DBCtrls,  Forms, Controls, Graphics, Dialogs, ExtCtrls, Buttons, StdCtrls, Menus,  ExtDlgs, ActnList, MaskEdit, LR_DBSet, LR_Class, JvDBLookup, JvDBSearchEdit,  SynHighlighterHTML, PrintersDlgs, jdblabeleddateedit,  { Custom units added below... }  CatMaint, CntriesMaint, StatesProvMaint, AboutCntkMgt, CntksMgtLicense,  Rebuildtables, Windows, LCLIntf, CntksDM; Then skip down to past private, public ...var  FrmCntksMain: TFrmCntksMain;  Keys: TStringList;  IntRecCount: Integer; procedure TFrmCntksMain.FormCreate(Sender: TObject);begin  //-> Add items to the CmboBxSelIndex component  CmboBxSelIndex.Items.Clear;  CmboBxSelIndex.Items.Add('Name (L,F,M)');  CmboBxSelIndex.Items.Add('Company');  CmboBxSelIndex.Items.Add('Category');  CmboBxSelIndex.Items.Add('State/Province');  CmboBxSelIndex.Items.Add('Country');  CmboBxSelIndex.Items.Add('City, State, Country');end; procedure TFrmCntksMain.FormShow(Sender: TObject);begin  SQLite3DSCntks.IndexFieldNames:= 'CNTKNAME';  //SQLite3DSCntks.Open;  SQLite3DSStates.IndexFieldNames:= 'STPROVCODE';  //SQLite3DSStates.Open;  SQLite3DSCntries.IndexFieldNames:= 'CODE';  //SQLite3DSCntries.Open;  SQLite3DSCategories.IndexFieldNames:= 'CATEGORIES';  //SQLite3DSCategories.Open;  DSStates.DataSet.FieldByName('STPROVCODE').DisplayWidth:= 2;  DSStates.DataSet.FieldByName('STPROVNAME').DisplayWidth:= 12;  DSCntries.DataSet.FieldByName('CODE').DisplayWidth:= 2;  DSCntries.DataSet.FieldByName('COUNTRY').DisplayWidth:= 10;  DSCategories.DataSet.FieldByName('CATID').DisplayWidth:= 2;  DSCategories.DataSet.FieldByName('CATEGORY').DisplayWidth:= 25;  BitBtnSaveCntk.Enabled:= False;  BitBtnCancel.Enabled:= False;  EditTTLCntks.ReadOnly:= False;  IntRecCount:= DbfCntks.ExactRecordCount;  EditTTLCntks.Text:= IntToStr(IntRecCount); //-> Show total contacts...  EditTTLCntks.ReadOnly:= True;end;  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. ---------------------------------------------------------------------- 1HuntnMan · 2025-04-12 20:43 Success! Thanks all for all your knowledge and great advice. No more DBase tables.