Show: Delphi C++
Display Preferences

Connect to PostgreSQL (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to PostgreSQL.

Supported Versions

The FireDAC native driver supports PostgreSQL Server and PostgreSQL Advanced Server version 7.4 and later, because it requires a PG protocol 3.0 connection.

Windows Client Software

FireDAC requires the LIBPQ.DLL x86 or x64 client library for connecting to the PostgreSQL server. Using libpq.dll also requires the "Microsoft Visual C++ 2010 Redistributable Package" installed. You can get this package from http://www.microsoft.com/en-us/download/details.aspx?id=8328. Ideally, the libpq.dll version should be equal to the server version. The full set of the v 9.0 client files:

  • libpq.dll
  • ssleay32.dll
  • libeay32.dll
  • libintl-8.dll
  • libiconv-2.dll

You can take them from the server (details) installation Bin folder and place them in:

  • a folder listed in your PATH environment variable
  • your application EXE folder

Alternatively you may put the required files in any other folder, and specify their path in FDDrivers.ini:

[PG]
VendorLib=<folder>\libpq.dll

If the PostgreSQL client library has not been installed properly, an exception is raised when you try to connect:

[FireDAC][Phys][PGSQL]-314. Cannot load vendor library [LIBPQ.DLL]. The specified module could not be found. Check [LIBPQ.DLL], which is located in one of the PATH directories or in application EXE directory.

or

The ordinal Nnn could not be located in the dynamic link library SSLEAY32.dll.

OS X Client Software

FireDAC requires:

  • the libpq.dylib x86 client library.

It comes pre-installed on OS X or can be installed separately (more).

iOS Client Software

The article (more) explains how to build libpq.dylib for iOS.

Note, Embarcadero Technologies has not tested this and does not provide assistance with it.

Driver Linkage

To link the driver:

Additional Setup

If an application is using the {TIMESTAMPDIFF(MONTH, ....)} escape function, then we recommend that you create 3 PostgreSQL functions with the following types of arguments:

  • DATE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

The function template:

CREATE OR REPLACE FUNCTION MONTHS_BETWEEN (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS
$body$
DECLARE
  mes INTEGER;
  mes1 INTEGER;
  ano INTEGER;
BEGIN
  mes=EXTRACT(MONTH FROM (age($1,$2)));
  ano=EXTRACT(YEAR FROM (age($1,$2)));
  mes1=ABS((ano*12) + mes);
  RETURN mes1;
END
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Connection Definition Parameters

To connect to a PostgreSQL DBMS, most applications require that you specify DriverID, Server, Database, User_Name, Password, and CharacterSet (see Defining Connection (FireDAC) for details).

DriverID=PG

Parameter Description Example value
Server The TCP/IP address or host name of the server running a PostgreSQL server. 127.0.0.1
Port The TCP/IP port on which PostgreSQL server is listening. 5432
Database Name of the current database for the connection. If the Database is not specified, no current database is set up. MyDB
User_Name The PostgreSQL user ID. postgres
Password The PostgreSQL user password.
CharacterSet The default character set for the connection. For details, see the Character Set Support chapter. WIN1251
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 30
ExtendedMetadata

Controls the extended description of the query result sets:

  • True - FireDAC describes a result set to get all the possible column attributes - is nullable, is auto incrementing, to which domain it belongs, etc. Setting this option to True, slightly slows down a dataset opening.
  • False - FireDAC uses the restricted information about the query columns (default).
False
OidAsBlob

Controls the interpretation of an OID column in a table:

  • No -- an OID column is a dtUInt32 column (contains unsigned integer values).
  • Yes -- an OID column is a dtHBlob column (contains Large Object values).
  • Choose -- if a query selects data from the dictionary tables or a column that is not of a LO, LargeObject or BLOB domain, then an OID column is a dtUInt32 one, otherwise - a dtHBlob one. The ExtendedMetadata option must be True to get a column domain (default).
Yes
UnknownFormat

Controls the handling of an unknown PostgreSQL data type:

  • Error -- raises the exception "Cannot describe type" (default).
  • BYTEA -- represents as a BLOB value.
BYTEA
ArrayScanSample Determines whether the constrained arrays are mapped to ftArray or ftDataSet.

To specify this connection parameter use ArrayScanSample=<RowsToScan>[;<DefaultArraySize>].

It performs as follows:

  • If specified, FireDAC analyzes the PostgreSQL array-type fields to get their constraints and define them as ftArray. First, FireDAC scans <RowsToScan> rows to get the array size. If all rows has NULL value or empty array for the array field, then FireDAC uses <DefaultArraySize> as the default array size. The default value is 5.
  • Otherwise the fields will be defined as ftDataSet. This includes when:
    • ArrayScanSample is not specified (default).
    • ArrayScanSample is specified but <RowsToScan> is zero.
    • ArrayScanSample is specified but the evaluated array size is zero.
ApplicationName Name of the application. If specified, this value is stored in the pg_stat_activity table, application_name column. AllBooks
PGAdvanced Additional PostgreSQL server connection options. For details, see the Database Connection Control Functions chapter, PQconnectdb paragraph. A value format is - <option>=<value>[;...].
MetaDefSchema Default schema name. The design time code excludes the catalog name from the object name if it is equal to MetaDefSchema. Setting MetaDefSchema does not change the current schema search path in the PostgreSQL session. 'public' is the default value. MySchema

Use Cases

  • Connect to a server running locally, listening on the default (5432) port:
DriverID=PG
Database=addemo
  • Connect to a remote server, listening on a non-default port, using Unicode for character data:
DriverID=PG
Server=pgsrv
Port=5433
Database=addemo
CharacterSet=utf8
MetaDefSchema=MySchema
ExtendedMetadata=True

See Also

Personal tools
In other languages
Previous Versions
Assistance