Lazarus Databases

Databases
This page is an introduction to the topic 'Lazarus and databases'. The following table provides an overview
of supported databases.
Only the database components for which there are client libraries should be installed (if the database needs
any client libaries), otherwise Lazarus could fail to start because of missing files. Then Lazarus must be
reinstalled as uninstalling the component is not possible.
Contents
1 Supported databases
2 The bindings to the database clients
3 Datasets
o
3.1 Using datasets from code
o
3.2 Using the visual (data-aware) controls
o
3.3 Dataset State
o
3.4 Dataset UpdateStatus
o
3.5 Post and Cancel
o
3.6 Inserting a new record
o
3.7 How to quickly jump to 1 particular record in the table
3.7.1 After selecting all records of the table
3.7.2 Selecting only the desired record

o
3.8 Filtering
o
3.9 Locate/lookup
o
3.10 Using TSQLQuery
o
3.11 Exporting
4 Data Controls
o
4.1 Datasource Control
o
4.2 Single Field Controls
o
4.3 DBGrid control
o
4.4 Navigator Control
5 Running FPC database tests
6 Database packages contained in Lazarus
o
6.1 sqldblaz.lpk
o
6.2 dbflaz.lpk
o
6.3 sqlitelaz.lpk
o
6.4 sdflaz.lpk
o
6.5 lazreport.lpk
o
6.6 lazdbexport.lpk
7 External packages / libraries
o
7.1 Zeos DataBase Objects
o
7.2 Pascal Data Objects
o
7.3 TPSQL
o
7.4 FIBL
o
7.5 IBX
o
7.6 FBLib Firebird Library
o
7.7 Unified Interbase
o
7.8 TechInsite Object Persistence Framework (tiOPF)
o
7.9 Advantage TDataSet Descendant
o
7.10 ZMSQL, sql-enhanced in-memory database
Supported databases
Database
Package
name
Need
client
lib?
Need
server?
Supported
versions
Supported platforms
Advantage
TAdsDataSet
Yes
No
10.1 and greater
i386: Linux, Win32
DBase
DBFLaz
No
No
III+, IV, VII
All
In memory
memds
No
No
All
In memory
bufdataset
No
No
All
1 - 2.5
i386: Linux, Win32
Firebird
SQLdb
Yes
Depends
Pagina 1 din 69
Database
Package
name
Need
client
lib?
Need
server?
Supported
versions
(Visual) FoxPro
DBFLaz
No
No
2.0, 2.5, 3.0 (not

All
completely)
Interbase
SQLdb
Yes
Yes
4-6
i386: Linux, Win32
SQLdb
Yes
Yes
6-
FPC 2.6.2+. Linux, OSX,

Win32, probably *BSD,
probably Solaris2
MySQL
SQLdb
Yes
Yes
3.0 - 5.5
i386: Linux, Win32
ODBC
SQLdb
Yes
Depends
3.x 3
i386: Linux, Win32
Oracle
SQLdb
Yes
Yes
Paradox
TParadoxDataSet No
No
up to Table Level
All
7 (and up ??)
PostgreSQL
SQLdb
Yes
Yes
6.6 - 8
i386: Linux, Win32
Sybase
Server
(ASE)
Adaptive
Enterprise SQLdb
Yes
Yes
Any
Linux,
OSX,
Win32,
probably *BSD, probably
Solaris2)
SQLite
SQLdb
Yes
No
sqlite3
All
SQLite
SQLite(3)Laz
Yes
No
sqlite2,sqlite3
All
Microsoft
Server
SQL
Supported platforms
Text files
sdf
No
No
All
Note (1): You can use an embedded version of Firebird on Windows and Linux (possibly on Mac OS X too),
or you can connect to a Firebird server running on Windows/Unix/OSX/FreeBSD/other Firebird supported
platforms
Note (2): These connectors use the FreeTDS library as a driver. The FreeTDS documentation indicates it
should build on at least these platforms. Windows versions for x86 and x64 can be downloaded from
e.g. [1] and [2]
Note (3): This version number refers to the ODBC standard, not to the version number of a driver or driver
manager. There are ODBC 3.x drivers for most DBMSs.
The bindings to the database clients

If you want to use one of the databases that need client libraries, those libraries have to be installed. Not
only on the computer you're programming on, but also on the computers where the application must run.
Note that some databases (in particular MySQL) only work if the bindings which are compiled in the
application are from the same version as those of the installed libraries. You can find out how to install those
libraries (.so files on *nix systems, and .dlls on windows) on the website of the database developers. The
binding units can be found in the packages/base directory in the fpc-sources. They basically consist of the
client API calls like mysql_connect_database, which are completely different for each database. It is
possible to write database applications using these units, but it is usually far more work and bug-sensitive
than using the DB-unit Lazarus components.
Most of these bindings packages are hard-linked to the client libraries. This means that if the application is
compiled with one of these units in it, the whole application can not be linked if the client libraries are not
available on the workstation. This means that your program executable will not be generated if you do not
have installed - for example - a MySQL client on your computer, and you are using the mysql4.pp unit in
your program. If you succeed in compiling the program on a computer which has the MySQL client libraries
installed, it still won't start on any other machine without the appropriate MySQL client libraries. In other
words: for these databases, you need to install client libraries on your development machine, and you need
to install these client libraries with your application.
To avoid such problems some of the packages are also able to link dynamically to the libraries. Before any
calls to those libraries can be made, the unit has to be 'initialized'. This initialization fails if the database
client isn't installed on the computer. If the program is ready using the client library, the unit has to be
'released'.
Datasets
Database use in Lazarus (or Free Pascal) is fundamentally based on the TDataset class. This represents a
table or query to your application. However, like many other such fundamental classes, you don't use the
TDataset class itself, you use a descendant of it. There are many of these. They provide access to different
Pagina 2 din 69
kinds of databases, such as local dbase or text files, or back-end databases such as PostgreSQL, Firebird,
MySQL and so forth. Some dataset descendants link directly to database tables, while others use additional
components or libraries to perform the link.
Dataset descendants, being non-visual components are (usually) part of the Free Component Library (FCL)
rather than the Lazarus Component Library (LCL).
Datasets can be used both programmatically and with visual controls. A typical Lazarus database
application will often use both methods. In either case, the first step is to create the TDataset descendant,
initialise it to connect to the table or query you want, and open it. This can be done either in code at run time
or by putting a component on your form and setting it's properties at design time. The details of this vary
considerably with different TDataset descendants, so see the various guides under Databases for what has
to be done for your database.
When the dataset is opened, a set of field components are created, one for each field or column of the table
or query you opened. Each field component is a descendant of TField, appropriate to the particular data
type of the field, eg, TStringField.
Using datasets from code

Programmatic access will be explained in more detail in Using Dataset and Field components, but as a very
simple overview:
Use the TDataset descendant to open the table or query, filter the rows you want to see, and to
move from row to row.
Use the TField descendants to:
Access general information about fields
Access the specific data values for the current row. (use the As... properties, such as
AsString, AsInteger, etc.)
Access the fields of a TDataset descendant by using either:
The fields property, eg Fields[0] is the first field,
The FieldByName method, eg FieldByName('AGE') returns the field associated with the
database field called 'AGE'
See Database_field_type for a list of field types.
Using the visual (data-aware) controls

To use databases in a simple, "RAD" style Lazarus application, you usually configure the dataset
descendant at design time and the use the data-aware controls. To do this:
Add the dataset descendant for the database of your choice, together with any supporting
components, to your form, and open it (Set the ' Active' property to true )
Add a TDataSource component (from the Data Access tab) to the form, and "link" it to the dataset
(set the DataSet property)
Add data-aware controls from the Data Controls tab to the form, and link each one to the
DataSource (not dataset) component
Most controls link to a single field, so you also need to set the Field for each tab.
See #Data Controls below for more details on the controls
Dataset State
Datasets can be in a number of states. While there are quite a few (look up TDataSetState in the source),
the main ones to be aware of initally are
State
Function
dsInactive The dataset is closed
dsBrowse The user can browse through the dataset, looking at values
dsEdit
The user can edit values on the current row.Values are not saved until a post is performed.
A new row has been added, and the user can set the values.The record is not saved until a post is
performed
The other states are fairly transitory, and are usually handled "automatically". They are used internally and
in more complicated code. If your database only views data, and you open the dataset at design time, you
can largely ignore the state, as it will mostly be dsBrowse. However, most applications will want to change
the data at some stage. If you are using data-aware controls, they will handle a lot of this automatically. If
you change the text in a TDBEdit control, for example, it will put the dataset into dsEdit state - unless you
are already in dsEdit or dsInsert. If you "scroll" to a different record while the current state is dsEdit or
dsInsert, that record will be "posted" and the dataset revert to dsBrowse. However, if you are accessing the
dataset from code, you will often have to change the state in code as well. The TDBNavigator control (see
below) allows the user to change the state explicitly.
dsInsert
Dataset UpdateStatus
Pagina 3 din 69
UpdateStatus determines the current state of the record buffer, if updates have not yet been applied to the
database.
Example how to detect if ApplyUpdates will Insert, Update or Delete data:
procedure QueryAfterPost(DataSet: TDataSet);
begin
case DataSet.UpdateStatus of
usUnmodified : ShowMessage('Unmodified');
usModified
: ShowMessage('Modified');
usInserted
: ShowMessage('Inserted');
usDeleted
: ShowMessage('Deleted');
end;
end;
Value Explanation
usUnmodified: Record is unmodified
usModified: Record exists in the database but is locally modified
usInserted: Record does not yet exist in the database, but is locally inserted
usDeleted: Record exists in the database, but is locally deleted
Post and Cancel

If you have edited or inserted a record, the new values are held in a buffer.
Calling the dataset cancel method removes the new record (insert) or reverts the values to their
previous values (edit).
Calling the dataset post method saves the values (edit) or record (insert). In some dataset
descendants, they will be written to the database immediately, while in others they will be stored in a list
of updates until a further call is made to save all changes to the database. Finally, even when they are
written to the database, you may still have to call a "commit" method to make the database write them
permanently. All of this also varies considerably with the dataset descendant, so look up the details for
the one you are using.
Inserting a new record

To insert a new record into a TDataset descendent, one should use the method Insert. After that one can
set the field values and then finally call Post to commit the new record, as the example below shows.
The example also shows how to insert BLOB data from a file - you can also use LoadFromStream to load
the data from a stream.
MyDataset.Insert;
MyDataset.Fields[0].AsInteger := 4; //an integer field
MyDataset.Fields[1].AsString := 'First Name'; //a string field
TBlobField(MyDataset.Fields[2]).LoadFromFile('SomeBlobfile.bin');
//blob
field
MyDataset.Post;
How to quickly jump to 1 particular record in the table

After selecting all records of the table
If you use SELECT * FROM to select all records of the table and then desires to quickly jump between
them, you will have to build an index and search on it. It is more efficient to select only the record that you
want.
Selecting only the desired record

One fast solution to jump to a particular record is to select only it, for example doing:
var
MyDataset: TSQLQuery;
begin
//...
MyDataset.FieldDefs.Add('SessionId', ftLargeint);
MyDataset.FieldDefs.Add('GameEvent', ftLargeint);
MyDataset.FieldDefs.Add('TableId', ftInteger);
MyDataset.FieldDefs.Add('LoggedIn', ftBoolean);
MyDataset.FieldDefs.Add('PlayerId', ftInteger);
MyDataset.Active := False;
{ Non-parameterized format; may run into issues with text containing ' and
dates
SQLText := Format('select * from "GameSession" WHERE "SessionId"=%d',
[ASessionId]);
}
// Solution: parameterized query:
Pagina 4 din 69
// Actually, if this is done in a loop, you only need to set the SQL.Text
once,
// and only change the parameter value
MyDataset.SQL.Text
:=
'select
*
from
"GameSession"
WHERE
"SessionID"=:SessionID');
MyDataSet.ParamByName('SessionID').AsLargeInt := ASessionID;
try
MyDataset.Active := True;
except
//...
end;
You can then read information using something like this:
lPlayerId := MyDataset.Fields[4].AsInteger;
Filtering
You can filter your dataset to restrict the records to a subset you want (e.g. all surnames starting with
Smith).
Using .Filter:
TDbf, TBufDataset and descendants (including TSQLQuery) use the TDBF filtering parser;
see Lazarus Tdbf Tutorial#Expressions for details on using this.
TMemDataset does not support .Filter
Using a callback/event procedure with OnFilter where you can program your own filter function
Locate/lookup
Although more often used in non-SQL datasets (e.g. TParadoxDataSet, TDbf) you can jump between
records using locate/lookup.
Using TSQLQuery
For more information about TSQLQuery see Working With TSQLQuery
Exporting
FPC/Lazarus contains functionality to let you export datasets to various formats; see e.g.
fpXMLXSDExport
fpdbfexport
the other components on the Data Export tab

Of
course,
you
could
also
do
it
manually
e.g. FPSpreadsheet#Converting_a_database_to_a_spreadsheet for export to Excel format
fpspreadsheet)
(see
using
Data Controls
To use any of these controls, add the control to a form and set at least the datasource property. Other key
properties will be noted.
Datasource Control
This control keeps track of which record the linked controls currently are on. The datasource contorl must
be linked to a dataset (e.g. a TSQLQuery).
Single Field Controls

These controls all attach to a single field. As well as datasource, set the field name. Controls include:
DBText control Displays a text field (readonly, no border)
DBEdit control Displays / edits a text field as an edit box
DBMemo control Displays / edits a text field in a multi-line edit box
DBImage control Displays a picture stored in a database as a BLOB. Note: by default, Lazarus
stores a header with the image type before the image data in the database BLOB field. This is different
from Delphi. However, you can make TDBImage Delphi compatible: see Lazarus For Delphi
Users#TDBImage
DBListBox control and DBComboBox Control Allow the user to insert values into a database field
from the list of values in the Items property of the controls
DBLookupListBox control and DBLookupComboBox control, see also dblookupcombobox Allow the
user to insert values into a database field by displaying the contents of a lookup field in another table.
Though these controls store their results in a single field, you need another field for the lookup
values. Note: at lest for DBLookupComboBox, there is a bug with FPC 2.6.0 that requires the listfield to
be present in the datasource as well, you can bypass it by declaring a calculated field with the same
name as the listfield in the datasource's dataset that does nothing.
DBCheckBox control Displays / edits a boolean field by checking/clearing a check box
DBRadioGroup control Displays the items as in a normal radio group, reading/setting the field value
from a matching values list
Pagina 5 din 69
DBCalendar control Displays / edits a date field using a calendar panel

DBGroupBox control
DBGrid control
This control can show a number of fields in a row/column layout - in fact by default it shows them all.
However, you can put entries into the columns collection to restrict it to specific fields and to set the widths
and titles of each column.
Apart from the mentioned documentation, some more detail can be found here: Grids Reference
Page#TCustomDBGrid
Navigator Control
This control gives the user some direct control over the dataset. It allows the user to:
Move to the next or previous record, or to the start or end of the records
Add a new record (equivalent to a dataset.insert method call)
Put the dataset into edit mode
Delete a record
Post or Cancel current changes
Refresh the data (useful in multiuser database applications)

Key Properties:
VisibleButtons: Lets you control what the user can do. For example, if deletes are not allowed, hide
the delete button. If you have a DBGrid attached to the same dataset, you may decide you do not need
the next and prior buttons.
Width: If you do not show all buttons, you may want to set the width to
(height*number_of_visible_buttons)
Running FPC database tests

Free Pascal database components include a fpcunit-based test framework, dbtestframework, that can be
used to verify functionality. See the directory source\packages\fcl-db\tests\ in your FPC source tree.
Included is a test framework that can be run on various database components, as well as some other tests
(e.g. test of database export).
To run the test framework on a certain database:
1. Save source\packages\fcl-db\tests\database.ini.txt as source\packages\fcl-db\tests\database.ini
2. Modify source\packages\fcl-db\tests\database.ini to choose which database type you will use.
Example for Interbase/Firebird:
[Database]
type=interbase
3. In the same file, customize settings for your database. E.g. if you chose interbase before:
[interbase]
connector=sql
connectorparams=interbase
; Database name/path (note: database needs to exist already)
; You can use aliases (see aliases.conf in your Firebird documentation)
name=testdb
user=sysdba
password=masterkey
; your hostname may very well differ:
; Leave blank if you want to use an embedded Firebird database
hostname=192.168.0.42
4. Compile and run source\packages\fcl-db\tests\dbtestframework.pas (You can also use Lazarus to
compile and run the GUI version, dbtestframework_gui) If you are using an embedded database on
Windows (e.g. Firebird embedded or sqlite), copy the required DLL files to the directory first. The output will
be in XML format (or displayed on your screen if you use dbtestframework_gui).
Please see source\packages\fcl-db\tests\README.txt for more details.
Database packages contained in Lazarus

sqldblaz.lpk
This package provides access to different databases. These include:
Interbase/Firebird
Microsoft SQL Server (except on Lazarus/FPC x64 for Windows)
MySQL
Pagina 6 din 69
Oracle (except on Lazarus/FPC x64 for Windows)

PostgreSQL (except on Lazarus/FPC x64 for Windows)
SQLite (with support for the Spatialite extension)
Sybase ASE (Adaptive Server Enterprise - not to be confused with Sybase ASA) (except on
Lazarus/FPC x64 for Windows)
any database that has an ODBC driver.

The components (TSQLQuery, TSQLTransaction, TIBConnection, TODBCConnection, TOracleConnection,
TMSSQLConnection,
TMySQL40Connection,
TMySQL41Connection,
TMySQL50Connection,
TPQConnection, TSybaseConnection) are on the 'SQLdb' tab in the component palette.
SQLdb Package
dbflaz.lpk
This package provides access to dBase and FoxPro databases. You can get more information in
the Lazarus Tdbf Tutorial. The TDbf component is on the Data Access tab in the component palette.
sqlitelaz.lpk
This package provides access to SQLite databases. You can get more information in the Lazarus Database
Overview.
sdflaz.lpk
The component TSdfDataSet can be found on the Data Access tab in the component palette.
lazreport.lpk
The homepage of the report generator is http://lazreport.sourceforge.net/. More informationen (et al. an
additional link) can be found here. LazReport depends on the Printer4Lazarus package. With revision 11950
LazReport was included in the Lazarus SVN repository.
lazdbexport.lpk
See lazdbexport.
External packages / libraries

Zeos DataBase Objects
These components provide access to different databases. You can find more information here. This wiki
also contains a Zeos tutorial.
Pascal Data Objects

There is now an alternative.
Support:
MySQL 4.1 and 5.0
sqlite-2 and sqlite-3
pgsql-8.1
interbase-5, interbase-6, firebird-1.0, firebird-1.5, firebird-1.5E, firebird-2.0, firebird-2.0E
mssql (Microsoft library) and sybase (FreeTDS library)
oracle
like prepared statements, binding, and stored procedures are supported by database API called Pascal
Data Objects, which is inspired by PHP Data Objects. All the code and documentation necessary to use this
new API is available on Sourceforge:
http://pdo.sourceforge.net
TPSQL
These components provide access via TCP/IP to PostgreSQL databases. You can find more information
on this page.
FIBL
These components provide access
is http://sourceforge.net/projects/fibl.
to
Interbase
and
Firebird
databases.
The
homepage
IBX
IBX For Lazarus are components to access Firebird databases: see IBX
FBLib Firebird Library

FBLib is an open Source Library No Data Aware for direct access to Firebird Relational Database from
Borland Delphi/Kylix, Free Pascal and Lazarus.
Current Features include:
Direct Access to Firebird 1.0.x, 1.5.x and 2.x Classic or SuperServer
Multiplatform [Win32,Gnu/Linux,FreeBSD)
Automatic select client library 'fbclient' or 'gds32'
Query with params
Support SQL Dialect 1/3
Pagina 7 din 69

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 FBLib's website.
Unified Interbase
UIB provides access to Interbase, Firebird and YAFFIL databases. The homepage is www.progdigy.com. A
svn repository is available under https://uib.svn.sourceforge.net/svnroot/uib .
TechInsite Object Persistence Framework (tiOPF)

More information about tiOPF can be found on this page.
Advantage TDataSet Descendant

The Advantage TDataSet Descedant provides a means of connecting to (and opening tables with) the
Advantage Database Server. Advantage is a flexible, administration-free embedded database that provides
Client/Server as well as Peer-to-peer access to Clipper, FoxPro and Visual FoxPro 9 DBF file formats, as
well as a proprietary file format that provides a migration path allowing the use of newer features.
Key Features:
Royalty-free peer-to-peer database access with migration path to Client/Server
Multi-Platform (Clients supported on Windows and Linux, Server supported on Windows, Linux,
and NetWare)
Supports Both navigational and relational SQL database access
Full-text search engine
Table, Index, Memo, and communication encryption
Compatible with native TDataset components
Online Backup
Server supports Replication

For more information, see the Advantage Database Server website.
ZMSQL, sql-enhanced in-memory database

For more information, see the ZMSQL wiki page
ZMSQL is an open source, TBufDataset descendant SQL enhanced in-memory database for Free Pascal
(FPC), operating with semicolon-separated values flat text tables. Completely written in Pascal, it has no
dependencies on external libraries. It uses JanSQL engine for SQL implementation.
It offers:
Loading from and saving to flat text tables
Use of SQL to query the data
Copy data and schema from other datasets
Option to predefine fielddefs or create it on-the fly
Master/detail filtering
Referential integrity
Parameterized queries
The download contains the source code, some demo applications illustrating the features of the component
as well as a readme.
Pagina 8 din 69
Lazarus Database Overview

Contents
1 Overview
2 Lazarus and Interbase / Firebird
3 Lazarus and MySQL
4 Lazarus and MSSQL/Sybase
5 Lazarus and ODBC
5.1 Microsoft Access
6 Lazarus and Oracle
7 Lazarus and PostgreSQL
8 Lazarus and SQLite
9 Lazarus and Firebird/Interbase
10 Lazarus and dBase
11 Lazarus and Paradox
12 TSdfDataset and TFixedDataset
13 Lazarus and Advantage Database Server
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:
SQLdb Tutorial0
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
1.
2.
3.
4.
Please see mysql for details on various access methods, which include:
Built-in SQLdb support
PDO
Zeos
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 Mac OS X. 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
Pagina 9 din 69
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 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.
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, Mac OS X, 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.
Pagina 10 din 69
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. See this 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.
See CSV for example code.
Lazarus and Advantage Database Server

Overview
This brief tutorial is intended to get you started using the Advantage TDataSet descendent to access tables
hosted by the Advantage Database Server.
Installing the Advantage TDataSet

Windows
If installing the Advantage TDataSet on Windows, you will need to download and install the Advantage
Delphi Components (version 10.1 or greater). The install media can be obtained from the Advantage web
site here.
Linux
If installing the Advantage TDataSet on Linux, you will need to complete a two-part installation. First,
download and install the Advantage Client Engine for Linux (version 10.1). Second, download and extract
the Linux TDataSet source tarball. Both downloads are available at the Advantage Developer Zone
at http://devzone.advantagedatabase.com. (The Advantage Client Engine download is located in the
"Product Download" section of the site, and the Linux TDataSet source is available in the Delphi
Applications section of the CodeCentral page of the Advantage Developer Zone.)
Installing the Advantage Package

Once the TDataSet Descendant is installed, you'll want to move on to installing the package into Lazarus.
1. From Lazarus, click on "Package", then "Open Package File (.lpk)..." and browse to the adsl.lpk file
in the TDataSet installation directory (or in the directory you extracted the TDataSet source).
2. In the package window, click the "Compile" button.
3. Upon successful compilation, click the "Install" button, and select "Yes" to rebuild Lazarus.
4. The Lazarus IDE should successfully compile and re-start with the Advantage components
installed.
A simple Advantage project

Start a new project to start working with Advantage data.
1. Drop a TAdsConnection object from the Advantage tab of the palette onto your form. (The
TAdsConnection object is the left-most object on the Advantage tab.)
Pagina 11 din 69
2. In the Object Inspector, click the drop-down for the AliasName property, and select
"ADTDemoData".
Alternately, you can select the ConnectPath property and enter (or browse to) the path to
the Help\ADS_Data directory beneath your TDataSet (or acesdk on Linux) install directory.
3. Expand the AdsServerTypes property, and change the "stADS_LOCAL" property to True.
If you have an Advantage Database server configured and running, you may set
"stADS_REMOTE" to true instead.
4. Drop a TAdsTable object on the form. (The TAdsTable object is immediately to the right of the
TAdsConnection object on the tab, and it looks like a red table.)
5. Set the AdsConnection Property for AdsTable1 to AdsConnection1 (the connection object you just
dropped in step 1.)
6. Scroll down to the "TableName" property, and use the drop-down box to select the biolife.adt table.
7. Drop a TDataSource component on the form, and set its DataSet property to "AdsTable1".
8. Drop a TDBGrid component on the form, and set its "DataSource" property to "Datasource1".
9. At this point, you should be able to select the AdsTable1 object and set its "Active" property to
"True".
10. Finally, run the project. (Note that you may need to save the project before running it.)
Since the Advantage components descend from the TDataSet, you can use these components with any
data-aware component that supports the TDataSet. This extremely simple example should demonstrate
how simple it is to get started with Advantage.
Pagina 12 din 69
SqlDBHowto
This text is setup as a 'how-to'. I want to answer a number of questions one by one, and explain how you
can use the various classes. All those questions are put one after the other and form a sort of tutorial.
I will try to word it in such a way that the text can be used for Lazarus as well as FreePascal. However, the
examples are for FreePascal (i.e. they are console applications.)
Contents
1 Where can I find official documentation?
2 How to connect to a database server?
3 How to execute direct queries/make a table?
4 How to read data from a table?
o
4.1 Example: reading data from a table
o
4.2 Why does TSQLQuery.RecordCount always return 10?
o
4.3 Lazarus
5 How to change data in a table?
6 How does SqlDB send the changes to the database server?
7 How to execute a query using TSQLQuery?
8 How to use parameters in a query?
o
8.1 Select query
o
8.2 Example
9 Troubleshooting: TSQLConnection logging
o
9.1 FPC (or: the manual way)
o
9.2 Lazarus (or: the quick way)
Where can I find official documentation?

Please see the official documentation at SQLDB documentation.
How to connect to a database server?

SqlDB doesn't connect to a database server directly but uses a client that corresponds to the used
database server. SqlDB sends the commands to the client library; the client library connects to the database
and and transfers the commands. This means that a client library must be installed on the computer to
make a connection to a database. Under Windows a client is usually a .dll, under Linux an .so and under
OS/X a .dylib.
When the client library is installed properly you can connect to a database server using a TSQLConnection
component. Various TSQLConnection components are available for different database servers
(see SQLdb_Package):
Firebird/Interbase: TIBConnection
MS SQL Server: TMSSQLConnection (available since FPC 2.6.1)
MySQL v4.0: TMySQL40Connection
MySQL v5.1: TMySQL51Connection (available since FPC version 2.5.1
MySQL v5.5: TMySQL55Connection (available since Lazarus 1.0.8/FPC version 2.6.2
MySQL v5.6: TMySQL56Connection (available in Lazarus 1.2.4/FPC version 2.6.4
ODBC: TODBCConnection (see ODBCConn#TODBCConnection)
Oracle: TOracleConnection (see Oracle)
PostgreSQL: TPQConnection (see postgresql#SQLDB)
Sqlite3: TSQLite3Connection (available since FPC version 2.2.2, see SQLite#Built-in_SQLDB)
Sybase ASE: TSybaseConnection (available since FPC 2.6.1, see Lazarus and MSSQL/Sybase)
Note for MySQL - There are many differences between the client versions to the extent that the clients and
connections cannot be interchanged. If a MySQL client library version 4.1 is installed, you have to use a
TMySQL41Connection. This is not related to the MySQL server; using the MySQL 4.1 client library you can
probably connect to a MySQL 5.0 server (see MySQL documentation regarding what combinations are
supported).
Although details differ for the various databases, in general you need to set four properties to connect to a
database server:
the server name or IP address
the name of the database
the username
the password
Pagina 13 din 69
When these properties are set, you can create a connection with the 'open' method. If the connection fails,
a EDatabaseError exception is thrown. Use the property 'connected' to test if a connection has been made
with the database server. Use the 'close' method to end the connection with the server.
Program ConnectDB;
function CreateConnection: TIBConnection;
begin
result := TIBConnection.Create(nil);
result.Hostname := 'localhost';
result.DatabaseName := '/opt/firebird/examples/employee.fdb';
result.UserName := 'sysdba';
result.Password := 'masterkey';
end;
var
AConnection : TIBConnection;
begin
AConnection := CreateConnection;
AConnection.Open;
if Aconnection.Connected then
writeln('Successful connect!')
else
writeln('This is not possible, because if the connection failed, ' +
'an exception should be raised, so this code would not ' +
'be executed');
AConnection.Close;
AConnection.Free;
end.
If an exception is thrown, read the error message carefully. It may be that the database server is not
running, the user name or password are incorrect or the database name or IP address are typed incorrectly.
If the error message states that the client library cannot be found, then check if the client is installed
correctly. Often the error message states literally the name of the file looked for.
How to execute direct queries/make a table?

SqlDB - the name says it all - only works with database server that make use of SQL. SQL stands for
'Structured Query Language' SQL is a language developed to allow working with relational databases.
Virtually every database system has its own dialect, but a large number of SQL statements are the same for
all database systems.
In FPC, there is a difference between:
SQL statements that return information (a dataset). For this, you have to use the TSQLQuery
component; see #How to read data from a table?.
statements that do not return information but do something else, e.g. update data. For this, you
may also use the 'ExecuteDirect' method of a TSQLConnection. (You can also use this if you get a
dataset back but are not interested in the results, e.g. in a selectable stored procedure).
Most database system execute SQL statements within a transaction. If you want changes made within a
transaction available in other transactions, or have those changes available even after closing the
transaction(!), then you have to 'commit' the transaction.
To support transactions Sqldb contains the TSQLTransaction component. A SQL statement that is executed
by Sqldb must always be executed within a transaction, even if the database system does not support
transactions. Also, there are database systems that do support transaction for which TSQLConnection does
not (yet) support transaction. Even then, you must use the TSQLTransaction component.
To use TSQLConnection.ExecuteDirect to execute a SQL statement you must specify which
'Transaction' must be used. In turn, to use TSQLTransaction you must specify which TSQLConnection
component must be used.
The following example creates a table 'TBLNAMES' with fields 'NAME' and 'ID' and inserts two records. The
used SQL statements are not explained. For more information about the SQL statements, their use and
syntax, please refer to the database system documentation. The procedure 'CreateConnection' is defined in
the code example in #How to connect to a database server? above.
program CreateTable;
function CreateTransaction(pDB: TIBConnection): TSQLTransaction;
begin
result := TSQLTransaction.Create(pDB);
Pagina 14 din 69
result.Database := pDB;
end;
var
AConnection : TSQLConnection;
ATransaction : TSQLTransaction;
begin
ATransaction := CreateTransaction(AConnection);
AConnection.Transaction := ATransaction;
AConnection.Open;
ATransaction.StartTransaction;
AConnection.ExecuteDirect('create
table
TBLNAMES
varchar(40));');
(ID
integer,
NAME
// Some database-server types need a commit before you can use a newly
created table. (Firebird)
// With .Commit you also close the transaction
ATransaction.Commit;
ATransaction.StartTransaction;
AConnection.ExecuteDirect('insert
(1,'Name1');');
AConnection.ExecuteDirect('insert
(2,'Name2');');
AConnection.Close;
AConnection.Free;
ATransaction.Free;
end.
into
TBLNAMES
(ID,NAME)
values
into
TBLNAMES
(ID,NAME)
values
How to read data from a table?

Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to
a TSQLConnection component and a TSQLTransaction component to do its work. Setting the
TSQLConnection and TSQLTransaction is discussed in #How to connect to a database server? and #How
to execute direct queries/make a table?.
When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs to
be further configured to work. TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL'
property contains a SQL statement that must be executed. If all data from a table tablename must be
read, then set the 'SQL' property to:
'SELECT * FROM tablename;'
.
Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be
accessed through TSQLQuery until the query is closed using 'close'.
TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the
table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the
current record. 'Bof' returns 'True' if the first record is reached, and 'Eof' returns 'True' if the last record is
reached. To read the value of a field in the current record, first find the right 'TField' object and then use
'AsString', 'AsInteger', etc.
Example: reading data from a table

Below is an example that displays all values of the table as it was made in #How to execute direct
queries/make a table? above.
Program ShowData;
function
CreateQuery(pConnection:
TSQLTransaction): TSQLQuery;
begin
result := TSQLQuery.Create(nil);
result.Database := pConnection;
result.Transaction := pTransaction
end;
TIBConnection;
Pagina 15 din 69
pTransaction:
var
Query
: TSQLQuery;
begin
Query := CreateQuery(AConnection, ATransaction);
Query.SQL.Text := 'select * from tblNames';
AConnection.Open;
Query.Open;
while not Query.Eof do
begin
Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
Query.FieldByName('Name').AsString);
Query.Next;
end;
Query.Close;
AConnection.Close;
Query.Free;
ATransaction.Free;
AConnection.Free;
end.
(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code
intends to show the database code and thus the finishing touches are left out.) Please note that
'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the
SQL statement is executed and if no transaction is available then a transaction is automatically started. The
programmer does not need to start the transaction explicitly. The same applies for the connection
maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not
really required. If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. Possible
changes to data contained in the transaction will be lost.
Why does TSQLQuery.RecordCount always return 10?

To count the records in a dataset, use '.RecordCount'. However, notice that '.RecordCount' shows the
number of records that is already loaded from the server. For performance reasons, SqlDB does not read all
records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed
will the next set of 10 records be loaded, etc. Using '.Last', all records will be loaded.
When you want to know the real number of records on the server you can first call '.Last' and then call
'.RecordCount'.
An alternative is available. The number of records returned by the server is set by the '.PacketRecords'
property. The default value is 10; if you make it -1 then all records will be loaded at once.
In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.
If you need the exact number of records, it often is a better idea to directly query the number of records in a
query using another SQL query, but you would have to do that in the same transaction, as other
transactions may have changed the number of records in the meanwhile.
Lazarus
Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and
Writeln statements as used above, you can use the components to show the data in a table. Place the right
TSQLConnection, TSQLTransaction and TSQLQuery components on a form, then connect them and set
them properly. In addition you will need a TDatasource; set the 'TDatasource.Dataset' property to the
TSQLQuery component you used. (Note do not set the 'TSQLQuery.Datasource' property to the
TDatasource compnent you used. The 'TSQLQuery.Datasource' property is used only in master-detail
tables - see MasterDetail) Subsequently you may put a TDBGrid onto the form and set the 'Datasource'
property of the grid to the TDatasource component you added before.
To see if it all works, set the 'Connected' property of the TSQLConnection to 'True' in the Lazarus IDE. The
IDE will try to connect to the database server immediately. If this works you can set the 'TSQLQuery.Active'
property to 'True'. If everything is right, you will see - within the IDE - all data from the table immediately on
the screen.
How to change data in a table?

To change the data in a record, the TDataset (from which TSQLQuery is derived) must be set to edit mode.
To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the
current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new
Pagina 16 din 69
record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use
'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to
discard all changes you made since the last '.Post' call and leave the edit mode.
Query.Edit;
Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;
The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of
buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible
immediately, but they are not sent to the database server. What does happen is that the changes are
maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log
are sent to the database. Only then will database server know of the changes. The changes are sent to the
server within a transaction of TSQLTransaction. Make sure to properly set the transaction before
'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the
database server.
The below is an example of changing the data in a table, sending the changes to the server and comitting
the transaction.
Program EditData;
var
Query : TSQLQuery;
begin
AConnection.Transaction := ATransaction;
Query.SQL.Text := 'select * from tblNames';
Query.Open;
Query.Edit;
Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;
Query.UpdateMode := upWhereAll;
Query.ApplyUpdates;
Query.Free;
ATransaction.Free;
AConnection.Free;
end.
For a discussion of 'UpdateMode' continue reading.
How does SqlDB send the changes to the database server?

In the code example in #How to change data in a table?, you will find the line
Query.UpdateMode := upWhereAll;
without explanation of what it does. The best way to find out what that line does is to leave it out. If you
leave out the statement and the followed this howto precisely, then you will receive the following error
message:
No update query specified and failed

inclusion in where statement found)
to
generate
one.
(No
fields
for
To understand what went wrong, you must understand how changes are sent to the database server. The
only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three
different ways of manupulating a record. To create a new record, change or delete a record insert, update
and delete statements are executed respectively. An update statement may be as follows:
UPDATE TBLNAMES SET NAME='Edited name' WHERE ID=1;
To send a change to the database server, Sqldb must assemble an update query. To assemble the query,
three things are needed:
The name of the table
The table name is retrieved from parsing the select query, although this doesn't always work.
UPDATE or INSERT clause
These contain the fields that must be changed.
Pagina 17 din 69
WHERE clause
This contains the fields that determine which records should be changed.
Every field (each TField in Fields) has a ProviderFlags property. Only fields
with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By
default all fields have pfInUpdate set in their ProviderFlags property.
Which fields are used in the WHERE clause depends on the UpdateMode property of the query
and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are
always used in the WHERE clause. A field will have the pfInKey flag set automatically if the field
is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.
The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only
fields with pfInkey in their ProviderFlags property are used in the WHERE clause. If none of the
fields have their pfInKey flag set, then no fields are available for the WHERE clause and the
error message from the beginning of this section will be returned. You can solve the issue by:
Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
Setting the pfInkey flag for one or more fields in code.
The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add
all fields with the 'pfInWhere' flag set to the WHERE clause. By default all fields have this flag
set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag
set and that are changed in the current record.
How to execute a query using TSQLQuery?

Next to statements that return a dataset (see #How to read data from a table?) SQL has
statements that do not return data. For example INSERT, UPDATE and DELETE statements
do not return data. These statements can be executed using TSQLConnection.ExecuteDirect,
but TSQLQuery can also be used. If you do not expect return data
use TSQLQuery.ExecSQL instead
of TSQLQuery.Open.
As
mentioned
earlier,
use TSQLQuery.Open to open the dataset returned by the SQL statement.
The following procedure creates a table and inserts two records using TSQLQuery.
procedure CreateTable;
var
Query : TSQLQuery;
begin
Query.SQL.Text
:=
'create
table
TBLNAMES
varchar(40));';
Query.ExecSQL;
(ID
integer,
NAME
Query.SQL.Text
(1,''Name1'');';
Query.ExecSQL;
:=
'insert
into
TBLNAMES
(ID,NAME)
values
Query.SQL.Text
(2,''Name2'');';
Query.ExecSQL;
:=
'insert
into
TBLNAMES
(ID,NAME)
values
Query.Close;
Query.Free;
end;
How to use parameters in a query?

In the code example of #How to execute a query using TSQLQuery? the same query is used
twice, only the values to be inserted differ. A better way to do this is by using parameters in the
query.
The syntax of parameters in queries is different per database system, but the differences are
handled by TSQLQuery. Replace the values in the query with a colon followed by the name of
the parameter you want to use. For example:
Query.SQL.Text
:=
'insert
into
TBLNAMES
(ID,NAME)
values
(:ID,:NAME);';
This query will create two parameters: 'ID' and 'NAME'. To determine the parameters, the
query is parsed at the moment the text of TSQLQuery.SQL is assigned or changed. All
existing parameters will be removed and the new parameters will be added to the
Pagina 18 din 69
'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value

to a field in the dataset:
Query.Params.ParamByName('Name').AsString := 'Name1';
You can't tell from the query what kind of data must be stored in the parameter. The data type
of the parameter is determined at the moment a value is first assigned to the parameter. By
assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can
determine the data type directly by setting the 'DataType' property. If an incorrect datatype is
assigned to the parameter, then problems will occur during opening or executing the query.
See Database field type for more information on data types.
Select query
An example of a select query with parameters would be to change something like this:
Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME =
'''+Edit1.Text+''' ORDER BY NAME ';
to something like this:
Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME
= :NAMEPARAM ORDER BY NAME ';
Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;
Example
The following example creates the same table as the previous example, but now parameters
are used:
procedure CreateTableUsingParameters;
var
Query : TSQLQuery;
begin
Query.SQL.Text
:=
'create
table
TBLNAMES
varchar(40));';
Query.ExecSQL;
(ID
integer,
NAME
Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';

Query.Prepare;
Query.Params.ParamByName('ID').AsInteger := 1;
Query.Params.ParamByName('NAME').AsString := 'Name1';
Query.ExecSQL;
Query.Params.ParamByName('ID').AsInteger := 2;
Query.Params.ParamByName('NAME').AsString := 'Name2';
Query.ExecSQL;
//Query.UnPrepare; // no need to call this; should be called by
Query.Close
Query.Close;
Query.Free;
end;
Notice that this example requires more code than the example without the parameters. Then
what is the use of using parameters?
Speed is one of the reasons. The example with parameters is faster, because the database
server parses the query only once (in the .Prepare statement or at first run).
Another reason to use prepared statements is prevention of SQL-injection (see also Secure
programming.
Finally, in some cases it just simplifies coding.
Troubleshooting: TSQLConnection logging

You can let a TSQLConnection log what it is doing. This can be handy to see what your
Lazarus program sends to the database exactly, to debug the database components
themselves and perhaps to optimize your queries. NB: if you use prepared
statements/parametrized queries (see section above), the parameters are often sent in binary
by the TSQLConnection descendent (e.g. TIBConnection), so you can't just copy/paste the
logged SQL into a database query tool. Regardless, connection logging can give a lot of
insight in what your program is doing.
Pagina 19 din 69
Alternatives are:
1. you can use the debugger to step through the database code if you have built FPC
(and Lazarus) with debugging enabled.
2. if you use ODBC drivers (at least on Windows) you could enable tracelog output in
the ODBC control panel.
3. many databases allow you to monitor all statements sent to it from a certain IP
address/connection.
If you use TSQLConnection logging, two things are required:
1. indicate which event types your TSQLConnection should log
2. point TSQLConnection at a function that receives the events and processes them
(logs them to file, prints them to screen, etc.).
That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:
TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType :
TDBEventType; Const Msg : String) of object;
FPC (or: the manual way)

A code snippet can illustrate this:
uses
...
TSQLConnection,
//or
a
child
object
like
TIBConnection,
TMSSQLConnection
...
var
type
TMyApplication = class(TCustomApplication); //this is our
application that uses the connection
...
private
// This example stores the logged events in this stringlist:
FConnectionLog: TStringList;
...
protected
// This procedure will receive the events that are logged by the
connection:
procedure GetLogEvent(Sender: TSQLConnection; EventType:
TDBEventType; Const Msg : String);
...
procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
EventType: TDBEventType; const Msg: String);
// The procedure is called by TSQLConnection and saves the received
log messages
// in the FConnectionLog stringlist
var
Source: string;
begin
// Nicely right aligned...
case EventType of
detCustom:
Source:='Custom: ';
detPrepare: Source:='Prepare: ';
detExecute: Source:='Execute: ';
detFetch:
Source:='Fetch:
';
detCommit:
Source:='Commit: ';
detRollBack: Source:='Rollback:';
else Source:='Unknown event. Please fix program code.';
end;
FConnectionLog.Add(Source + ' ' + Msg);
end;
...
// We do need to tell our TSQLConnection what to log:
FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare,
detExecute, detFetch, detCommit, detRollBack]
// ... and to which procedure the connection should send the
events:
Pagina 20 din 69
FConnection.OnLog:[email protected];
...
// now we can use the connection and the FConnectionLog stringlist
will fill with log messages.
You can also use TSQLConnection's GlobalDBLogHook instead to log everything from
multiple connections.
Lazarus (or: the quick way)

Finally, the description above is the FPC way of doing things as indicated in the introduction; if
using Lazarus, a quicker way is to assign an event handler to the TSQLConnection's OnLog
event.
Pagina 21 din 69
Working With TSQLQuery
Contents
1 General
2 Official documentation
3 Commonly used controls
4 Updating data
5 Cached Updates
6 Primary key fields
7 Controlling the update
8 Customizing the SQL in TSQLQuery
o
8.1 TSQLQuery.SQL: Basic SQL Customization
o
8.2 TSQLQuery.InsertSQL, TSQLQuery.UpdateSQL and TSQLQuery.DeleteSQL: Basic Use of
Parameters
o
8.3 Parameters in TSQLQuery.SQL
8.3.1 Select query example
8.3.2 Insert query example

o
8.4 Query with Format function
9 Running your own SQL and getting metadata
10 Troubleshooting
o
10.1 Logging
o
10.2 Poor performance
o
10.3 Error messages
o
10.4 Out of memory errors
o
10.5 Dataset is read-only
General
TSQLQuery is an object that can embody a dataset coming from a database (RDBMS that uses SQL, such
as Firebird, MS SQL Server, Oracle...). Using a SELECT SQL statement in the TSQLQuery's SQL property,
you can determine what data is retrieved from the database into the dataset. When the dataset is changed
by the program (or user), the changes can be submitted back to the database.
A TSQLQuery can also be used to directly modify data: if you specify the desired INSERT, UPDATE,
DELETE etc SQL statement in the SQL property and call the ExecSQL method of the TSQLQuery, the
query object will send the SQL to the database without retrieving any results.
Apart from its use in FPC, Lazarus also provides a component: TSQLQuery
Official documentation
See TSQLQuery documentation
A lot of context-sensitive documentation is now available in Lazarus. Unfortunately, TSQLQuery does not
appear in the index of Lazarus 1.0 help. If you place your cursor on TSQLQuery methods and properties, try
pressing F1 to see if that code is documented; e.g. this will work:
var
Q: TSQLQuery
...
Q.Open; //<--- place cursor on Open and press F1
Commonly used controls

The dataset returned by TSQLQuery can conveniently be viewed with an instance of TDBGrid, but it is not
very suitable for editing the data in the individual fields and cells. For this purpose you need to place some
Data-Aware single-field controls such as TDBEdit on your form, and set their DataSource poperty to the
data source being used. The DataField property should be set to a named field (eg 'IDENTITY') or to some
expression that returns a suitable string.
Addition of a TDBNavigator toolbar makes it very easy to navigate through the records, and to select
records for editing. When a record is selected by the toolbar or by moving the mouse through the data grid,
the data for the relevant row and column appear in the TDBEdit box and if the 'Edit' button is clicked, the
contents in the Edit box can be modified. Clicking on the 'Post' button confirms the change, or clicking on
the 'Cancel' button cancels the changes.
In general, the process is as follows:
1. Drop a TSQLQuery on a form/datamodule, and set the Database, Transaction and SQL properties.
2. Drop a TDataSource component, and set its DataSet property to the TSQLQuery instance.
3. Drop a TDBGrid on the form and set its DataSource property to the TDataSource instance.
4. Optionally, drop a TDBNavigator instance on the form, and set its Datasource property to the
TDatasource instance.
Pagina 22 din 69
After this, the Active property can be set to True, and it should be possible to see the data retrieved by the
query. (provided both the TSQLConnection and TSQLTransaction components are active)
Updating data
If you need to be able to DELETE or otherwise modify records, your database table should either
1. contain one PRIMARY KEY column.
2. have a set of fields that uniquely determine the record. Normally, they should be part of a unique
index. This is not required, but will speed up the queries quite a lot
If there is no primary field, or no fields that uniquely determine your record, then a primary key field should
be added. This is done preferably when the table structure is designed, at CREATE time, but can be added
at a later time.
For instance the following example code in your MySQL client will add a unique index to your table:
ALTER TABLE testrig
ADD COLUMN autoid INT
PRIMARY KEY AUTO_INCREMENT;
Adding this field will not hurt, and will allow your applications to update the field.
Cached Updates
The TSQLQuery component caches all updates. That is, the updates are not sent immediately to the
database, but are kept in memory till the ApplyUpdates method is called. At that point, the updates will be
transformed to SQL update statements, and will be applied to the database. If you do not
call ApplyUpdates, the database will not be updated with the local changes.
Primary key fields

When updating records, TSQLQuery needs to know which fields comprise the primary key that can be used
to update the record, and which fields should be updated: based on that information, it constructs an SQL
UPDATE, INSERT or DELETE command.
The construction of the SQL statement is controlled by the UsePrimaryKeyAsKey property and
the ProviderFlags properties.
The Providerflags property is a set of 3 flags:
pfInkey
The field is part of the primary key
pfInWhere
The field should be used in the WHERE clause of SQL statements.
pfInUpdate
Updates or inserts should include this field.
By default, ProviderFlags consists of pfInUpdate only.
If your table has a primary key (as described above) then you only need to set
the UsePrimaryKeyAsKey property to True and everything will be done for you. This will set
the pfInKey flag for the primary key fields.
If the table doesn't have a primary key index, but does have some fields that can be used to
uniquely identify the record, then you can include the pfInKey option in
the ProviderFlags property all the fields that uniquely determine the record.
The UpdateMode property will then determine which fields exactly will be used in the WHERE
clause:
upWhereKeyOnly
When TSQLQuery needs to construct a WHERE clause for the update, it will collect all fields that
have the pfInKey flag in their ProviderFlags property set, and will use the values to construct a
WHERE clause which uniquely determines the record to update -- normally this is only needed for
an UPDATE or DELETE statement.
upWhereChanged
In addition to the fields that have pfInKey in the ProviderFlags property, all fields that
have pfInWhere in their ProviderFlags and that have changed, will also be included in the WHERE
clause.
upWhereAll
In addition to the fields that have pfInKey in the ProviderFlags property, all fields that
have pfInWhere in their ProviderFlags, will also be included in the WHERE clause.
Controlling the update

It is possible to specify which fields should be updated: As mentioned above: Only fields that
have pfInUpdate in their ProviderOptions property will be included in the SQL UPDATE or INSERT
statements. By default, pfInUpdate is always included in the ProviderOptions property.
Pagina 23 din 69
Customizing the SQL in TSQLQuery

Normally TSQLQuery will use generic SQL statements based on properties as discussed above. However,
the generic SQL created by sqldb may not be correct for your situation. TSQLQuery allows you to
customize SQL statements used for the various actions, to work best in your situation with your database.
For this purpose you use the properties SQL, InsertSQL, UpdateSQL and DeleteSQL.
All these properties are of type TStringList, a list of strings, that accepts multiple lines of SQL. All four come
with a property editor in the IDE. In the IDE, select the property and open the editor by clicking the ellipsis
button. In this editor (TSQLQuery metadata tool), you may also look up table information etc.
In code, use for example InsertSQL.Text or InsertSQL.Add() to set or add lines of SQL
statements. One statement may span several lines and ends with a semicolon.
Also, all four properties accept parameters explained further below.
TSQLQuery.SQL: Basic SQL Customization

The SQL property is normally used to fetch the data from the database. The generic SQL for this property
is SELECT * FROM fpdev where fpdev is the table as set in the database.
The dataset returned by the generic SQL statement will be kind of rough. If you show the result in a
TDBGrid, the order of the records may seem random, the order of the columns may not be what you want
and the field names may be technically correct but not user friendly. Using customized SQL you can
improve this. For a table called fpdev with columns id, UserName and InstEmail, you could do something
like this:
SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER
BY id;
The dataset that results from the above query uses the field names as given in the query (ID, User and email), the column order as given in the query and the records are sorted by their id.
TSQLQuery.InsertSQL, TSQLQuery.UpdateSQL and TSQLQuery.DeleteSQL:

Basic Use of Parameters
When you assign a SELECT query to an SQLQuery's SQL property the SQLQuery knows how to get data
from the database. However, when using databound controls such as a DBGrid, SQLQuery will also need to
be able to insert, update and delete rows from the database based on the user's actions.
In order to speed development, SQLQuery can try and deduce the required SQL statements. If the SQL
property exists and the ParseSQL property is true (which it is by default), SQLQuery will try to generate
these
statements
by
parsing
the SQL property.
SQLDB
stores
these
statements
in
the InsertSQL, UpdateSQL and DeleteSQL properties.
However, sometimes the generated statements will not work (e.g. when inserting in tables with autoincrement/autonumber primary keys) or will be very slow. If needed, you can manually assign the
statements.
The statements in the InsertSQL, UpdateSQL and DeleteSQL properties accept parameters that represent
fields in the dataset. The following rules apply:
Parameter names must be exactly the same as the field names used in the dataset. The field
names in the dataset may be different from the column names in the table, depending on the used select
statement (see above).
Just as parameters in other SQLDB queries, parameter names must be written preceded by a
colon.
For use in update/delete statements, precede the dataset field name with OLD_ (strictly
uppercase, at least in Lazarus v. 1.0) to get the value of the record before it was edited instead of the new
value.
If you have a table called fpdev and columns id, UserName and InstEmail, linked to a dataset with fields ID,
User and e-mail (see example in select statement), you could write this InsertSQL query:
INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:USER,:e-mail);
This statement will insert the values of ID, User and e-mail from the current record of the dataset into
the respective fields of table fpdev.
This example statement is actually more or less what SQLDB itself would autogenerate. The given
statement may result in errors when the id field is an auto-increment field in a unique key. Different
databases solve this problem in different ways. For example, the following works for MySQL.
INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:USER,:e-mail)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();
The above statement tries to insert a new record using 0 (zero) for the id column. If zero is already used as
a key, then a duplicate is detected and the id is updated to use the last inserted id. Well, actually an id one
increment higher than the last one used.
For Firebird, if you emulate autoincrement keys [1] something like this should work:
INSERT INTO fpdev(UserName, InstEmail) VALUES(:USER,:e-mail);(
Pagina 24 din 69
The statement inserts everything except the primary key and lets the Firebird before insert trigger use a
generator/sequence to insert an id value for you.
For an INSERT statement you may want to use the current field values of the selected record.
For UPDATE statements, you will want to use the field values as they were before editing in
the WHERE clause. As mentioned before, the field values before editing must be written as the field name
precede by OLD_ (strictly uppercase, at least in Lazarus v. 1.0). For example, this query:
UPDATE fpdev SET UserName=:USER, InstEmail=:e-mail WHERE UserName=:OLD_User;
The above statement updates the UserName and InstEmail columns of all records where User equals
the old User value.
We leave it as an exercise to the reader to use the current field values and the old field values in DELETE
statements.
See also the official documentation:
InsertSQL documentation
UpdateSQL documentation
DeleteSQL documentation
Parameters in TSQLQuery.SQL
In most situations, the SQL property of TSQLQuery will contain the select statement which in most
situations doesn't need parameters. However, it can contain them. This allows a very easy and powerful
way to filter your records.
Parameters have the following advantages:
no need to format your data as SQL text, date etc arguments (i.e. no need to remember how to
format a date for MySQL, which might differ from the Firebird implementation; no need to escape text data
like O'Malley's "SQL Horror"
possibly increased performance
protection against SQL injection attacks
The use of parameters may help performance of the database. Most databases support prepared
statements, which means that the statement is prepared and cached in the database. A prepared statement
can be used more than once and doesn't require parsing and query planning every time it is used, only the
parameters are changed each time it is used. In situations where the same statement is used a large
number of times (where only the values of the parameters differ), prepared statements can help
performance considerably. Additionally, SQL injection attacks can be mitigated by use of parameters.
The InsertSQL, UpdateSQL and DeleteSQL properties have predefined parameters for current and old field
values. However, the SQL property does not. You can create your own parameters in the Paramsproperty.
Select query example

This example shows how to select data using parameters. It also demonstrates using aliases (... AS ...) in
SQL.
sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS
''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
...
//This will create a parameter called emailsearch.
//If we want to, we can explicitly set what kind of parameter it is... which
might only be necessary if FPC guesses wrong:
//sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
//We can now fill in the parameter value:
sql_temp.params.parambyname('emailsearch').asstring
'[email protected]';
...
//Then use your regular way to retrieve data,
//optionally change the parameter value & run it again
:=
Insert query example

This example shows how to insert a new record into the table using parameters:
sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values
(:OURITEMNR,:OURDESCRIPTION)'
...
sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
sql_temp.ExecSQL;
SQLTransaction1.Commit; //or possibly CommitRetaining, depending on how your
application is set up
Pagina 25 din 69
Another way of doing this is something like:

tsqlquery1.appendrecord(['XXXX', 'description'])
tsqltransaction1.commit; //or commitretaining
Query with Format function

Using parameterized queries is the preferred approach, but in some situations the format function can be an
alternative. (see warning below). For example, parameters can't be used when you execute statements with
the connection's ExecuteDirect procedure (of course, you can just as well use a query to run the SQL
statement in question). Then this can come in handy:
procedure InsertRecord
var
aSQLText: string;
aSQLCommand: string;
begin
aSQLText:= 'INSERT INTO products(item_no, description) VALUES(%d, %s)';
aSQLCommand:= Format(aSQLText, [strtoint(Edit1.Text), Edit2.Text]);
aConnection.ExecuteDirect(aSQLCommand);
aTransaction.Commit;
end;
The values of the variables can change and the query values will change with them, just as with
parameterized queries.
The parameter %d is used for integers, %s for strings; etc. See the documentation on the Format function
for details.
Warning: Be aware that you may run into issues with text containing ' and dates using this technique!
Running your own SQL and getting metadata

If you want to just check some SQL statements, troubleshoot, or get metadata (e.g. list of tables) from the
database, you can do so from within the IDE. In your program, with your T*Connection, transaction, query
object etc set up at design-time, go into the SQL property for the query object, then click the ... button.
You'll see a window with SQL code, and you can run some statements like
SELECT * FROM EMPLOYEE
by pressing the play icon:
You can also get metadata: table names, column names etc (if the sqldb connector supports it but most of
them do nowadays):
Pagina 26 din 69
(See also: Database metadata#Lazarus TSQLQuery metadata tool)
Troubleshooting
Logging
See here: SqlDBHowto#Troubleshooting: TSQLConnection logging for more detail.
Poor performance
Make sure your database queries are optimized (use proper indexes etc). Use your database tools
(e.g. providing query plans) for this.
See #Out of memory errors below for possible performance improvements when moving forward
through an SQLQuery.
Error messages
Out of memory errors
TSQLQuery is a descendant of BufDataset, a dataset that buffers the data it receives into memory. If you
retrieve a lot of records (e.g. when looping through them for an export), your heap memory may become full
(with records you already looped through) and you will get out of memory errors.
Although this situation has improved in the FPC development version, a workaround is to tell bufdataset to
discard the records you have already read by setting the Unidirectional property to true before opening the
query:
MySQLQuery.UniDirectional:=True;
This may also improve performance.
Dataset is read-only
This may happen if you specify a query that you know is updatable but FPC doesn't.
Example:
SELECT p.dob, p.surname, p.sex FROM people p;
The SQL parser in FPC is quite simplistic and when it finds a comma or space in the FROM part it
considers multiple tables are involved and sets the dataset to read only. To its defense, aliasing tables is
usually not done when only one table is involved. Solution: rewrite the query or specify your
own InsertSQL, UpdateSQL and DeleteSQL.
Pagina 27 din 69
SQLdb Tutorial0
Contents
1 Introduction
2 Requirements
3 Firebird installation
o
3.1 Install Firebird client/server
o
3.2 Firebird database libraries on Windows
o
3.3 Firebird database libraries on other systems
4 No Firebird or employee.fdb installed?
o
4.1 Automatic creation
o
4.2 Manual creation
o
4.3 SQLite
o
4.4 PostgreSQL
Introduction
This article will help you set up your database environment with sample tables and data to use in SQLdb
Tutorial 1, 2 and 3.
Although this page is quite long, you will only go through a few of the sections as this article contains
instructions for various database systems.
If you want to get up and running quickly, I'd suggest installing the Firebird server and example database as
described below.
Requirements
This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions
Lazarus 0.9.30.
Furthermore you need an SQL/relational database, such as Firebird (if possible version 2.0 or newer). It's
easiest if you use standard settings (e.g user name SYSDBA and password masterkey), and that you have
the employee sample database installed.
You can use another database (e.g. Microsoft SQL Server, MySQL, PostgreSQL, Oracle, SQLite, Sybase
ASE or another database using ODBC). Please make sure you have the required database client libraries
installed (see the various wiki articles on databases) Please see below (section No Firebird or
employee.fdb installed?) for a way to set up your tables
Firebird installation
In case you haven't yet installed Firebird or the sample database that is used with the tutorials, please find
some instructions below.
Install Firebird client/server

If you haven't installed anything, you can download and run the installer from www.firebirdsql.org and install
the server (e.g. "32-bit Classic, Superclassic & Superserver").
Firebird database libraries on Windows

After installation, on Windows you will need to have the Firebird client DLLs present:
they could be in your system directory (available for all programs)
they can also be in your Lazarus directory (for design time support in the IDE) and in the output
directory where the executable is (to run the compiled program).
If you haven't installed the Firebird server, an easy way to get the client DLLs is: download Firebird
Embedded 2.5 from [1] Extract these files to your application directory:
fbclient.dll #only if using the server
#or
fbembed.dll #only if using embedded
firebird.msg
ib_util.dll
icudt30.dll
icuin30.dll
icuuc30.dll
Microsoft.VC80.CRT.manifest
msvcp80.dll
msvcr80.dll
Rename fbembed.dll to fbclient.dll (the name for a regular, client-server Firebird client - this helps use on
older Lazarus/FPC versions). The embedded Firebird DLL can also act as a regular Firebird client.
Make sure the employee.fdb database is in your project directory. You can copy it from the
examples/empbuild/ directory of the firebird 2.5 server.
Pagina 28 din 69
Finally, compile your project (even if it empty) once to create the output directory, and copy the dlls, as well
as the employee.fdb database, into that directory.
Firebird database libraries on other systems

On Linux/OSX, you will also need the Firebird client shared libraries. On Linux you can use your
distribution's method of getting programs to get the Firebird client packages, e.g. on Debian:
aptitude install libfbclient2 firebird-dev #we need the dev version because FPC
2.6 and lower will look for libfbclient.so
No Firebird or employee.fdb installed?

If you don't have the employee sample database installed or are using a different database, here is a
minimal version of the table we'll be using (note: directions for some specific databases can be found
below).
Automatic creation
The easiest way of setting this up is to create a new empty database on your system, then connect to it with
the TSQLScriptSample sample/utility program. This program is included with current Lazarus development
releases in the ./examples/database/tsqlscript directory, but can also be downloaded from
http://svn.freepascal.org/svn/lazarus/trunk/examples/database/tsqlscript/
Compile and run the program, then connect to the database:
then click the button Copy table creation script, and Run script:
Pagina 29 din 69
The program should indicate success. Now do the same for Copy sample data script, and Run script
In case of problems, you can try the manual steps below.
Manual creation
We will create CUSTOMER and EMPLOYEE tables that will be used in a later tutorial.
Run these SQL commands in your database editor/tool:
CREATE TABLE CUSTOMER
(
CUST_NO INTEGER NOT NULL,
CUSTOMER VARCHAR(25) NOT NULL,
CITY VARCHAR(25),
COUNTRY VARCHAR(15),
CONSTRAINT CT_CUSTOMER_PK PRIMARY KEY (CUST_NO)
);
CREATE TABLE EMPLOYEE
(
EMP_NO INTEGER NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
PHONE_EXT VARCHAR(4),
JOB_CODE VARCHAR(5) NOT NULL,
JOB_GRADE INTEGER NOT NULL,
JOB_COUNTRY VARCHAR(15) NOT NULL,
SALARY NUMERIC(10,2) NOT NULL,
CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
);
Some data so you can at least show something.... first some clients:
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (1, 'Michael
Design', 'San Diego', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (2, 'VC
Technologies', 'Dallas', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (3, 'Klmpfl,
Van Canneyt', 'Boston', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (4, 'Felipe
Bank', 'Manchester', 'England');
Pagina 30 din 69
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (5, 'Joost
Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (6, 'Van der
Voort Int.', 'Ottawa', 'Canada');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (7, 'Mrs.
Mauvais', 'Pebble Beach', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (8, 'Asinine
Vacation Rentals', 'Lihue', 'USA');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (9, 'Fax',
'Turtle Island', 'Fiji');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (10, 'FPC
Corporation', 'Tokyo', 'Japan');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (11, 'Dynamic
Intelligence Corp', 'Zurich', 'Switzerland');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (12, '3D-Pad
Corp.', 'Paris', 'France');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (13, 'Swen
Export, Ltd.', 'Milan', 'Italy');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (14, 'Graeme
Consulting', 'Brussels', 'Belgium');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES (15, 'Klenin
Inc.', 'Den Haag', 'Netherlands');
Then some employees:
INSERT INTO EMPLOYEE (emp_no, first_name, last_name, phone_ext, job_code,
job_grade,
job_country, salary)
VALUES (1,'William','Shatner','1702','CEO',1,'USA',48000);
job_grade,
VALUES (2,'Ivan','Rzeszow','9802','Eng',2,'Russia',38000);
job_grade,
VALUES (3,'Erin','Powell','1703','Admin',2,'USA',45368);
Please create the database, table and insert the data in your database environment.
SQLite
If you are using SQLite, you can create the database mentioned above in your project directory by
running the sqlite executable:
sqlite employee.sqlite
Now copy and paste the above CREATE TABLE and INSERT statements. To test if the right data is
present, enter this query:
SELECT * FROM customer;
End your session with
.quit
A file called employee.sqlite should now be created in your project directory.
Make sure the required sqlite dll/so is installed - e.g. on Windows, sqlite3.dll should be present in
your Lazarus + project output directory or
your system directory
Also, if you have a 32-bit sqlite dll, make sure you have a 32-bit lazarus.
Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy
the dll, as well as the employee.sqlite database, into that directory.
PostgreSQL
This section assumes you're using a Linux server and the shell; comparable steps can be done using
Windows and GUI tools such as pgadmin.
Log in to your server and switch to the postgres account:
su - postgres -c psql # immediately start up psql SQL interpreter
Create a user for the database and the tables:
CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust
password to taste
-- something like 'CREATE ROLE' should appear indicating success.
-- to later change the password you can use something like
-- alter user employee with password '<newpasswordhere>';
Pagina 31 din 69
-- We're going to let the password never expire; if you want more security,
you can leave this step out:
ALTER USER employee VALID UNTIL 'infinity'; --password never expires
-- Now we're tightening it up a bit again:
-- Don't allow user to create a database or create other users:
ALTER USER employee NOCREATEDB NOCREATEUSER; --restrict object creation
-- something like 'ALTER ROLE' should appear indicating success.
-- Create our database:
CREATE DATABASE employee;
-- something like CREATE DATABASE should appear indicating success.
-- Assign all privileges on database employee to user employee:
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full
permissions to database
-- something like GRANT should appear indicating success.
-We
create
the
table
using
a
autonumber/autoincrement:
CREATE TABLE customer
(
cust_no serial NOT NULL,
customer CHARACTER VARYING(25) NOT NULL,
city CHARACTER VARYING(25),
country CHARACTER VARYING(15),
CONSTRAINT integ_60 PRIMARY KEY (cust_no )
);
serial
datatype
aka
-- Then create the employee table:

CREATE TABLE EMPLOYEE
(
EMP_NO SERIAL NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
PHONE_EXT VARCHAR(4),
JOB_CODE VARCHAR(5) NOT NULL,
JOB_GRADE INTEGER NOT NULL,
JOB_COUNTRY VARCHAR(15) NOT NULL,
SALARY NUMERIC(10,2) NOT NULL,
CONSTRAINT CT_EMPLOYEE_PK PRIMARY KEY (EMP_NO)
);
-- Now copy and paste the INSERT statements from the section above to insert
the data.
-- To test if the right data is present, enter this query:
SELECT * FROM customer;
-- You should see some customer data.
-- Exit out of psql:
\q
Now you should be on a shell logged in as the postgres user.
If your server is on another machine than your development machine, make sure you allow network
access to the database. See your postgresql documentation for details, but something like this should
work:
# please adjust nano (e.g. use vim,emacs,joe...) and the postgres version
number depending on situation
nano /etc/postgresql/8.4/main/pg_hba.conf
Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range
#allow access from local network using md5 hashed passwords: host all all 192.168.0.1/24 md5
or more restrictive:
Pagina 32 din 69
# only allow network access to the employee database by the employee user host employee employee
192.168.0.1/24 md5
If there isn't such a line, add the line at the end, save and close your editor. See PostgreSQL
documentation for more details.
Reload PostgreSQL settings:
psql
then
SELECT pg_reload_conf(); --reload settings...
-- ...and exit back to shell:
\q
Test logging in to PostgreSQL.
Note: by default PostgreSQL tries an ident/unix domain socket login which doesn't allow passwords. So
we specify a host to force TCP/IP login:
psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. Enter your
db password.
Make sure the required PostgreSQL dll/so and any required other libraries is installed - e.g. on
Windows, they should be either:
in your Lazarus + project output directory or
in your system directory
Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy
the dlls into that directory.
Pagina 33 din 69
SQLdb Tutorial1
Contents
1 Introduction
2 Requirements
3 Basic example
o
3.1 Project and components
o
3.2 Link the components
o
3.3 Connecting to the database
3.3.1 PostgreSQL
3.3.2 SQLite
o
3.4 Choosing what data to show
Introduction
This tutorial shows you how to program databases using practical example code based on the SQLdb
Package. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he
should read relevant books/documentation. For this tutorial I use Firebird with the example database
employee.fdb. Other databases can also be used; some adjustments will need to be made which are
mentioned in the text.
While this tutorial may seem long, it mostly is just a lot of text that explains why you should type what you
type. As you can see at the end, the amount of actual code you will need for a working application is not
that great. More experienced developers will hopefully be able to glance through the instructions and
quickly understand what's going on. Also, you can stop at the end of the Basic example chapter and have a
working program.
This tutorial is based on a German tutorial by Swen, but it is extended, especially after the Basic
example. Swen wants the German version to remain as-is. If this is a problem, we can rename this version
and base a new German translation on that.
From Swen: thanks to Joost and Michael. Without their help this tutorial probably never would have come
about.
Requirements
This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions
Lazarus 0.9.30.
Please see SQLdb_Tutorial0, which will walk you through making sure you have the right sample database
set up.
Basic example
Project and components
First you should create a new Lazarus project.
To get access to our database we need one TIBConnection,
one TSQLQuery component from the 'SQLdb' tab in the component palette:
one TSQLTransaction and
TIBConnection is an Interbase/Firebird specific connection component. If you are using a different

database, substitute the proper component from the 'SQLDB' tab, e.g. a TSQLite3Connection for an SQLite
database, PQConnection for a PostgreSQL database. Discussion of setting up any database access
libraries is out of scope for this tutorial; see e.g. Databases for that.
Click on the TIBConnection (or equivalent connection component) on your form, and in the Object
Inspector, change the name to DBConnection. This will simplify the rest of the tutorial when using different
databases. It's also generally a good idea to name your components for something useful in your program
(e.g. MainframeDBConnection) so you know what it stands for.
The other two components, TSQLTransaction and TSQLQuery, can be used for all databases that are
supported by SQLdb.
To display the data, we use a TDBGrid component, which can be found on the 'Data Controls' tab. To
connect this component to the database components we need a TDatasource component from the 'Data
Access' tab.
Now we have all database components needed for the first example. You can enlarge the TDBGrid to have
enough space to display all data.
Link the components

Next we need to connect our components. A very simple way is to use the object inspector, but you can also
do this in your source code.
Pagina 34 din 69
Change
the Transaction property
of
DBConnection
to
'SQLTransaction1'.
This
causes
the Database property of SQLTransaction1 to automatically change to 'DBConnection'.
Then, change the Database property of SQLQuery1 to 'DBConnection'. Lazarus automatically adds the
value for the 'Transaction' property.
Next, change the Dataset property of Datasource1 to 'SQLQuery1'.
Finally we change the Datasource property of DBGrid1 to 'Datasource1'.
We now end up with a connection that links its default transaction to a transaction component. The
transaction component links its database property to the connection object. These two components are
enough to connect and execute instructions, but not enough to show queries. For this, an SQLQuery
component is used, which points to the database (and links to its default transaction). With the SQLQuery,
we'll later on retrieve data and post it back to the database.
Finally, the datasource component, which is linked to the query component, is a sort of place holder. It
keeps track of where in the query dataset we are and the GUI components are linked to that so they all
show the same record.
If this is gibberish to you, don't despair: with just some more work, we'll be able to show our first data.
Connecting to the database

How can we now show the data from our database on the screen?
First we need to tell DBConnection where the employee.fdb database is located. Locations of that db differ
depending on operating system, it could be something like:
.../examples/empbuild/ subdirectory of your Firebird installation on Linux
C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB on a Windows

machine
Using Firebird embedded, the file should be in your project directory

Again you have the choice: you can use the object inspector to assign the path or do it directly in your
source code.
We choose to use the object inspector: set the DBConnection 'HostName' property to the Firebird server
name or IP adddress. Use localhost if a Firebird server is running on your development machine; use a
blank value if you use the embedded Firebird client. Change the DatabaseName property of DBConnection
to the path to the employee.fdb file on the database server. If you use embedded Firebird, the path part
should be empty and you should just specify the filename.
Before the database server grants access to the data, it will check authorisation via username and
password. A serious database application will ask the user for both values when the application is started,
and send these to the server when connecting. A possible way of doing this is shown in SQLdb Tutorial3.
However, for now, to simplify matters, we use the object inspector again to hard code these. Change the
'UserName' property to 'SYSDBA' and 'Password' to 'masterkey' (of course, adjust if your database
installation has a different username/password).
Now check if all settings so far are correct: set the 'Connected' property to 'True'. If the database path isn't
correct or if username or password are wrong, you will get an error message. If the connection was
successful, you should cut it now (set 'Connected' to 'False').
PostgreSQL
The situation with PostgreSQL is very similar to that on Firebird. The database name does not have a path you just specify a name part (e.g. 'employee'). PostgreSQL has no embedded mode, so you need to fill out
the HostName property for the connection test to work.
SQLite
For SQLite, you can leave the 'HostName', 'UserName', and 'Password' properties empty. Set the
'DatabaseName' to the name of your SQLite file, e.g. employee.sqlite. Note: sqlite will create the database
specified if it doesn't exist, so be careful here.
You should now have something like the following screenshot - todo: this screenshot is actually further
along, we only have a button now:
Pagina 35 din 69
Form and components set up
Choosing what data to show

Although the connection was successful, no data was displayed. The reason is simple. We haven't told the
database server which data to return: the employee.fdb database contains several tables, and we haven't
told Firebird the table we want to see. If you don't know the structure of a database, you can use tools
like FlameRobin, to display the contents. Lazarus also provides such a tool - the DataDesktop. You can find
it in the /tools/lazdatadesktop/ subdirectory of Lazarus. Save our project and then open the project
lazdatadesktop.lpi and compile it.
The DataDesktop in action
Pagina 36 din 69
Back to our example.

We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:
SELECT * FROM CUSTOMER
We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this
would look like:
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
The SQL instruction must be enclosed by single quotes. You also have the ability to assign the content of
another component (e.g. Edit1.Text). This is not always a good idea; see Secure programming (a more
advanced text) for details on SQL injection.
Let's add a TButton from the 'Standard' tab on the form. When the user clicks on the button, data retrieval
should start. Change its Caption property from "Button1" to Show data".
We will need some code for this. Double click on Button1. Lazarus then creates the skeleton of the
necessary procedure. In our source code we should find the following lines:
procedure TForm1.Button1Click(Sender: TObject);
begin
end;
Between begin and end we must enter the instructions needed to display the data.... obviously that will be
something to do with SQLQuery1..
The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close
the component first:
SQLQuery1.Close;
Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:
Now we need to establish the connection to the database, activate the transaction and open the query:
DBConnection.Connected := True;
SQLTransaction1.Active := True;
SQLQuery1.Open;
You can omit the first two instructions, because they are done automatically by the third instruction. If you
compile the project at this point, you could already see the data from the 'CUSTOMER' table.
However, a serious application must make sure that all open database connections are properly closed
when not needed anymore. Otherwise the secondary effects would not be foreseeable. So, we use the
OnClose event of our form (create it with a double click in the object inspector):
procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
end;
To close the connection we use the reverse order compared to our opening code:
SQLQuery1.Close;
SQLTransaction1.Active := False;
DBConnection.Connected := False;
If you set DBConnection.Connected to False, the Transaction and the Query is automatically closed and
you can omit closing them manually.
Summary
Up to now we have learned how to connect to a database using the SQLdb package and how to display the
contents of a table on the screen. If you want to add more functionality such as editing, please continue
with SQLdb Tutorial2
If you followed the previous steps, then your code should look like:
unit Unit1;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
Graphics, Dialogs, DBGrids, StdCtrls;
type
{ TForm1 }
TForm1 = class(TForm)
Pagina 37 din 69
Button1: TButton;
Datasource1: TDatasource;
DBGrid1: TDBGrid;
DBConnection: TIBConnection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
procedure Button1Click(Sender: TObject);
procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
{ TForm1 }
begin
SQLQuery1.Close;
SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
DBConnection.Connected:= True;
SQLTransaction1.Active:= True;
SQLQuery1.Open;
end;
procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
SQLQuery1.Close;
SQLTransaction1.Active:= False;
DBConnection.Connected:= False;
end;
end.
Pagina 38 din 69
SQLdb Tutorial2
Contents
1 Overview
o
1.1 Dynamic database connection
1.1.1 SQLite, other databases

o
1.2 Filtering data
o
1.3 Error handling
1.3.1 SQLite, PostgreSQL, other databases

2 Editing data using the grid
o
2.1 Editing
o
2.2 Hiding primary key column
2.2.1 SQLite, other databases

o
2.3 Inserting new data
o
2.4 Deleting data
3 Summary
4 Embedded database without code changes
o
4.1 Firebird on Windows
o
4.2 Firebird on Linux/OSX/Unix
o
4.3 SQLite
o
4.4 Other databases
Overview
If you have followed SQLdb Tutorial1, you have a basic grid showing database information. While this
application works, we can add some refinements on this.
Dynamic database connection

Up to now, we used a fixed database server name, database location, username and password for
simplicity. As mentioned, "real" applications normally let users specify their own username and password.
Let's change the form so we can specify them: add two TEdits from the standard menu. Set their name
properties to Username and Password. Set the Password's PasswordChar property to * (the asterisk) for
some security against people looking over your shoulder.
If you want to make it easier (and less secure, of course) to connect, you can set the UserName Text
property to a valid database user, such as SYSDBA. You could even set the Password Text property to a
default value like masterkey, easy for testing on developer machines if security doesn't matter...
Cosmetically, adding some labels so people know what they're supposed to type is useful.
Also, to make it easier to connect to any employee sample database on any Firebird/Interbase server, we
add two textboxes for server name and database path. Add another two TEdits, and name them
ServerName and DatabaseName.
If you want, you can set the 'Text' property to default sensible values for your situation, e.g. localhost
and C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB
Labels to explain what users need to enter would help here, too.
For clarity, we're going to remove the connection info from our designtime components: on the
DBConnection component, remove all text from the UserName, Password, DatabaseName and HostName
properties.
Now, finally, we need to tell our database connection component how to connect. This should normally only
be necessary at the beginning of an application run. In our case the existing 'Button1' code is a good way to
set up the connection.
Add code until you get:
begin
SQLQuery1.Close;
//Connection settings for Firebird/Interbase database
//only needed when we have not yet connected:
if not DBConnection.Connected then
begin
DBConnection.HostName := ServerName.Text;
DBConnection.DatabaseName := DatabaseName.Text;
DBConnection.Username := UserName.Text;
DBConnection.Password := Password.Text;
// Now we've set up our connection, visually show that
// changes are not possibly any more
ServerName.ReadOnly:=true;
DatabaseName.ReadOnly:=true;
Pagina 39 din 69
UserName.ReadOnly:=true;
Password.ReadOnly:=true;
end;
SQLQuery1.Open;
end;
Now run and test if you can connect.
SQLite, other databases

Adjust the Text property in the DatabaseName TEdit as needed; e.g. employee.sqlite for SQLite.
For sqlite, specifying HostName, Username and Password doesn't make sense, so you can omit these
TEdits. Obviously, leave out/comment out assigning the corresponding values to DBConnection in the code
above. For Firebird embedded, please hardcode the Username to SYSDBA; specifying this when sqlite is
used won't hurt.
The code will look something like:
begin
SQLQuery1.Close;
//Connection settings for embedded databases
begin
DBConnection.UserName := 'SYSDBA'; //Firebird embedded needs this;
doesn't harm if using SQLite
end;
SQLQuery1.Open;
end;
Filtering data
Often, tables contain a huge amount of data that the user doesn't want to see (and that might take a long
time to query from the database and travel over the network). Let's assume that only the customers from
the USA should be displayed. Therefore the SQL instruction in 'SQLQuery1' would look like:
SELECT * FROM CUSTOMER WHERE COUNTRY = 'USA'
... which would translate to something like this in our code:
SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA'';
There are two reasons why we will not use this instruction for our example application:
First there is a problem with the usage of the single quote. The compiler would interpret the quote before
USA as a closing quote (the first quote is before the select from...) and so the SQL instruction would
become invalid. Solution: double the inside quotes:
SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = ''USA''';
The second, more important reason is the fact, that we probably don't know what constraints the user will
want to filter on. We don't want to limit the flexibility of the user.
To get this flexibility, first we change our SQL query statement and replace 'USA' by a placeholder (a
parameter in SQL speak): change the Button1click procedure and replace
with
SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
In FPC SQLDB, the SQL parameter is marked by the leading colon (other languages/environments use
other conventions like ?). To allow the user to enter a value for the filter, we place a TEdit component on our
form. Delete the value of its 'Text' property.
We can now take the text entered in the TEdit and fill the SQL COUNTRY parameter by using the 'Params'
property of TSQLQuery. Add this below the previous statement:
SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
The parameter can be specified by its position or name. Using the name should improve the readability of
the source code, and obviously helps if you insert more parameters in the middle of existing parameters.
Pagina 40 din 69
We use .AsString to assign a string value to the parameter; there are equivalent property assignments for
integer parameters, boolean parameters etc.
The code up to now forces us to use a filter. If a user specifies an empty value in the edit box, no record will
be displayed. This is probably not what we want. Let's test for an empty value and build our query
accordingly. We should end up with a procedure like this:
begin
SQLQuery1.Close;
begin
end;
// Show all records, or filter if user specified a filter criterium
if Edit1.Text='' then
SQLQuery1.SQL.Text := 'select * from CUSTOMER'
else
begin
SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
end;
SQLQuery1.Open;
end;
Now you can play around a bit with filtering using Edit1. If you enter a country that's not present in the
database, an empty grid is shown.
Error handling
The application should run, but sometimes problems can occur. Databases, even embedded databases can
crash (e.g. when the database server crashes, the disk is full, or just due to a bug), leaving the application
hanging.
Access to a database (any external process, really) should therefore always be integrated in a try ... except
and/or try ... finally construct. This ensures that database errors are handled and the user isn't left out in the
cold. A rudimentary routine for our example application could look like this:
begin
try
SQLQuery1.Close;
...
SQLQuery1.Open;
except
//We could use EDatabaseError which is a general database error, but we're
dealing with Firebird/Interbase, so:
on E: EDatabaseError do
begin
MessageDlg('Error','A database error has occurred. Technical error
message: ' + E.Message,mtError,[mbOK],0);
Edit1.Text:='';
end;
end;
end;
SQLite, PostgreSQL, other databases

You can either use the more generic EDatabaseError, or - if available - your own specialized databaseerror,
if you need more details. E.g. SQLite and the PostgreSQL driver in FPC 2.6.1 and lower doesn't have a
Pagina 41 din 69
specialized E*DatabaseError; you'd have to use EDatabaseError. PostgreSQL on FPC trunk (development
version) has EPQDatabaseError.
Editing data using the grid

Editing
Up to now, if you tried to edit data in the grid, the changes would not be saved. This is because
the SQLQuery1 is not instructed to send the changes to the database transaction at the right moment. We
need to fix this, and then commit the transaction in the database, so all changes get written. For this, you
would use code like this:
SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
SQLTransaction1.Commit; //... and commit them using the transaction.
//SQLTransaction1.Active now is false
We want to make sure any edits (inserts, updates, deletes) are written to the database:
when the users changes the filtering criteria and presses the button to query the database
when the form is closed

It makes sense to make a separate procedure for this that is called in those two instances. Go to the code,
and add an empty line here:
Button1: TButton;
DBGrid1: TDBGrid;
Edit1: TEdit;
*****insert the empty line here****
procedure Button1click(Sender: TObject);
procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
private
then type
procedure SaveChanges;
press shift-ctrl-c (default combination) to let code completion automatically create the corresponding
procedure body.
We need to add error handling and check that the transaction is active - remember, this code also gets
called when pressing the button the first time, when the transaction is not active yet. We get:
procedure Tform1.SaveChanges;
// Saves edits done by user, if any.
begin
try
if SQLTransaction1.Active then
// Only if we are within a started transaction;
// otherwise you get "Operation cannot be performed on an inactive dataset"
begin
end;
except
begin
MessageDlg('Error', 'A database error has occurred. Technical error
message: ' +
E.Message, mtError, [mbOK], 0);
Edit1.Text := '';
end;
end;
end;
Now we need to call this procedure at the appropriate moments:
procedure Tform1.Button1click(Sender: TObject);
begin
SaveChanges; //Saves changes and commits transaction
try
SQLQuery1.Close;
....
Pagina 42 din 69
and
procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
begin
SQLQuery1.Close;
....
Now test and see if edits made in the dbgrid are saved to the database.
Hiding primary key column

Often, you don't want your users to see autonumber/generated primary keys as they are only meant to
maintain referential integrity. If users do see them, they might want to try the edit the numbers, get upset
that the numbers change, that there are gaps in the numbers, etc.
In our example, CUST_NO is the primary key, with content auto-generated by Firebird using triggers and a
sequence/generator. This means that you can insert a new record without specifying the CUST_NO;
Firebird will create one automatically.
We could simply change our SQLQuery1.SQL.Text property to not include CUST_NO, but this would lead to
problems when editing data - a primary key is needed in those circumstances for uniquely identifying the
row/record in question.
Therefore, let's use a trick to query for all columns/fields in the table, but keep the grid from showing the first
field, CUST_NO: in the Button1Click procedure, add code so it looks like:
begin
...
SQLQuery1.Open;
// Hide the primary key column which is the first column in our queries.
// We can only do this once the DBGrid has created the columns
DBGrid1.Columns[0].Visible:=false;
Recompile, and check to see if the primary key column is really hidden.
SQLite, other databases
Other databases: a lot of other databases use an 'autonumber' or 'autoinc' type of field to provide
auto-generated field content. Try changing your table definition and see if it works.
Sqlite: the example above works for SQLite as is because we're using an integer primary key. See
the documentation for details.
Inserting new data

If you insert new rows/records without any CUST_NO information you may have noticed that you get an
error message: Field CUST_NO is required, but not supplied. This also happens if you hid
the CUST_NO column, as in the previous section.
The reason: Lazarus thinks that CUST_NO is required. That's not so strange, because it is a primary key
and the underlying table definition in the database does say it is required.
If we can instruct Lazarus that this field is not actually required, we can pass empty values (=NULL values)
to the database. Fortunately, a query's field object has a Required property that does exactly that.
Change the code to something like:
SQLQuery1.Open;
{
Make sure we don't get problems with inserting blank (=NULL) CUST_NO
values, e.g.:
Field CUST_NO is required, but not supplied
We need to tell Lazarus that, while CUST_NO is a primary key, it is not
required
when inserting new records.
}
SQLQuery1.FieldByName('CUST_NO').Required:=false;
// Hide the primary key column which is the first column in our queries.
// We can only do this once the DBGrid has created the columns
Deleting data
You can let your users use the mouse to do this. You don't even need to code a single line for this
functionality...
On the 'Data Controls' tab, select a TDBNavigator component and drop it on the form, above the grid.
To indicate what the navigator should be linked to, set its DataSource property to your existing datasource
('DataSource1') using the Object Inspector. Now you can use the button on the DBNavigator to delete
records, but also insert them, and move around the records. Also, when editing cells/fields, you can use
the Cancel button to cancel your edits.
Pagina 43 din 69
To allow users to delete the row they're in on the grid using the
Delete
key, add LCLType (this contains
defintions for key codes) to your uses clause:

uses
Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;
... then handle the KeyUp event for the grid, which occurs when a key is released if in the grid. However, we
do need to check that the user is not editing a field - as he'll probably use the
Delete
key to delete letters
rather than the record he's working on.

Select the grid, then go to events and create an OnKeyUp event like this:
procedure
TForm1.DBGrid1KeyUp(Sender:
TObject;
var
Key:
Word;
Shift:
TShiftState
);
begin
// Check for del key being hit and delete the current record in response
// as long as we're not editing data
if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
begin
//... delete current record and apply updates to db:
SQLQuery1.Delete;
SQLQuery1.ApplyUpdates;
end;
end;
Note: By default TDBGrid property Options / dgDisableDelete is set to false, this means a user can delete any
record with the ctrl - delete key combo. You may not want this behaviour.
Summary
If you followed along up to now, you can retrieve data from the database, filter it, and edit and delete data in
the grid. Your code should look something like this:
unit sqldbtutorial1unit;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;
type
{ TForm1 }
Button1: TButton;
DatabaseName: TEdit;
DBGrid1: TDBGrid;
Dbnavigator1: Tdbnavigator;
Edit1: TEdit;
Label2: Tlabel;
Label3: Tlabel;
Label4: Tlabel;
Label5: Tlabel;
Password: TEdit;
UserName: TEdit;
ServerName: TEdit;
Label1: TLabel;
procedure SaveChanges;
Pagina 44 din 69
procedure Button1click(Sender: TObject);

procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
{ TForm1 }
procedure Tform1.Savechanges;
// Saves edits done by user, if any.
begin
try
if SQLTransaction1.Active then
// Only if we are within a started transaction
// otherwise you get "Operation cannot be performed on an inactive dataset"
begin
end;
except
begin
message: ' +
Edit1.Text := '';
end;
end;
end;
procedure
TForm1.DBGrid1KeyUp(Sender:
TObject;
var
Key:
Word;
Shift:
TShiftState
);
begin
// Check for del key being hit and delete the current record in response
// as long as we're not editing data
if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
begin
//... delete current record and apply updates to db:
SQLQuery1.Delete;
SQLQuery1.ApplyUpdates;
end;
end;
begin
try
SQLQuery1.Close;
begin
Pagina 45 din 69
end;
// Show all records, or filter if user specified a filter criterium
if Edit1.Text='' then
SQLQuery1.SQL.Text := 'select * from CUSTOMER'
else
begin
SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
end;
DBConnection.Connected := True;
SQLTransaction1.Active := True; //Starts a new transaction
SQLQuery1.Open;
{
Make sure we don't get problems with inserting blank (=NULL) CUST_NO
values, i.e. error message:
"Field CUST_NO is required, but not supplied"
We need to tell Lazarus that, while CUST_NO is a primary key, it is not
required
when inserting new records.
}
SQLQuery1.FieldByName('CUST_NO').Required:=false;
{
Hide the primary key column which is the first column in our queries.
We can only do this once the DBGrid has created the columns
}
except
// EDatabaseError is a general error;
// you could also use one for your specific db, e.g.
// use EIBDatabaseError for Firebird/Interbase
begin
message: ' +
Edit1.Text := '';
end;
end;
end;
procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
begin
SQLQuery1.Close;
SQLTransaction1.Active := False;
DBConnection.Connected := False;
end;
end.
Embedded database without code changes

Firebird on Windows
A bonus for Firebird users on Windows: if you have been following this tutorial (even if you only did the
basic example), you renamed the fbembed.dll embedded Firebird library to fbclient.dll. With this,
Lazarus could connect to regular Firebird servers (either on another machine or on your local machine).
Pagina 46 din 69
However, you can also copy the employee.fdb database to your application directory, run the
application, clear the Server name TEdit and use Firebird embedded to directly connect to the database file,
without any servers set up.
This is great if you want to deploy database applications to end users, but don't want the hassle of installing
servers (checking if a server is already installed, if it's the right version, having users check firewalls, etc).
See Firebird embedded for more details.
September 2011: in recent development (SVN) versions of Free Pascal, FPC tries to first
load fbembed.dll, so you need not rename fbclient.dll anymore for this to work.
Firebird on Linux/OSX/Unix
There must be a way to get this to work on Linux/OSX. See Firebird for hints and links. Updates to the wiki
are welcome.
SQLite
SQLite certainly offers embedded functionality - it does not allow a client/server setup on the other hand. By
following the tutorial above, you can see that switching between databases (e.g. SQLite and Firebird) is not
so much work at all.
Other databases
Your database might offer similar functionality. Updates of this wiki for other database systems are
welcome.
Pagina 47 din 69
SQLdb Tutorial3
Contents
1 Overview
2 Multi database support
3 Login form
o
3.1 Connection test callback function
o
3.2 Additions/modifications
4 Getting database data into normal controls
5 Adapting SQL for various databases
o
5.1 Revisiting our lowest/highest salary
6 Getting data out of normal controls into the database
o
6.1 Parameterized queries
7 Summary
8 Code
Overview
In this tutorial, you will learn how to
make your application suitable for multiple databases, including using a login form
get database data into normal controls (instead of database controls)
get data out of the controls and back into the database
execute parametrized queries.
Multi database support

Using any database and a login form, you can support multiple different database servers/embedded
libraries that SQLDB supports.
Advantages:
the user/programmer can dynamically use any sqldb t*connection, so he can choose between dbs
Disadvantages:
More complicated SQL will likely need to still be adapted. Each database has its own dialect; it is of
course possible to call db-specific SQL, this can grow into a maintenance problem.
You can't use T*connection specific properties (like TIBConnection.Dialect to set Firebird dialect).
To use multi-database support, instead of your specific T*Connection such as TIBConnection,
use TSQLConnector (not TSQLConnection), which dynamically (when the program is running) chooses the
specific T*Connection to use based on its ConnectorType property:
uses
...
var
Conn: TSQLConnector;
begin
Conn:=TSQLConnector.Create(nil);
try
// ...actual connector type is determined by this property.
// Make sure the ChosenConfig.DBType string matches
// the connectortype (e.g. see the string in the
// T*ConnectionDef.TypeName for that connector .
Conn.ConnectorType:=ChosenConfig.DBType;
// the rest is as usual:
Conn.HostName:='DBSERVER';
Conn.DatabaseName:='bigdatabase.fdb';
Conn.UserName:='SYSDBA';
Conn.Password:='masterkey';
try
Conn.Open;
Login form
As mentioned in SQLdb Tutorial1, a user should login to the database using a form (or perhaps a
configuration file that is securely stored), not via hardcoded credentials in the application. Besides security
considerations, having to recompile the application whenever the database server information changes is
not a very good idea.
In dbconfiggui.pas, we will set up a login form that pulls in default values from an ini file, if it exists. This
allows you to set up a default connection with some details (database server, database name) filled out for
Pagina 48 din 69
e.g. enterprise deployments. In the form, the user can add/edit his username/password, and test the
connection before going further.
We use a separate dbconfig.pas unit with a TDBConnectionConfig class to store our chosen connection
details. This class has support for reading default settings from an ini file.
This allows use without GUI login forms (e.g. when running unattended/batch operations), and allows reuse
in e.g. web applications.
This TDBConnectionConfig class is surfaced in the login form as the Config property, so that the main
program can show the config form modally, detect an OK click by the user and retrieve the selected
configuration before closing the config form.
Connection test callback function

To keep the login form flexible (it may be used with other database layers like Zeos), we implement the test
section as a callback function and let the main program deal with it.
The definition in the login form in dbconfiggui.pas:
type
TConnectionTestFunction
=
function(ChosenConfig:
TDBConnectionConfig):
boolean of object;
The main form must implement a function that matches this definition to handle the test request from the
config form.
The callback function takes the config object passed on by the config form, and uses that to construct a
connection with the chosen database type. It then simply tries to connect with the server; if it is succesful, it
sets the function result to true, otherwise the result stays false.
Because database connection attempts to non-existing servers can have a long timeout, we indicate to the
user that he must wait by setting the cursor to the hourglass icon.
uses
...
dbconfig, dbconfiggui
...
procedure TForm1.FormCreate(Sender: TObject);
LoginForm:=TDBConfigForm.Create(self);
try
// The test button on dbconfiggui will link to this procedure:
... this links the callback in ''dbconfiggui.pas'' to the ConnectionTest
function here...
Pagina 49 din 69
LoginForm.ConnectionTestCallback:=@ConnectionTest;
...
function TForm1.ConnectionTest(ChosenConfig: TDBConnectionConfig): boolean;
// Callback function that uses the info in dbconfiggui to test a connection
// and return the result of the test to dbconfiggui
var
// Generic database connector...
Conn: TSQLConnector;
begin
result:=false;
Conn:=TSQLConnector.Create(nil);
Screen.Cursor:=crHourglass;
try
// ...actual connector type is determined by this property.
// Make sure the ChosenConfig.DBType string matches
// the connectortype (e.g. see the string in the
// T*ConnectionDef.TypeName for that connector .
Conn.ConnectorType:=ChosenConfig.DBType;
Conn.HostName:=ChosenConfig.DBHost;
Conn.DatabaseName:=ChosenConfig.DBPath;
Conn.UserName:=ChosenConfig.DBUser;
Conn.Password:=ChosenConfig.DBPassword;
try
Conn.Open;
result:=Conn.Connected;
except
// Result is already false
end;
Conn.Close;
finally
Screen.Cursor:=crDefault;
Conn.Free;
end;
end;
Finally, the code in dbconfiggui.pas that actually calls the callback is linked to the Test button. It tests if the
callback function is assigned (to avoid crashes), for completeness also checks if there is a valid
configuration object and then simply calls the callback function:
...
TDBConfigForm = class(TForm)
...
private
FConnectionTestFunction: TConnectionTestFunction;
public
property
ConnectionTestCallback:
TConnectionTestFunction
write
FConnectionTestFunction;
...
procedure TDBConfigForm.TestButtonClick(Sender: TObject);
begin
// Call callback with settings, let it figure out if connection succeeded and
// get test result back
if assigned(FConnectionTestFunction) and assigned(FConnectionConfig) then
if FConnectionTestFunction(FConnectionConfig) then
showmessage('Connection test succeeded.')
else
showmessage('Connection test failed.')
else
showmessage('Error: connection test code has not been implemented.');
end;
Additions/modifications
Possible additions/modifications for the login form:
Add command line arguments handling for dbconfig to preload suitable defaults, so the program
can be used in batch scripts, shortcuts etc
Add a "Select profile" combobox in the login form; use multiple profiles in the ini file that specify
database type and connection details.
Pagina 50 din 69
Hide database type combobox when only one database type supported.
Hide username/password when you're sure an embedded database is selected.
Add support for specifying port number, or instance name with MS SQL Server connector
Add support for trusted authentication for dbs that support it (Firebird, MS SQL): disable
ussername/password controls
If an embedded database is selected but the file does not exist: show a confirmation request and
create the database
Create a command-line/TUI version of the login form (e.g. using the curses library) for commandline applictions
Updates to this article/the code warmly welcomed.
Getting database data into normal controls

Note: Before starting this section, please make sure you have set up the sample employee database as specified
in SQLdb Tutorial0#Requirements
In previous tutorials, data-bound controls were covered: special controls such as the dbgrid that can bind its
contents to a datasource, get updates from that source and send user edits back.
It is also possible to programmatically retrieve database content and fill any kind of control (or variable) with
that content. As an example, we will look at filling a stringgrid with some salary details for the sample
employee database table.
On the main form, let's add a stringgrid and retrieve the data (e.g. via a procedure LoadSalaryGrid called in
the OnCreate event):
// Load from DB
try
if not FConn.Connected then
FConn.Open;
if not FConn.Connected then
begin
ShowMessage('Error connecting to the database. Aborting data
loading.');
exit;
end;
// Lowest salary
// Note: we would like to only retrieve 1 row, but unfortunately the SQL
// used differs for various dbs. As we'll deal with db dependent SQL
later
// in the tutorial, we leave this for now.
// MS SQL: 'select top 1 '...
FQuery.SQL.Text:='select ' +
'
e.first_name, ' +
'
e.last_name, ' +
'
e.salary ' +
'from employee e ' +
'order by e.salary asc ';
// ISO SQL+Firebird SQL: add
//'rows 1 '; here and below... won't work on e.g. PostgreSQL though
FTran.StartTransaction;
FQuery.Open;
SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString; // i.e. Cells[Col,Row]
SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString;
FQuery.Close;
// Always commit(retain) an opened transaction, even if only reading
// this will allow updates by others to be seen when reading again
FTran.Commit;
...
end;
except
on D: EDatabaseError do
begin
message: ' +
Pagina 51 din 69
D.Message, mtError, [mbOK], 0);

end;
end;
Things to note: we catch database errors using try..except. You'll notice we forgot to roll back the
transaction in case of errors - which is left as an exercise to the reader.
We Open the query object, thereby asking FQuery to query the database via its SQL statement. Once this
is done, we're on the first row of data. We simply assume there is data now; this is actually a programming
error: it would be tidier to check for FQuery.EOF being true (or FQuery.RecordCount being >0).
Next, we retrieve the data from the first row of results. If we wanted to move to the next row, we'd
use FQuery.Next, but that is not necessary here. We put the results in the stringgrid, giving the lowest
salary in the list. A similar approach can be taken for the highest salary.
Adapting SQL for various databases

As we noticed above, various databases support various versions of SQL (either in addition to or in
contradiction to the official ISO SQL standards). Fortunately, you can customize your application based on
which DB it ends up using, which will be demonstrated by getting the standard deviation of the employees'
salaries - built into e.g. PostgreSQL SQL but not available by default in e.g. Firebird.
In our LoadSalaryGrid procedure, we'll use the SQL for PostgreSQL and build a code solution for all other
databases. First detect which database is loaded, below the other lines add:
...
FQuery.Close;
FTran.Commit;
//end of existing code
if FConn.ConnectorType = 'PostGreSQL' then
begin
// For PostgreSQL, use a native SQL solution:
FQuery.SQL.Text:='select stddev_pop(salary) from employee ';
FQuery.Open;
if not FQuery.EOF then
FQuery.Close;
FTran.Commit;
end
else
begin
// For other database, use the code approach:
....see below...
end;
Notice the use of ConnectorType; the string used must match exactly. We also properly check for empty
results from the query (which might happen if the employee table is empty).
... now let's implement a code-based solution for other databases that do not support standard deviation:
// For other databases, use the code approach:
// 1. Get average of values
FQuery.SQL.Text:='select avg(salary) from employee ';
FQuery.Open;
if FQuery.EOF then
SalaryGrid.Cells[3,3]:='No data'
else
begin
Average:=FQuery.Fields[0].AsFloat;
FQuery.Close;
// 2. For each value, calculate the square of (value-average), and add it
up
FQuery.SQL.Text:='select salary from employee where salary is not null ';
FQuery.Open;
while not FQuery.EOF do
begin
DifferencesSquared:=DifferencesSquared+Sqr(FQuery.Fields[0].AsFloatAverage);
Pagina 52 din 69
Count:=Count+1;
FQuery.Next;
end;
// 3. Now calculate the average "squared difference" and take the square
root
if Count>0 then //avoid division by 0
SalaryGrid.Cells[3,3]:=FloatToStr(Sqrt(DifferencesSquared/Count))
else
SalaryGrid.Cells[3,3]:='No data';
end;
FQuery.Close;
Note that we use FQuery.EOF to check for empty data (and avoid division by zero errors etc). The loop
shows how to:
retrieve a database value into a variable
use FQuery.Next to move to the next record
properly check if the query dataset has hit the last record, then stop retrieving data.
The resulting screen should show something like this - note the use of a decimal comma - while your
computer may show a decimal point depending on your locale:
Revisiting our lowest/highest salary

This section gives some more useful details on SQL but is not required to work through for the rest of the
tutorial
Now we know how to deal with detecting various database connections, we can adjust the SQL that gets
the lowest and highest salary as well to make use of db specific functionality.
An example: this would work for MS SQL Server by limiting the number of returned rows to just the first:
SELECT top 1
e.first_name, e.last_name, e.salary
FROM employee e
ORDER BY e.salary ASC
to get the lowest salary.
This efficiently returns one record. Other databases use other syntax, such as the ISO ROWS 1. The
diligent SQL student will soon learn not to miss out that important part and request entire large recordsets
just for one required record!
Let's briefly examine other ways to achieve the same thing, that are worth knowing.
Another way to retrieve the record(s) with the minimum salary would be :
Pagina 53 din 69
SELECT
e.first_name,
e.last_name,
e.salary
FROM
employee
e
WHERE
e.salary=(SELECT MIN(salary) FROM employee)
SQL students would greatly benefit from researching Common Table Expressions.
A CTE allows a virtual temporary table to be used in a following expression, allowing you to clearly code
some very complex queries that otherwise may not be possible. Knowing about CTEs will catapult you
ahead of colleagues who have never heard of them! For example the above may be rewritten (example in
Microsoft SQL Server syntax) as :
WITH TheMinimum AS
(
SELECT MIN(salary) AS MinimumPay FROM Employee
)
SELECT
e.first_name,
e.last_name,
e.salary
FROM
Employee
e
WHERE
e.salary=(SELECT MinimumPay FROM TheMinimum)
Several such temporary tables may be chained together, each using the results from the previous tables.
You can treat these virtual tables as though they were real tables in the database, using JOINs to link
recordsets together. And it can be very useful for quick tests using hardcoded data - this can be run without
any database connection :
WITH TestEmployee AS
(
SELECT 'Fred' AS first_name, 'Bloggs' AS last_name, 10500 AS salary
UNION
SELECT 'Joe' AS first_name, 'Public' AS last_name, 10000 AS salary
UNION
SELECT 'Mike' AS first_name, 'Mouse' AS last_name, 11000 AS salary
),
TheMinimum AS
(
SELECT MIN(salary) AS MinimumPay FROM TestEmployee
)
SELECT
e.first_name,
e.last_name,
e.salary
FROM
TestEmployee
e
WHERE
e.salary=(SELECT MinimumPay FROM TheMinimum)
You can end up with quite long strings for the code of such SQL queries, but it is only one query and may be
called from anywhere where you are limited to a simple single expression - it can be useful to answer
complex queries without resorting to functions or stored procedures.
Getting data out of normal controls into the database

Previously, we have seen:
how to let SQLDB update the database with data-bound controls (earlier tutorials)
how to get data out of the database using queries (the section above)
You can also execute SQL to get arbitrary data back into the database via code. This allows you to use
variables or controls that have no db aware equivalent such as sliders or custom controls to enter data into
the database, at the expense of a bit more coding.
As an example, we are going to allow the user to change the lowest and highest salary in the stringgrid.
For ease of editing, set the grid's Options/goEditing to true; then assign the procedure below to the
OnValidate event for the grid, which will be called every time a user has finished updating the grid.
Parameterized queries
The following code also demonstrates how to use parameterized queries to avoid SQL injection, fiddling
with quoting for string values, date formatting, etc.
As you can see in the code, you can name your parameters whatever you wish and prefix them with : in the
SQL.
In
code,
you
can
set/get
their
values
by <somequery>.Params.ParamByName('<thename>').As'<variabletype>';
the
code
demonstrates .AsFloat and .AsString.
Parameterized queries are especially useful (and can be much faster) if you run the same query, only with
different parameters, in a loop (think e.g. bulk loading of data).
Continuing with our example: after having set up the query SQL and parameters, the transaction is started
(and later on committed) as usual, then the query is run by calling ExecSQL (which does not return a result
set; if the SQL statement were e.g. a SELECT or INSERT...RETURNING that does return data, you would
use Open as in the examples above):
procedure TForm1.SalaryGridValidateEntry(sender: TObject; aCol, aRow: Integer;
const OldValue: string; var NewValue: String);
begin
// Only these cells have min and max salary:
if (aCol=3) and ((aRow=1) or (aRow=2)) then
begin
Pagina 54 din 69
// Allow updates to min and max salary if positive numerical data is

entered
if StrToFloatDef(NewValue,-1)>0 then
begin
// Storing the primary key in e.g. a hidden cell in the grid and using
that in our
// update query would be cleaner, but we can do it the hard way as well:
FQuery.SQL.Text:='update employee set salary=:newsalary '+
' where first_name=:firstname and last_name=:lastname and
salary=:salary ';
FQuery.Params.ParamByName('newsalary').AsFloat:=StrToFloatDef(NewValue,0)
;
FQuery.Params.ParamByName('firstname').AsString:=SalaryGrid.Cells[1,aRow]
;
FQuery.Params.ParamByName('lastname').AsString:=SalaryGrid.Cells[2,aRow];
FQuery.Params.ParamByName('salary').AsFloat:=StrToFloatDef(OldValue,0);
FQuery.ExecSQL;
FTran.Commit;
LoadSalaryGrid; //reload standard deviation
end
else
begin
// Notify user that his input was wrong... he'll be wondering otherwise:
Showmessage('Invalid salary entered.');
NewValue:=OldValue;
end;
end
else
begin
// Silently discard edits to any other cells
NewValue:=OldValue;
end;
end;
Note how we forgot to add a try..except block to this code to nicely catch database errors and display a
sensible error message. If you are running the Firebird sample EMPLOYEE database for this tutorial, try to
change the salary to a very low value (say 1) and see what happens.
Finally, while this example showed an UPDATE SQL query, you could just as well run INSERT queries to
insert new data programmatically. Also, you can use parameters in any kind of SQL query (SELECT,
UPDATE, etc) as long as you use them for fields, not for table/view/procedure names.
Summary
This tutorial explained:
how to code for multiple database types
how to use a login form to decouple db access configuration from your program
how to retrieve and update data programmatically
Code
Since November 2012, the code can be found in $(lazarusdir)examples/database/sqldbtutorial3
If you have an older version (e.g. Lazarus 1.0.2), you can also download the code via the Lazarus SVN
website
Pagina 55 din 69
SQLdb Tutorial4
Contents
1 Introduction
2 Why use datamodules?
3 Getting started
4 Everyone has their own way
5 More uses of data modules
o
5.1 Additional components
o
5.2 Debugging
Introduction
This tutorial is an attempt to demonstrate the use of Lazarus Data Modules to isolate the data access
components of a project from the program logic associated with the access. Such isolation makes program
maintenance and debugging easier.
The tutorial was developed using Windows 7 and SQLite3 as the database, with Lazarus 1.0.8 with FPC
2.6.2; however it should work with earlier versions. Similarly, other DBMS and Operating Systems should
require minimal change, if any.
Why use datamodules?

Simple - after following the Lazarus Tutorials:
SQLdb Tutorial0
SQLdb Tutorial1
SQLdb Tutorial2
SQLdb Tutorial3
developing a 'real' application becomes harder. 'Form1' grows to an exponential size handling the different
Events and Database Queries.
Isolating each table access into a single datamodule makes debugging and maintenance so much easier.
An application may have any number of datamodules - a small application with just one or 2 tables can
probably suffice with just one Datamodule - a larger application could probably benefit from having a data
module for each table or view.
The sample shown here uses just 2 tables with simple queries, but it can be expanded to include more
options with each table.
Getting started
In the Lazarus IDE, create a new Application and then click File --> New --> Data Module
You will be presented with a window as if you selected 'New Form':

The difference is this window/form will only accept
non-visual components. In fact, look at your
Component Palette: it has been greatly reduced to
only allow selection of ONLY non-visual components.
Pagina 56 din 69
Everyone has their own way

Your use of data modules will vary to suit your own needs. but as an example, I have at least 2 data
modules:
Unit: DataModule1 On this module I 'drop' a T*Connection and a TSQLTransaction.
DataModule1 is used as the
connection for all queries.
I then create a DataModuleN for
each table or view.
Each DataModuleN will need to
have the DataModule1 unit (unit2
in this example) added to the
USES clause to connect to the
database.
From here everything is the same
as stated in SQLdb Tutorial1. The
components are connected in the same way and

access is identical.
More uses of data modules

Additional components
In this example, DataModule1 had nothing more than a Connection and Transaction, but in a 'real'
application, this container would typically also hold global non-visual components to be used by the
application.
For example, Load and Save INI settings, TOpenDialog, TSaveDialog, etc. The concept here is to isolate
data access from the business logic of an application. A change in data source for any application is never a
minimal task, but having the datasources isolated will make the change much easier.
Debugging
Debugging a program is also a difficult task. By separating data access and business logic, the code to be
viewed is halved. Data access and business logic can be tested separately to at least halve the problem.
The importance of the DataModule will become even more obvious when developing other applications
using the same database and tables. The data module can of course be reused in the new application.
Pagina 57 din 69
postgres
Contents
1 Overview
2 Direct access to PostgreSQL
3 Zeos
4 SQLDB
o
4.1 Example
o
4.2 Event monitoring
o
4.3 Installation and errors
4.3.1 Windows 64 bit driver
4.3.2 Error: "Can not load PostgreSQL client library "libpq.dll""
4.3.3 Problems clearing parameters

5 PostgreSQL package: the low level units
o
5.1 Requirements
o
5.2 Installation
Overview
You can use Free Pascal/Lazarus to access a PostgreSQL database server. If you are looking for
information
on
the postgres package
in
FPC,
please
see postgres#PostgreSQL_package:_the_low_level_units below.
Advantages of PostgreSQL:
It is very widely used and available
Very stable and has a complete feature set
Liberal license (no costs) in comparison with MySQL

Disadvantage of PostgreSQL:
Some hosters may not offer PostgreSQL)
No embedded version
Win64: please see warning here on not using certain FPC/Lazarus Win64 versions.
Direct access to PostgreSQL

You can connect Lazarus with PostgreSQL by using PostgreSQL Data Access Components (PgDAC). It is a
library of components that provides native connectivity to PostgreSQL from Lazarus (and Free Pascal) on
Windows, Mac OS X, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. PgDAC is
designed to help programmers develop really lightweight, faster and cleaner PostgreSQL database
applications without deploying any additional libraries.
You can download this Lazarus component for free.
Zeos
Zeos supports PostgreSQL; please see ZeosDBO
SQLDB
FPC/Lazarus supports PostgreSQL out of the box with a PostgreSQL connection component/class. If you
are using FPC only or want to manually add PostgreSQL support, add pqconnection to your usesclause.
Otherwise, Lazarus provides a component:
Note: The libpq C client contains some memory leaks (at least up till version 9.3 of Postgres) when a library
is repeatedly loaded/unloaded. SQLDB loads the library when the first connection is made, and unloads it
when the last connection closes. This means that whenever the last connection is closed, a small memory
leak is created. To prevent this from happening (and speed up the application), you can load the library
once at the start of the process with the InitialisePostgres3 call.
The charset property is used for client encoding.
The TPQConnection component does not directly support a Port property, but one can pass the port into the
component via the Params parameter:
PQConnection.Params.Add('port=' + VariableContainingPort);
Other PostgreSQL specific connection parameters can be specified using the Params property:
PQConnection.Params.Add('application_name=''yourappname''')
Example
See SQLdb_Tutorial1 for a tutorial on creating a GUI database-enabled program that is written for
PostgreSQL/SQLDB, as well as SQLite/SQLDB, Firebird/SQLDB, basically any RDBMS SQLDB supports).
Pagina 58 din 69
Event monitoring
If you have FPC2.6.2+ and a recent version of Lazarus, you can use the TPQTEventMonitor component to
monitor events coming from PostgreSQL.
It is a thin wrapper around FPC PQEventMonitor; please see the FPC pqeventstest.pp example programs
for details.
Installation and errors

As with all sqldb units, you need to add your driver libraries (all required PostgreSQL .dll/.manifest files)
to a directory in the (library search) path (e.g. c:\windows\system32 for Windows)
or (Windows) to the program output directory (e.g. lib/something/ in your project directory, and the
project directory
Windows 64 bit driver

If you are developing 64 bit applications, you must use a 64 bit DLL.
Note: FPC 2.6.0 (therefore Lazarus 1.0.4 or lower) does not yet support PostgreSQL on Windows 64 bit (unless
you patch and recompile it).
A Windows 64 driver is fairly hard to find but can be downloaded here: [1]. The driver library can be installed
in c:\windows\system32; 32 bit driver libraries can be installed in the confusingly named
c:\windows\syswow64
Error: "Can not load PostgreSQL client library "libpq.dll""

The program cannot find your PostgreSQL driver files.
See above on instructions where to install the libraries.
A good example that demonstrates how to include drive DLL files when connecting Lazarus with
PostgreSQL under Windows is easyDB.
On Linux/Unix/OSX: make sure the PostgreSQL libraries are in your library search path, e.g.:
On 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 symbolic link from a specific library version to a general library
name:
ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
. Alternatively, install the postgresql client -dev package using your distribution's package manager
Problems clearing parameters

Note: Verify this: is .paramtype really necessary?
At least in FPC <= 2.6.2: if you .Clear a parameter (i.e. set it to NULL), PostgreSQL may have difficulty
recognizing the parameter type.
In that case, explicitly specify the type, e.g.:
FWriteQuery.Params.ParamByName('LONGITUDE').ParamType:=ptInput; //required for
postgresql
FWriteQuery.Params.ParamByName('LONGITUDE').Clear
PostgreSQL package: the low level units

As with all databases, the SQLDB code depends on a lower level PostgreSQL specific unit that wraps
around the PostgreSQL driver library (.so/.dll/.dylib). Normally, you would use the higher-level SQLDB code
as it allows you to code more quickly, easily switch databases etc.
Using this is very easy, all you need to do is compile some units, and use these units in your program. You
need to specify the place of the PostgreSQL client Library (libpq) when compiling, and that is it.
The main unit is called postgres, normally this is the only unit you must include in your uses clause.
Requirements
You need at least version 0.99.5 of Free Pascal (basically any version of FPC except extremely old ones).
The headers are translated from PostgreSQL version 6.3.1.
Installation
The postgres unit comes with the Free Pascal packages, and is distributed together with the compiler. This
contains a directory postgres with the units, a test program and a makefile. cd to the directory and edit the
Makefile to set the variables for your system. You must provide only 1 thing:
1. The directory where the libpq library resides, usually /usr/local/pgsql/lib
Typing
Pagina 59 din 69
make
Should compile the units and the program. If compilation was succesfull, you can install with
make install
(Remember to set the directory where the units should be installed.)
You can then test the program by running
make test
This will:
Run the test program testpg. It is a straightforward pascal translation of the example program in the
PostGreSQL programmers' guide.
Run a script to create a table in a database, and fill it with some data. (the psql program should be
in your PATH for this) . By default, the used database is testdb.
Run the testprogram testemail
Run a shell script again to remove the created table.

You will see a lot of messages on your screen, giving you feedback and results. If something went wrong,
make will inform you of this.
Pagina 60 din 69
TPSQL
TPSQL is a modified-LGPL postgres database package for Lazarus. It defines two components,
TPSQLDatabase and TPSQLDataset, allowing applications to connect to PostgreSQL database servers
over TCP/IP networks.
The download contains the component Pascal files, the Lazarus package file and resource files and the
modified-LGPL license text files.
This component was designed for applications running on Linux and Win32 platforms.
Note: TPSQL is not the same as TPSQLConnection, the PostgreSQL connector that is part of the SQLDB database
components.
Contents
1 Author
2 License
3 Download
4 Change Log
5 Dependencies / System Requirements
6 Installation
7 Usage
Author
Antonio d'Avino
License
Modified LGPL (read COPYING.modifiedLGPL and COPYING.LGPL included in package).
Download
The latest stable release can be found on the Lazarus CCR Files page or on author's web
pages http://infoconsult.homelinux.net.
Change Log
Version 0.4.0 2005/06/01

Version 0.4.1 2005/06/02
ClientEncoding property added to TPSQLDatabase class.
Version 0.4.2 2005/06/03
Some changes to destroy method of TPSQLDatabase/TPSQLDataset for avoiding
exception when closing IDE/project with components in Active/Connected status.
Base path in archived files changed from 'usr/share/lazarus/components/psql' to 'psql'
Version 0.4.6 2005/06/06
Executing queries that doesn't return data column now raises an exception.
commandQuery function added (see Usage section).
beginTransaction/commitTransaction/rollbackTransaction support added (see Usage
section).
More ClientEncoding entities added.
Dependencies / System Requirements
Lazarus 0.9.6 (FPC 1.9.8)

Status:
Stable
Issues:
Tested on Windows (Win2K) and Linux (Mdk 10.1). Database server: PostgreSQL 8.0.3 (running on
both platforms).
Installation
In the lazarus/components directory, untar (unzip) the files from psql-laz-package<version>.tar.gz file. The psql folder will be created.
Open lazarus
Open the package psql_laz.lpk with Component/Open package file (.lpk)
(Click on Compile only if you don't want to install the component into the IDE)
Click on Install and answer 'Yes' when you are asked about Lazarus rebuilding. A new tab
named 'PSQL' will be created in the components palette.
Note: Important for Win32 users. In case you experience some difficults in compiling the Pascal files in
the package, you may need to add the following path: <some-drive>:<some-
Pagina 61 din 69
path>\lazarus\lcl\units\i386-win32(ie: c:\programs\lazarus\lcl\units
\i386-win32) to the 'Other unit files ...' in 'Paths' tab of 'Compiler Options' of the lazarus package
manager window.
Usage
Drop a TPSQLDatabase component on a form, for any different PostgreSQL database your
application needs to connect to. Mandatory property of this component you have to set are:
DatabaseName : The name of the PostgreSQL database that your application needs to
connect to.
HostName : the IP address or the URL of the PC hosting the PostgreSQL server.
UserName : The name of an user having permission to access to host/database.
Optionally you may need to set Password property for correct connection to server. Then, to
activate the connection, you neet to set the Connected property to 'True'. An exception will be
raised if connection failed (wrong parameters, user with no permission to access the
host/database, network errors or PostgreSQL server not active). Set this property to 'False' for
closing connection.
Note about the ClientEncoding property. The ClientEncoding property allows user to set a
character set for the client application different from the one defined for the database. The
PostgreSQL server make a 'translation' between the two sets. However a "ClientEncoding change
failed" exception may be the result of a ClientEncoding property change. This may be due to a
database character set incompatible with the clientencoding you selected. IE, the char set 'LATIN9'
is not compatible with a 'WIN1250'. You select the default char set for the whole database cluster
when you create it with the command 'initdb', using the -E option: IE. 'initdb -E UNICODE'. Also,
you can define a different character set for any database you create (different from the default one
of the whole db cluster), also with the -E option: IE. 'createdb -E UNICODE Test' or
CREATE DATABASE TEST WITH ENCODING 'UNICODE';

(note you must quote the character set name in the 'CREATE DATABASE' command). I suggest to
set UNICODE character set as the default for your db cluster (or for the database you create),
because it is compatible with the whole set of charset available for the client, except the
MULE_INTERNAL.
Now you may drop a TPSQLDataset component on the form for any table connection you need.
The main property to set on this component type is the Database one. A dropdown menu allows
you to select one of any TDatabase descendant component present on form (of course, you must
select a TPSQLDatabase component).
You also need to provide a valid SQL statement in SQL property. Now, you are able to open the
TPSQLDataset, setting to 'True' the Active property. Several exceptions are provided for signaling
failing conditions. Please, refer to TDataSet documentation for infos and examples about using the
TPSQLDataset component in order to access to SQL retrieved data rows as well as conneting to
Data Controls components. However, procedures and functions added to parent class are
explained here:
function commandQuery( query: String ): ShortInt
Use this function for submit queries that doesn't returns data columns, as update queries (ie.
update, insert, delete etc.). Current dataset is not affected, however it will reflect changes made by
execution of commandQuery itself, if it has some influence on current dataset.Function returns 0 if
succeded, -1 if failed. No exceptions are provided.
procedure beginTransaction()
Starts an SQL transaction session. Changes to table may be submitted using the commandQuery
function.
procedure commitTransaction()
Ends an SQL transaction session, committing changes submitted starting from last
beginTransaction execution.
procedure rollbackTransaction()
Ends an SQL transaction session, aborting changes.
Pagina 62 din 69
TSQLConnector
TSQLConnector
is a versatile database connector component for use with any supported database.
The component is found on SQLdb tab of the Component Palette.
To configure database access, most important properties are:
ConnectorType: Firebird, MSSQLServer, MySQL

4.0-MySQL
5.7, ODBC, Oracle, Postgres, SQLite3, Sybase
DatabaseName
Hostname
Password
Transaction
For
any
connector
to
function,
it
should
be
able
to
load
the
right
.dll style drivers are found if they are located in the same directory as the corresponding .exe
drivers.
TSQLTransaction
TSQLTransaction
is a non-visual component that intermediates between a database connection and
a TDataSet derivative like a TSQLQuery.
SQLTransaction1.Database := SQLConnection1;
SQLQuery1.Transaction := SQLTransaction1;
DataSource1.DataSet := SQLQuery1;
DBGrid1.DataSource := DataSource1;
TSQLQuery
TSQLQuery
is a fundamental database query component for use with any supported database. The
component is found on SQLdb tab of the Component Palette.
Typical use of a TSQLQuery:
Select
Insert a new record into table somelist:
SQLQuery1.SQL.Text := 'SELECT * FROM somelist';
SQLTransaction1.Open();
Insert
Insert a new record into table somelist:
SQLQuery1.SQL.Text := 'INSERT
(1231,2)';
SQLQuery1.ExecSQL();
SQLTransaction1.Commit();
INTO
somelist
(ItemNr,ItemCount)
VALUES
TDataSet
TDataSet is the main link to the actual data in a database. A TDataSet descendant acts like a cursor on a
table or query-result.
DataSource1.'''DataSet''' := SQLQuery1;
A dataset (for example a query restult) can be edited:
ds.Edit();
ds.FieldByName('NAME').AsString := 'Edited name';
ds.Post();
Pagina 63 din 69
ds.UpdateMode := upWhereAll;
ds.ApplyUpdates();
TDBNavigator
TDBNavigator
is a navigation control for use with a connected database. It is available from the Data
Controls tab of the Component Palette.
To be used, a TDBNavigator must be linked to a TDataSource component. Depending on TDataSources
state, more or less buttons on the navigator become active.
TDBGrid
TDBGrid
is a visual component that displays tabular data from database contents through means of
a TDataSet derivative like a TSQLQuery. The TDBGrid component is available from the Data Controls
tab of the Component Palette.
// configure connection with database
SQLConnector1.ConnectorType := 'MySQL 5.1';
SQLConnector1.HostName := 'MyServer';
SQLConnector1.DatabaseName := 'MyDBName';
SQLConnector1.UserName := 'MyName';
SQLConnector1.Password := 'MyPass';
// connect SQLConnector, SQLTransaction, DataSource, SQLQuery and DBGrid
SQLTransaction1.Database := SQLConnector1;
// setup query to get (at least) two fields from MyTable
SQLQuery1.SQL := 'SELECT * FROM MyTable';
// setup grid with result from query
DBGrid1.Columns[0].Title.Caption := 'Name';
DBGrid1.Columns[0].FieldName := 'fieldDescription';
DBGrid1.Columns[1].Title.Caption := 'Description';
DBGrid1.Columns[1].FieldName := 'fieldName';
// Open result and show in grid
SQLQuery1.Open();
TDBEdit
TDBEdit
edit control for use with a connected database. It is available from the Data Controls tab of
the Component Palette.
To be used, a TDBEdit must be linked to a TDataSource component. The property DataField determines
what will be displayed from the current row of the datasource.
TDBImage
TDBImage
is a data-bound control that shows images stored in BLOB fields in databases. It is
available from the Data Controls tab of the Component Palette.
Storage
By default, Lazarus stores the binary image data preceded by the file extension to define the file type. This
allows storing multiple image formats at will.
Delphi only allows a single file format (jpg?). Recent Lazarus versions allow this storage format as well and
try to autodetect the image type based on magic data in the beginning of the binary data.
Example
See e.g. the sqlite_mushrooms/image_mushrooms example program in your Lazarus directory
examples\database\
Pagina 64 din 69
SQLdb tab
The SQLdb tab of the Component
with TDataSet and TDataSource.
Icon
Palette contains
non-visible
database
Component
Description
TSQLQuery
SQL query
TSQLTransaction
Transaction
Pagina 65 din 69
connectors
for
use
Icon
Component
Description
TSQLScript
Scripting
TSQLConnector
generic connector
TMSSQLConnection
MS SQL
TSybaseConnection
Sybase
TPQConnection
Postgres
TPQTEventMonitor
Postgres event monitor
TOracleConnection
Oracle
TODBCConnection
ODBC
TMySQL40Connection
MySQL 4.0
TMySQL41Connection
MySQL 4.1
TMySQL50Connection
MySQL 5.0
TMySQL51Connection
MySQL 5.1
TMySQL55Connection
MySQL 5.5
TMySQL56Connection
MySQL 5.6
TSQLite3Connection
SQLite
TIBConnection
InterBase/Firebird
TFBAdmin
Firebird admin
TFBEventMonitor
Firebird Event monitor
TSQLDBLibraryLoader
Library loader
Pagina 66 din 69
TSQLScript
TSQLScript
lets you run a batch of SQL statements/multiple SQL statements in one run. It is useful if
you want to set up a new database or update an existing database schema.
TSQLScript is available for both FPC and Lazarus and runs on any database that SQLdb supports. It can
be found on the SQLdb tab of the Component Palette.
Example
Suppose you have a set of SQL statements like the following DDL dump of a Firebird database script in the
FlameRobin tool (in Database Properties/DDL):
Notice that unlike TSQLQuery, TSQLScript requires a semicolon (;) after each block of commands.
This example assumes you have existing SQL connection and transaction objects set up.
uses
...sqldb, TIBConnection,...
const
DBSchemaFile='dbreporter.sql';
var
FBScript:TSQLScript;
ScriptText:TStringList;
TranWasStarted: boolean;
begin
TranWasStarted:=FTran.Active; //Ftran is the transaction, defined somewhere
in our class
if not TranWasStarted then FTran.StartTransaction;
FBScript:=TSQLScript.Create(nil);
ScriptText:=TStringList.Create;
try
if not fileexists(DBSchemaFile) then
raise Exception.CreateFmt('dbreporter: could not load database schema
file %s',[DBSchemaFile]);
ScriptText.LoadFromFile(DBSchemaFile);
FBScript.DataBase:=(FConn as TIBConnection);
FBScript.Transaction:=FTran;
FBScript.Script:=ScriptText;
// Now everything is loaded in, run all commands at once:
FBScript.Execute;
//... and then commit to make them stick and show them to the SQL that
comes
// after the commit
FTran.Commit;
finally
FBScript.Free;
ScriptText.Free;
end;
// Make sure we leave the transaction state as we found it, handy for
switchnig
// between explicit start/commit transaction and commitretaining:
if TranWasStarted then FTran.StartTransaction;
See also \examples\database\tsqlscript in your Lazarus directory for an example program demonstrating
TSQLScript.
Warning: (At least) FPC 2.6.4 and earlier: TSQLScript will not correctly parse all Firebird DDL, so you
may need to test your script in advance. TSQLScript has been improved in FPC trunk (2.7.1). An
alternative could be to call Firebird's isql executable using e.g. TProcess
Note: Setting the CommentsInSQL property to false may help improve TSQLScript's reliability if- e.g. if you have
comments inside stored procedure declarations.
TSQLExporter
TSQLExporter
is a database export component for use with a TDataSet. It may be found on the Data
Export tab of the Component Palette To export the contents of a TDataSet (for instance the result of
a TSQLQuery correctly
setup
to
link
with
a TSQLConnector and TSQLTransaction)
also FormatSettings.TableName need be provided.
In the example below a a TSynEdit is filled with exported contents of table mytable.
type
myForm = class(TForm)
btnExport: TButton;
myConnector: TSQLConnector;
myExporter: TSQLExporter;
myQuery: TSQLQuery;
myTransaction: TSQLTransaction;
myScriptText: TSynEdit;
mySyntax: TSynSQLSyn;
procedure btnExportClick(Sender: TObject);
private
procedure ExportTable( const tblnam: String );
end;
implementation
procedure myForm.btnExportClick(Sender: TObject);

begin
ExportTable( 'mytable' );
end;
procedure myForm.ExportTable( const tblnam: string );
var
ts: TStringStream;
begin
try
ts := TStringStream.Create( '' );
myTransaction.Database := myConnector;
myQuery.Database := myConnector;
myExporter.DataSet := myQuery;
myQuery.SQL.Text := 'SELECT * FROM ' + tblnam;
myQuery.Open();
with myExporter.FormatSettings do begin
TableName :=
tblnam;
insert statements
DateFormat :=
'YYYY-MM-DD';
DateTimeFormat := 'YYYY-MM-DD hh:mm:ss';
end;
myExporter.ExportToStream( ts );
myScriptText.Text := ts.DataString;
myQuery.Close();
finally
ts.Free();
end;
end;
// exporter uses tablename in

// SQL date format
//
//show formatted script text

Lazarus Databases

Uploaded by

Copyright:

Available Formats

Lazarus Databases

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lazarus Databases

Uploaded by

Copyright:

Available Formats

Databases

3.7.1 After selecting all records of the table

3.7.2 Selecting only the desired record

10.1 and greater

i386: Linux, Win32

III+, IV, VII

i386: Linux, Win32

2.0, 2.5, 3.0 (not

i386: Linux, Win32

FPC 2.6.2+. Linux, OSX,

i386: Linux, Win32

i386: Linux, Win32

i386: Linux, Win32

The bindings to the database clients

Using datasets from code

Use the TField descendants to:

Access general information about fields

Access the fields of a TDataset descendant by using either:

The fields property, eg Fields[0] is the first field,

Using the visual (data-aware) controls

usUnmodified: Record is unmodified

usModified: Record exists in the database but is locally modified

usDeleted: Record exists in the database, but is locally deleted

Post and Cancel

Inserting a new record

How to quickly jump to 1 particular record in the table

Selecting only the desired record

TMemDataset does not support .Filter

the other components on the Data Export tab

Single Field Controls

DBText control Displays a text field (readonly, no border)

DBEdit control Displays / edits a text field as an edit box

DBMemo control Displays / edits a text field in a multi-line edit box

DBCheckBox control Displays / edits a boolean field by checking/clearing a check box

DBCalendar control Displays / edits a date field using a calendar panel

Add a new record (equivalent to a dataset.insert method call)

Put the dataset into edit mode

Post or Cancel current changes

Refresh the data (useful in multiuser database applications)

Running FPC database tests

Database packages contained in Lazarus

This package provides access to different databases. These include:

Microsoft SQL Server (except on Lazarus/FPC x64 for Windows)

Oracle (except on Lazarus/FPC x64 for Windows)

any database that has an ODBC driver.

External packages / libraries

Pascal Data Objects

MySQL 4.1 and 5.0

sqlite-2 and sqlite-3

interbase-5, interbase-6, firebird-1.0, firebird-1.5, firebird-1.5E, firebird-2.0, firebird-2.0E

mssql (Microsoft library) and sybase (FreeTDS library)

FBLib Firebird Library

Direct Access to Firebird 1.0.x, 1.5.x and 2.x Classic or SuperServer

Automatic select client library 'fbclient' or 'gds32'

Query with params

Support SQL Dialect 1/3

Simple Script Parser

Only 100-150 KB added into final EXE

Support BLOB Fields