Converting a Project from TDbf to SQLite [SOLVED]

[1HuntnMan]

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]

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]

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]

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]

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]

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!!!

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]

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]

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][+][-]

1. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
2. begin
3.   SQLite3PMSDB:= TSQLite3Connection.Create(nil); <--Error
4.   SQLite3PMSDB.Connected:= True;
5.   //->SQLite3PMSDB.DatabaseName:= PMSDB;
6. end;
7.  
8. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
9. begin
10.   SQLQueryCntries.SQL.Text:= 'select * from COUNTRIES';
11.   Screen.Cursor:= crDefault;
12. end;
13.  
14. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
15. begin
16.   try
17.     SQLQueryCntries.Close;
18.   finally
19.     SQLTransactionCntries.Active:= False;
20.     SQLite3PMSDB.Connected:= False;
21.   end;
22.   FrmCntriesMaint.Close;
23.   FrmCntriesMaint.Free;
24. end;
25.  

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]

You did not specify the name of the database file, modify the code as follows

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

1. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
2. begin
3.   SQLite3PMSDB := TSQLite3Connection.Create(nil);
4.   SQLite3PMSDB.DatabaseName := 'c:\path\to\sqlite\database\file.db';
5.   SQLite3PMSDB.Connected := True;
6. end;
7.  

[TRon]

@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]

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][+][-]

1. procedure TFrmCntriesMaint.FormCreate(Sender: TObject);
2. begin
3.   SQLite3PMSDB := TSQLite3Connection.Create(nil);
4.   SQLite3PMSDB.DatabaseName :=
5.   'C:\Users\DKing\Documents\Software Development\PMS Project\PMS Data\PMSDB.sqlite';
6.   SQLite3PMSDB.Connected := True;
7. end;
8.  
9. procedure TFrmCntriesMaint.FormShow(Sender: TObject);
10. begin
11.   SQLQueryCntries.SQL.Text:= 'select * from COUNTRIES';
12.   Screen.Cursor:= crDefault;
13. end;
14.  
15. procedure TFrmCntriesMaint.SpdBtnCloseCntriesClick(Sender: TObject);
16. begin
17.   try
18.     SQLQueryCntries.Close;
19.   finally
20.     SQLTransactionCntries.Active:= False;
21.     SQLite3PMSDB.Connected:= False;
22.   end;
23.   FrmCntriesMaint.Close;
24.   FrmCntriesMaint.Free;
25. end;
26.  

Thanks TRon, I was just attempting to get just one form to work and then tackle the rest of the units.

[1HuntnMan]

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]

What 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][+][-]

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

[JanRoza]

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][+][-]

1. // Database standaard in program folder
2.   strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'yourdatabasename.db';
3.  

This way your program keeps working even when you relocate is to another location.
Just my 5 cents of advice.

[1HuntnMan]

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.