Looking for an embedded SQL Database
lazarus / 16-Databases

----------------------------------------------------------------------
teco · 2024-03-13 09:22

Hi,

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.

Thank you for help.

----------------------------------------------------------------------
rvk · 2024-03-13 09:57

Your best bet would be SQLite3.
Easiest would be to just distribute the dll with your .exe.
If you really only want one .exe and absolutely no extra supporting
files, you need to look into embedding sqlite3.obj into your .exe with
static linking.

Like mORMot2 does:
https://blog.synopse.info/?post/2019/09/21/SQLite3-static-linking-for-
Delphi-Win64.
I'm not sure if there are examples of this here on the forum.

----------------------------------------------------------------------
teco · 2024-03-13 10:06

Quote from: rvk on March 13, 2024, 10:57:34 amYour best bet would be
SQLite3.
Easiest would be to just distribute the dll with your .exe.
If you really only want one .exe and absolutely no extra supporting
files, you need to look into embedding sqlite3.obj into your .exe with
static linking.

Like mORMot2 does:
https://blog.synopse.info/?post/2019/09/21/SQLite3-static-linking-for-
Delphi-Win64.
I'm not sure if there are examples of this here on the forum.

Dear rvk,
Thank for the information, but I am a hobby programmer and have never
included an .obj file. I don't know how to do this without an
example/tutorial.

Sorry.

----------------------------------------------------------------------
Чебурашка · 2024-03-13 10:10

In windows if you copy the libsqlite3.ddl in the same directory where
the executable is, then this directory will be used as library search
path, so the easiest way with the dll is easy to do.

----------------------------------------------------------------------
Thaddy · 2024-03-13 10:14

For a truly embedded database, link f.e. SQLite statically. That is
the true meaning of embedded, btw.
Ignore any other nitwits.
Embedded means single executable, no dependencies.
Alas the term is used rather loosely. (FB)

----------------------------------------------------------------------
teco · 2024-03-13 10:21

Quote from: Thaddy on March 13, 2024, 11:14:35 amFor a truly embedded
database, link f.e. SQLite statically. That is the true meaning of
embedded, btw.
Ignore any other nitwits.
Embedded means single executable, no dependencies.
Alas the term is used rather loosely. (FB)

Thank you. This is exactly what I am looking for.
Is there an example for hobby programmer to recommend? I have no
expericence with static linking.

----------------------------------------------------------------------
Zvoni · 2024-03-13 10:36

Quote from: Чебурашка on March 13, 2024, 11:10:07 amIn windows if you
copy the libsqlite3.ddl in the same directory where the executable is,
then this directory will be used as library search path, so the
easiest way with the dll is easy to do.
There is no libsqlite3.dll ......
.... it doesn't exist.....

That said: What Thaddy said. Static linking is the way

----------------------------------------------------------------------
Thaddy · 2024-03-13 10:53

I will add an examlpe later, but mORMot has also an example. Linking
object files is not difficult at all once you know how to do it. You
do not need any dll's. (that would mean fake embedded   )
RVK and I are on the same line, I believe.

----------------------------------------------------------------------
Чебурашка · 2024-03-13 11:10

Quote from: Zvoni on March 13, 2024, 11:36:18 amQuote from: Чебурашка
on March 13, 2024, 11:10:07 amIn windows if you copy the
libsqlite3.ddl in the same directory where the executable is, then
this directory will be used as library search path, so the easiest way
with the dll is easy to do.
There is no libsqlite3.dll ......
.... it doesn't exist.....

That said: What Thaddy said. Static linking is the way

Sorry, correct name is "sqlite3.dll".

----------------------------------------------------------------------
Чебурашка · 2024-03-13 11:13

Quote from: Thaddy on March 13, 2024, 11:53:59 amI will add an examlpe
later...

Despite so far I always followed the external library approach both in
windows and linuxes, I am also interested in the topic of static
linking, so I look forward for this post, as it will be very useful
knowledge.

----------------------------------------------------------------------
Zvoni · 2024-03-13 11:32

Quote from: Чебурашка on March 13, 2024, 12:13:32 pmQuote from: Thaddy
on March 13, 2024, 11:53:59 amI will add an examlpe later...

Despite so far I always followed the external library approach both in
windows and linuxes, I am also interested in the topic of static
linking, so I look forward for this post, as it will be very useful
knowledge.

In a nutshell:
{$LinkLib LIBNAME} --> you need the LIBNAME.a file
{$Link LIBNAME} --> you need the LIBNAME.o file

In any case, your Declarations are as follows:

Function
SomeFunction(SomeArguments):SomeReturnType;call_convention;external;
--> NO LIBNAME!!

call_convention whatever the lib is compiled with. On Windows usually
stdcall, in case of sqlite3 though it's cdecl

----------------------------------------------------------------------
MarkMLl · 2024-03-13 13:18

Quote from: Zvoni on March 13, 2024, 12:32:33 pmIn a nutshell:
{$LinkLib LIBNAME} --> you need the LIBNAME.a file
{$Link LIBNAME} --> you need the LIBNAME.o file

Thanks for that, useful and concise summary.

MarkMLl

----------------------------------------------------------------------
Zvoni · 2024-03-13 13:31

Quote from: MarkMLl on March 13, 2024, 02:18:43 pmQuote from: Zvoni on
March 13, 2024, 12:32:33 pmIn a nutshell:
{$LinkLib LIBNAME} --> you need the LIBNAME.a file
{$Link LIBNAME} --> you need the LIBNAME.o file

Thanks for that, useful and concise summary.

MarkMLl

Sidenote: difference between "a" and "o"-files (at least as i
understand it).
"o"-files are usually the compile-output of a single source-code-unit
(e.g. "mycode.c" --> "mycode.o")
"a"-files are more like an archive, that can contain multiple
"o"-files

Difference in Linking:
specified "o"-files get sucked into the final executable. Period
a specified "a"-file only sucks in those "o"-files you actually try to
access in your code

See here: https://stackoverflow.com/questions/654713/o-files-vs-a-
files
Answer by Tom Böhmer

----------------------------------------------------------------------
rvk · 2024-03-13 14:38

Quote from: Thaddy on March 13, 2024, 11:53:59 amI will add an examlpe
later, but mORMot has also an example. Linking object files is not
difficult at all once you know how to do it. You do not need any
dll's. (that would mean fake embedded   )
RVK and I are on the same line, I believe.
Yep. I agree. FB has 'fake' embedding.
But saying that... so does SQLite3 on Windows in standard FPC.
I don't see any static loading code of SQLite3 for Windows in the
sources.
Or am I looking at the wrong place?

So SQLite3 also has always been 'fake' embedded 

Only way I see it is using mORMot2 at the moment (or changing a lot of
code in FPC).

----------------------------------------------------------------------
Zvoni · 2024-03-13 14:53

Quote from: rvk on March 13, 2024, 03:38:44 pmYep. I agree. FB has
'fake' embedding.
But saying that... so does SQLite3 on Windows in standard FPC.
I don't see any static loading code of SQLite3 for Windows in the
sources.
Or am I looking at the wrong place?

So SQLite3 also has always been 'fake' embedded 

Only way I see it is using mORMot2 at the moment (or changing a lot of
code in FPC).
Would have to agree. It's "fake" static/embedded.
The crucial file is sqlite3.inc, so if anyone is up for it?

----------------------------------------------------------------------
Чебурашка · 2024-03-13 15:19

Quote from: rvk on March 13, 2024, 03:38:44 pmI don't see any static
loading code of SQLite3 for Windows in the sources.

This is why I said that I was looking forward to see a working example
of static linking with great interest.
I had already in the past similar "whishes" but I was not able to
obtain it just with a couple of configurations/directives.

----------------------------------------------------------------------
Zvoni · 2024-03-13 15:29

Quote from: Чебурашка on March 13, 2024, 04:19:08 pmQuote from: rvk on
March 13, 2024, 03:38:44 pmI don't see any static loading code of
SQLite3 for Windows in the sources.

This is why I said that I was looking forward to see a working example
of static linking with great interest.
I had already in the past similar "whishes" but I was not able to
obtain it just with a couple of configurations/directives.

There is also no true static linking for Linux.

Only for Darwin as far as i could see

----------------------------------------------------------------------
Чебурашка · 2024-03-13 15:36

Quote from: Zvoni on March 13, 2024, 04:29:02 pmThere is also no true
static linking for Linux.

This is even more why I said that I was looking forward to see a
working example of static linking with great interest.

----------------------------------------------------------------------
Zvoni · 2024-03-13 15:55

Quote from: Чебурашка on March 13, 2024, 04:36:06 pmQuote from: Zvoni
on March 13, 2024, 04:29:02 pmThere is also no true static linking for
Linux.

This is even more why I said that I was looking forward to see a
working example of static linking with great interest.
Which is actually pretty simple.
See my answer #10 and #12

What the work is, is creating the „interface“ and the a/o file, since
not everyone is comfortable compiling c-code
In case of sqlite, it‘s the sqlite3.inc file and the corresponding a/o
file

I might take up the challenge

----------------------------------------------------------------------
Thaddy · 2024-03-13 15:58

Static linking can be done on Windows and Linux. I am not at home. so
be patient.

----------------------------------------------------------------------
DonAlfredo · 2024-03-13 16:12

For me, the main selling point of static mORMot is the fact that the
database can be encrypted. In use(static)  on Windows, Linux and
Android.

----------------------------------------------------------------------
rvk · 2024-03-13 17:10

Quote from: Zvoni on March 13, 2024, 04:55:57 pmWhat the work is, is
creating the „interface“ and the a/o file, since not everyone is
comfortable compiling c-code
In case of sqlite, it‘s the sqlite3.inc file and the corresponding a/o
file
The sqlite3.inc already largely consist of ifdef for symbol S which is
set when not dynamically.
So that could be 'easy'.

But everywhere in FPC itself (for the connector), sqlite3dyn is used
in the uses. So that also needs to be changed.

----------------------------------------------------------------------
Thaddy · 2024-03-13 17:43

I know that. But this from a Phone.

----------------------------------------------------------------------
Zvoni · 2024-03-14 07:02

Quote from: rvk on March 13, 2024, 06:10:15 pmQuote from: Zvoni on
March 13, 2024, 04:55:57 pmWhat the work is, is creating the
„interface“ and the a/o file, since not everyone is comfortable
compiling c-code
In case of sqlite, it‘s the sqlite3.inc file and the corresponding a/o
file
The sqlite3.inc already largely consist of ifdef for symbol S which is
set when not dynamically.
So that could be 'easy'.

But everywhere in FPC itself (for the connector), sqlite3dyn is used
in the uses. So that also needs to be changed.
Which should be pretty simple.
As far as i could find out, sqlite3dyn is only "used" in sqlite3conn
(which makes sense) (maybe on those sqliteXXds files. Haven't looked)
Since you would need a compile-time define, it should go along the
lines of in the uses-clause
Uses ......, {$IFDEF
LOAD_DYNAMICALLY}sqlite3dyn,{$ELSE}sqlite3static,{$ENDIF} ....
with sqlite3static being the "container" defining "S" and then pulling
in the sqlite3.inc
or along those lines.....
haven't spent much time thinking about it

----------------------------------------------------------------------
Zvoni · 2024-03-14 08:33

Quote from: MarkMLl on March 13, 2024, 02:18:43 pmQuote from: Zvoni on
March 13, 2024, 12:32:33 pmIn a nutshell:
{$LinkLib LIBNAME} --> you need the LIBNAME.a file
{$Link LIBNAME} --> you need the LIBNAME.o file

Thanks for that, useful and concise summary.

MarkMLl

Proof of concept:
Lazarus 2.2.2 32-Bit/FPC3.2.2 32-Bit

File: libzvoni.c
Code: C  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i #include
<stdio.h> int ZvoniAdd(int a, int b, int* c){        *c=42;       
return a+b;} I used Geany, and compiled with
gcc -Wall -m32 -c libzvoni.c -o libzvoni.o
(-m32 because i only have Laz/FPC 32-Bit. For 64 leave out the -m32 or
specify your own Architecture)

libzvoni.c/libzvoni.o are placed directly besides my lpr/pas files
(same folder)

File project1.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
project1;{$mode objfpc}{$H+}{$Link libzvoni} Function ZvoniAdd(Const
a:Integer;Const b:Integer;out
Surprise:Integer):Integer;cdecl;external;Var  Check:Integer;begin 
Writeln('Result: ',ZvoniAdd(2,5,Check),'- Surprise: ',Check);end.Once
your exe is compile, you can remove/rename the o-file for proof, and
run it again from console

Note: No Idea about name-mangling.
I did a nm -a on my project1.exe, and found
_ZvoniAdd (Note the underscore)
I admit: definitely not knowledgable about stuff like that

----------------------------------------------------------------------
PierceNg · 2024-03-14 12:25

Quote from: Чебурашка on March 13, 2024, 04:36:06 pmThis is even more
why I said that I was looking forward to see a working example of
static linking with great interest.

mORMot does static linking of SQLite on Linux and Windows.

Windows 10 running 64-bit executable cross-compiled from FPC trunk on
WSL2 Ubuntu 22.04 running on said Windows:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Software
version tested: 2.2.6584 (2024-03-14 19:25:30) Windows 10 64bit
(10.0.19045) [utf8 13.9GB 4A651701]Using mORMot 2.2.6584 x64MMs   
TSqlite3LibraryStatic 3.44.2 with internal MMGenerated with: Free
Pascal 3.3.1 64 bit Win compiler Total assertions failed for all test
suits:  24 / 79,223,382
Doh, some failed tests!

Same Windows 10 running 32-bit executable cross-compiled from FPC
trunk on same WSL2 Ubuntu:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Software
version tested: 2.2.6584 (2024-03-14 19:32:44) Windows 10 64bit
(10.0.19045) [utf8 13.9GB 4A651701]Using mORMot 2.2.6584   
TSqlite3LibraryStatic 3.44.2 with internal MMGenerated with: Free
Pascal 3.3.1 32 bit Win compiler Total assertions failed for all test
suits:  0 / 79,213,645
Another computer running Ubuntu 22.04:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Software
version tested: 2.2.6584 (2024-03-13 16:33:54) Ubuntu 22.04.4 LTS -
Linux 5.15.0-100-generic [utf8 31.2GB 50F0010]Using mORMot 2.2.6584
and OpenSSL 30000020    TSqlite3LibraryStatic 3.44.2 with internal
MMGenerated with: Free Pascal 3.3.1 64 bit Linux compiler Total
assertions failed for all test suits:  0 / 79,307,848
Relevant source file is db/mormot.db.raw.sqlite3.static.pas:

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 {$ifdef
FPC}  // FPC expects .o linking   {$ifdef OSWINDOWS}    {$ifdef
CPU64}      {$L ..\..\static\x86_64-win64\sqlite3.o}    {$else}     
{$L ..\..\static\i386-win32\sqlite3.o}    {$endif CPU64}  {$endif
OSWINDOWS} // blah blah blah

----------------------------------------------------------------------
Чебурашка · 2024-03-14 12:57

Examples posted are nice, but would it be possible to make the same
with sqlite, which is what OP seems to need?

----------------------------------------------------------------------
rvk · 2024-03-14 13:07

Quote from: Чебурашка on March 14, 2024, 01:57:43 pmExamples posted
are nice, but would it be possible to make the same with sqlite, which
is what OP seems to need?
Thaddy said he was working on an example/solution 

But seeing the code of sqldb in FPC, I wonder if it's that easy. You
probably don't want to include the static sqlite in lazarus.exe
itself, but only for compilation. And because sqldb uses it in
TSQLite3Connection it would mean a complete rewrite, I think. If using
it in lazarus.exe static too, it might just be a matter of a few lines
(just adding the link/linklib).

Probably the easiest solution is to look at mORMot2.

----------------------------------------------------------------------
rvk · 2024-03-14 13:33

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.

----------------------------------------------------------------------
Zvoni · 2024-03-14 13:50

Quote from: rvk on March 14, 2024, 02:07:12 pm*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

----------------------------------------------------------------------
teco · 2024-03-14 14:31

Quote from: rvk on March 14, 2024, 02:33:43 pmBTW. 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 · 2024-03-14 15:00

Quote from: teco on March 14, 2024, 03:31:57 pmDo 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$R
myimage.rc}//...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 · 2024-03-14 15:34

Quote from: Zvoni on March 14, 2024, 02:50:28 pmQuote from: rvk on
March 14, 2024, 02:07:12 pm*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 · 2024-03-14 15:39

Quote from: rvk on March 14, 2024, 04:34:24 pmQuote from: Zvoni on
March 14, 2024, 02:50:28 pmQuote from: rvk on March 14, 2024, 02:07:12
pm*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 · 2024-03-14 16:13

Quote from: dsiders on March 14, 2024, 04:39:13 pmQuote from: rvk on
March 14, 2024, 04:34:24 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$R
sqlite3.rc} procedure TForm1.FormCreate(Sender: TObject);begin 
SQLite3Connection1.Connected := true;end; procedure
LoadBeforeStart;var  SQLiteDll: TResourceStream;begin  SQLiteDll :=
TResourceStream.Create(HInstance, 'SQLITE3DLL', 'RT_RCDATA');  try   
SQLiteDll.SaveToFile('sqlite3.dll');  finally    SQLiteDll.Free; 
end;end; initialization  if not FileExists('sqlite3.dll') then   
LoadBeforeStart; 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 · 2024-03-15 07:23

Quote from: rvk on March 14, 2024, 05:13:03 pm@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$R
sqlite3.rc} procedure TForm1.FormCreate(Sender: TObject);begin 
SQLite3Connection1.Connected := true;end; procedure
LoadBeforeStart;var  SQLiteDll: TResourceStream;begin  SQLiteDll :=
TResourceStream.Create(HInstance, 'SQLITE3DLL', 'RT_RCDATA');  try   
SQLiteDll.SaveToFile('sqlite3.dll');  finally    SQLiteDll.Free; 
end;end; initialization  if not FileExists('sqlite3.dll') then   
LoadBeforeStart; 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i lazres.exe
sqliteresource.rc  \somefolder\sqlite.dll    // to be added as RCDATA
on project options - resources
Thank you.

----------------------------------------------------------------------
TRon · 2024-03-15 07:26

Quote from: teco on March 15, 2024, 08:23:14 amOnly question is: how
to create the resource file?
You can get Lazarus to do it for you with this

----------------------------------------------------------------------
teco · 2024-03-15 07:32

Quote from: TRon on March 15, 2024, 08:26:40 amQuote from: teco on
March 15, 2024, 08:23:14 amOnly question is: how to create the
resource file?
You can get Lazarus to do it for you with this

Thank you for the information.

----------------------------------------------------------------------
Чебурашка · 2024-03-15 18:10

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 · 2024-03-15 18:17

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 · 2024-03-15 18:21

Quote from: Чебурашка on March 15, 2024, 07:10:39 pmApart 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.

Quote from: teco on March 13, 2024, 10:22:30 amI 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   ).

----------------------------------------------------------------------
Чебурашка · 2024-03-15 19:32

Quote from: rvk on March 15, 2024, 07:21:00 pm[...] 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 · 2024-03-15 20:46

Quote from: domasz on March 15, 2024, 07:17:53 pmUser "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 · 2024-03-15 23:58

Quote from: rvk on March 15, 2024, 07:21:00 pmAlthough... 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 · 2024-03-16 04:49

Quote from: Zvoni on March 15, 2024, 09:46:25 pmThe „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

----------------------------------------------------------------------
Thaddy · 2024-03-16 09:41

compile the amalgamation like so;Code: Text
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i gcc -c
sqlite3.cThen create the library like so:Code: Bash
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ar rcs
libsqlite3.a sqlite3.oYou will now have a libsqlite3.a that you can
statically link to your application. For windows you will need mingw
or cygwin.
It is really easy.
you only need to make some small changes to the top of sqlite3.inc to
prevent it choosing a dynamic library. and {$define S}
This is actually a bug that the default is dynamically linked, but
easy to solve.

----------------------------------------------------------------------
rvk · 2024-03-16 11:14

Quote from: Thaddy on March 16, 2024, 10:41:48 amcompile the
amalgamation like so;Code: Text  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i gcc -c sqlite3.cThen create the library like so:Code: Bash
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ar rcs
libsqlite3.a sqlite3.oYou will now have a libsqlite3.a that you can
statically link to your application. For windows you will need mingw
or cygwin.
It is really easy.
[/quote
And can you show an example of that where you use the TSQLConnector
with that?
Or does TS need to program all that again himself?

The problem was never creating an .a or .o (mORMot explains how to do
that somewhere)  but to use it with Lazarus and its existing
components. fcl-db just isn't written for embedding.

----------------------------------------------------------------------
Thaddy · 2024-03-16 12:00

You should not use fcl-db but the standalone sqlite package. you do
not need fcl-db.

----------------------------------------------------------------------
Zvoni · 2024-03-25 10:50

Sorry for the "bump", but i was thinking about this, why FPC/Lazarus
don't "preoffer" a fully static linking, and i think i've found the
answer:
It's because of those "visual" components you can drop on a Form (and
further connecting them to DB-Bound controls).

Think about it: If we switch from dynamic to fully static linking for
a project, how would Lazarus handle those components, if e.g. you set
"Connected" to true in the Object Inspector?
and we haven't "compiled" a single line of code yet
Remember how many times we forgot to place the sqlite3.dll besides the
Lazarus.exe for a quick test or proof of concept (where you don't want
to save the project first to a dedicated folder).

----------------------------------------------------------------------
rvk · 2024-03-25 11:06

Quote from: Zvoni on March 25, 2024, 11:50:51 amSorry for the "bump",
but i was thinking about this, why FPC/Lazarus don't "preoffer" a
fully static linking, and i think i've found the answer:
It's because of those "visual" components you can drop on a Form (and
further connecting them to DB-Bound controls).
Yes, but you could still program it so that lazarus.exe uses a dynamic
sqlite.dll for itself (components) and static for compiling and
executing an external exe.

Isn't that how it's done with other db components?
Why is there a static option for darwin?

(BTW I never use connected stage for those components when designing
anyways  )

----------------------------------------------------------------------
Zvoni · 2024-03-25 11:37

Quote from: rvk on March 25, 2024, 12:06:40 pmYes, but you could still
program it so that lazarus.exe uses a dynamic sqlite.dll for itself
(components) and static for compiling and executing an external exe.
Yes, was thinking about that, too, but have no idea what you would
have to do where

QuoteIsn't that how it's done with other db components?
Why is there a static option for darwin?No idea.

Quote(BTW I never use connected stage for those components when
designing anyways  )
I hear you. No DB-Bound controls or design-time components for me,
either.
Nevermind, that i usually separate Database-Access-Layer from Frontend