Difference between revisions of "Lazarus Database Overview"

From Lazarus wiki
Jump to navigationJump to search
 
(46 intermediate revisions by 9 users not shown)
Line 1: Line 1:
{{Translate}}
+
{{Lazarus Database Overview}}
 +
 
 +
{{Infobox databases}}
  
 
== Overview ==
 
== Overview ==
 
+
This article is an overview of which databases can work with Lazarus.
This article is an overview of the 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.  
 
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.
+
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 Interbase / Firebird ==
 
* Please see [[Firebird_in_action|Firebird in action]] for details on various access methods, which include
 
# Built-in SQLDB support
 
# FBLib
 
# IBX
 
# PDO
 
# Zeos
 
  
For a tutorial that leads you through creating a Lazarus GUI for a Firebird database, see:
+
See the tutorials for Lazarus/FPC built in database access, suitable for Firebird, MySQL, SQLite, PostgreSQL etc:
 +
* [[SQLdb Tutorial0]]
 
* [[SQLdb Tutorial1]]
 
* [[SQLdb Tutorial1]]
 +
* [[SQLdb Tutorial2]]
 +
* [[SQLdb Tutorial3]]
 +
* [[SQLdb Tutorial4]]
  
 +
== 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 ==
 
== Lazarus and MySQL ==
 
* Please see [[mysql]] for details on various access methods, which include:
 
* Please see [[mysql]] for details on various access methods, which include:
# Built-in SQLDB support
+
# Built-in [[SQLdb_Package|SQLdb]] support
 
# PDO
 
# PDO
# Zeos
+
# [[ZeosDBO|Zeos]]
 +
# [https://www.devart.com/mydac/ MySQL data access Lazarus components]
  
 
== Lazarus and MSSQL/Sybase ==
 
== Lazarus and MSSQL/Sybase ==
 
You can connect to Microsoft SQL Server databases using
 
You can connect to Microsoft SQL Server databases using
# The built-in '''SQLdb''' (recent Lazarus/FPC 2.6.1+): '''TMSSQLConnection''' (MS SQL) and '''TSybaseConnection''' (Sybase ASE)
+
# [https://www.devart.com/sdac/ SQL Server data access Lazarus components].They are working on Windows and macOS. Free to download.
## On Windows, you can download a recent 32 or 64 bit version of the FreeTDS library '''dblib.dll''' here: [ftp://ftp.freepascal.org/fpc/contrib/windows/]
+
# The built-in '''SQLdb''' connectors '''TMSSQLConnection''' and '''TSybaseConnection''' (since Lazarus 1.0.8/FPC 2.6.2): see [[mssqlconn]].
## Not necessary normally: By modifying FPC file '''dblib.pas''' SQLDB could use the "native" library '''ntwdblib.dll''' instead of the default FreeTDS library '''dblib.dll''' (in this case FPC needs to be recompiled).
 
## On Linux by default it uses the FreeTDS library '''libsybdb.so''' (for example, in Debian is in package '''libsybd5''').
 
 
# '''Zeos''' component '''TZConnection''' (latest CVS, see links to Zeos elsewhere on this page)
 
# '''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 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''').
 
## On Linux the only way is with FreeTDS protocols and libraries (you should use '''libsybdb.so''').
# '''ODBC''' (MSSQL and Sybase ASE) with SQLdb '''TODBCConnection''' (see also [http://wiki.freepascal.org/ODBCConn#Connecting_to_Microsoft_SQL_Server])
+
# '''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 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.
 
## 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.
  
Please see the FPC and Lazarus help files for details on using SQLDB with Sybase ASE and MSSQL Server.
+
== 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.
  
{{Note| In any of the above forms, you can connect using TCP/IP, e.g. on port 1433. }}
+
=== Microsoft Access ===
 
+
You can use the ODBC driver on Windows as well as Linux to access Access databases; see [[MS Access]]
If you use native Microsoft Libraries (over Windows) it could be possible to connect to remote server with Named Pipes. FreeTDS libraries do not support Named Pipes.
 
 
 
{{Note| Lazarus 1.0 was released with FPC 2.6.0 and does not include TMSSQLConnection or TSybaseConnection. If you want these, it might be easiest to download a snapshot of the latest Lazarus built with FPC 2.6.1, optinally installing that in a directory different from your Lazarus 1.0 version. You can use --primary-config-path to set up a separate environment for the Lazarus snapshot (see [[Multiple Lazarus]]) }}
 
 
 
== Lazarus and ODBC ==
 
ODBC is a general database connection standard which is available on Linux, Windows and OSX. 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
 
  
 
== Lazarus and Oracle ==
 
== Lazarus and Oracle ==
* Acces methods include:
+
* See [[Oracle]]. Access methods include:
 
# Built-in SQLDB support
 
# Built-in SQLDB support
 
# Zeos
 
# Zeos
 +
# [https://www.devart.com/odac/ Oracle data access Lazarus component]
  
 
== Lazarus and PostgreSQL ==
 
== 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:
 
* Please see [[postgres]] for details on various access methods, which include:
# Built-in SQLdb support. Use component '''TPQConnection''' from palette '''SQLdb'''
+
# Built-in SQLdb support. Use component '''TPQConnection''' from the [[SQLdb tab]] of the [[Component Palette]]
# Zeos. Use component '''TZConnection''' with protocol 'postgresql' from palette '''Zeos Access'''
+
# [[Zeos]]. Use component '''TZConnection''' with protocol 'postgresql' from palette '''Zeos Access'''
 +
# [https://www.devart.com/pgdac/ PostgreSQL data access Lazarus component]
  
 
== Lazarus and SQLite ==
 
== Lazarus and SQLite ==
Line 67: Line 65:
  
 
Please see the [[SQLite]] page for details on various access methods, which include:
 
Please see the [[SQLite]] page for details on various access methods, which include:
# Built-in SQLDb support
+
# Built-in SQLDb support. Use component '''TSQLite3Connection''' from palette '''SQLdb'''
 
# Zeos
 
# Zeos
 
# SQLitePass  
 
# SQLitePass  
 
# TSQLite3Dataset
 
# TSQLite3Dataset
 +
# [https://www.devart.com/litedac/ 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.
 +
 +
[https://www.devart.com/ibdac/download.html Firebird data access components for Lazarus] are free to download.
  
 
== Lazarus and dBase ==
 
== Lazarus and dBase ==
Line 76: Line 83:
 
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.
 
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.
  
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.
+
'''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.
 
It doesn't require any sort of runtime database engine. However it's not the best option for large database applications.
Line 82: Line 89:
 
See the [[Lazarus Tdbf Tutorial|TDbf Tutorial page]] for the tutorial as well as documentation.
 
See the [[Lazarus Tdbf Tutorial|TDbf Tutorial page]] for the tutorial as well as documentation.
  
You can use e.g. OpenOffice/LibreOffice Base to visually create/edit dbf files.
+
You can use e.g. OpenOffice/LibreOffice Base to visually create/edit dbf files, or create DBFs in code using [[TDbf]].
 
 
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.
 
 
 
  
 
== Lazarus and Paradox ==
 
== 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:
  
Paradox was the default format for table files in Delphi and before (view [http://en.wikipedia.org/wiki/Paradox_%28database%29 Wikipedia]).
+
* '''[[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.  
The concept is similar to DBF's, 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 this 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. In order to use this component you have to include "paradox" in the uses list. This component is not standalone, it uses a "native" library, namely the [http://pxlib.sourceforge.net pdxlib library] which is available for Linux. Alternatively for example in Debian, you could to install '''pxlib1''' from package manager.
+
* '''[[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).  
  
* '''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. The package to install is "paradoxlaz.lpk" and the component should appear in the "Data Access" palette.  
+
* '''[[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.
  
* '''TParadoxDataSet''': is a TDataSet that can only read Paradox Files up to Version 7. See this [http://wiki.lazarus.freepascal.org/TParadoxDataSet wiki page]. 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.
  
== Using TSdfDataset and TFixedDataset ==
+
See [[CSV]] for example code.
 
 
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.
 
 
 
Note: December 2012/FPC 2.7.1: SDFDataset is supposed to read/write CSV files according to RFC4180, but still has some problems with quoted fields etc. For more details, see [[CSV]]
 
 
 
To start with this format, 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.
 
* 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.
 
 
 
<syntaxhighlight>
 
uses sdfdata, db;
 
 
 
constructor TComponentsDatabase.Create;
 
var
 
  FDataset: TSdfDataset;
 
begin
 
  inherited Create;
 
 
 
  FDataset := TSdfDataset.Create(nil);
 
  FDataset.FileName := vConfigurations.ComponentsDBFile;
 
 
 
  // Not necessary with TSdfDataset
 
//  FDataset.TableName := STR_DB_COMPONENTS_TABLE;
 
//  FDataset.PrimaryKey := STR_DB_COMPONENTS_ID;
 
 
 
  // Adds field definitions
 
  FDataset.FieldDefs.Add('ID', ftString);
 
  FDataset.FieldDefs.Add('NAMEEN', ftString);
 
  FDataset.FieldDefs.Add('NAMEPT', ftString);
 
  FDataset.FieldDefs.Add('HEIGHT', ftString);
 
  FDataset.FieldDefs.Add('WIDTH', ftString);
 
  FDataset.FieldDefs.Add('PINS', ftString);
 
  FDataset.FieldDefs.Add('DRAWINGCODE', ftString);
 
 
 
  // Necessary for TSdfDataset
 
  FDataset.Delimiter := ',';
 
  FDataset.FirstLineAsSchema := True;
 
 
 
  FDataset.Active := True;
 
 
 
  // Sets the initial record
 
  CurrentRecNo := 1;
 
  FDataset.First;
 
end;</syntaxhighlight>
 
 
 
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.
 
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
 
 
 
<syntaxhighlight>{@@
 
  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
 
}
 
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;</syntaxhighlight>
 
 
 
=== Using with data-aware controls ===
 
  
 
== Lazarus and Advantage Database Server ==
 
== Lazarus and Advantage Database Server ==
Line 204: Line 112:
 
==See also==
 
==See also==
 
(Sorted alphabetically)
 
(Sorted alphabetically)
 +
* [[Portal:Databases|Database Portal]]
 
* [[Databases]]
 
* [[Databases]]
 
* [[Database_field_type]]
 
* [[Database_field_type]]
* [[Firebird in action]]
 
 
* [[How to write in-memory database applications in Lazarus/FPC]]
 
* [[How to write in-memory database applications in Lazarus/FPC]]
 
* [[Lazarus DB Faq]]
 
* [[Lazarus DB Faq]]
 
* [[Lazarus Tdbf Tutorial]]
 
* [[Lazarus Tdbf Tutorial]]
* [[MS Access]]
 
 
* [[multi-tier_options_with_fpc|Multi-tier options with FPC]]
 
* [[multi-tier_options_with_fpc|Multi-tier options with FPC]]
* [[MySQLDatabases]]
 
* [[ODBCConn]]
 
 
* [[SQLdb Tutorial1]]
 
* [[SQLdb Tutorial1]]
 
* [[SqlDBHowto]]
 
* [[SqlDBHowto]]
* [[SQLite]]
 
 
* [[tiOPF]] - a free and open source Object Persistence Framework.
 
* [[tiOPF]] - a free and open source Object Persistence Framework.
 
* [[Zeos tutorial]]
 
* [[Zeos tutorial]]
Line 224: Line 128:
 
* [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.
 
* [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.
 
* [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)
 
* [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)
 
 
[[Category:Databases]]
 
[[Category:Tutorials]]
 

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)