SQLite3Connection testing

[MoonWolf_001]

Hi, I am MoonWolf from Japan. New to here. And Lazarus user in Japanese.

I have a DB question. I am looking for 100% sure DB Connection logic.

Test Case:

DBMS : SQLIte3 64bit for Win
A Table : created by SQLite3
Lazarus : v3.6 in Japanese
OS : Windows 11 Pro v24H2 in Japanese

Component : Form1 , SQLite3Connection

[1] Place SQLite3Connection on Form1
[2] Enter DB name to DataBaseName( ex. TEST01 )
[3] Check the connection by OI Connected -- Cannot be activated
[4] Restart Lazarus
[5] Check the connection by OI Connected -- Connected
// OI = Object Inspector

Many of case, by doing this, the connection is established.

But this is not 100% sure.
If it's not done,  these are my alternatives,

1. check SQLite3.DLL or overwrite the DLL for new one.
2. check Third Party Security Program
and do on..

Do you have any other idea so that make SQLite3Connection stable?

It would be really appreciate if I could have some advice.

[rvk]

1. check SQLite3.DLL or overwrite the DLL for new one.
2. check Third Party Security Program
and do on..

Do you have any other idea so that make SQLite3Connection stable?

It would be really appreciate if I could have some advice.

Where did you copy the SQLite3.dll?

You need it at two locations.
When using the IDE you need the SQLite3.dll inside the directory where lazarus.exe is.
And for running your program outside the IDE, you need it in the directory of your .exe.

[Zvoni]

1. check SQLite3.DLL or overwrite the DLL for new one.
2. check Third Party Security Program
and do on..

Do you have any other idea so that make SQLite3Connection stable?

It would be really appreciate if I could have some advice.

Where did you copy the SQLite3.dll?

You need it at two locations.
When using the IDE you need the SQLite3.dll inside the directory where lazarus.exe is.
And for running your program outside the IDE, you need it in the directory of your .exe.

And remember the "bitness":
If your Lazarus-IDE is 64-Bit, you need the 64-Bit SQlite3.dll besides the lazarus.exe, if IDE is 32Bit.....
If Target-Bitness is 64Bit, you need 64Bit Sqlite3.dll besides the project.exe, if 32Bit....

All said: You're using the GUI-components, so i'm out of here....

[backprop]

1. check SQLite3.DLL or overwrite the DLL for new one.
2. check Third Party Security Program
and do on..

Do you have any other idea so that make SQLite3Connection stable?

In my experience, always is better to provide SQLite library which is fully tested with executable. Any new "stable" version may actually be incompatible, broken, slower etc.

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

1. uses sqlite3dyn;
2. ...
3. procedure TfrmMain.FormCreate(Sender: TObject);
4. begin
5.  
6.   sqlite3dyn.SQLiteDefaultLibrary := 'libsqlite3.so.0.8.6'; /// Similar with windows library, with full path optionally
7.   ...
8.  

[backprop]

DB creation example:

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

1. procedure CreateDB(Filename: string);
2. var
3.   Connect: TSQLite3Connection;
4.   Trans: TSQLTransaction;
5.   st: TStringList;
6. begin
7.  
8.   Connect := TSQLite3Connection.Create(nil);
9.   Trans := TSQLTransaction.Create(Connect);
10.  
11.   st := TStringList.Create;
12.  
13.   try
14.  
15.     Connect.Transaction := Trans;
16.     Connect.DatabaseName := Filename;
17.  
18.     Trans.StartTransaction;
19.  
20.     // Create database
21.  
22.     st.Clear;
23.     st.Add('CREATE TABLE IF NOT EXISTS...'); // whatever
24.  
25.     Connect.ExecuteDirect(st.Text);
26.  
27.     // additional statements, commit at end
28.  
29.     Trans.Commit;
30.  
31.   finally
32.     st.Free;
33.     Trans.Free;
34.     Connect.Free;
35.   end;
36.  
37. end;
38.  
39.  

Similar with query, just use TSQLQuery. I can provide examples if you want.

[MoonWolf_001]

Thank you so much for your advice:

For rvk,
Yes, SQLite3.dll inside the directory where lazarus.exe is.

For Zvoni,
I use Win11, my IDE and SQLite3 are both 64-bit.

For backprop
Thank you. Your explicit definition of DB is valuable.
I only check with Object Inspector.
I haven't check like yours in code.

I was not 100% srue for this matter.
But now, it comes close to 100%

[CharlyTango]

if you like to have a working framework look at
https://github.com/CharlyTango/ct_sqldatabaseconnection

You then have a working SQLite Connection with somegoodies.
Furthermore it shows how to use Frames and Forms

[backprop]

For backprop
Thank you. Your explicit definition of DB is valuable.
I only check with Object Inspector.
I haven't check like yours in code.

It is the same, just without visual components. Only you need to connect necessary components in IDE as shown.

If you use specific SQLite library, ensure to be added as shown and that is all. Provide SQLite library you need with your executable, not Lazarus.exe.

[MoonWolf_001]

To CharlyTango,
Thanks for sharing good project. To be honest, it's beyond my understanding, however I can learn many things. For exsample how to use Github itself. I will come back to your code when I need larger db project.

To backprop,
Thank you for guide me by using code only case. The idea itself is very valuable. And you are very kind.

---------------------------------------------------------------------------

In Summary, the reason why ask this question was to gain enough knowledge for stable SQlite3 connection. In my case, more than 95%
 case , it's done stably. Rest of the case, I manage to overcome establishing the connection.

Please find an attached Picture.This is my largest DB project so far. This is my 'To do list Level10', using SQL3Connection, SQLTransaction, SQLQuery, DataSource, DBGrid, DBNavigator.

I really satisfied with lazarus. And I would like to share the nice IDE, Lazarus, to Japanese.

And This is my first post, the issue had been solved.
Thank you so much for your valuable advice.

[MoonWolf_001]

Hi, This is MoonWolf. This is just for your information.
I would like to report :
Lazarus DBGrid and DBNavigator work well in Japanese environment.

Testing Environment:
IDE : Lazarus 3.6 in JA language
DB : SQLite3
OS : Windows11 Pro in Japanese

Testing Case:
- Shown table is prepared by SQLite3 in advance.
  The table has some Japanese characters.
- SQLite3.DLL is the same folder of *.exe
- Connect SQLite3 by SQLite3Connection
- Place SQLTransaction, SQLQuery, DataSource, DBGrid and DBNavigator.
- First, only show table by DBGrid.
- Next, modify field data, insert record, delete record by DBNavigator.

Everything works well for this scenario.
Testing result summary in short video:
https://www.youtube.com/watch?v=fgowvdOZrKU

Pascal is not known in Japan. Lazarus has Form Editor and Object Inspecotor. Compare to other language, Lazarus is advanced and easy to use. So, I would like to nicely share this IDE to Japanese.