Empty bytea values on 9.2.1

[Nils Gösche]

Hi!

On an 8.4 installation, I have a table with a bytea column. In some rows, the
value of this column has length 0. When I query that table with a normal SELECT,
using the ODBC driver from C#, I get a byte[] object of length 0 on those rows.

So far so good.

Now, I dumped the database and restored it on a 9.2.1 installation. The tables
look the same on the new installation: Zero length of the bytea values in some
columns. However, if I try to retrieve such rows with the exact same program as
above, with the same ODBC driver, same connection string except the host name,
suddenly I get a byte[] object of length 1! And the one byte in that array
appears to be random!

At first, I thought it might be a Postgres bug. I wrote a simple program that
uses Npgsql to retrieve those rows, however, the behavior wasn't reproducible
anymore that way. It occurs only if I use ODBC. So, I suspect there is something
in the ODBC driver causing this.

Can anybody else confirm this?

Regards,
--
Nils Gösche
"Don't ask for whom the <CTRL-G> tolls."




--
Sent via pgsql-odbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

[Tom Lane]

=?utf-8?Q?Nils_G=C3=B6sche?= <car...@cartan.de> writes:
> On an 8.4 installation, I have a table with a bytea column. In some rows, the
> value of this column has length 0. When I query that table with a normal SELECT,
> using the ODBC driver from C#, I get a byte[] object of length 0 on those rows.

> So far so good.

> Now, I dumped the database and restored it on a 9.2.1 installation.

9.0 and later default to showing bytea output in "hex" format. I wonder
whether you are using a version of the ODBC code that handles that.
If not, you many need to set "bytea_output" to "escape".

regards, tom lane

[Nils Gösche]

Tom wrote:

> 9.0 and later default to showing bytea output in "hex" format. I
> wonder whether you are using a version of the ODBC code that handles
> that.

I am using version 9.01.02.00 which is rather recent, I think.

> If not, you many need to set "bytea_output" to "escape".

Good idea: That fixes it!

Interestingly, longer bytea values seem to work fine even without
setting bytea_output to 'escape'! It's only the empty arrays that
seem to make problems.

Regards,
--
Nils Gösche
"Don't ask for whom the <CTRL-G> tolls."

[Tom Lane]

=?UTF-8?Q?Nils_G=C3=B6sche?= <car...@cartan.de> writes:
> Interestingly, longer bytea values seem to work fine even without
> setting bytea_output to 'escape'! It's only the empty arrays that
> seem to make problems.

That suggests that ODBC is doing something wrong with just '\x' ...

regards, tom lane