|Working with BLOBs||digulla||3/27/13 8:20 AM|
The question came up on SO: http://stackoverflow.com/questions/15642421/b-clob-handling-in-jooq
I posted some code, maybe you can have a look at it.
|Re: Working with BLOBs||Lukas Eder||3/28/13 12:00 AM|
Yes, I had noticed that question. Thanks a lot for answering it.
Indeed JDBC's Blob/Clob handling doesn't really fit well into jOOQ's
type management philosophy. It is far from trivial to get this right,
as InputStream / OutputStream lifecycles may not match that of a query
execution. In other words, fetching a LOB without immediately
consuming it might compromise jOOQ's model of when to "release()" a
used connection - to use the term from recent discussions.
Nonetheless, the current situation is not satisfying, as ignoring LOB
existence isn't a good long-term strategy for jOOQ :-) I'm very open
to suggestions and implementation ideas. When I have time, I'll also
comment on that question.
2013/3/27 digulla <adig...@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.
|Re: Working with BLOBs||Biziclop||4/2/13 5:10 AM|
Thanks for taking the time to try to deal with my problem.
To add a bit of an extra detail to it, I'd want to write and query reasonably large (between 1-16 megabytes) values in an Oracle database but only one at a time. The values are stored in byte arrays or Strings, so I don't really need the stream semantics of standard LOB handling.
Now querying isn't a problem, as ResultSet.getBytes() seems to work fine for any size values but
Due to these limitations I thought about using OraclePreparedStatement.setBytesForBlob() to bind my values for write operations but there's no extension point I can find where I could do this. So as a limited (but still fairly generic) solution I thought about some kind of extension mechanism in the binding process, maybe an interface called 'SelfBindingValue', with a single method bindSelf( PreparedStatement ps, SQLDialect d) that is called by DefaultBindContext.bindValue0().
Or is there an obvious flaw in this idea?
Thanks in advance,
|Re: Working with BLOBs||Lukas Eder||4/2/13 5:55 AM|
If the only problem is binding variables to an
OraclePreparedStatement, you might try to implement a CustomField for
your bind value:
With an appropriate API, you should then be able to do:
create.insertInto(TABLE, TABLE.ID, TABLE.BLOB)
.values(1, new MyBlob(byteArray))
Note that you might need to do some tricks to extract the actual
OraclePreparedStatement from the Statement provided by jOOQ.
Otherwise, just wrap your byte in a ByteArrayInputStream, and pass
it to PreparedStatement.setBinaryStream
Let me know how that works.
|Re: Working with BLOBs||Lukas Eder||4/2/13 12:42 PM|
Just a short note that I forgot to mention:
Do note that my suggestion would be a temporary workaround. In the
long run, I think that jOOQ should be able to handle those "special"
cases of larger LOBs as well, and do the work for you.
2013/4/2 Lukas Eder <lukas...@gmail.com>:
|Re: Working with BLOBs||Biziclop||4/2/13 1:04 PM|
Of course and thanks for the suggestion.
I'm going to try the CustomField workaround and report back on the results.
|Re: Working with BLOBs||Biziclop||4/12/13 12:24 PM|
It seems from my test that CustomFields binding LOBs as a binary/character stream work perfectly for this purpose (i.e. if you want to insert the contents of a byte array into a BLOB or a String into a CLOB), with Oracle 11g at least.
As an interesting aside, regular inserts and updates do seem to work even without a workaround (up to the size of data I needed) but merge statements fail without it.
|Re: Working with BLOBs||Lukas Eder||4/13/13 4:03 AM|
2013/4/12 biziclop <bizi...@gmail.com>:
> It seems from my test that CustomFields binding LOBs as a binary/characterThanks for the feedback.
Interesting, I didn't know about this MERGE statement behaviour. Are
you using any stored procedures, VARRAY / TABLE types, or OBJECT types
(or combinations thereof)?
|Re: Working with BLOBs||Biziclop||4/13/13 12:17 PM|
No, nothing fancy, just a simple table with two columns, one is a varchar2 (for id), the other is a clob and I'm trying to merge a Java String object into the clob. So it's really the simplest setup and I get an "ORA-01461 can bind a LONG value only for insert into a LONG column" with it.
I can send you more details on how to reproduce this next week if you're interested.
|Re: Working with BLOBs||Lukas Eder||4/14/13 10:20 AM|
2013/4/13 biziclop <bizi...@gmail.com>:
> Hi Lukas,Yes, if it's not too much effort, I'd be curious to know more about