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

[Madhu Mohan]

Hi Lukas,

I am facing the same problem here.

JOOQ output:

"data1": "[***@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": "[***@518d0d6b"

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

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)?

Hello,
thanks for your quick reply.
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
--
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
For more options, visit https://groups.google.com/groups/opt_out.

[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

Post by Madhu Mohan
Hi Lukas,
I am facing the same problem here.
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';
[startDate:null, description:ABC-123456789, isBundled:null,
externalId:null, endDate:null, taxCode:TX123]
But with JOOQ output is as below.
If I use record.get(TABLE1.data).toString() then the output is a huge
hexadecimal or something else.
Please advise.

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)?

Hello,
thanks for your quick reply.
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
--
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
For more options, visit https://groups.google.com/groups/opt_out.

--

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
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.

Post by 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

Post by Madhu Mohan
Hi Lukas,
I am facing the same problem here.
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';
[startDate:null, description:ABC-123456789, isBundled:null,
externalId:null, endDate:null, taxCode:TX123]
But with JOOQ output is as below.
If I use record.get(TABLE1.data).toString() then the output is a huge
hexadecimal or something else.
Please advise.

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)?

Hello,
thanks for your quick reply.
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
--
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
For more options, visit https://groups.google.com/groups/opt_out.

--

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
For more options, visit https://groups.google.com/d/optout.

--
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
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Madhu,

Post by Madhu Mohan
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.
[startDate:null, description:ABC-123456789, isBundled:null,
externalId:null, endDate:null, taxCode:TX123]

I see, thanks for clarifying

Post by Madhu Mohan
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)

Post by Madhu Mohan
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