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.

Client Software

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.

Linux Client Software

To install the PostgreSQL client library:

  1. On Ubuntu Server 16.04 LTS, run:
    sudo apt-get install libpq5
    sudo ln -s /usr/lib/x86_64-linux-gnu/libpq.so.5 /usr/lib/x86_64-linux-gnu/libpq.so
    
  2. On Red Hat Enterprise Linux 7, run:
    sudo yum install postgresql-libs
    sudo ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so
    

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
Pooled Enables connection pooling for this connection definition. The default value is False. To use pooled connection, the connection definition must be persistent or private. True
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.
MonitorBy Specifies the trace / monitor output for this connection definition. One of the following values can be specified:
  • FlatFile. The trace output is sent to a text file. To enable this, include TFDMoniFlatFileClientLink in your application and set its Tracing property to True. FireDAC shows the name of a trace file after an application is finished.
  • Remote. The trace output is sent to the FDMonitor utility in order to help you monitor your application. To enable this, include TFDMoniRemoteClientLink in your application and set its Tracing property to True. FDMonitor must be run before the connection is established.
  • Custom. The trace output is sent to the TFDMoniCustomClientLink.OnOutput event handler. To enable this, include TFDMoniCustomClientLink in your application and set its Tracing property to True.
FlatFile
Server The TCP/IP address or hostname of the server running a PostgreSQL server. 127.0.0.1
Port The TCP/IP port on which the PostgreSQL server is listening. 5432
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 30
CharacterSet The default character set for the connection. For details, see the Character Set Support chapter. WIN1251
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 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 equals MetaDefSchema. Setting MetaDefSchema does not change the current schema search path in the PostgreSQL session. 'public' is the default value. MySchema
MetaCurSchema Specifies the current schema for the application. If it is not specified, then its value is received from the DBMS. If an application asks for metadata and you do not specify a schema name, then FireDAC implicitly uses the current schema. If MetaCurSchema is '*', then the schema name is omitted from the metadata parameters. *
GUIDEndian It defines how the GUID value is represented on the client. 'Little' is the default value. Big
FastFetchMode Fetchs the records optimization goal:
  • Choose - let FD choose what the appropriate optimization goal is. This is the default value.
  • Default - FD does not optimize anything. This is the mode before the change.
  • All - FD always optimizes resultset fetching to finish collecting all records.
  • First - FD always optimizes resultset fetching to finish collecting the first record.
All

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