В одном из проектов понадобилось получить доступ к БД PostgreSQL из Delphi и, в частности, читать BLOB поля из этой БД. Разумеется, дабы не изобретать велосипед, решил поискать готовые компоненты. Нашлось два решения:
Производительность была ключевым моментом, поэтому я решил их сравнить.
Тестирование производилось на базе PostgreSQL 8.2.5, в обоих компонентах использовалась dll той же версии. Предварительно запрос был выполнен пару раз, для того, чтобы база его закэшировала.
В скобках указано усреднённое значение для следующих трёх попыток, которые были выполнены без закрытия программы. Запрос был вида «select * from table», где table — таблица с 450 тысячами неодинаковых записей, не обработанная с помощью vacuum. Позиционирование представляло из себя код:
Query.First; repeat Query.Next; until Query.Eof;
Результаты:
PostgresDAC Соединение с базой: 170 мс (65 мс)*. Выполнение запроса: 5900 мс (5900 мс). Позиционирование: 4150 мс (4150 мс)
ZeosLib Соединение с базой: 60 мс (60 мс). Выполнение запроса: 5200 мс (5200 мс). Позиционирование: 8100 мс (1900 мс)
Но самое интересное, как оказалось, ждало меня дальше. При попытке получить данные из BLOB поля, оба компонента возвращали nil. Недолгие и несложные эксперименты показали, что так происходит при превышении Binary Object’ом размера в 20 килобайт.
Гугл проблему решить не помог, пришлось браться за напильник самому. Я конвертировал libpq.h из поставки PostgreSQL в pas, при помощи замечательной утилиты и стал проверять.
Действительно, стандартная функция PQexec отдавала nil вместо данных. Проблема решилась только использованием асинхронного запроса.
Вот такой код, со стандартными функциями из libpq.dll, получает из базы поле BLOB любой длинны (разумеется, для этого нужен unit с определёнными функциями и предварительно установленное соединение myConnection):
PS: Первый мой топик на хабре. Надеюсь, написаное действительно кому-нибудь поможет.
PPS: Ах да, есть ещё способ доступа через ADO, но он по всем показателям где-то в 2-2,5 раза медленнее Zeos’а и DAC’а. Да и искал я именно специализированные компоненты, так что в тестах ADO не присутствует.
Connect to PostgreSQL (FireDAC)
This topic describes how to connect to PostgreSQL.
Contents
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:
If the PostgreSQL client library has not been installed properly, an exception is raised when you try to connect:
OS X Client Software
the libpq.dylib x86 client library.
It comes pre-installed on OS X or can be installed separately (more).
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:
On Ubuntu Server 16.04 LTS, run:
Driver Linkage
To link the driver:
drop a TFDPhysPgDriverLink component from the «FireDAC Links» palette page
or include the FireDAC.Phys.PG unit in a uses clause.
Additional Setup
If an application is using the 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:
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).
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= [; ].
Connect to PostgreSQL (FireDAC)
This topic describes how to connect to PostgreSQL.
Contents
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:
If the PostgreSQL client library has not been installed properly, an exception is raised when you try to connect:
OS X Client Software
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:
On Ubuntu Server 16.04 LTS, run:
Driver Linkage
To link the driver:
drop a TFDPhysPgDriverLink component from the «FireDAC Links» palette page
or include the FireDAC.Phys.PG unit in a uses clause.
Additional Setup
If an application is using the 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:
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).
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= [; ].
DataBind Controls to PostgreSQL Data in Delphi
Ready to get started?
Download for a free trial:
The PostgreSQL ODBC Driver is a powerful tool that allows you to connect with live PostgreSQL data, directly from any applications that support ODBC connectivity.
Access PostgreSQL databases from virtually anywhere through a standard ODBC Driver interface.
DataBind to PostgreSQL data in Delphi with standard data access components and controls.
The CData ODBC Driver for PostgreSQL supports the ODBC standard to enable integration of live PostgreSQL data with visual form designers and other rapid development tools in Delphi. The ODBC driver simplifies data access strategies for applications that share a single codebase like Delphi by providing a single API for database development. This article shows how to how to connect to PostgreSQL data and query data from a simple visual component library (VCL) application, as well as from Delphi code.
Create a FireDAC Connection to PostgreSQL Data
If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.
To connect to PostgreSQL, set the Server, Port (the default port is 5432), and Database connection properties and set the User and Password you wish to use to authenticate to the server. If the Database property is not specified, the data provider connects to the user’s default database.
You can then follow the steps below to use the Data Explorer to create a FireDAC connection to PostgreSQL data.
In a new VCL Forms application, expand the FireDAC node in the Data Explorer.
Right-click the ODBC Data Source node in the Data Explorer.
Click Add New Connection.
Enter a name for the connection.
In the FireDAC Connection Editor that appears, set the DataSource property to the name of the ODBC DSN for PostgreSQL.
Create VCL Applications with Connectivity to PostgreSQL Data
Follow the procedure below to start executing queries to PostgreSQL data from a simple VCL application that displays the results of a query in a grid.
Drop a TFDConnection component onto the form and set the following properties:
ConnectionDefName: Select the FireDAC connection to PostgreSQL data.
Connected: Select True from the menu and, in the dialog that appears, enter your credentials.
Drop a TFDQuery component onto the form and set the properties below:
Connection: Set this property to the TFDConnection component, if this component is not already specified.
SQL: Click the button in the SQL property and enter a query. For example:
SELECT ShipName, ShipCity FROM Orders
Active: Set this property to true.
Drop a TDataSource component onto the form and set the following property:
DataSet: In the menu for this property, select the name of the TFDQuery component.
Drop a TDBGrid control onto the form and set the following property:
DataSource: Select the name of the TDataSource.
Drop a TFDGUIxWaitCursor onto the form — this is required to avoid a run-time error.
Execute Commands to PostgreSQL with FireDAC Components
You can use the TFDConnection and TFQuery components to execute queries to PostgreSQL data. This section provides PostgreSQL data-specific examples of executing queries with the TFQuery component.
Connect to PostgreSQL Data
To connect to the data source, set the Connected property of the TFDConnection component to true. You can set the same properties from code:
Parameterized resources can improve performance: Preparing statements is costly in system resources and time. The connection must be active and open while a statement is prepared. By default, FireDAC prepares the query to avoid recompiling the same query over and over. To disable statement preparation, set ResourceOptions.DirectExecute to True; for example, when you need to execute a query only once.
Execute a Query
To execute a query that returns a result set, such as a select query, use the Open method. The Open method executes the query, returns the result set, and opens it. The Open method will return an error if the query does not produce a result set.
FDQuery1.Open(‘select * from Orders where ShipCountry = :ShipCountry’,[‘USA’]);
To execute a query that does not return a result set, such as a delete, use the ExecSQL method. The ExecSQL method will return an error if the query returns a result set. To retrieve the count of affected rows, use the TFD.RowsAffected property.
FDQuery1.ExecSQL(‘delete from Orders where := FDQuery1.RowsAffected;
CData Software is a leading provider of data access and connectivity solutions. Our standards-based connectors streamline data access and insulate customers from the complexities of integrating with on-premise or cloud databases, SaaS, APIs, NoSQL, and Big Data.
