Delphi + PostgreSQL

In one of the projects, it was necessary to get access to the PostgreSQL database from Delphi and, in particular, to read BLOB fields from this database. Of course, in order not to reinvent the wheel, I decided to look for ready-made components. There were two solutions:



-free. ZeosLib .

- paid. PostgresDAC .



Performance was key, so I decided to compare them.

')

Testing was based on PostgreSQL 8.2.5, dll of the same version was used in both components. Previously, the request was executed a couple of times, in order for the database to be cached.



The brackets indicate the average value for the next three attempts that were made without closing the program. The request was of the form “select * from table”, where table is a table with 450 thousand unequal records, not processed by vacuum. Positioning is the code:



Query.First;

repeat

Query.Next;

until Query.Eof;



Results:



PostgresDAC

Connection with base: 170 ms (65 ms) *.

Query execution: 5900 ms (5900 ms).

Positioning: 4150 ms (4150 ms)



Zeoslib

Connection with base: 60 ms (60 ms).

Query execution: 5200 ms (5200 ms).

Positioning: 8100 ms (1900 ms)



But the most interesting, as it turned out, was waiting for me further.

When trying to get data from BLOB fields, both components returned nil. Short-lived and simple experiments have shown that this happens when the Binary Object exceeds the size of 20 kilobytes.



Google did not help solve the problem, I had to take on the file myself. I converted libpq.h from the PostgreSQL delivery to pas, with the help of a wonderful utility and started checking.



Indeed, the standard PQexec function gave nil instead of data. The problem was solved only using an asynchronous request.



This code, with standard functions from libpq.dll, receives a BLOB field of any length from the database (of course, this requires a unit with certain functions and a pre-established myConnection connection):



PQsendQuery(myonnection, pchar(myQuery));

myResult:=PQGetResult(myConnection);

buf:=PQunescapeBytea(PQgetvalue(myResult, 0, 0), resultKey);





PS: My first topic on Habré. I hope that writing really helps someone.



PPS: Oh yes, there is another way of access through ADO, but by all indications it is somewhere 2-2.5 times slower than Zeos and DAC. Yes, and I was looking for exactly specialized components, so there is no ADO in the tests.