Re: Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote:
> In one of our applications, we use the dbi_link for communication with a firebird db,
> works very well in version 8.3 we have one of our PostgreSQL server (CentOS 5.3).
> We are doing tests for migration to version 9.4 or 9.1, and the use of tests dbi_link got the following errors:
>
> dbi_fortes = # SELECT "NAME" FROM ag. "CLI";
> WARNING: SELECT dbi_link.cache_connection (1) at line 12.
> CONTEXT: PL / Perl function "remote_select"
> ERROR: invalid byte sequence for encoding "LATIN1": 0x00 at line 198.
> CONTEXT: PL / Perl function "remote_select"
>
> Originally the db was SQL_ASCII but was migrated to use LATIN1, and the same problem occurs when
> using the original encoding (SQL_ASCII).
>
> Using the query to collect just one of the linked table fields, "dbi_fortes = # SELECT * FROM
> dbi_link.remote_select (1, 'SELECT NAME FROM CLI':: text) remote_select (" NAME "text) LIMIT 10;"
> it returns without no problem.
>
> We think the field of this table that is causing the error, and it contains NULL values.

Do you really mean null values or do you mean zero bytes?
The latter would fit in with the error message.

> Using "isql" I can usually return the data.
>
> questions:
>
> 1. which may have changed from version 8.3/8.4 (works well) to version 9.* which can cause this kind
> of incompatibility?

Probably this commit:
http://archives.postgresql.org/pgsql-committers/2010-01/msg00028.php

> 2. does anyone know of any bug dbi_link about it?

The error comes from the server, not from DBI-Link -- also, the fact that
it works on 8.4 and not in 9.0 points in the direction that DBI-Link is not
at fault.

> 3. Is there any other tool similar to dbi_link use?
>
> 4. Something else that can help me about it?

It worked in older PostgreSQL versions because they did not check for
incorrect values well enough. A zero byte (0x00) is not a valid character
in PostgreSQL in any encoding.

The only option I can think of is to fix the data in the orignal database,
if that is an option.

Yours,
Laurenz Albe