Zeoslib Portal

I am using a 7.1 alpha version that seems to be working quite well for me.
Recently we have tried to upgrade to PostgreSQL 9.3. Everything works well, except for the jpg that my software stores in a bytea field. It can be stored without an error message, but I get jpg error 53 when I try to view it.
While trying to get more info, I backed up a database created in PostgreSQL 8.1, and restored it in 9.3. The jpg could be viewed with no error message. Therefore, I think the problem occurs when putting the jpg into the database.
I did a bug report on the PostgreSQL web site, and found that it is not actually a bug, but a known change. Prior to PostgreSQL 9.1, the standard_conforming_strings setting defaulted to OFF. In 9.1 and later, it defaults to ON. I think that is what I need to change, but I am not completely sure how I do it in Zeos.
My guess is that I have to add a line to the properties of the connection:
standard_conforming_strings=OFF
However, I just tried this, and when I tried to run the project I got an EZSQLException error: Parameter "standard_conforming_strings" cannot be changed. So how do I change this setting? (Maybe this setting can only be changed in PostgreSQL 9.1 and higher?)

The PostgreSQL documentation says that ON is more like the rest of the world, and there was another suggestion. This was to set bytea_output = escape. Would this be more appropriate? The first suggestion seems like it is definitely backward compatible. (My users must be able to backup an 8.1 database and restore it in 9.x and still view the jpg.) I am not so sure about this 2nd solution. If it is "better", how would I change it?

EgonHugeist, you have helped me in the past -- I know this is a simple question for you, and thanks in advance for your help!

Hi,

PostgreSQL 9.0 introduced bytea_output=hex (as default also), prior to that version only bytea_output=escape was supported.

If you using libpq.dll from postgres 8.4 it will support only byte_output=escape.
Either change your libpq.dll version, or change bytea_output to escape, so it will be properly supported by your version of libpq.dll.

Best regards,
Marek

I checked the version of libpq.dll. It is the correct version. I assume this is because it is installed as part of the PostgreSQL 9.3 installation.
I tried adding the property bytea_output=escape to the connection object, and it did not cause an error, but it did not help.

I am pretty sure the problem happens when I put the jpg into the database record. The reason I say this is, if I backup a database that already has the jpg in it, and then restore it in the PostgreSQL 9.3 server, I can view the jpg just fine. It is only when I store the jpg in the database in the 9.3 server that the problem occurs.

I am using Delphi to store the jpg into the database, so I am not exactly sure how to deal with whatever the different format is. I use the following code to store the jpg:
---------
var
Jpg: TJPEGImage;
BlobField : TBlobField;
MemStream :TMemoryStream;
begin
...
BlobField := TBlobField(zqTests.FieldByName('graph'));
Jpg.SaveToStream(MemStream);
MemStream.Position := 0;
BlobField.LoadFromStream(MemStream);
MemStream.Free;
...
----------
Maybe I need to do something different with the way I load the blobfield? Is there a property I need to set on the jpg or blobfield?

I figured out the solution, and am posting it in case anyone else has this problem.
I need to "set standard_conforming_strings=off". I can run this statement in an SQLProcessor object.
This statement will cause an error if it is run on a PostgreSQL server earlier than version 9.
If you use the SQL "SELECT Version()" to get the server version, it gets you a whole version string that you have to parse for the number.
I found the following statement on the internet to get just the major version. It parses it for me.
"SELECT substring(version() FROM $$(\d+)\.\d+\.\d+$$) AS major;"
The result is text, so I have to convert it to an integer.
I would have liked to have done the following statement. It works in pgAdmin III, but fails in Zeos, because it does not like the ":".
"SELECT substring(version() FROM $$(\d+)\.\d+\.\d+$$)::smallint AS major;"

joycekai,

I would have liked to have done the following statement. It works in pgAdmin III, but fails in Zeos, because it does not like the ":".
"SELECT substring(version() FROM $$(\d+)\.\d+\.\d+$$)::smallint AS major;"

You can also change ":" in Query.ParamChar to another.

Michal

Hello joycekai,

the :: seems to be a way of casting. This query should work as expected too:
SELECT cast(substring(version() FROM $$(\d+)\.\d+\.\d+$$) as smallint) AS major
Best regards,

Jan