Installation6
Usage7
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-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.
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 drivers.
.dll style drivers are found if they are located in the same directory as the corresponding .exe
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:
SQLTransaction1.Database := SQLConnection1;
SQLQuery1.Transaction := SQLTransaction1;
DataSource1.DataSet := SQLQuery1;
DBGrid1.DataSource := DataSource1;
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 INTO somelist (ItemNr,ItemCount) VALUES (1231,2)';
SQLQuery1.ExecSQL();
SQLTransaction1.Commit();
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.
SQLTransaction1.Database := SQLConnection1;
SQLQuery1.Transaction := SQLTransaction1;
DataSource1.'''DataSet''' := SQLQuery1;
DBGrid1.DataSource := DataSource1;
A dataset (for example a query restult) can be edited:
ds.Edit();
ds.FieldByName('NAME').AsString := 'Edited name';
ds.Post();
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;
SQLQuery1.Transaction := SQLTransaction1;
DataSource1.DataSet := SQLQuery1;
DBGrid1.DataSource := DataSource1;
// 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\