Difference between revisions of "postgres"

From Free Pascal wiki
Jump to navigationJump to search
(Added example; still needs editing)
m (Fixed syntax highlighting)
 
(47 intermediate revisions by 10 users not shown)
Line 1: Line 1:
 +
{{postgres}}
 +
 +
{{Infobox databases}}
 +
 
== Overview ==  
 
== Overview ==  
  
You can use Free Pascal/Lazarus to access a PostgreSQL database server. If you are looking for information on the ''mysql'' package in FPC, please see [[postgres#PostgreSQL_package:_the_low_level_units]] below.
+
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:
 
Advantages of PostgreSQL:
 
* It is very widely used and available
 
* It is very widely used and available
 
* Very stable and has a complete feature set
 
* Very stable and has a complete feature set
 +
* Liberal license (no costs) in comparison with MySQL
  
 
Disadvantage of PostgreSQL:
 
Disadvantage of PostgreSQL:
 
* Some hosters may not offer PostgreSQL)
 
* Some hosters may not offer PostgreSQL)
 
* No embedded version
 
* No embedded version
 +
 +
Win64: please see warning [[Windows Programming Tips#FPC 2.6.x/Lazarus 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 [https://www.devart.com/pgdac/download.html Lazarus component] for free.
 +
 +
==Zeos==
 +
 +
Zeos supports PostgreSQL; please see [[ZeosDBO]]
  
 
==SQLDB==
 
==SQLDB==
Lazarus SQLDB has a PostgreSQL connection component; FPC has a corresponding connection class.
+
 
 +
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 ''uses'' clause. Otherwise, Lazarus provides a component:
 +
 
 +
[[Image:sqldbcomponents.png‎]]
 +
 
 +
'''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:
 +
 
 +
<syntaxhighlight lang=pascal>PQConnection.Params.Add('port=' + VariableContainingPort);</syntaxhighlight>
 +
 
 +
Also other PostgreSQL specific connection parameters can be specified using the Params property:
 +
<syntaxhighlight lang=pascal>PQConnection.Params.Add('application_name=''yourappname''')</syntaxhighlight>
 +
For all supported connection parameters see: [https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS Connection Parameter Key Words]
  
 
===Example===
 
===Example===
''BigChimp: todo: this was taken from the DB FAQ; it needs to be checked and modified. In general, we'd better move the entire section to a PostgreSQL specific page as it is way to big''
 
the component doesn't support the following connection options:
 
* Port
 
* ClientEncoding
 
Maybe there are other unsupported connection parameters.
 
  
The TPQConnection component does not directly support a Port property, but one can pass the port into the component via the Params parameter:
+
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).
 +
 
 +
===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: [http://www.enterprisedb.com/products-services-training/pgdownload#windows].
 +
The driver library can be installed in c:\windows\system32; 32 bit driver libraries can be installed in the confusingly named c:\windows\syswow64
  
<syntaxhighlight>PQConnection.Params.Add('port=' + VariableContainingPort);</syntaxhighlight>
+
==== Error: "Can not load PostgreSQL client library "libpq.dll"" ====
  
Also other connection parameters can be specified using Params property:
+
The program cannot find your PostgreSQL driver files.
<syntaxhighlight>PQConnection.Params.Add('application_name=''yourappname''')</syntaxhighlight>
 
''BigChimp: end extract from DB FAQ''
 
  
This is a very short tutorial to get Lazarus 0.9.12 or later to connect to a PostgreSQL database, local or remote, using TPQConnection. This component is added to Lazarus when the package lazarus/components/sqldb/sqldblaz.lpk is installed.
+
See above on instructions where to install the libraries.
  
After correct install, follow these steps:
+
A good example that demonstrates how to include drive DLL files when connecting Lazarus with PostgreSQL under Windows is [http://paginas.fe.up.pt/~asousa/wiki/doku.php?id=proj:lazarus_projects#easy_db easyDB].
  
* Place a PQConnection    from the SQLdb tab
+
On Linux/Unix/OSX: make sure the PostgreSQL libraries are in your library search path, e.g.:
* Place a SQLQuery        from the SQLdb tab
+
* 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
* Place a SQLTransaction  from the SQLdb tab
+
* It may be necessary to create a symbolic link from a specific library version to a general library name: <syntaxhighlight lang="bash">ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so</syntaxhighlight>. Alternatively, install the postgresql client -dev package using your distribution's package manager
* Place a DataSource      from the DataAccess tab
 
* Place a DBGrid          from the DataControls tab
 
  
* In the PQConnection fill in:
+
==== Problems clearing parameters ====
** transaction property with the respective SQLTransaction object
 
** Database name
 
** HostName
 
** UserName + password
 
  
* Check that the SQLTransaction was automatically changed to point to the PQConnection
+
{{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 the SQLQuery fill in:
+
In that case, explicitly specify the type, e.g.:
** transaction property with the respective object
 
** database property with respective object
 
** SQL (something like 'select * from anytable')
 
  
* In the DataSource object fill in the DataSet property with the SQLQuery object
+
<syntaxhighlight lang=pascal>
 +
FWriteQuery.Params.ParamByName('LONGITUDE').ParamType:=ptInput; //required for postgresql
 +
FWriteQuery.Params.ParamByName('LONGITUDE').Clear
 +
</syntaxhighlight>
  
* In the DBGrid fill in the datasource as the DataSource Object
+
==How To==
  
Turn everything to connected and active and the DBGrid should be filled in design time.
+
===Use keyword RETURNING for values===
TDBText and TDBEdit seem to work but (for me) they only _show_ _data_.
 
  
To change contents in the database, I called the DB Engine direct with the following code:
+
====Get Last Inserted ID====
  
<syntaxhighlight>try
+
With PostGres there is no need to run a second query to get the last inserted ID.
  sql:= 'UPDATE table SET setting=1';
 
  PQDataBase.Connected:=True;
 
  PQDataBase.ExecuteDirect('Begin Work;');
 
  PQDataBase.ExecuteDirect(sql);
 
  PQDataBase.ExecuteDirect('Commit Work;');
 
  PQDataBase.Connected:=False;
 
except
 
  on E : EDatabaseError do
 
    MemoLog.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
 
  on E : Exception do
 
    MemoLog.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
 
end;</syntaxhighlight>
 
  
* Notes:
+
Use INSERT RETURNING and read the value:
** Tested on windows, Lazarus 0.9.12 + PgSQL 8.3.1
 
** Some tests in linux, Lazarus 0.9.12 and PgSQL 8.0.x
 
  
 +
<syntaxhighlight lang=pascal>
 +
var
 +
  ID: Integer
 +
..
 +
Query.SQL.Text:= 'INSERT INTO myschema.films(film_name)' +
 +
                'VALUES(:film_name) RETURNING film_id;';
 +
Query.Open;
 +
ID:= Query.FieldByName('film_id').AsInteger;
 +
</syntaxhighlight>
  
* Instalation and errors:
+
====Get multiple fields and expressions====
** In the tested version of Lazarus .12, fields of type "text" and "numeric" have bugs
 
** I used with no problems char fixed size, int and float8
 
** Sometimes restarting Lazarus solves stupid errors...
 
** After some errors, the transactions remain active and should be deactivated mannually
 
** Changes made in Lazarus are of course not visible until transaction commited
 
** The integrated debugger seems buggy (at least in windows) - sometimes running outside of the IDE may help to find errors
 
** In linux certain error messages are printed in the console -- run your program in the command line, sometimes there is some extra useful debugging info
 
** Error: "Can not load Postgresql client. Is it installed (libpq.so) ?"
 
*** Add the path to seach libpq* from the PostgreSQL installation.
 
*** In linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
 
*** It may be necessary to create a symboloic link ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
 
***  In windows, add these libs anywhere in the Path environment variable or project dir
 
***  I windows, I copied all the DLLs in my C:\Program Files\PostgreSQL\8.1\bin dir to another dir in the PATH
 
***  Or add this postgres\bin dir to the path
 
  
A good example about connecting Lazarus with PostgreSQL under Windows is [http://paginas.fe.up.pt/~asousa/wiki/doku.php?id=proj:lazarus_projects#easy_db easyDB].
+
RETURNING in PostgreSQL is more flexible than most database engines. It works with INSERT, UPDATE, and DELETE statements and can be any list of fields, constants, or expressions that would be found in a SELECT list.
 +
<syntaxhighlight lang="SQL">
 +
INSERT INTO films (film_name) VALUES ('val') RETURNING film_id;  -- Returns id's for newly created rows.
 +
INSERT INTO films (film_name) VALUES ('val') RETURNING film_id, kind;  -- Returns id and kind fields in newly created rows.
 +
INSERT INTO films (film_name) VALUES ('val') RETURNING *; -- Returns all fields in newly created rows.
 +
   
 +
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id; -- Returns id's of updated rows.
 +
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id, film_name; -- Returns id and film names of updated rows.
 +
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING *; -- Returns all fields of updated rows.
 +
   
 +
DELETE FROM films RETURNING film_id; --Returns id's of deleted rows.
 +
DELETE FROM films RETURNING film_id, film_name; --Returns id's and film names of deleted rows.
 +
DELETE FROM films RETURNING *;  -- Returns all fields of deleted rows. </syntaxhighlight>
  
 +
RETURNING using fields, constants, and expressions:
 +
<syntaxhighlight lang="SQL">
 +
INSERT INTO films (film_name) VALUES ('val') RETURNING id, 'a' AS a, id*2 AS doubled_id, CASE WHEN id > 100 THEN 'a' ELSE 'b' END AS foo;
 +
</syntaxhighlight>
  
 
==PostgreSQL package: the low level units==
 
==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). Normally, you would use the higher-level SQLDB code as it allows you to code more quickly, easily switch databases etc.
+
 
 +
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.
 
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.
Line 107: Line 149:
  
 
=== Requirements ===
 
=== 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.
+
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 ===
 
=== Installation ===
The prostgres 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:
+
 
 +
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:
  
 
# The directory where the libpq library resides, usually /usr/local/pgsql/lib  
 
# The directory where the libpq library resides, usually /usr/local/pgsql/lib  
Line 141: Line 184:
 
==See also==
 
==See also==
  
 +
* [[TPSQL]] - A different set of PostgreSQL dataset/database components
 
* [[Lazarus DB Faq]] - More about database programming
 
* [[Lazarus DB Faq]] - More about database programming
 
* [[Lazarus Database Tutorial]] - Getting Lazarus working with various databases
 
* [[Lazarus Database Tutorial]] - Getting Lazarus working with various databases
 
 
[[Category:Databases]]
 
[[Category:FPC]]
 
[[Category:Lazarus]]
 

Latest revision as of 07:42, 23 February 2020

Deutsch (de) English (en) español (es) français (fr)

Databases portal

References:

Tutorials/practical articles:

Databases

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

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 uses clause. Otherwise, Lazarus provides a component:

sqldbcomponents.png

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);

Also other PostgreSQL specific connection parameters can be specified using the Params property:

PQConnection.Params.Add('application_name=''yourappname''')

For all supported connection parameters see: Connection Parameter Key Words

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).

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.

Light bulb  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

Light bulb  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

How To

Use keyword RETURNING for values

Get Last Inserted ID

With PostGres there is no need to run a second query to get the last inserted ID.

Use INSERT RETURNING and read the value:

var 
  ID: Integer
..
Query.SQL.Text:= 'INSERT INTO myschema.films(film_name)' +
                 'VALUES(:film_name) RETURNING film_id;';
Query.Open;
ID:= Query.FieldByName('film_id').AsInteger;

Get multiple fields and expressions

RETURNING in PostgreSQL is more flexible than most database engines. It works with INSERT, UPDATE, and DELETE statements and can be any list of fields, constants, or expressions that would be found in a SELECT list.

INSERT INTO films (film_name) VALUES ('val') RETURNING film_id;  -- Returns id's for newly created rows.
INSERT INTO films (film_name) VALUES ('val') RETURNING film_id, kind;  -- Returns id and kind fields in newly created rows.
INSERT INTO films (film_name) VALUES ('val') RETURNING *; -- Returns all fields in newly created rows.
     
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id; -- Returns id's of updated rows.
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING film_id, film_name; -- Returns id and film names of updated rows.
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama' RETURNING *; -- Returns all fields of updated rows.
     
DELETE FROM films RETURNING film_id; --Returns id's of deleted rows.
DELETE FROM films RETURNING film_id, film_name; --Returns id's and film names of deleted rows.
DELETE FROM films RETURNING *;  -- Returns all fields of deleted rows.

RETURNING using fields, constants, and expressions:

INSERT INTO films (film_name) VALUES ('val') RETURNING id, 'a' AS a, id*2 AS doubled_id, CASE WHEN id > 100 THEN 'a' ELSE 'b' END AS foo;

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

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.

Go back to Packages List

See also