bytea value is read incorrectly using protocol version 2

cbt · Post by **cbt** » Mon 31 Oct 2011 14:16

Hi,

I am using dotConnect PostgreSQL version 5.50.228.0 against PostgreSQL 9.0.5 (on a Windows box).

I have several bytea fields that are not read correctly when using protocol=2 on the connection string. If I change to protocol=3 it seems to work.

On my machine I am able to set the bytea_output = 'escape' setting in postgresql.conf file to make it work using protocol=2, but this is not the case on another developer machine (running PostgreSQL 9.0.4). On this machine the bytea_output setting has no effect.

Both PostgreSQL versions are 32 bit.

I also have several PostgreSQL 9.0.3 instances running on Ubuntu and they have the same problem. Using protocol 2 gives problems reading the bytea fields using dotConnect driver.

I would like to be able to continue using protocol 2 AND avoid to modify the postgresql.conf to make it work. Also this does not even seem to work in all cases (as explained above).

So the questions are:

1) Is there a bug in the driver when using protocol 2 and having bytea_output = 'hex' in the config file? It should still be able to read the bytea value correct.

2) What are the fundamental differences between protocol 2 and 3? It seems like this is very difficult to get an answer for. I have only found this link: http://www.devart.com/dotconnect/univer ... irect.html and it does not explain this in details, other than saying that protocol 2 should be used if multiple queries in a single statement is needed.

Thanks,

Best regards,
Claus Thomsen

Pinturiccio · Post by **Pinturiccio** » Wed 02 Nov 2011 17:28

The protocol 2 is a text protocol. Everything that you will send to a server and receive from it will be in text format. The protocol 3 is a binary protocol. Protocol 3 also can work with multiple queries with the "UnpreparedExecute=true;" connection string parameter. The performance of the protocol 3 is better than the one of the protocol 2 when working with BLOBs. Why don't you use protocol 3?

We have reproduced the issue with ByteA using protocol 2 and byteA_output="hex". We will investigate it and notify you about the results as soon as possible.

You can set byteA_output="hex" only for one connection by using PgSqlCommand "SET bytea_output = escape". But I am not sure that it will help you if configure postgresql.conf.

cbt · Post by **cbt** » Mon 07 Nov 2011 08:18

Hi,

Thank you for the explanation of the difference between protocol 2 and 3.

The reason that we are using protocol 2 is that we experienced other problems with protocol 3, specifically when using array types with large datasets.

See: http://www.devart.com/forums/viewtopic.php?t=21882 for a description of this issue. This issue was never really solved, since it was not possible for us to supply you guys with an example for reproducing the problem.

The running solution is to use protocol 2.

I will try executing "bytea_output = 'escape';" and see if this will work on all environments.

Br,
Claus

Pinturiccio · Post by **Pinturiccio** » Thu 10 Nov 2011 08:59

We have fixed the bug with byteA type when using protocol 2. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

cbt · Post by **cbt** » Fri 11 Nov 2011 12:53

That sounds good.

Do you have an estimate of when you will release a new version?

Best regards,
Claus

Pinturiccio · Post by **Pinturiccio** » Fri 11 Nov 2011 14:28

New build of dotConnect for Oracle 5.50.244 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only). For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22545