Database example without GUI
lazarus / 16-Databases

----------------------------------------------------------------------
Tanto · 2024-11-01 12:29

Hello guys,
I am looking for an example to create a database connection without
using a graphical interface.
I have already searched google and searched everything here in the
forum, but always find only examples with GUI
Do any of you have a page or example that I can work with?

Thank you very much

----------------------------------------------------------------------
tr_escape · 2024-11-01 12:37

Did you search google?

https://gist.github.com/pakLebah/277e0875a9ff50b9186fa9e166667add

----------------------------------------------------------------------
MarkMLl · 2024-11-01 12:46

I suggest looking at the tutorials etc. under "S" at
https://wiki.freepascal.org/Category:Databases

Broadly speaking, with the exception of the explicitly GUI stuff most
of the classes etc. are in the FCL (Freepascal Class Library) rather
than in the LCL (Lazarus Class Library).

MarkMLl

----------------------------------------------------------------------
Tanto · 2024-11-01 13:28

thx to:
tr_escape
and
MarkMLl
i will show and try and give an answer

thx

----------------------------------------------------------------------
Handoko · 2024-11-01 13:30

Quote from: Tanto on November 01, 2024, 01:29:20 pmI am looking for an
example to create a database connection without using a graphical
interface.

The 49 lines of code testfirebird provided by rvk, tested and worked:
https://forum.lazarus.freepascal.org/index.php/topic,63142.msg477919.h
tml#msg477919

----------------------------------------------------------------------
MarkMLl · 2024-11-01 13:43

Quote from: Tanto on November 01, 2024, 02:28:43 pmi will show and try
and give an answer

Keep at it, we're definitely not trying to give you the brushoff and I
for one agree that it's something that's under-discussed.

Having said which, there's some things that a database's backend
typically allows you to do (database and user/role creation as
examples) that have portability problems, so don't expect that you'll
be able to write a single text frontend and apply it to all of the
available servers. I've successfully done that targeting PostgreSQL +
Firebird, but to get much further with... well, quite frankly anything
you're going to need to tell us what you're using.

MarkMLl

----------------------------------------------------------------------
Tanto · 2024-11-01 14:58

Hello Handoko and the other guy's,

i've tried the example (and a lot more), by adapting it for sqlite,
but everytime i got an "Error: Access violation"
by line see '<<--'

here my example code wich i wrote by all examples of everything i
show:

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
DataBaseModul1; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils,
fpWeb, db, sqldb, sqlite3conn; type  TMyDataBaseConnector =
class(TObject)    sqlite3: TSQLite3Connection;    dbquery: TSQLQuery; 
private    { will be come later }  public    constructor Create;   
function CheckUserEntry(uname: String; ukey: String): String; 
end; var  DBModul: TMyDataBaseConnector; implementation   constructor
TMyDataBaseConnector.Create;  begin      end;   function
TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String):
String;  begin    sqlite3:= TSQLite3Connection.Create(nil);    
sqlite3.Transaction:= TSQLTransaction.Create(sqlite3);   
sqlite3.Transaction.DataBase:= sqlite3;    sqlite3.LoginPrompt:=
False;    sqlite3.DatabaseName:= 'test.db';    sqlite3.Connected:=
True;  // <<-- here comes the error     dbquery:=
TSQLQuery.Create(sqlite3);    dbquery.DataBase:= sqlite3;   
dbquery.Transaction:= sqlite3.Transaction;     try      if
FileExists(sqlite3.DatabaseName) then         WriteLn('File
EXISTS');       if sqlite3.Connected then       
 WriteLn('CONNECTED');       WriteLn('SELECT');     
dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';       WriteLn('>
Execute SQL');      dbquery.Open;       WriteLn('Read EOF');     
while not dbquery.EOF do      begin       
WriteLn(dbquery.FieldByName('username').AsAnsiString);       
dbquery.Next;      end;    except on E: Exception do     
WriteLn('Something happens: ', E.ToString);    end;     
CheckUserEntry:= '';  end; end.  
i hope anybody can help me to solve the problem

thx at all

----------------------------------------------------------------------
dseligo · 2024-11-01 15:30

Try this for sqlite:
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
sqlite_sqldb; uses SysUtils, sqlite3conn, sqldb; var 
SQLite3Connection: TSQLite3Connection;  Query: TSQLQuery; 
Transaction: TSQLTransaction; begin  SQLite3Connection :=
TSQLite3Connection.Create(nil);  Transaction :=
TSQLTransaction.Create(nil);  Query := TSQLQuery.Create(nil);  try   
Transaction.Action := caCommit;    Transaction.Options :=
[stoUseImplicit];     // choose database (file) name   
SQLite3Connection.DatabaseName := 'mytest.db3';   
SQLite3Connection.Transaction := Transaction;    try     
SQLite3Connection.Connected := True;    except      on E: Exception
do      begin        WriteLn('Error: ', E.Message);        Exit;     
end;    end;     Query.DataBase := SQLite3Connection;   
Query.Transaction := Transaction;     // check if 'mytablename'
exists    Query.SQL.Text := 'select name ' +                     
'from sqlite_schema ' +                      'where type = ''table'' '
+                      'and name = ''mytablename''';    Query.Open;  
If Query.EOF then    begin      Query.Close;      Query.SQL.Text :=
'create table mytablename ' +                        '(myid integer,
somedata integer, primary key (myid))';      Query.ExecSQL;      
Query.SQL.Text := 'insert into mytablename ' +                       
'(somedata) values ' +                        '(:somedata)';      
Query.ParamByName('somedata').AsInteger := 5;      Query.ExecSQL;    
Query.ParamByName('somedata').AsInteger := 10;      Query.ExecSQL;   
 Query.ParamByName('somedata').AsInteger := 15;      Query.ExecSQL;  
  Query.ParamByName('somedata').AsInteger := 20;      Query.ExecSQL; 
   Query.ParamByName('somedata').AsInteger := 25;      Query.ExecSQL; 
    Query.ParamByName('somedata').AsInteger := 30;     
Query.ExecSQL;    end;     Query.Close;    Query.SQL.Text := 'select
myid, somedata ' +                      'from mytablename';   
Query.Open;    While not Query.EOF do    begin     
WriteLn(Query.FieldByName('myid').AsInteger, ': ',
Query.FieldByName('somedata').AsInteger);      Query.Next;    end;   
Query.Close;  finally    Query.Free;    Transaction.Free;   
SQLite3Connection.Free;  end;end.
If you want to use Zeos components you can do it like 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 program
sqlite_zeos; uses  SysUtils, ZConnection, ZDataset; var  ZConn:
TZConnection;  ZQuery: TZQuery; begin  try    ZConn :=
TZConnection.Create(Self);    ZConn.Database := 'mytest.db3';   
ZConn.Protocol := 'sqlite';    // ZConn.LibraryLocation :=
'sqlite-3.dll'; // you can put path and name here to sqlite dll if it
doesn't find it    ZConn.Properties.Append('ExtendedErrorMessage=1');
// show detailed errors    try      ZConn.Connect;    except      on
E: Exception do        WriteLn('Error connecting: ' + E.Message);   
end;     if not ZConn.Connected then      Exit;     ZQuery :=
TZQuery.Create(nil);    try      ZQuery.Connection := ZConn;      
ZQuery.SQL.Text :=        'select * ' +        'from mytablename';   
 ZQuery.Open;       While not ZQuery.EOF do      begin       
WriteLn(ZQuery.Fields[0].AsString);        ZQuery.Next;      end;   
finally      ZQuery.Free;    end;  finally    ZConn.Disconnect;   
ZConn.Free;  end;end.
Be sure that you have sqlite dll in the same directory (and that is of
the correct bitness).

----------------------------------------------------------------------
MarkMLl · 2024-11-01 15:36

Quote from: dseligo on November 01, 2024, 04:30:42 pmTry this for
sqlite:
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
sqlite_sqldb;    SQLite3Connection.Transaction := Transaction; 
That I think is the missing line.

MarkMLl

----------------------------------------------------------------------
dseligo · 2024-11-01 15:40

Quote from: Tanto on November 01, 2024, 03:58:26 pmi've tried the
example (and a lot more), by adapting it for sqlite,
but everytime i got an "Error: Access violation"
by line see '<<--'

here my example code wich i wrote by all examples of everything i
show:

It would be helpful if you make complete compilible project so we can
test it.
As MarkMLI wrote, you seem to lack TSQLTransaction - it is required
for SQLDB components.

----------------------------------------------------------------------
Handoko · 2024-11-01 15:41

Quote from: Tanto on November 01, 2024, 03:58:26 pmbut everytime i got
an "Error: Access violation"

Have you made sure your SQLite already installed correctly? If you're
using Linux you need to have libsqlite3-dev package installed. But if
you're using Windows, you need to put sqlite3.dll in the same folder
of the exe file.

----------------------------------------------------------------------
Tanto · 2024-11-01 16:19

@dseligo
here a simple project that will run:

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+} uses  {$IFDEF UNIX}  cthreads, 
{$ENDIF}  Classes,  databasemodul2; var  MyDataBase :
TMyDataBaseConnector;begin  MyDataBase:= TMyDataBaseConnector.Create; 
MyDataBase.CheckUserEntry('', '');end. 
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
databasemodul2; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils,
fpWeb, db, sqldb, sqlite3conn,  HelpModul1; type  TMyDataBaseConnector
= class(TObject)    sqlite3: TSQLite3Connection;    dbquery:
TSQLQuery;    dbtrans: TSQLTransaction;  private    GHVar :
TGlobalHelpModul;  public    constructor Create;    function
CheckUserEntry(uname: String; ukey: String): String;  end; var 
DBModul: TMyDataBaseConnector; implementation   constructor
TMyDataBaseConnector.Create;  begin    GHVar:=
TGlobalHelpModul.Create;  end;   function
TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String):
String;  begin    try      sqlite3:= TSQLite3Connection.Create(nil); 
   dbtrans:= TSQLTransaction.Create(nil);       dbtrans.Action :=
caCommit;      dbtrans.Options := [stoUseImplicit];      
sqlite3.DatabaseName:= 'keys.sq3';      sqlite3.Transaction :=
dbtrans;       try        WriteLn('Connect');       
sqlite3.Connected:= True;         dbquery:= TSQLQuery.Create(nil);   
   dbquery.DataBase:= sqlite3;        dbquery.Transaction:= dbtrans;  
      if FileExists(sqlite3.DatabaseName) then           WriteLn('File
EXISTS');         if sqlite3.Connected then         
 WriteLn('CONNECTED');         WriteLn('SELECT');       
dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';        
WriteLn('> Execute SQL');        dbquery.Open;         WriteLn('Read
EOF');        while not dbquery.EOF do        begin         
WriteLn(dbquery.FieldByName('username').AsAnsiString);         
dbquery.Next;        end;      except on E: Exception do       
WriteLn('Something happens: ', E.ToString);      end;    finally     
sqlite3.Free;    end;      CheckUserEntry:= '';  end; end.  
@Handoko
yes, i have the sqlite3.dll on the next to the program and on lazarus,
by installing the packages for sqlite3

@MarkMLl
i have the transaction in line 35

thx

----------------------------------------------------------------------
rvk · 2024-11-01 16:37

Quote from: Tanto on November 01, 2024, 05:19:38 pmhere a simple
project that will run:
A few small remarks. (you didn't include TGlobalHelpModul but we can
just strip it)

*) You don't have a "inherited;" in the TMyDataBaseConnector.Create.
Although TObject.Create doesn't do much... it's best practice to
always call inherited;

*) You have some major leaks
You do TSQLite3Connection.Create(nil); That's ok because you do
sqlite3.Free at the end, so you can use nil.
But you also have TSQLTransaction.Create(nil); and
TSQLQuery.Create(nil); You don't free those.
So either use an owner, like TSQLTransaction.Create(sqlite3); and
TSQLQuery.Create(sqlite3); , so the sqlite3.free will also free the
transaction and sqlquery.
Or... free them separately in the finally section. The first method is
easier (because then you can't forget  ).

/edit/ I see you did SQLQuery.Create(sqlite3); etc with the earlier
example but changed it in nil (creating the memory leak).

*) You have CheckUserEntry := ''; at the end of the function. Although
you can use the function-name for the result... it might be easier to
read if you use Result := ''; (or other string).

*) You don't need var DBModul: TMyDataBaseConnector; because in
project1 you use MyDataBase as 'local' variable.

But overall... yes, you're on your way...

----------------------------------------------------------------------
Tanto · 2024-11-01 17:01

@rvk
thx

i want to give you the information, i am working on windows 11 with
the latest lazarus and fpc 3.2.2

best regards

----------------------------------------------------------------------
dseligo · 2024-11-01 17:02

Quote from: Tanto on November 01, 2024, 05:19:38 pm@dseligo
here a simple project that will run:

As rvk already said, it doesn't run.

Here is changed unit that runs:
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
databasemodul2; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils,
fpWeb, db, sqldb, sqlite3conn;   //,  HelpModul1; type 
TMyDataBaseConnector = class(TObject)    sqlite3: TSQLite3Connection; 
  dbquery: TSQLQuery;    dbtrans: TSQLTransaction;  private    //GHVar
: TGlobalHelpModul;  public    constructor Create;    function
CheckUserEntry(uname: String; ukey: String): String;  end; var 
DBModul: TMyDataBaseConnector; implementation   constructor
TMyDataBaseConnector.Create;  begin    //GHVar:=
TGlobalHelpModul.Create;  end;   function
TMyDataBaseConnector.CheckUserEntry(uname: String; ukey: String):
String;  begin    try      sqlite3:= TSQLite3Connection.Create(nil); 
   dbtrans:= TSQLTransaction.Create(nil);      dbquery:=
TSQLQuery.Create(nil);      try        dbtrans.Action := caCommit;   
   dbtrans.Options := [stoUseImplicit];         sqlite3.DatabaseName:=
'keys.sq3';        sqlite3.Transaction := dbtrans;        
dbquery.DataBase:= sqlite3;        dbquery.Transaction:= dbtrans;    
   WriteLn('Connect');        sqlite3.Connected:= True;         if
FileExists(sqlite3.DatabaseName) then           WriteLn('File
EXISTS');         if sqlite3.Connected then         
 WriteLn('CONNECTED');         WriteLn('SELECT');       
dbquery.SQL.Text:= 'SELECT * FROM "web_app_admin";';        
WriteLn('> Execute SQL');        dbquery.Open;         WriteLn('Read
EOF');        while not dbquery.EOF do        begin         
WriteLn(dbquery.FieldByName('username').AsAnsiString);         
dbquery.Next;        end;      except on E: Exception do       
WriteLn('Something happens: ', E.ToString);      end;    finally     
sqlite3.Free;      dbtrans.Free;      dbquery.Free;    end;    
CheckUserEntry:= '';  end; end.
I get this output (last line is expected since I don't have tables
created):
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 ConnectFile
EXISTSCONNECTEDSELECT> Execute SQLSomething happens:
ESQLDatabaseError: TSQLite3Connection : no such table: web_app_admin
Try my code and post exact error messages. Did you put sqlite3.dll in
directory with your program and check if bitness matches to your
program?
Do you use Lazarus or only FPC?

----------------------------------------------------------------------
MarkMLl · 2024-11-01 17:07

Quote from: Tanto on November 01, 2024, 05:19:38 pmi have the
transaction in line 35

Hmm. I admit that I overlooked that since it's not the "usual
idiom"...

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    
sqlite3.Transaction:= TSQLTransaction.Create(sqlite3); // Your line
35 
But in your program that works you've got

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      
dbtrans:= TSQLTransaction.Create(nil);      sqlite3.Transaction :=
dbtrans; 
...which /is/ the usual idiom. So there's two changes there: the first
is that you're no longer relying on the connection object to store the
(pointer to the) transaction object, and the second is the nil
parameter.

Referring to the documentation root at
https://www.freepascal.org/docs.html hence via the FCL
https://www.freepascal.org/docs-
html/current/fcl/sqldb/tsqltransaction.create.html:

"AOwner Owner of the transaction component."

Deferring to the example from @dseligo, I note that he also uses a nil
parameter: I suspect that you were setting up some sort of circular
reference.

I also admit to being slightly uncomfortable with the number of times
different database components need to be told about each other, and am
particularly uncomfortable when this is done using the object
inspector (effectively embedded in the form designer) of a GUI app.
But it's a complex area and by and large the implementers have done a
good job making the same framework work over a fairly wide number of
backends...

MarkMLl

----------------------------------------------------------------------
Tanto · 2024-11-01 17:22

@dseligo
your example works, thx very much

but in the first try, i got the same error like before 

now the funny reality of the life:
it was something wrong with the sqlite3.dll, i download a new one from
sqlite.org and now it runs 

thx for everyone who try to help

----------------------------------------------------------------------
Tanto · 2024-11-01 18:38

Hello @all

here the working example to connect to a SQLite3 Database.

thx for helping @all again

Download the sqlite3.dll from here
https://sqlite.org/download.html
i've used the following sqlite3.dll
https://sqlite.org/2024/sqlite-dll-win-x64-3470000.zip

if something missing, please tell me

----------------------------------------------------------------------
Handoko · 2024-11-01 18:45

I got this error message:
QuoteCompile Project, Target:
/home/handoko/Desktop/example_without_gui/project1: Exit code 1,
Errors: 1
databasemodul2.pas(9,3) Error: Cannot find HelpModul1 used by
databasemodul2 of the Project Inspector.

----------------------------------------------------------------------
Tanto · 2024-11-01 19:14

@Handoko
sorry i forgot to clean this

now the hopefully cleaned version

----------------------------------------------------------------------
Handoko · 2024-11-01 19:19

Still has problem.

----------------------------------------------------------------------
Tanto · 2024-11-01 19:27

@Handoko

ok, all good things come in three

i hope her is the last and clean version who works for you ;-)

----------------------------------------------------------------------
Handoko · 2024-11-02 02:38

I just downloaded and tested the code running the from Lazarus' IDE
and Linux Terminal. It did not generate any error. Below is the
screenshot running it on Linux Terminal.

----------------------------------------------------------------------
Tanto · 2024-11-02 07:17

Hey,
this is very cool, because on Linux i doesn't test it, but now i know
it runs also there.

thx

----------------------------------------------------------------------
Tanto · 2024-11-08 11:39

Hello guys,
here is another example who contains the variant with sqlite and now
with oracle to

on windows you need some dll's you have to download

best regards

----------------------------------------------------------------------
MarkMLl · 2024-11-08 12:47

Thanks for that. Example code tested against Oracle is always
appreciated, because they are perceived as being outside the reach or
the average developer.

MarkMLl

----------------------------------------------------------------------
marcov · 2024-11-08 15:02

(and of course also the databasename  (the database file) might need a
path, specially when the exe is generated in lib/i386-win32 or so)

----------------------------------------------------------------------
Tanto · 2024-11-08 15:30

Hi guys,

in the next time, I will extend this example with MySQL/MariaDB,
MongoDB and PostgreSQL.

I think then, I will have covered all common databases.

So be happy and wait for the extension of this example.

best regards

----------------------------------------------------------------------
Tanto · 2024-11-10 16:12

Hi @all,
i think i have finished the example project, for connecting a database
without gui.

here my last example with PostgreSQL and MongoDB.

For MongoDB i use the mORMot2 Framework:
https://github.com/synopse/mORMot2

have fun and best regards