Looking for an embedded SQL Database

[teco]

BTW. There are also two other options.
Storing the sqlite3.dll in a resource and either:
1) unpacking the sqlite3.dll to a temp directory and loading it
2) loading it directly from the resource (via BTMemoryModule) (Edit: Woops, no 64 bit for that???)

Where 1 would be the easiest.
No need to change anything in FPC.

But this might still be called "fake" embedding

So... there isn't really a "real" embedded option for any database at all in FPC.

Do you have an example for this?
Can this be done only with executable or also with other files?

It brings me to an idea. I send my financee an image from me, packed as resource in an exe file. Image will be stored to the hard disk if she answers the question "Do you love me!" with Yes. . . 


... I am not good in programming as a hobby programmer, but good in having crazy ideas ...

[rvk]

Do you have an example for this?
Can this be done only with executable or also with other files?

Although this is kind of off topic in this topic.... because it's about SQL database...

<<off-topic>>
You can load everything from resource. Directly from resource or storing it to a temporary file first.

For images and other files it is really easy. You can just store it in a resource and use Component.LoadFromResource.

For Fonts it becomes a bit trickier because those need to be registered in Windows but they too can be loaded directly from resource without temporary file. I have an example here somewhere on the forum.

For DLL and executables it becomes even more trickier because then you would need to simulate the loading process of the DLL/executable.
The BTMemoryModule code does this but works only in 32 bit for now.

But for your idea... you can just create a file myimages.rc.

Code: [Select]

myimage RCDATA  "myimage.jpg"(png can be PNG and bmp can be BITMAP, but jpg needs to be RCDATA)

Include it in your .pp/.pas source with

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

1. {$R myimage.rc}
2. //...
3. Image1.Picture.Jpeg.LoadFromResourceName(HInstance, 'myimage');

Now, when compiling, the recource will be build and included in your executable and you can load it like above with LoadFromResourceName.
<<//off-topic>>

If you have other questions about this you might want to open your own topic for it

[rvk]

*snip* And because sqldb uses it in TSQLite3Connection it would mean a complete rewrite, I think. *snip*

I took a look at sqlite3conn, and i think pretty much everything can be done with conditional compilation (That "InitializeSQLite"/"ReleaseSQLite"-Stuff or whatever it's called).

I found two places in sqlite3conn, where they check
"... If Assigned(sqlite3_somefunction) Then ..."
which would obviously complain if using static ("wrong number of arguments"), since the standard is dynamic it uses variables to store the function-pointer which you can check with Assigned
No idea how to resolve that

Yep, you would need to change that too.
So... as I said... complete rewrite.

I thought someone here said something about a certain database engine not being "Embedded".
Well... it seems none of them really are 

[dsiders]

*snip* And because sqldb uses it in TSQLite3Connection it would mean a complete rewrite, I think. *snip*

I took a look at sqlite3conn, and i think pretty much everything can be done with conditional compilation (That "InitializeSQLite"/"ReleaseSQLite"-Stuff or whatever it's called).

I found two places in sqlite3conn, where they check
"... If Assigned(sqlite3_somefunction) Then ..."
which would obviously complain if using static ("wrong number of arguments"), since the standard is dynamic it uses variables to store the function-pointer which you can check with Assigned
No idea how to resolve that

Yep, you would need to change that too.
So... as I said... complete rewrite.

I thought someone here said something about a certain database engine not being "Embedded".
Well... it seems none of them really are 

To be fair, fcl-db packages are not the database(s).

[rvk]

I thought someone here said something about a certain database engine not being "Embedded".
Well... it seems none of them really are 

To be fair, fcl-db packages are not the database(s).

True... but I imagine everyone (someone) thought they could just be embedded with standard FPC/Lazarus (like it seems to be with Darwin).
But that seems to be a bit harder then initially thought

The Object Pascal techniques (possible with FPC itself) are not the problem (static linking works perfectly fine).

@teco... if adding the dll as resource and extracting it automatically to a temporary directory is also acceptable, this could be implemented more easily.

Create a textfile sqlite3.rc:

Code: [Select]

SQLITE3DLL RT_RCDATA "static\sqlite3.dll"Put the sqlite3.dll in a subdirectory static under your own program code.

Put this in your form source:

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

1. {$R sqlite3.rc}
2.  
3. procedure TForm1.FormCreate(Sender: TObject);
4. begin
5.   SQLite3Connection1.Connected := true;
6. end;
7.  
8. procedure LoadBeforeStart;
9. var
10.   SQLiteDll: TResourceStream;
11. begin
12.   SQLiteDll := TResourceStream.Create(HInstance, 'SQLITE3DLL', 'RT_RCDATA');
13.   try
14.     SQLiteDll.SaveToFile('sqlite3.dll');
15.   finally
16.     SQLiteDll.Free;
17.   end;
18. end;
19.  
20. initialization
21.   if not FileExists('sqlite3.dll') then
22.     LoadBeforeStart;
23.  
24. end.

LoadBeforeStart will extract the sqlite3.dll to the same directory as your .exe and load it correctly when needed (in the Connected := true line).

(Note: You need to set Connected to false before you compile your program otherwise you need to put this code in your .lpr file to extract it even sooner.)

[teco]

@teco... if adding the dll as resource and extracting it automatically to a temporary directory is also acceptable, this could be implemented more easily.

Create a textfile sqlite3.rc:

Code: [Select]

SQLITE3DLL RT_RCDATA "static\sqlite3.dll"Put the sqlite3.dll in a subdirectory static under your own program code.

Put this in your form source:

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

1. {$R sqlite3.rc}
2.  
3. procedure TForm1.FormCreate(Sender: TObject);
4. begin
5.   SQLite3Connection1.Connected := true;
6. end;
7.  
8. procedure LoadBeforeStart;
9. var
10.   SQLiteDll: TResourceStream;
11. begin
12.   SQLiteDll := TResourceStream.Create(HInstance, 'SQLITE3DLL', 'RT_RCDATA');
13.   try
14.     SQLiteDll.SaveToFile('sqlite3.dll');
15.   finally
16.     SQLiteDll.Free;
17.   end;
18. end;
19.  
20. initialization
21.   if not FileExists('sqlite3.dll') then
22.     LoadBeforeStart;
23.  
24. end.

LoadBeforeStart will extract the sqlite3.dll to the same directory as your .exe and load it correctly when needed (in the Connected := true line).

(Note: You need to set Connected to false before you compile your program otherwise you need to put this code in your .lpr file to extract it even sooner.)

Dear rvk,

this solution with the resource file is perfect. I can add the sqlite.dll and some reporting .lrf files as resource and extract when/if needed.

Only question is: how to create the resource file?
Under Project options-resources    rescource files can be added - just to place the sqlite.dll file here or does I need to use first the rescource converter from the tools directory to create and add the resource file?
 

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

1. lazres.exe sqliteresource.rc  \somefolder\sqlite.dll    // to be added as RCDATA on project options - resources

Thank you.

[TRon]

Only question is: how to create the resource file?

You can get Lazarus to do it for you with this

[teco]

Only question is: how to create the resource file?

You can get Lazarus to do it for you with this

Thank you for the information.

[Чебурашка]

Apart the everlasting curiosity/interest in the static linking approach (that would be applicable in many other contexts and it is really different from the external library approach), I am a little puzzled when I hear that the last proposed solution (exe with resource that eventually is extracted to a dll on usage) is preferred to the much simpler solution pack exe+dll into a zip and distribute the zip. To me all this seems transforming a nothing into a problem.

[domasz]

User "o":
https://forum.lazarus.freepascal.org/index.php?action=profile;u=72135
has created a static Sqlite:
https://github.com/SCLOrganization/Libraries/tree/main/SQLite3/x86_64-win64
https://github.com/SCLOrganization/SCL/tree/main/Source/SharedLibrary/SQLite3

[rvk]

Apart the everlasting curiosity/interest in the static linking approach (that would be applicable in many other contexts and it is really different from the external library approach), I am a little puzzled when I hear that the last proposed solution (exe with resource that eventually is extracted to a dll on usage) is preferred to the much simpler solution pack exe+dll into a zip and distribute the zip. To me all this seems transforming a nothing into a problem.

Because that was the original question.

I am looking for an embedded SQL Database I can include in an Windows application. I am trying to make a small cash box tool as single file application. So when I share it, I need only to give the application file and everything is included.

Of course using an install procedure would be the normal way to go.
Putting the sqlite3.dll besides the program.exe via an install program.
But then the question would be different.

Although... forum users here often don't know what they want or don't know how to ask a question (and ask for something they think is correct while in the meantime there is a perfectly fine other solution to their original problem, like you now suggest  ).

[Чебурашка]

[...] like you now suggest [...]

I said since the very beginning that shipping a simple {IFDEF LINUX}shared lib{$ENDIF}{IFDEF WINDOWS}dll{$ENDIF} together with the executable would be{IFDEF LINUX} almost{$ENDIF} straightforward{IFDEF LINUX}, as later you need either to place the shared lib in the search path or run the executable supplying the LD_LIBRARY_PATH where the library is{$ENDIF}.

[Zvoni]

User "o":
https://forum.lazarus.freepascal.org/index.php?action=profile;u=72135
has created a static Sqlite:
https://github.com/SCLOrganization/Libraries/tree/main/SQLite3/x86_64-win64
https://github.com/SCLOrganization/SCL/tree/main/Source/SharedLibrary/SQLite3

…. which is worthless in the context of this thread.
Those are just the object o-files, which are pretty easy to produce (see my answer „proof of concept“).
Take the sqlite amalgamation source, run the compilation as described. Done.

The „problem“is how to tell FPC to USE that file (as in: compile it INTO the executable), and how to „access“ the functions within (as in: how to declare)

[teco]

Although... forum users here often don't know what they want or don't know how to ask a question (and ask for something they think is correct while in the meantime there is a perfectly fine other solution to their original problem, like you now suggest  ).

Just as background information why I was looking for an embeedded database engine and why the resource files are the best option for me:

I have made some weeks ago a cash box tool for an old girlfriend. She use a notebook but has only Windows on it. Everything is stored on a Gmail account and on her google cloud storage. Exept her personal finance. This was the reason to write her a cash box tool and placed it as portable tool on a USB SSD. This is the only tool/application she has because everything else is cloud based.

She does not understand that there is not only the .exe file and the sqlite database file but also the sqlite3.dll and some report files (the .lrf files). In her opinion some of these files are not needed because she need not so much files when using a cloud application so some of these files are deleted often by her and calls me that the software is not working...

Telling her to leave the files and don't delete them is annoying. Telling a wall to fall down will have more success.

With the resource file I can add the sqlite engine and the report files. So this false embedded is for me the easiest solution to bypass that my old girlfriend is deleting files. On every start the tool checks for missing files and restore them.

Maybe my initial question was not perfect explained but the solution was more as I have expected.

[domasz]

The „problem“is how to tell FPC to USE that file (as in: compile it INTO the executable), and how to „access“ the functions within (as in: how to declare)

These files seem to do that, don't they?
https://github.com/SCLOrganization/SCL/tree/main/Source/SharedLibrary/SQLite3