Postgres bytea (BLOB) is read as hex instead of byte[]

[mva...@googlemail.com]

Hello,

I want to write a byte array into a 'bytea' table field.
This is the BLOB type of Postgres.

Writing the binary content seems to work properly as the fields content length is correct.
I write using 'map.put(t.BINARYDATA,<bytearray>)' and the proper jooq sql statement.

But when the record is read from the database the data is not given back the same way it was when writing it.

Using 'bytes = record.getValue(t.BINARYDATA);' gives the bytes in hex-format.
Eg. 'System.out.println(new String(bytes));' prints out:

'I504e470d0a1a0a0000000d4948445200000004000000040802000000269309290000000467414d410000b18f0bfc610...'

Did I miss something or is this a problem with Postgres?

Greetings

Mike

[mva...@googlemail.com]

Ok, we changed a property in the Postgres database so that the output is returned as 'escape' instead of 'hex'.
'Hex' is the default in Prosgres 9.

We did:

'SET bytea_output = 'escape';'

Now it works as expected.

Greetings from Dresden

[Lukas Eder]

Hello,

Is there anything you think should be done from the jOOQ side? By default, jOOQ uses bind variables, and the encoding of byte arrays shouldn't be an issue. Are you inlining bind values? If so, can you provide a reproducible test case?

Regards,

Lukas

2014-01-29 <mva...@googlemail.com>

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[mva...@googlemail.com]

Hello,

thanks for your quick reply.

I just do:

Result<Record> records = context.select(<list of fields>).from(DOCUMENTSTABLE).where(DOCUMENTSTABLE.IDELEMENT.eq(filters.getElementId())).fetch();

And then I read the field's values with record.getValue(DOCUMENTSTABLE.<tablefield>) for every field.

The problem there was, that the returned value of the BLOB (bytea) field was much larger than when writing the data.
I write and read it as byte array.
It turned out that the content of the read byte array was made of hex numbers (in text format) instead of bytes containing the original data.

Maybe there is a way to detect the returned type of data for postgres' 'bytea' datatype from version 9 on.

Greetings

Mike

[Lukas Eder]

That's really curious. jOOQ does not do any magic here. Just a plain simple call to JDBC's ResultSet.getBytes(). Do you get the same, funny behaviour when running your query through plain JDBC and accessing the byte array using ResultSet.getBytes(int)?

2014-01-29 <mva...@googlemail.com>

[Madhu Mohan]

Hi Lukas,

I am facing the same problem here.

JOOQ output:

      "data1": "[B@518d0d6b"

But the actual data when I query postgres database

select encode(table1.data1, 'escape') as data  from table1 where id = '03681e16-278d-4e11-bfae-5f8740cd751b';

SQL output:

[startDate:null, description:ABC-123456789, isBundled:null, externalId:null, endDate:null, taxCode:TX123]

But with JOOQ output is as below.

"data1": "[B@518d0d6b"

If I use record.get(TABLE1.data).toString() then the output is a huge hexadecimal or something else.

Please advise.

[Lukas Eder]

Hi Madhu,

I'm confused by your examples.

- Your SQL query projects only one column: "data"

- Your "SQL output" shows a few random columns, all of which are not "data"

- Your jOOQ output is "data1", which is not the result of your encoding, but seems to be the original table1.data1 column

So, I'd say from your examples, everything seems to work as expected, no?

Cheers,

Lukas

For more options, visit https://groups.google.com/d/optout.

[Madhu Mohan]

Hi Lukas,

Thanks for your reply, Sorry for the confusion.

Expected output from JOOQ should be as follows. data1 column is of type "BYTEA", so it contains the following value as a whole. Following is not the values of multiple columns.

Data1 contains:

[startDate:null, description:ABC-123456789, isBundled:null, externalId:null, endDate:null, taxCode:TX123]  

I tried with Field<String> DATA1 = create.field("encode(data, 'escape'), String.class")

But getting an error from JOOQ.

[Lukas Eder]

Madhu,

On Wed, Nov 14, 2018 at 1:35 AM Madhu Mohan <gude.ma...@gmail.com> wrote:

Expected output from JOOQ should be as follows. data1 column is of type "BYTEA", so it contains the following value as a whole. Following is not the values of multiple columns.

Data1 contains:

[startDate:null, description:ABC-123456789, isBundled:null, externalId:null, endDate:null, taxCode:TX123]  

I see, thanks for clarifying

 

I tried with Field<String> DATA1 = create.field("encode(data, 'escape'), String.class")

There three two things here (from what I can tell):

1. create is probably DSLContext, but there's no field method on DSLContext. It is on DSL

2. Your column is called data1, not data

3. The String.class argument should not be part of the plain SQL template, but a second argument to the field() method: DSL.field("encode(data1, 'escape')", String.class)

 

But getting an error from JOOQ.

There could be many reasons for the error to happen. So far, I'm just looking at a fairly incomplete set of examples, so it's difficult for me to spot the error(s). You didn't even post the error you were getting in your second attempt. Just "an error" isn't something I can really help you solving :)

As a general rule of thumb, the more thorough your problem description, the easier it will be for anyone else to help you. This includes you yourself, as by being thorough in describing a problem, you might stumble upon the solution yourself. I like Stack Overflow's description of an MCVE (Minimal Complete Verifiable Example), which is also very helpful when troubleshooting:

https://stackoverflow.com/help/mcve

I'll be happy to provide further help if I can

Cheers,

Lukas