Lazarus database help/documentation
lazarus / 16-Databases

----------------------------------------------------------------------
rwebb616 · 2021-02-06 04:50

Hello,

I'm somewhat new to Lazarus but learning quickly.  I'm wondering
really a few things -

a) is there any good solid documentation (other than the SQL
tutorials) about writing database applications in Lazarus?
b) I keep seeing things about Zeos - is that something that I should
be favoring for pascal db programming? 
c) Any good information anywhere on building a solid data access layer
or is that unnecessary with the help of the datamodule?

Just some background - I've currently managed to get a shell of an
application built that uses the TcryptIni unit for securely storing my
database connection credentials in a local INI.  Got my program to
fire up, look for the config file, if not found throw a dialog to
configure the database (using postgresql) with a test connection
button and then writes the connection parameters to the ini file with
encrypted username and password.  If the file IS found it checks to be
sure it's valid and if not also throws the dialog. 

My thoughts are do I build a class with a data model with a procedure
to create the database so that if the specified database doesn't exist
it can create it.  Also this model would also have procedures for
retrieving records etc (data access layer)

Thanks for any help!  So far really liking Lazarus!

Rich

----------------------------------------------------------------------
speter · 2021-02-06 09:18

Welcome to the forum.

Have a look at
https://wiki.freepascal.org/Lazarus_Database_Overview
and/or
https://wiki.freepascal.org/SQLdb_Tutorial0
(there are 4 tutorials I think).

cheers
S.

----------------------------------------------------------------------
Leledumbo · 2021-02-07 18:31

Quote from: rwebb616 on February 06, 2021, 05:50:12 ama) is there any
good solid documentation (other than the SQL tutorials) about writing
database applications in Lazarus?
You can use similar articles that talk about it for Delphi, mostly the
same applies.
Quote from: rwebb616 on February 06, 2021, 05:50:12 amb) I keep seeing
things about Zeos - is that something that I should be favoring for
pascal db programming?
No, it's just an option. However, it's also Delphi compatible so if
you want to write a project that compiles for both, Zeos is a good
option.
Quote from: rwebb616 on February 06, 2021, 05:50:12 amc) Any good
information anywhere on building a solid data access layer or is that
unnecessary w ith the help of the datamodule?
If that's your thing, there are several OPF that uses published
properties. However, I think the classic manual mapping, just wrapped
in a subroutine is a much better option. You have complete control
over the query and you can choose what and how to map efficiently. The
code that calls it still has no idea what happens underneath. Data
module doesn't assist much, it's just a container that helps with the
component lifecycle but that's it.

----------------------------------------------------------------------
rwebb616 · 2021-02-08 02:52

QuoteIf that's your thing, there are several OPF that uses published
properties.
What is OPF? 

QuoteHowever, I think the classic manual mapping, just wrapped in a
subroutine is a much better option.
Could you elaborate on what you mean here?  Sorry - I'm pretty new -
still learning.  I'm finding though that writing an application is
better teaching sometimes than reading!

Rich

----------------------------------------------------------------------
Leledumbo · 2021-02-08 05:22

Quote from: rwebb616 on February 08, 2021, 03:52:39 amWhat is OPF? 
https://en.wikipedia.org/wiki/Persistence_framework
Quote from: rwebb616 on February 08, 2021, 03:52:39 amCould you
elaborate on what you mean here?  Sorry - I'm pretty new - still
learning.  I'm finding though that writing an application is better
teaching sometimes than reading!
A program can contain:
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 AnObject :=
TheObject.Create(10); it doesn't know where the object gets its data
from, it could be:
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 constructor
TheObject.Create(const AID: Int64);var  LJSONObj: TJSONObject;begin 
LJSONObj := TJSONObject(GetJSON(TFPHTTPClient.SimpleGet('http://mydata
source.somewhere.in.the.network?id=' + IntToStr(AID)));  FID := AID; 
FName := LJSONObj['name'].AsString;  FAge :=
LJSONObj['age'].AsInteger;  ...end; or:
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 constructor
TheObject.Create(const AID: Int64);var  Conn: TSQLConnector;  Query:
TSQLQuery;begin  Conn := TSQLConnector.Create(nil);  with Conn do
begin    ConnectorType := 'mysql 5.6';    HostName := 'localhost';   
DatabaseName := 'mydb';    UserName := 'whatever';    Password :=
'nomatter';    Transaction := TSQLTransaction.Create(Conn);  end;  
Query := TSQLQuery.Create(nil);  Query.DataBase := Conn;  
Query.SQL.Text := 'select name,age from wherever where id = :id'; 
Query.ParamByName('id').AsInteger := AID;  Query.Open;   FID := AID; 
FName := Query.FieldByName['name'].AsString;  FAge :=
Query.FieldByName['age'].AsInteger;  ...end; switching between the two
is transparent to the program, it won't see nor need any changes to
the code it's using.

----------------------------------------------------------------------
rwebb616 · 2021-02-08 23:13

Thank you for that!  I think I get it - so the interface to the
calling program is the same but the db class can connect to whatever
datasource you program it for - thus allowing you to write the same
class for multiple DB backends (or JSON data as well) and not have to
change the calling program.

The persistence framework is interesting - I think I'll read some more
on that but I like the idea of doing it the way you lay it out in your
example.

Rich