missing escapes for bytea columns

[jwiechmann]

I had tested a table with a "raw" column.

SQL> create table tt (c raw(100));
Table created.
SQL> insert into tt (c) values (hextoraw ('ABBAFF'));
1 row created.
SQL> select dump(c), c from tt;

DUMP(C)

C

Typ=23 Len=3: 171,186,255
ABBAFF

The tool ora2pg results a table file and a copy file.

ora2pg -t TABLE -c x.conf -o TABLE.sql

-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4
SET client_encoding TO 'LATIN1';

\set ON_ERROR_STOP ON

CREATE TABLE tt (
c bytea
);

ora2pg -t COPY -c x.conf -o COPY.sql

-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4

SET client_encoding TO 'LATIN1';
\set ON_ERROR_STOP ON
TRUNCATE TABLE tt;
BEGIN;
COPY tt (c) FROM STDIN;
ABBAFF
.
COMMIT;

In the COPY statement I am missing the escapes of the binary output like this: \xABBAFF
If I import this without the escapes in a postgresql DB 9.x the result is a string "ABBAFF" and
not the hex bytes 0xAB 0xBA 0xFF.

select * from tt;

   c        

---

\x414242

With the escapes this works correct:
COPY tt (c) FROM STDIN;
\xABBAFF
.

select * from tt;

c     

---

\xabbaff
(1 row)

[darold]

Hi

Thanks for the report. Do you have the same issue with BLOB ?

[darold]

Fixed for RAW datatype in commit 199a4b5, please use latest code from github. Normally BLOB should not have the same issue because they are not returned as HEX string. Let us know if you have any other problem with that.

Regards,

[jwiechmann]

Hi,
I had tested this with a raw column only (no BLOB).

Best regards
Jens

From: Darold [mailto:notifications@github.com]
Sent: Mittwoch, 26. Februar 2014 10:34
To: darold/ora2pg
Cc: Jens Wiechmann
Subject: Re: [ora2pg] missing escapes for bytea columns (#52)

Hi

Thanks for the report. Do you have the same issue with BLOB ?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/52#issuecomment-36106811.

[jwiechmann]

Hi,
thanks for the quick response.
Here is the result of the latest sources from the github:
-- Generated by Ora2Pg, the Oracle database Schema converter, version 12.1
:
SET client_encoding TO 'LATIN1';
\set ON_ERROR_STOP ON

BEGIN;
TRUNCATE TABLE tt;

COPY tt (c) FROM STDIN;
\xABBAFF
.

COMMIT;

But a second backslash is missing. I see \x but not \x
The wrong result is this:

select * from tt;

  c       

---

\xab42414646
(1 row)
Only the first two byte are correct.

[darold]

Right, fixed in commit d4b1c8c

[jwiechmann]

It works now. Thank you.