Thread: 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

Emanuel Araújo wrote:
> found that the problem occurs when the dbi_link makes parsing of a field float / double to a text
> field, because when it creates the tables already created with this type of data.

I do not understand that.

> Another thing we see is that the problem is not with null fields or zero bytes but with a value of
> 1.5, no problem in NULLs or Zero Bytes.
>
> Based on that there is any solution?

What I would do is check how the data look in Perl.
Build a simple Perl script that selects the problem data and display them byte for byte.

That should help understand the problem.

Yours,
Laurenz Albe

Emanuel Araújo wrote:
> The field where the problem occurs is of type float or double, when extracting data from firebird, it
> creates the tables materialized as type text. There is problem with null values ​​or zero bytes. There
> are three records with value "1.5" and are those records that generates the error.

>> What I would do is check how the data look in Perl.
>> Build a simple Perl script that selects the problem data and display them byte for byte.
>>
>> That should help understand the problem.

> I will try

Another possibility is to use the 8.4 installation where things work
and display the actual content with something like:

SELECT encode(col, 'escape') FROM tab WHERE ...

That should show any zero bytes.

Yours,
Laurenz Albe