Retrieving BLOB and CLOB columns

[timjohnc...@gmail.com]

I am a new user to JOOQ but have a question about retrieving BLOB and CLOB columns (I understand that there is not full support for LOBs, and that's fine)

When I query a table with a CLOB column

ResultQuery<Record> q = // setup a query

for (Result<Record> r : q.fetch()) {

    java.sql.Clob c = r.getValue("CLOB_COLUMN", java.sql.Clob.class);

}

I get an error telling me that oracle.sql.CLOB cannot be converted to java.sql.Clob. But oracle.sql.CLOB implements java.sql.Clob.

In the ConvertAll class if after all the checks have been performed before failing the conversion could you check if the two types are compatible with Class.isAssignableFrom(Class) ?

I can just retrieve the value as an Object for the time being and cast it myself to java.sql.Clob but feels like the conversion system should handle this case for me.

Thanks,

Tim

[Lukas Eder]

Hi Tim,

2013/6/19 <timjohnc...@gmail.com>

I am a new user to JOOQ but have a question about retrieving BLOB and CLOB columns (I understand that there is not full support for LOBs, and that's fine)

When I query a table with a CLOB column

ResultQuery<Record> q = // setup a query

for (Result<Record> r : q.fetch()) {

    java.sql.Clob c = r.getValue("CLOB_COLUMN", java.sql.Clob.class);

}

I get an error telling me that oracle.sql.CLOB cannot be converted to java.sql.Clob. But oracle.sql.CLOB implements java.sql.Clob.

So, in order to get an oracle.sql.CLOB type, I suspect that you have used "plain SQL" to generate your query?

ResultQuery<Record> q = // setup a query with plain SQL?

 

That case indeed isn't covered by integration tests, which it should. I have registered #2534 for this:

https://github.com/jOOQ/jOOQ/issues/2534

Given the fact that jOOQ currently doesn't expose actual LOB types, this is probably a bug. I'll have to see how this could be fixed.

In the ConvertAll class if after all the checks have been performed before failing the conversion could you check if the two types are compatible with Class.isAssignableFrom(Class) ?

Yes, that's a good idea, too. In fact, this check could be made very early. I have registered #2535 for this:

https://github.com/jOOQ/jOOQ/issues/2535

 

I can just retrieve the value as an Object for the time being and cast it myself to java.sql.Clob but feels like the conversion system should handle this case for me.

Absolutely! Thanks for reporting

Cheers

Lukas

[Lukas Eder]

2013/6/20 Lukas Eder <lukas...@gmail.com>

Hi Tim,

2013/6/19 <timjohnc...@gmail.com>

I am a new user to JOOQ but have a question about retrieving BLOB and CLOB columns (I understand that there is not full support for LOBs, and that's fine)

When I query a table with a CLOB column

ResultQuery<Record> q = // setup a query

for (Result<Record> r : q.fetch()) {

    java.sql.Clob c = r.getValue("CLOB_COLUMN", java.sql.Clob.class);

}

I get an error telling me that oracle.sql.CLOB cannot be converted to java.sql.Clob. But oracle.sql.CLOB implements java.sql.Clob.

So, in order to get an oracle.sql.CLOB type, I suspect that you have used "plain SQL" to generate your query?

ResultQuery<Record> q = // setup a query with plain SQL?

 

That case indeed isn't covered by integration tests, which it should. I have registered #2534 for this:

https://github.com/jOOQ/jOOQ/issues/2534

Given the fact that jOOQ currently doesn't expose actual LOB types, this is probably a bug. I'll have to see how this could be fixed.

In the ConvertAll class if after all the checks have been performed before failing the conversion could you check if the two types are compatible with Class.isAssignableFrom(Class) ?

Yes, that's a good idea, too. In fact, this check could be made very early. I have registered #2535 for this:

https://github.com/jOOQ/jOOQ/issues/2535

#2535 is implemented in Github master and will be available in the next 3.1.0-SNAPSHOT, today:

https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

Cheers

Lukas

[Tim Carpenter]

Hi Lucas,

Yes, the Query is created using "plain SQL", DSL.field("column_name") etc

Thanks a lot for putting the in the casting fix so quickly.

Cheers,

Tim

[Lukas Eder]

Hi Tim,

I've added a failing integration test for your issue:

https://github.com/jOOQ/jOOQ/commit/753d5f367d61d347f75963b5efa9a918df19e90e

I have to check how this can be fixed, as LOB types shouldn't leak through jOOQ, even when using plain SQL. See also the other related discussion here:

https://groups.google.com/forum/?fromgroups#!topic/jooq-user/W_3leQ7pZFs

Upcasting should now be possible via the 3.1 fix #2535:

https://github.com/jOOQ/jOOQ/issues/2535

As a workaround, you can let jOOQ transform LOBs into String / byte[] by specifying:

DSL.field("clob_column", String.class)

DSL.field("clob_column", byte[].class)

Cheers

Lukas

2013/6/27 Tim Carpenter <timjohnc...@gmail.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.
 
 

[Lukas Eder]

Hello,

This is now fixed in jOOQ 3.2. When no type information is available, jOOQ will try to "unlob" all LOBs fetched from ResultSet.getObject() and return byte[] or String values for consistency with the rest of the API.

This change is committed and will be pushed to GitHub master in a larger push.

Cheers

Lukas

2013/7/4 Lukas Eder <lukas...@gmail.com>