Re: UpdateQuery.addValue(Clob)

[Lukas Eder]

Hello Espen,
Long time no see! I had quite a good trip to Thailand where I was thinking about 300 new jOOQ features ;-) just kidding.

I have analysed this issue reported by James and I came to the conclusion, that there is probably a misuse of JDBC's java.sql.Blob and java.sql.Clob data types in org.jooq.util.h2.H2DataType. jOOQ does not really support reading and writing actual Blob's and Clob's. Instead, jOOQ tries to use the JDBC drivers' "automatic mapping support" BLOB to byte[] and CLOB to String, where such a mapping is implemented by the JDBC driver. As I pointed out to James, in any case, this is more user-friendly (no manual freeing resources, etc) and works quite well, if the BLOBs and CLOBs are reasonably large.

I will correct this issue as of ticket #230:
https://sourceforge.net/apps/trac/jooq/ticket/230

Since this problem only seems to occur with the H2 database integration, I wanted to check with you, whether you had anything in mind when adding support for java.sql.Clob and java.sql.Blob? Maybe I am missing something, or maybe we should have true support for those data types?

Thanks for some short feedback!
Cheers
Lukas

2011/1/31 Lukas Eder <lukas...@gmail.com>

Hi James,

There are java.sql.Clob and java.sql.Blob interfaces that would be the standard mapping in JDBC. This is useful only for very large clob's and blob's. Usually it's easier to use String and byte[] directly. I'll be back from vacation in a week, so I'll try to see what is causing the datatype mismatch between MySQL and H2 generated source code.

Thanks for your feedback. I'll keep jOOQ evolving at the same pace in 2011, as I can see that JPA's criteria query is still not progressing into a similar direction... even with JPA 2.1, criteria query will remain quite verbose and far from SQL. Another solution you might want to check out is QueryDSL. It is quite active as well, and similar to jOOQ.

If you have some time: I'm curious about the problems you've experienced when inheriting from the jOOQ classes. What was the use-case that caused problems? Which classes did you try to inherit from?

Cheers
Lukas

2011/1/25 James Moger <james...@gmail.com>

I think your approach of mapping Clob and Blob to String and byte [] is the way to go.  Actually, not being an SQL expert, I'm not sure what else they would map to.

I haven't tried any of your proposed changes - mostly because of the jOOQ class hierarchy requirement.  I was trying to fit this into an existing project where I already have dozens of model classes that I serialize and pass between VMs.  Inheriting from the jOOQ classes is problematic for me.  I like that jOOQ is active and making progress where things like JaQu seem to be inactive... but with JaQu all I need is an interface dependency.  I've stopped jOOQ eval - and actually also stopped JaQu eval at the moment as more pressing projects have come to the foreground.

I'll keep lurking around jOOQ and see what evolves.  :)

Thanks for your help.

-J

[Espen Strømsnes]

Hi Lukas,

Welcome back from what sounds like a nice vacation. I could use some warmer temperatures myself. It's been a cold winter so far.

I haven't been as active as I wanted to the last couple of weeks. I'm in the middle of switching jobs, When it comes to the java.sql.Blob and java.sql.Clob datatypes I didn't have anything special in mind when choosing those types. As far as I'm concerned you may just replace these with byte[] and String.

Regards,

Espen

2011/2/3 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

OK, nice. I have added a ticket for true BLOB / CLOB support that could be added later, in case that is needed by jOOQ users:
http://sourceforge.net/apps/trac/jooq/ticket/231

In most cases, they can behave like byte[] and String. But some (most?) RDBMS do not support grouping or selecting distinct over BLOB / CLOB columns, so there will be a minor need to correct that.

Cheers
Lukas

2011/2/9 Espen Strømsnes <estro...@gmail.com>