Difference between revisions of "Lazarus Database Overview"

From Lazarus wiki
Jump to navigationJump to search
 
(122 intermediate revisions by 19 users not shown)
Line 1: Line 1:
{{Translate}}
+
{{Lazarus Database Overview}}
  
{{Database Programming}}
+
{{Infobox databases}}
  
 
== Overview ==
 
== Overview ==
 +
This article is an overview of which databases can work with Lazarus.
  
This tutorial is about getting Lazarus to work with a variety of existing databases.
+
Lazarus supports several databases out of the box (using e.g. the SQLDB framework), however the developer must install the required packages (client libraries) for each one.  
  
Lazarus supports several databases out-of-the-box, however the developer must install the adequate packages for each one. You can access the database through code or by dropping components on a form. The data-aware components represent fields and are connected by setting the DataSource property to point to a TDataSource. The Datasource represents a table and is connected to the database components (examples: TPSQLDatabase, TSQLiteDataSet) by setting the DataSet property. The data-aware components are located on the "Data Controls" tab. The Datasource and the database controls are located on the "Data Access" tab.
+
You can access the database through code or by dropping components on a form. The data-aware components represent fields and are connected by setting the DataSource property to point to a [[TDataSource]]. The Datasource represents a table and is connected to the database components (examples: ''[[TPSQLDatabase]]'', ''[[TSQLiteDataSet]]'') by setting the DataSet property. The data-aware components are located on the [[Data Controls tab]]. The Datasource and the database controls are located on the "Data Access" tab.
  
== Lazarus and MySQL ==
+
See the tutorials for Lazarus/FPC built in database access, suitable for Firebird, MySQL, SQLite, PostgreSQL etc:
 +
* [[SQLdb Tutorial0]]
 +
* [[SQLdb Tutorial1]]
 +
* [[SQLdb Tutorial2]]
 +
* [[SQLdb Tutorial3]]
 +
* [[SQLdb Tutorial4]]
  
=== Get MySQL working in Linux or Windows ===
+
== Lazarus and Interbase / Firebird ==
Follow the instructions in the MySQL User Manual.  Make sure that the mysqld daemon runs reliably, and that all potential users (including root, mysql, yourself and anybody else that may need it) have as many privileges as they need, from as many hosts as may be needed (including 'localhost', the local host's name, any other hosts on your network) as far as is consistent with security.  It is preferable that all users including root have passwords.  Test the action of the database system using the examples given in the manual, and check that all users really do have reliable access.
+
* Firebird is very well supported out of the box by FPC/Lazarus (using SQLDB); please see [[Firebird]] for details.
 +
* [[Other Firebird libraries]] has a list of alternative access libraries (e.g. PDO, Zeos, FBlib)
  
=== Get MySQL working for FPC in text mode ===
+
== Lazarus and MySQL ==
There is a directory with an example program in $(fpc<b>src</b>dir)/packages/base/mysql/. You can find the fpc source directory in Lazarus: Environment menu -> Environment Options -> Paths tab -> FPC source directory. Possible paths for the mysql directory are /usr/share/fpcsrc/packages/base/mysql/ (rpm install) or C:\lazarus\fpcsrc\packages\base\mysql\ (windows). This directory also contains the units mysql.pp, mysql_com.pp and mysql_version.pp.  Before running the test script, you need to create a database called testdb:  do this by logging into the mysql monitor (as root with full privileges) and issuing the following SQL statement
+
* Please see [[mysql]] for details on various access methods, which include:
CREATE DATABASE testdb;
+
# Built-in [[SQLdb_Package|SQLdb]] support
then make sure that all relevant users have appropriate access privileges to it
+
# PDO
GRANT ALL ON testdb TO johnny-user IDENTIFIED BY 'johnnyspassword';
+
# [[ZeosDBO|Zeos]]
There is a script called mkdb which you should now try to run:
+
# [https://www.devart.com/mydac/ MySQL data access Lazarus components]
sh ./mkdb
 
This will probably fail, as the system will not allow an anonymous user to access the database.  So change the script using an editor so that the line invoking mysql reads:
 
mysql -u root -p  ${1-testdb} << EOF >/dev/null
 
and try running it again, entering your password when prompted.  With luck you might have managed to create the test database:  test it (while logged in to the mysql monitor) by issuing the mysql statement
 
select * from FPdev;
 
You should see a table listing the ID, username and email address of some of the FPC developers.
 
 
 
Now try to run the test program testdb.pp (this may need to be compiled, and will almost certainly fail on the first attempt!!).
 
 
 
I found that the program could not connect to mysql for several reasons:
 
 
 
* My system (SuSE Linux v9.0) installs mysql v4.0.15, not the version3 for which the package was designed. 
 
 
 
* The program needs to have user names and passwords to get access to the database.
 
 
 
* The compiler needs to know where to find the mysql libraries (IF YOU HAVEN'T INSTALLED THE MYSQL DEVELOPMENT LIBRARIES, DO SO NOW!)
 
 
 
I created a copy of testdb.pp called trydb.pp, rather than editing the original - this means that the original files still get fixed in subsequent CVS updates.
 
I also copied the files found in the subdirectory mysql/ver40/ into the main mysql/ subdirectory, renaming them mysql_v4.pp, mysql_com_v4.pp and mysql_version_v4.pp, being sure to rename the units within each file correspondingly.  I changed the uses statement in trydb.pp to
 
uses mysql_v4
 
and the statement in mysql_v4.pp to
 
 
 
uses mysql_com_v4
 
 
 
I added a line to /etc/fpc.cfg to point to my libraries:
 
-Fl/lib;/usr/lib
 
The following step might not be necessary if the devel-libraries are installed as the links will be created for you, but it never hurts to check.
 
I had to find the real name of the mysqlclint library in the /usr/lib directory and in my case I had to issue the shell command:
 
ln -s libmysqlclient.so.12.0.0 lmysqlclient
 
to make a symbolic link allowing FPC to find the library.  For good measure I also created the link
 
ln -s libmysqlclient.so.12.0.0 mysqlclient
 
and placed similar links in various other directories:  not strictly necessary, but just in case ...!
 
Some users might need to add the following link:
 
ln -s libmysqlclient.so.12.0.0 libmysqlclient.so
 
 
 
I modified trydb.pp to include user details, initially by adding host, user and password as constants:
 
 
 
const
 
  host : Pchar= 'localhost';
 
  user : Pchar= 'myusername';
 
  passwd: Pchar = 'mypassword';
 
 
 
I also found that I couldn't connect to mysql using the mysql_connect() call, but had to use mysql_real_connect() which has many more parameters.  To complicate things further, the number of parameters seems to have changed between version3 (where there are seven) and version4 (where there are eight).  Before using mysql_real_connect I had to use mysql_init() which is not found in the original mysql.pp but is found in mysql_v4.pp.
 
 
 
So the code for connection to the database is now:
 
 
 
{ a few extra variables}
 
var
 
  alloc : PMYSQL;
 
 
 
{main program fragment}
 
 
 
begin
 
  if paramcount=1 then
 
    begin
 
    Dummy:=Paramstr(1)+#0;
 
    DataBase:=@Dummy[1];
 
    end;
 
 
 
Writeln ('Allocating Space...');
 
  alloc := mysql_init(PMYSQL(@qmysql));
 
  Write ('Connecting to MySQL...');
 
  sock := mysql_real_connect(alloc, host, user, passwd, database, 0, nil, 0);
 
  if sock=Nil then
 
    begin
 
    Writeln (stderr,'Couldn''t connect to MySQL.');
 
    Writeln (stderr, 'Error was: ', mysql_error(@qmysql));
 
    halt(1);
 
    end;
 
  Writeln ('Done.');
 
  Writeln ('Connection data:');
 
{$ifdef Unix}
 
  writeln ('Mysql_port      : ',mysql_port);
 
  writeln ('Mysql_unix_port : ',mysql_unix_port);
 
{$endif}
 
  writeln ('Host info      : ',mysql_get_host_info(sock));
 
  writeln ('Server info    : ',mysql_stat(sock));
 
  writeln ('Client info    : ',mysql_get_client_info);
 
 
 
  Writeln ('Selecting Database ',DataBase,'...');
 
  if mysql_select_db(sock,DataBase) < 0 then
 
    begin
 
    Writeln (stderr,'Couldn''t select database ',Database);
 
    Writeln (stderr,mysql_error(sock));
 
    halt (1);
 
    end;
 
{... as original contents of testdb.pp}
 
 
 
 
 
Now - ready to start compiling trydb.pp?
 
  fpc trydb
 
success!  Now run it:
 
  ./trydb
 
whoopee!  I got the listing of the FPC developers!
 
 
 
A few extra refinements:  make the entry of user details and the mysql commands interactive, using variables rather than constants, and allow several SQL commands to be entered, until we issue the quit command: see the [[Lazarus Database Tutorial/TryDbpp|full program listing]], where user details are entered from the console, and the program goes into a loop where SQL commands are entered from the console (without the terminal semicolon) and the responses are printed out, until 'quit' is entered from the keyboard.
 
 
 
See [[Lazarus Database Tutorial/SampleListing|Sample Console Listing]].
 
 
 
===Connecting to MySQL from a Lazarus Application ===
 
 
 
This tutorial shows how to connect Lazarus to the MySQL database, and execute simple queries, using only the basic Lazarus components; it uses no Data Aware components, but illustrates the principles of interfacing with the database.
 
 
 
Create a new project in Lazarus:
 
Project -> New Project -> Application
 
A new automatically generated Form will appear.
 
 
 
Enlarge the form to fill about half of the screen, then re-name the form and its caption to 'TryMySQL'.
 
 
 
From the Standard Component tab place three Edit Boxes on the upper left side of the Form, and immediately above each box place a label.  Change the names and captions to 'Host' (and HostLLabel,HostEdit), 'UserName' (and UserLabel, UserEdit) and 'Password' (with PasswdLabel and PasswdEdit).  Alternatively you could use LabelledEdit components from the Additional tab.
 
 
 
Select the Passwd Edit box and find the PasswordChar property:  change this to * or some other character, so that when you type in a password the characters do not appear on your screen but are echoed by a series of *s.  Make sure that the Text property of each edit box is blank.
 
 
 
Now place another Edit box and label at the top of the right side of your form.  Change the label to 'Enter SQL Command' and name it CommandEdit.
 
 
 
Place three Buttons on the form:  two on the left under the Edit boxes, and one on the right under the command box.
 
 
 
Label the buttons on the left 'Connect to Database' (ConnectButton)and 'Exit' (ExitButton) and the one on the right 'Send Query' (QueryButton).
 
 
 
Place a large Memo Box labelled and named 'Results' (ResultMemo) on the lower right, to fill most of the available space. Find its ScrollBars property and select ssAutoBoth so that scroll bars appear automatically if text fills the space.  Make the WordWrap property True.
 
 
 
Place a Status Bar (from the Common Controls tab) at the bottom of the Form, and make its SimpleText property 'TryMySQL'.
 
 
 
A screenshot of the Form can be seen here: [http://lazarus-ccr.sourceforge.net/kbdata/trymysqldb.png Mysql Example Screenshot]
 
 
 
Now we need to write some event handlers.
 
 
 
The three Edit boxes on the left are for entry of hostname, username and password.  When these have been entered satisfactorily, the Connect Button is clicked.  The OnCLick event handler for this button is based on part of the text-mode FPC program above.
 
 
 
The responses from the database cannot now be written using the Pascal write or writeln statements: rather, the replies have to be converted into strings and displayed in the Memo box.  Whereas the Pascal write and writeln statements are capable of performing a lot of type conversion 'on the fly', the use of a memo box for text output necessitates the explicit conversion of data types to the correct form of string, so Pchar variables have to be converted to strings using StrPas, and integers have to be converted with IntToStr. 
 
 
 
Strings are displayed in the Memo box using
 
 
 
procedure ShowString (S : string);
 
(* display a string in a Memo box *)
 
begin
 
        trymysqlForm1.ResultsMemo.Lines.Add (S)
 
end;
 
 
 
The ConnectButton event handler thus becomes:
 
 
 
procedure TtrymysqlForm1.ConnectButtonClick(Sender: TObject);
 
(* Connect to MySQL using user data from Text entry boxes on Main Form *)
 
var strg: string;
 
 
 
begin
 
 
 
  dummy1 :=  trymysqlForm1.HostEdit.text+#0;
 
  host := @dummy1[1];
 
  dummy2 := trymysqlForm1.UserEdit.text+#0;
 
  user := @dummy2[1] ;
 
  dummy3 := trymysqlForm1.PasswdEdit.text+#0;
 
  passwd := @dummy3[1] ;
 
 
 
  alloc := mysql_init(PMYSQL(@qmysql));
 
  sock :=  mysql_real_connect(alloc, host, user, passwd, database, 0, nil, 0);
 
  if sock=Nil then
 
    begin
 
      strg :='Couldn''t connect to MySQL.'; showstring (strg);
 
      Strg :='Error was: '+ StrPas(mysql_error(@qmysql)); showstring (strg);
 
  end
 
    else
 
    begin
 
      trymysqlForm1.statusBar1.simpletext := 'Connected to MySQL';
 
      strg := 'Now choosing database : ' + database; showstring (strg);
 
{$ifdef Unix}
 
      strg :='Mysql_port      : '+ IntToStr(mysql_port); showstring (strg);
 
      strg :='Mysql_unix_port : ' + StrPas(mysql_unix_port); showstring (strg);
 
{$endif}
 
      Strg :='Host info      : ' + StrPas(mysql_get_host_info(sock));
 
      showstring (strg);
 
      Strg :='Server info    : ' + StrPas(mysql_stat(sock)); showstring (strg);
 
      Strg :='Client info    : ' + Strpas(mysql_get_client_info);  showstring (strg);
 
 
 
      trymysqlForm1.statusbar1.simpletext := 'Selecting Database ' + DataBase +'...';
 
  if mysql_select_db(sock,DataBase) < 0 then
 
  begin
 
    strg :='Couldn''t select database '+ Database; ShowString (strg);
 
    Strg := mysql_error(sock); ShowString (strg);
 
  end
 
  end;
 
end;
 
 
 
 
 
The Text Box on the right allows entry of a SQL statement, without a terminal semicolon;  when you are satisfied with its content or syntax, the SendQuery button is pressed, and the query is processed, with results being written in the ResultsMemo box.
 
 
 
The SendQuery event handler is again based on the FPC text-mode version, except that once again explicit type-conversion has to be done before strings are displayed in the box.
 
 
 
A difference from the text-mode FPC program is that if an error condition is detected, the program does not halt and MySQL is not closed;  instead, control is returned to the main form and an opportunity is given to correct the entry before the command is re-submitted.  The application finally exits (with closure of MySQL) when the Exit Button is clicked.
 
 
 
The code for SendQuery follows:
 
 
 
procedure TtrymysqlForm1.QueryButtonClick(Sender: TObject);
 
var
 
  dumquery, strg: string;
 
begin
 
      dumquery := TrymysqlForm1.CommandEdit.text;
 
      dumquery := dumquery+#0;
 
      query := @dumquery[1];
 
      trymysqlForm1.statusbar1.simpletext := 'Executing query : '+ dumQuery +'...';
 
      strg := 'Executing query : ' + dumQuery; showstring (strg);
 
      if (mysql_query(sock,Query) < 0) then
 
      begin
 
        Strg :='Query failed '+ StrPas(mysql_error(sock)); showstring (strg);
 
      end
 
      else
 
      begin
 
        recbuf := mysql_store_result(sock);
 
        if RecBuf=Nil then
 
        begin
 
          Strg :='Query returned nil result.'; showstring (strg);
 
        end
 
        else
 
        begin
 
          strg :='Number of records returned  : ' + IntToStr(mysql_num_rows (recbuf));
 
          Showstring (strg);
 
          Strg :='Number of fields per record : ' + IntToStr(mysql_num_fields(recbuf));
 
          showstring (strg);
 
          rowbuf := mysql_fetch_row(recbuf);
 
          while (rowbuf <>nil) do
 
          begin
 
              Strg :='(Id: '+ rowbuf[0]+', Name: ' + rowbuf[1]+ ', Email : ' +
 
                rowbuf[2] +')';
 
              showstring (strg);
 
              rowbuf := mysql_fetch_row(recbuf);
 
          end;
 
        end;
 
      end;
 
end;
 
 
 
 
 
Save your Project, and press Run -> Run
 
 
 
==== Download MYSQL Source Code ====
 
A full listing of the  program is available here [http://lazarus-ccr.sourceforge.net/kbdata/mysqldemo.tar.gz Sample Source Code]
 
 
 
=== Lazarus, MySQL and UTF-8 ===
 
''The following may be required for other codepages/character sets as well''
 
 
 
UTF-8 Unicode is a convenient multibyte character set encoding, that allows working with multilingual texts without requiring WideStrings. It is supported both by Lazarus SQLdb components and by MySQL since version 4.1 by choosing the appropriate character set.
 
 
 
However, simply setting this encoding as default for your tables and MySQL Connection component will result in incorrect storage and retrieval of utf-8 strings, when any accented/international character will show up as question mark (?). Apparently, the reason for this is that MySQL client library is compiled to expect Latin1 character set by default.
 
 
 
In order to enable proper communication between Lazarus, MySQL client library and MySQL server, additional two queries need to be executed '''each time''' a connection to the database is established:
 
SET CHARACTER SET `utf8`
 
and
 
SET NAMES 'utf8'
 
The first query will ensure your application receives strings in correct encoding, and the second tells MySQL not to convert strings it receives from your application.
 
 
 
== Simple MySQL Demo Using the TMySQL50Connection Component ==
 
 
 
Here is code that functions as a quick demo to get up and running simply (tested on Win XP with Lighty2Go, though xampp would be just as suitable). libmysql.dll was put in the project and lazarus.exe directories (available from a directory in Lighty2Go or xampp - but ensure that you use the correct version of libmysql.dll to match the chosen TMySQL5xxConnection Component - for TMySQL50Connection use the version for MySQL 5.0 (1484kB) and this can connect fine to both MySQL 5.0 and MySQL 5.1 DBMSs. The 5.1 version of libmysql.dll will not function with TMySQL50Connection). There is no requirement to place any components on the form other than the three edit boxes, a memo box and a few buttons. You need to add mysql50conn and sqldb to the uses statement. The Lazarus component directory must be rw for the programmer. The mysql dbms here has a user 'root' with no password, and a database test1 with table tPerson which has three fields: personid (int), surname (varchar(40)) and dob (datetime). phpMyAdmin (in Lighty2Go) was used to create the db, table and fields and insert some sample data. Note that dates in phpMyAdmin should be entered YYYY-MM-DD, though the program created below will accept dates in the usual formats. The button btnTest must be clicked first as it creates the connection with the dbms. Note the line that applies updates - without this the changed or new data will not be written back to the db though they will be in memory and can be viewed using btnFirst and btnNext.
 
<pre>
 
unit unt_db;
 
//Example based on:
 
//http://www.lazarus.freepascal.org/index.php?name=PNphpBB2&file=viewtopic&t=5761
 
//from tpglemur on that forum
 
{$mode objfpc}{$H+}
 
interface
 
uses
 
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
 
  mysql50conn, sqldb, StdCtrls;
 
type
 
  { TForm1 }
 
  TForm1 = class(TForm)
 
    btnTest: TButton;
 
    btnNext: TButton;
 
    btnFirst: TButton;
 
    btnNew: TButton;
 
    edtPersonID: TEdit;
 
    edtSurname: TEdit;
 
    edtDOB: TEdit;
 
    Memo1: TMemo;
 
    procedure btnFirstClick(Sender: TObject);
 
    procedure btnNewClick(Sender: TObject);
 
    procedure btnNextClick(Sender: TObject);
 
    procedure btnTestClick(Sender: TObject);
 
  private
 
    { private declarations }
 
    conn : TMySQL50Connection;
 
    query : TSQLQuery;
 
    transaction : TSQLTransaction;
 
    procedure Display;
 
  public
 
    { public declarations }
 
  end;
 
var
 
  Form1: TForm1;
 
implementation
 
{ TForm1 }
 
procedure TForm1.btnTestClick(Sender: TObject);
 
var
 
  S: String;
 
begin
 
  conn := TMySQL50Connection.Create(nil);
 
  query := TSQLQuery.Create(nil);
 
  transaction := TSQLTransaction.Create(nil);
 
  try
 
    try
 
      conn.HostName := '127.0.0.1';
 
      conn.UserName := 'root';
 
      conn.Password := '';
 
      conn.DatabaseName := 'test1';
 
      conn.Connected := True;
 
      conn.Transaction := transaction;
 
      query.DataBase := conn;
 
      //query.ParseSQL := true; //line not needed - this is the default anyway
 
      //query.ReadOnly := false; //line not needed - this is the default anyway
 
      query.SQL.Text := 'select * from tperson';
 
      query.Open;
 
  
      query.Last;
+
== Lazarus and MSSQL/Sybase ==
      S := IntToStr(query.RecordCount) + #13#10;
+
You can connect to Microsoft SQL Server databases using
      query.First;
+
# [https://www.devart.com/sdac/ SQL Server data access Lazarus components].They are working on Windows and macOS. Free to download.
 +
# The built-in '''SQLdb''' connectors '''TMSSQLConnection''' and '''TSybaseConnection''' (since Lazarus 1.0.8/FPC 2.6.2): see [[mssqlconn]].
 +
# '''Zeos''' component '''TZConnection''' (latest CVS, see links to Zeos elsewhere on this page)
 +
## On Windows you can choose between native library '''ntwdblib.dll''' (protocol '''mssql''') or FreeTDS libraries (protocol '''FreeTDS_MsSQL-nnnn''') where nnnn is one of four variants depending on the server version. For Delphi (not Lazarus) there is also another Zeos protocol '''ado''' for MSSQL 2005 or later. Using protocols mssql or ado generates code not platform independient.
 +
## On Linux the only way is with FreeTDS protocols and libraries (you should use '''libsybdb.so''').
 +
# '''ODBC''' (MSSQL and Sybase ASE) with SQLdb '''TODBCConnection''' (consider using '''TMSSQLConnection''' and '''TSybaseConnection''' instead)
 +
## See also [http://wiki.freepascal.org/ODBCConn#Connecting_to_Microsoft_SQL_Server]
 +
## On Windows it uses native ODBC Microsoft libraries (like sqlsrv32.dll for MSSQL 2000)
 +
## On Linux it uses unixODBC + FreeTDS (packages unixodbc or iodbc, and tdsodbc). Since 2012 there is also a Microsoft SQL Server ODBC Driver 1.0 for Linux which is a binary product (no open source) and provides native connectivity, but was released only for 64 bits and only for RedHat.
  
      while not query.EOF do
+
== Lazarus and ODBC ==
      begin
+
ODBC is a general database connection standard which is available on Linux, Windows and macOS. You will need an ODBC driver from your database vendor and set up an ODBC "data source" (also known as DSN).
        S := S + query.FieldByName('surname').AsString + #13#10;
+
You can use the SQLDB components ([[TODBCConnection]]) to connect to an ODBC data soruce.
        query.Next;
+
See [[ODBCConn]] for more details and examples.
      end;
 
    finally
 
      //query.Free;
 
      //conn.Free;
 
    end;
 
  except
 
    on E: Exception do
 
      ShowMessage(E.message);
 
  end;
 
  Memo1.Text:= S;
 
end;
 
  
procedure TForm1.Display;
+
=== Microsoft Access ===
begin
+
You can use the ODBC driver on Windows as well as Linux to access Access databases; see [[MS Access]]
  edtPersonID.Text := query.FieldByName('personid').AsString;
 
  edtSurname.Text := query.FieldByName('surname').AsString;
 
  edtDOB.Text := query.FieldByName('dob').AsString;
 
end;
 
  
procedure TForm1.btnFirstClick(Sender: TObject);
+
== Lazarus and Oracle ==
begin
+
* See [[Oracle]]. Access methods include:
  query.First;
+
# Built-in SQLDB support
  Display;
+
# Zeos
end;
+
# [https://www.devart.com/odac/ Oracle data access Lazarus component]
 
 
procedure TForm1.btnNewClick(Sender: TObject);
 
begin
 
  query.Append;
 
  query.FieldValues['personid'] := edtPersonID.Text;
 
  query.FieldValues['surname'] := edtSurname.Text;
 
  query.FieldValues['dob'] := edtDOB.Text;
 
  query.Post; 
 
  query.ApplyUpdates; //to apply update
 
  //transaction.Commit; //line not needed
 
end;
 
 
 
procedure TForm1.btnNextClick(Sender: TObject);
 
begin
 
  query.Next;
 
  Display;
 
end;
 
 
 
initialization
 
  {$I unt_db.lrs}
 
end.
 
</pre>
 
 
 
Here is a version using the TMySQL50Connection, TSQLQuery, TSQLTransaction, TDatasource and TDBGrid components that have been placed on the form:
 
<pre>
 
unit unt_mysql2;
 
{$mode objfpc}{$H+}
 
interface
 
uses
 
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
 
  mysql50conn, sqldb, StdCtrls, db, DBGrids, DbCtrls;
 
type
 
  { TForm1 }
 
  TForm1 = class(TForm)
 
    btnConnect: TButton;
 
    btnSave: TButton;
 
    btnNext: TButton;
 
    btnPrior: TButton;
 
    Datasource1: TDatasource;
 
    DBGrid1: TDBGrid;
 
    Memo1: TMemo;
 
    MySQL50Connection1: TMySQL50Connection;
 
    SQLQuery1: TSQLQuery;
 
    SQLTransaction1: TSQLTransaction;
 
    procedure btnConnectClick(Sender: TObject);
 
    procedure btnNextClick(Sender: TObject);
 
    procedure btnPriorClick(Sender: TObject);
 
    procedure btnSaveClick(Sender: TObject);
 
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
 
    procedure FormCreate(Sender: TObject);
 
  private
 
    { private declarations }
 
  public
 
    { public declarations }
 
  end;
 
var
 
  Form1: TForm1;
 
implementation
 
{ TForm1 }
 
 
 
procedure TForm1.FormCreate(Sender: TObject);
 
begin
 
  //Set properties of components:
 
  //(could be done in the Object Inspector)
 
  MySQL50Connection1.HostName := '127.0.0.1';
 
  MySQL50Connection1.UserName := 'root';
 
  MySQL50Connection1.Password := '';
 
  MySQL50Connection1.DatabaseName := 'test1';
 
  MySQL50Connection1.Transaction := SQLTransaction1;
 
  //SQLQuery1.ParseSQL := true; //line not needed - this is the default
 
  //SQLQuery1.ReadOnly := false; //line not needed - this is the default
 
  SQLQuery1.SQL.Text := 'select * from tperson';
 
  SQLQuery1.Transaction := SQLTransaction1;
 
  SQLQuery1.UpdateMode := upWhereChanged;
 
  Datasource1.Dataset := SQLQuery1;
 
  DBGrid1.DataSource := Datasource1;;
 
end;
 
 
 
procedure TForm1.btnConnectClick(Sender: TObject);
 
var
 
S : string;
 
begin
 
  try
 
    MySQL50Connection1.Connected := true;
 
    SQLQuery1.Open;
 
 
 
    //Tests to see if all is OK:
 
    SQLQuery1.Last;
 
    S := IntToStr(SQLQuery1.RecordCount) + #13#10;
 
    SQLQuery1.First;
 
    while not SQLQuery1.EOF do
 
    begin
 
      S := S + SQLQuery1.FieldByName('surname').AsString + #13#10;
 
      SQLQuery1.Next;
 
    end;
 
  except
 
    on E: Exception do
 
      ShowMessage(E.message);
 
  end;
 
  Memo1.Text:= S;
 
end;
 
 
 
procedure TForm1.btnNextClick(Sender: TObject);
 
begin
 
  SQLQuery1.Next;
 
end;
 
 
 
procedure TForm1.btnPriorClick(Sender: TObject);
 
begin
 
  SQLQuery1.Prior;
 
end;
 
 
 
procedure TForm1.btnSaveClick(Sender: TObject);
 
begin
 
  SQLQuery1.ApplyUpdates;
 
end;
 
 
 
procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
 
begin
 
  //Required or get EDatabase error on close:
 
  MySQL50Connection1.Connected := false;
 
end;
 
 
 
initialization
 
  {$I unt_mysql2.lrs}
 
end.
 
</pre>
 
  
 
== Lazarus and PostgreSQL ==
 
== Lazarus and PostgreSQL ==
 
+
* PostgreSQL is very well supported out of the box by FPC/Lazarus
This is a very short tutorial to get Lazarus 0.9.12 or later to connect to a PostgreSQL database, local or remote, using TPQConnection. This component is added to Lazarus when the package lazarus/components/sqldb/sqldblaz.lpk is installed.
+
* Please see [[postgres]] for details on various access methods, which include:
 
+
# Built-in SQLdb support. Use component '''TPQConnection''' from the [[SQLdb tab]] of the [[Component Palette]]
After correct install, follow these steps:
+
# [[Zeos]]. Use component '''TZConnection''' with protocol 'postgresql' from palette '''Zeos Access'''
 
+
# [https://www.devart.com/pgdac/ PostgreSQL data access Lazarus component]
* Place a PQConnection    from the SQLdb tab
 
* Place a SQLQuery        from the SQLdb tab
 
* Place a SQLTransaction  from the SQLdb tab
 
* Place a DataSource      from the DataAccess tab
 
* Place a DBGrid          from the DataControls tab
 
 
 
* In the PQConnection fill in:
 
** transaction property with the respective SQLTransaction object
 
** Database name
 
** HostName
 
** UserName + password
 
 
 
* Check that the SQLTransaction was automatically changed to point to the PQConnection
 
 
 
* In the SQLQuery fill in:
 
** transaction property with the respective object
 
** database property with respective object
 
** SQL (something like 'select * from anytable')
 
 
 
* In the DataSource object fill in the DataSet property with the SQLQuery object
 
 
 
* In the DBGrid fill in the datasource as the DataSource Object
 
 
 
Turn everything to connected and active and the DBGrid should be filled in design time.
 
TDBText and TDBEdit seem to work but (for me) they only _show_ _data_.
 
 
 
To change contents in the database, I called the DB Engine direct with the following code:
 
  try
 
    sql:= 'UPDATE table SET setting=1';
 
    PQDataBase.Connected:=True;
 
    PQDataBase.ExecuteDirect('Begin Work;');
 
    PQDataBase.ExecuteDirect(sql);
 
    PQDataBase.ExecuteDirect('Commit Work;');
 
    PQDataBase.Connected:=False;
 
  except
 
    on E : EDatabaseError do
 
      MemoLog.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
 
    on E : Exception do
 
      MemoLog.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
 
  end;
 
 
 
 
 
* Notes:
 
** Tested on windows, Lazarus 0.9.12 + PgSQL 8.3.1
 
** Some tests in linux, Lazarus 0.9.12 and PgSQL 8.0.x
 
 
 
 
 
* Instalation and errors:
 
** In the tested version of Lazarus .12, fields of type "text" and "numeric" have bugs
 
** I used with no problems char fixed size, int and float8
 
** Sometimes restarting Lazarus solves stupid errors...
 
** After some errors, the transactions remain active and should be deactivated mannually
 
** Changes made in Lazarus are of course not visible until transaction commited
 
** The integrated debugger seems buggy (at least in windows) - sometimes running outside of the IDE may help to find errors
 
** In linux certain error messages are printed in the console -- run your program in the command line, sometimes there is some extra useful debugging info
 
** Error: "Can not load Postgresql client. Is it installed (libpq.so) ?"
 
*** Add the path to seach libpq* from the PostgreSQL installation.
 
*** In linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
 
*** It may be necessary to create a symboloic link ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
 
***  In windows, add these libs anywhere in the Path environment variable or project dir
 
***  I windows, I copied all the DLLs in my C:\Program Files\PostgreSQL\8.1\bin dir to another dir in the PATH
 
***  Or add this postgres\bin dir to the path
 
 
 
A good example about connecting Lazarus with PostgreSQL under Windows is [http://paginas.fe.up.pt/~asousa/wiki/doku.php?id=proj:lazarus_projects#easy_db easyDB].
 
  
 
== Lazarus and SQLite ==
 
== Lazarus and SQLite ==
 +
SQLite is an embedded database; the database code can be distributed as a library (.dll/.so/.dylib) with your application to make it self-contained (comparable to Firebird embedded). SQLite is quite popular due to its relative simplicity, speed, small size and cross-platform support.
  
by Luiz Américo
+
Please see the [[SQLite]] page for details on various access methods, which include:
 
+
# Built-in SQLDb support. Use component '''TSQLite3Connection''' from palette '''SQLdb'''
WARNING: this section is about the sqlite components that are shipped with Free Pascal distribution (TSqliteDataset and TSqlite3Dataset). Free Pascal also provides access to sqlite through Sqldb (also in the default distribution), [http://zeos.firmos.at/portal.php Zeos] and [http://source.online.free.fr/ SqlitePass] components.
+
# Zeos
 
+
# SQLitePass
Visit the [http://sqlite4fpc.yolasite.com/ sqlite4fpc homepage] to find the API reference and more tutorials.
+
# TSQLite3Dataset
 
+
# [https://www.devart.com/litedac/ SQLite data access Lazarus components]
===Introduction===
 
 
 
TSqliteDataset and TSqlite3Dataset are TDataset descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. Below is a list of the principal advantages and disadvantages:
 
 
 
Advantages:
 
 
 
* Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
 
* Automatic database update: no need to  update the database manually with SQL statements, a single method take cares of it
 
* Fast: it caches the data in memory, making browsing in the dataset fast
 
* No server installation/configuration: just ship together with sqlite dynamic library
 
 
 
Disadvantages
 
 
 
* Requires external file (sqlite library)
 
 
 
===Requirements===
 
 
 
* For sqlite2 databases:
 
** fpc 2.0.0
 
** Lazarus 0.9.10
 
** sqlite runtime library 2.8.15 or above*
 
 
 
* Sqlite2 is not maintained anymore and the binary file cannot be found in the sqlite site
 
 
 
* For sqlite3 databases:
 
** fpc 2.0.2
 
** Lazarus 0.9.11 (svn revision 8443 or above)
 
** sqlite runtime library 3.2.1 or above (get from www.sqlite.org)
 
 
 
'''Before initiating a lazarus projects, ensure that:'''
 
* the sqlite library is on the system PATH or in the executable directory
 
* under Linux, put cmem as the first unit in uses clause of the main program
 
** In Debian, Ubuntu and other Debian-like distros, in order to build Lazarus IDE you must install the packages libsqlite-dev/libsqlite3-dev, not only sqlite/sqlite3 (Also applies to OpenSuSe)
 
 
 
===How To Use (Basic Usage)===
 
 
 
Install the package found at /components/sqlite directory (see instructions [[Install_Packages|here]])
 
 
 
At design time set the following properties:
 
 
 
* FileName: path of the sqlite file [required]
 
* TableName: name of the table used in the sql statement [required]
 
* Sql: a SQL select statement [optional]
 
 
 
'''Creating a Table (Dataset)'''
 
 
 
Double click in the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button.
 
A simple self-explaining table editor will be show.
 
 
 
  Here is all field types supported by TSqliteDataset and TSqlite3Dataset:
 
 
 
  Integer
 
  AutoInc
 
  String
 
  Memo
 
  Bool
 
  Float
 
  Word
 
  DateTime
 
  Date
 
  Time
 
  LargeInt
 
  Currency
 
 
 
'''Retrieving the data'''
 
 
 
After creating the table or with a previously created Table, open the dataset with Open method.
 
If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:
 
 
 
  SQL:='Select * from TABLENAME';
 
 
 
'''Applying changes to the underlying datafile'''
 
 
 
To use the ApplyUpdates function, the dataset must contain at least one field that fills the requirements for a Primary Key (values must be UNIQUE and not NULL)
 
 
 
It's possible to do that in two ways:
 
 
 
* Set PrimaryKey property to the name of a Primary Key field
 
* Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key)
 
 
 
If one of the two conditions is set then just call
 
 
 
  ApplyUpdates;
 
 
 
PS1: If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.
 
 
 
PS2: Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.
 
 
 
===Remarks===
 
 
 
* Although it has been tested with 10000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (principally with Memo Fields).
 
* The same datafile (Filename property) can host several tables/datasets
 
* Several datasets (different combinations of fields) can be created using the same table simultaneously
 
* It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
 
* It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
 
* Setting filename to a sqlite datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.
 
 
 
Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory
 
 
 
Luiz Américo
 
luizmed(at)oi(dot)com(dot)br
 
 
 
== Lazarus and MSSQL ==
 
 
 
It is working with Zeoslib (latest cvs), see the links on bottom of page.
 
 
 
Alternatively you can access MSSQL databases via ODBC - no need to install Zeoslib or any other additional libraries - just use the SQLdb component that is included (I tested this in Lazarus v0.9.26.2)
 
 
 
The example code below selects the rows from a table called 'journal_entries' and displays all the values of column 'journal_entry' in a Memo control called Memo1.
 
 
 
procedure TForm1.Button1Click(Sender: TObject);
 
var
 
  S: String;
 
  conn: TODBCConnection;
 
  query: TSQLQuery;
 
  transaction: TSQLTransaction;
 
 
begin
 
  conn := TODBCCOnnection.Create(nil);
 
  query := TSQLQuery.Create(nil);
 
  transaction := TSQLTransaction.Create(nil);
 
  try
 
    try
 
      conn.HostName := '127.0.0.1';
 
      conn.DatabaseName := 'diary'; {replace this with the name of your database}
 
      conn.Transaction := transaction;
 
      conn.UserName:= 'db_username';
 
      conn.Password:= '';
 
      query.DataBase := conn;
 
      query.UsePrimaryKeyAsKey:=false; {required, else you get "could not retrieve primary key metadata". I was really stuck on this until I found http://bugs.freepascal.org/view.php?id=13241}
 
      query.SQL.Text := 'select * from diary.dbo.journal_entries';
 
      query.Open;
 
      S := '';
 
      while not query.EOF do
 
      begin
 
        S := S + query.FieldByName('journal_entry').AsString + #13#10;
 
        query.Next;
 
      end;
 
    finally
 
      query.Free;
 
      conn.Free;
 
    end;
 
  except
 
    on E: Exception do
 
      ShowMessage(E.message);
 
  end;
 
  Memo1.Text:= S;
 
end;
 
 
 
== Lazarus and Interbase / Firebird ==
 
 
 
See [[Install Packages|Install Packages]]. On this page is a first small example en explanation about how to connect to an IB or FB server.
 
  
Also work with the latest Zeoslib (from cvs).
+
== Lazarus and Firebird/Interbase ==
  
===FBLib Firebird Library===
+
InterBase (and FireBird) Data Access Components (IBDAC) is a library of components that provides native connectivity to InterBase, Firebird and Yaffil from Lazarus (and Free Pascal) on Windows, macOS, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. IBDAC-based applications connect to the server directly using the InterBase client. IBDAC is designed to help programmers develop faster and cleaner InterBase database applications.
  
[http://fblib.altervista.org FBLib] is an open Source Library No Data Aware for direct access to Firebird Relational Database from Borland Delphi / Kylix, Free Pascal and Lazarus.
+
IBDAC is a complete replacement for standard InterBase connectivity solutions. It presents an efficient alternative to InterBase Express Components, the Borland Database Engine (BDE), and the standard dbExpress driver for access to InterBase.
  
Current Features include:
+
[https://www.devart.com/ibdac/download.html Firebird data access components for Lazarus] are free to download.
 
 
* Direct Access to Firebird 1.0.x 1.5.x Classic or SuperServer
 
* Multi platform [Win32,Gnu/Linux,FreeBSD)
 
* Automatic select client library 'fbclient' or 'gds32'
 
* Query with params
 
* Support SQL Dialect 1/3
 
* LGPL License agreement
 
* Extract Metadata
 
* Simple Script Parser
 
* Only 100-150 KB added into final EXE
 
* Support BLOB Fields
 
* Export Data to HTML SQL Script
 
* Service manager (backup,restore,gfix...)
 
* Events Alerter
 
 
 
You can download documentation on [http://fblib.altervista.org/ FBLib's website].
 
 
 
[http://www.firebirdnews.org/?p=1769 Here] you can find a updated version. Or, use the svn: [https://tiopf.svn.sourceforge.net/svnroot/tiopf/3rdParty/FBLib/Trunk] (Tip by [http://wiki.lazarus.freepascal.org/User:Guiosoft Guionardo])
 
  
 
== Lazarus and dBase ==
 
== Lazarus and dBase ==
  
[[user:Tonymaro|Tony Maro]]
+
FPC includes a simple database component that is derived from the Delphi TTable component called "TDbf" [http://tdbf.sourceforge.net/ TDbf Website]). It supports various DBase and Foxpro formats.
 
 
You might also want to visit the beginnings of the [[Lazarus Tdbf Tutorial|TDbf Tutorial page]]
 
 
 
FPC includes a simple database component that is similar in function to the Delphi TTable component called "TDbf" ([http://tdbf.sourceforge.net/ TDbf Website]) that supports a very basic subset of features for dBase files. It is not installed by default, so you will first need to install the Lazarus package from the "lazarus/components/tdbf" directory and rebuild your Lazarus IDE.  It will then appear next to the TDatasource in your component palette (Data Access tab).
 
 
 
The TDbf component has an advantage over other database components in that it doesn't require any sort of runtime database engine. However it's not the best option for large database applications.
 
 
 
It's very easy to use.  Simply drop a TDbf on your form, set the runtime path to the directory that your database files will be in, set the table name, and link it to your TDatasource component.
 
 
 
Real functionality requires a bit more effort, however.  If a table doesn't already exist, you'll need to create it programmatically, unless there's a compatible table designer I'm not familiar with.
 
Note: Current version of OpenOffice (2.0x) contains OpenOffice Base, which can create dbf files in a somewhat user-friendly way.
 
 
 
Attempting to open a nonexistent table will generate an error.  Tables can be created programmatically through the component after the runtime path and table name are set.
 
 
 
For instance, to create a table called "dvds" to store your dvd collection you would drop it on your form, set the runtime path, and set the table name to "dvds".  The resulting file will be called "dvds.dbf".
 
 
 
In your code, insert the following:
 
 
 
    Dbf1.FilePathFull := '/path/to/my/database';
 
    Dbf1.TableName := 'dvds';
 
    With Dbf1.FieldDefs do begin
 
        Add('Name', ftString, 80, True);
 
        Add('Description', ftMemo, 0, False);
 
        Add('Rating', ftString, 5, False);
 
    end;
 
    Dbf1.CreateTable;
 
 
 
When this code is run, your DVD collection table will be created.  After that, all data aware components linked through the TDatasource to this component will allow easy access to the data. 
 
 
 
Adding an index is a little different from your typical TTable.  It must be done after the database is open.  You use the same method also to rebuild the indices.  For instance:
 
 
 
    Dbf1.Exclusive := True;
 
    Dbf1.Open;
 
    Dbf1.AddIndex('dvdsname','Name',[ixPrimary, ixUnique, ixCaseInsensitive]);
 
    Dbf1.AddIndex('rating.ndx', 'Rating', [ixCaseInsensitive]);
 
    Dbf1.Close;
 
 
 
The first (primary) index will be a file called "dvdsname.mdx" and the second will be a file named "rating.ndx" so in a multiple table database you must be careful not to use the same file name again.
 
 
 
I will try to add a more detailed example at a later date, but hopefully this will get those old Delphi programmers up and running with databases in Lazarus!
 
 
 
 
 
 
 
===Searching and Displaying a data set===
 
 
 
[[user:svbfieldview.net|Simon Batty]]
 
 
 
In this example I wanted to search a database of books for all the titles an author has listed
 
and then display the list in a memo box
 
<delphi>
 
    Dbf1.FilePathFull := '/home/somelocatio/database_location/'; // path to the database directory
 
    Dbf1.TableName := 'books.dbase';                            // database file (including extension)
 
    DbF1.Open;
 
    memo1.Clear;                                                // clear the memo box
 
 
    Dbf1.FilterOptions := [foCaseInsensitive];
 
    Df1.Filter := 'AU=' + QuotedStr('anauthor');        // AU is the field name containing the authors
 
 
    Dbf1.Filtered := true;      // This selects the filtered set
 
    Dbf1.First;                  // moves the the first filtered data
 
    while not dbf1.EOF do        // prints the titles that match the author to the memo box
 
    begin
 
        memo1.Append(Dbf1.FieldByName('TI').AsString); // TI is the field name for titles
 
        dbf1.next;                                    // use .next here NOT .findnext!
 
    end;
 
    Dbf1.Close;</delphi> 
 
 
 
Note that  you can use Ddf1.findfirst to get the first record in the filtered set, then use
 
Dbf1.next to move though the data. I found that using Dbf1.Findnext just causes the program to hang. 
 
 
 
This database was generated using TurboBD that came with the Kylix 1. I cannot get TurboBD tables to work with Lazarus, however you can download a command line tool from TurboDB's website that allows you to convert TurboDB table to other formats.
 
 
 
== Using TSdfDataset and TFixedDataset ==
 
 
 
TSdfDataset and TFixedDataset are two simple datasets which offer a very simple textual storage format. These datasets are very convenient for small databases, because they are fully implemented as an object pascal unit, and thus require no external libraries, and because their textual format allows them to be easely edited with a text editor.
 
 
 
To start with this formats, a initial database file should be created. The format is very simple, so use a text editor to do this.
 
 
 
Bellow is a sample database for TSdfDataset. Note that the first line has the names of the fields and that we are using commas as separators:
 
 
 
<pre>
 
ID,NAMEEN,NAMEPT,HEIGHT,WIDTH,PINS,DRAWINGCODE
 
1,resistor,resistor,1,1,1,LINE
 
2,capacitor,capacitor,1,1,1,LINE
 
3,transistor npn,transistor npn
 
</pre>
 
 
 
And here is an example database for using with TFixedDataset. Each record occupies a fixed amount of space, and if the field is smaller then it, spaces should be used to fill the remaining size.
 
 
 
<pre>
 
Name = 15 chars; Surname = 15 chars; Tell = 10 chars; e_mail = 20 chars;
 
Piet          Pompies                  piet@pompies.net   
 
</pre>
 
 
 
=== Using the datasets directly ===
 
 
 
Sometimes it is useful to create the dataset and work with it completely in code, and the following code will do exactly this. Note some peculiarities of TSdfDataset/TFixedDataset:
 
  
* The lines in the database can have a maximum size of about 300. A fix is being researched.
+
'''TDbf''' does not accept SQL commands but you can use the dataset methods etc and you can also use regular databound controls such as the DBGrid.
* It is necessary to add the field definitions. Some datasets are able to fill this information alone from the database file
 
* One should set FirstLineAsSchema to true, to indicate that the first line includes the field names and positions
 
* The Delimiter property holds the separator for the fields. It will not be possible to use this char in strings in the database. Similarly it will not be possible to have lineendings in the database because they mark the change between records. It's possible to overcome this by substituting the needed comma or line ending with another not often used char, like # for example. So that when showing the data on screen all # chars could be converted to line endings and the inverse when storing data back to the database. The ReplaceString routine is useful here.
 
  
<delphi>
+
It doesn't require any sort of runtime database engine. However it's not the best option for large database applications.
constructor TComponentsDatabase.Create;
 
begin
 
  inherited Create;
 
  
  FDataset := TSdfDataset.Create(nil);
+
See the [[Lazarus Tdbf Tutorial|TDbf Tutorial page]] for the tutorial as well as documentation.
  FDataset.FileName := vConfigurations.ComponentsDBFile;
 
  
  // Not necessary with TSdfDataset
+
You can use e.g. OpenOffice/LibreOffice Base to visually create/edit dbf files, or create DBFs in code using [[TDbf]].
//  FDataset.TableName := STR_DB_COMPONENTS_TABLE;
 
// FDataset.PrimaryKey := STR_DB_COMPONENTS_ID;
 
  
  // Adds field definitions
+
== Lazarus and Paradox ==
  FDataset.FieldDefs.Add('ID', ftString);
+
Paradox was the default format for database files in old versions of Delphi.
  FDataset.FieldDefs.Add('NAMEEN', ftString);
+
The concept is similar to DBase files/DBFs, where the "database" is a folder, and each table is a file inside that folder. Also, each index is a file too.
  FDataset.FieldDefs.Add('NAMEPT', ftString);
+
To access this files from Lazarus we have these options:
  FDataset.FieldDefs.Add('HEIGHT', ftString);
 
  FDataset.FieldDefs.Add('WIDTH', ftString);
 
  FDataset.FieldDefs.Add('PINS', ftString);
 
  FDataset.FieldDefs.Add('DRAWINGCODE', ftString);
 
  
  // Necessary for TSdfDataset
+
* '''[[TParadox]]''': Install package "lazparadox 0.0" included in the standard distribution. When you install this package, you will see a new component labeled "PDX" in the "Data Access" palette. This component is not standalone, it uses a "native" library, namely the [http://pxlib.sourceforge.net pdxlib library] which is available for Linux and Windows. For example, to install in Debian, you could get '''pxlib1''' from package manager. In Windows you need the pxlib.dll file.  
  FDataset.Delimiter := ',';
 
  FDataset.FirstLineAsSchema := True;
 
  
  FDataset.Active := True;
+
* '''[[TPdx]]''': Paradox DataSet for Lazarus and Delphi from [http://tpdx.sourceforge.net/ this site]. This component is standalone (pure object pascal), not requiring any external library, but it can only read (not write) Paradox files. The package to install is "paradoxlaz.lpk" and the component should appear in the "Data Access" palette with PDX label (but orange colour).  
  
  // Sets the initial record
+
* '''[[TParadoxDataSet]]''': is a [[TDataSet]] that can only read Paradox Files up to Version 7.  The approach is similar to the TPdx component, the package to install is "lazparadox.lpk" and the component should also appear in the "Data Access" palette.
  CurrentRecNo := 1;
 
  FDataset.First;
 
end;
 
</delphi>
 
  
When using TSdfDataset directly be aware that RecNo, although it is implemented, does not work as a way to move through the dataset whether reading or writing records. The standard navigation routines like First, Next, Prior and Last work as expected, so you need to use them rather than RecNo.
+
== TSdfDataset and TFixedDataset ==
If you are used to using absolute record numbers to navigate around a database you can implement your own version of RecNo. Declare a global longint variable called CurrentRecNo which will hold the current RecNo value. Remember that this variable will have the same convention as RecNo, so the first record has number 1 (it is not zero-based). After activating the database initialize the database to the first record with TSdfDataset.First and set CurrentRecNo := 1
+
[[TSdfDataSet]] and [[TFixedFormatDataSet]] are two simple [[TDataSet]] descandants which offer a very simple textual storage format. These datasets are very convenient for small databases, because they are fully implemented as an Object Pascal unit, and thus require no external libraries. Also, their textual format allows them to be easily viewed/edited with a text editor.
  
<delphi>
+
See [[CSV]] for example code.
{@@
 
  Moves to the desired record using TDataset.Next and TDataset.Prior
 
  This avoids using TDataset.RecNo which doesn't navigate reliably in any dataset.
 
  
  @param AID Indicates the record number. The first record has number 1
+
== Lazarus and Advantage Database Server ==
}
+
* Please see [[Advantage Database Server]] for details on using Advantage Database Server
procedure TComponentsDatabase.GoToRec(AID: Integer);
 
begin
 
  // We are before the desired record, move forward
 
  if CurrentRecNo < AID then
 
  begin
 
    while (not FDataset.EOF) and (CurrentRecNo < AID) do
 
    begin
 
      FDataset.Next;
 
      FDataset.CursorPosChanged;
 
      Inc(CurrentRecNo);
 
    end;
 
  end
 
  // We are after the desired record, move back
 
  else if CurrentRecNo > AID  then
 
  begin
 
    while (CurrentRecNo >= 1) and (CurrentRecNo > AID) do
 
    begin
 
      FDataset.Prior;
 
      FDataset.CursorPosChanged;
 
      Dec(CurrentRecNo);
 
    end;
 
  end;
 
end;
 
</delphi>
 
  
=== Using with data-aware controls ===
+
==See also==
 +
(Sorted alphabetically)
 +
* [[Portal:Databases|Database Portal]]
 +
* [[Databases]]
 +
* [[Database_field_type]]
 +
* [[How to write in-memory database applications in Lazarus/FPC]]
 +
* [[Lazarus DB Faq]]
 +
* [[Lazarus Tdbf Tutorial]]
 +
* [[multi-tier_options_with_fpc|Multi-tier options with FPC]]
 +
* [[SQLdb Tutorial1]]
 +
* [[SqlDBHowto]]
 +
* [[tiOPF]] - a free and open source Object Persistence Framework.
 +
* [[Zeos tutorial]]
  
==Contributors and Changes==
+
==External links==
This page has been converted from the epikwiki [http://lazarus-ccr.sourceforge.net/index.php?wiki=LazarusDatabase version].
 
  
[[Category:Databases]]
+
* [http://pdo.sourceforge.net Pascal Data Objects] - a database API that worked for both FPC and Delphi and utilises native MySQL libraries for version 4.1 and 5.0 and Firebird SQL 1.5, and 2.0.  It's inspired by PHP's PDO class.
[[Category:Tutorials]]
+
* [http://lazaruszeos.blogspot.com Zeos+SQLite Tutorial] - Good tutorial using screenshots and screencasts it explain how to use SQLite and Zeos, spanish (google translate does a good work in translating it to english)

Latest revision as of 01:31, 24 November 2021

English (en) español (es) français (fr) 日本語 (ja) polski (pl) русский (ru)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Overview

This article is an overview of which databases can work with Lazarus.

Lazarus supports several databases out of the box (using e.g. the SQLDB framework), however the developer must install the required packages (client libraries) for each one.

You can access the database through code or by dropping components on a form. The data-aware components represent fields and are connected by setting the DataSource property to point to a TDataSource. The Datasource represents a table and is connected to the database components (examples: TPSQLDatabase, TSQLiteDataSet) by setting the DataSet property. The data-aware components are located on the Data Controls tab. The Datasource and the database controls are located on the "Data Access" tab.

See the tutorials for Lazarus/FPC built in database access, suitable for Firebird, MySQL, SQLite, PostgreSQL etc:

Lazarus and Interbase / Firebird

  • Firebird is very well supported out of the box by FPC/Lazarus (using SQLDB); please see Firebird for details.
  • Other Firebird libraries has a list of alternative access libraries (e.g. PDO, Zeos, FBlib)

Lazarus and MySQL

  • Please see mysql for details on various access methods, which include:
  1. Built-in SQLdb support
  2. PDO
  3. Zeos
  4. MySQL data access Lazarus components

Lazarus and MSSQL/Sybase

You can connect to Microsoft SQL Server databases using

  1. SQL Server data access Lazarus components.They are working on Windows and macOS. Free to download.
  2. The built-in SQLdb connectors TMSSQLConnection and TSybaseConnection (since Lazarus 1.0.8/FPC 2.6.2): see mssqlconn.
  3. Zeos component TZConnection (latest CVS, see links to Zeos elsewhere on this page)
    1. On Windows you can choose between native library ntwdblib.dll (protocol mssql) or FreeTDS libraries (protocol FreeTDS_MsSQL-nnnn) where nnnn is one of four variants depending on the server version. For Delphi (not Lazarus) there is also another Zeos protocol ado for MSSQL 2005 or later. Using protocols mssql or ado generates code not platform independient.
    2. On Linux the only way is with FreeTDS protocols and libraries (you should use libsybdb.so).
  4. ODBC (MSSQL and Sybase ASE) with SQLdb TODBCConnection (consider using TMSSQLConnection and TSybaseConnection instead)
    1. See also [1]
    2. On Windows it uses native ODBC Microsoft libraries (like sqlsrv32.dll for MSSQL 2000)
    3. On Linux it uses unixODBC + FreeTDS (packages unixodbc or iodbc, and tdsodbc). Since 2012 there is also a Microsoft SQL Server ODBC Driver 1.0 for Linux which is a binary product (no open source) and provides native connectivity, but was released only for 64 bits and only for RedHat.

Lazarus and ODBC

ODBC is a general database connection standard which is available on Linux, Windows and macOS. You will need an ODBC driver from your database vendor and set up an ODBC "data source" (also known as DSN). You can use the SQLDB components (TODBCConnection) to connect to an ODBC data soruce. See ODBCConn for more details and examples.

Microsoft Access

You can use the ODBC driver on Windows as well as Linux to access Access databases; see MS Access

Lazarus and Oracle

  • See Oracle. Access methods include:
  1. Built-in SQLDB support
  2. Zeos
  3. Oracle data access Lazarus component

Lazarus and PostgreSQL

  • PostgreSQL is very well supported out of the box by FPC/Lazarus
  • Please see postgres for details on various access methods, which include:
  1. Built-in SQLdb support. Use component TPQConnection from the SQLdb tab of the Component Palette
  2. Zeos. Use component TZConnection with protocol 'postgresql' from palette Zeos Access
  3. PostgreSQL data access Lazarus component

Lazarus and SQLite

SQLite is an embedded database; the database code can be distributed as a library (.dll/.so/.dylib) with your application to make it self-contained (comparable to Firebird embedded). SQLite is quite popular due to its relative simplicity, speed, small size and cross-platform support.

Please see the SQLite page for details on various access methods, which include:

  1. Built-in SQLDb support. Use component TSQLite3Connection from palette SQLdb
  2. Zeos
  3. SQLitePass
  4. TSQLite3Dataset
  5. SQLite data access Lazarus components

Lazarus and Firebird/Interbase

InterBase (and FireBird) Data Access Components (IBDAC) is a library of components that provides native connectivity to InterBase, Firebird and Yaffil from Lazarus (and Free Pascal) on Windows, macOS, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. IBDAC-based applications connect to the server directly using the InterBase client. IBDAC is designed to help programmers develop faster and cleaner InterBase database applications.

IBDAC is a complete replacement for standard InterBase connectivity solutions. It presents an efficient alternative to InterBase Express Components, the Borland Database Engine (BDE), and the standard dbExpress driver for access to InterBase.

Firebird data access components for Lazarus are free to download.

Lazarus and dBase

FPC includes a simple database component that is derived from the Delphi TTable component called "TDbf" TDbf Website). It supports various DBase and Foxpro formats.

TDbf does not accept SQL commands but you can use the dataset methods etc and you can also use regular databound controls such as the DBGrid.

It doesn't require any sort of runtime database engine. However it's not the best option for large database applications.

See the TDbf Tutorial page for the tutorial as well as documentation.

You can use e.g. OpenOffice/LibreOffice Base to visually create/edit dbf files, or create DBFs in code using TDbf.

Lazarus and Paradox

Paradox was the default format for database files in old versions of Delphi. The concept is similar to DBase files/DBFs, where the "database" is a folder, and each table is a file inside that folder. Also, each index is a file too. To access this files from Lazarus we have these options:

  • TParadox: Install package "lazparadox 0.0" included in the standard distribution. When you install this package, you will see a new component labeled "PDX" in the "Data Access" palette. This component is not standalone, it uses a "native" library, namely the pdxlib library which is available for Linux and Windows. For example, to install in Debian, you could get pxlib1 from package manager. In Windows you need the pxlib.dll file.
  • TPdx: Paradox DataSet for Lazarus and Delphi from this site. This component is standalone (pure object pascal), not requiring any external library, but it can only read (not write) Paradox files. The package to install is "paradoxlaz.lpk" and the component should appear in the "Data Access" palette with PDX label (but orange colour).
  • TParadoxDataSet: is a TDataSet that can only read Paradox Files up to Version 7. The approach is similar to the TPdx component, the package to install is "lazparadox.lpk" and the component should also appear in the "Data Access" palette.

TSdfDataset and TFixedDataset

TSdfDataSet and TFixedFormatDataSet are two simple TDataSet descandants which offer a very simple textual storage format. These datasets are very convenient for small databases, because they are fully implemented as an Object Pascal unit, and thus require no external libraries. Also, their textual format allows them to be easily viewed/edited with a text editor.

See CSV for example code.

Lazarus and Advantage Database Server

See also

(Sorted alphabetically)

External links

  • Pascal Data Objects - a database API that worked for both FPC and Delphi and utilises native MySQL libraries for version 4.1 and 5.0 and Firebird SQL 1.5, and 2.0. It's inspired by PHP's PDO class.
  • Zeos+SQLite Tutorial - Good tutorial using screenshots and screencasts it explain how to use SQLite and Zeos, spanish (google translate does a good work in translating it to english)