Working with BLOBs

[digulla]

Hi Lukas,

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.

Regards,

A. Digulla

[Lukas Eder]

Hi Aaron,

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.

Cheers
Lukas

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

[Lukas Eder]

Hello Peter,

If the only problem is binding variables to an
OraclePreparedStatement, you might try to implement a CustomField for
your bind value:

http://www.jooq.org/doc/3.0/manual/sql-building/queryparts/custom-queryparts

With an appropriate API, you should then be able to do:

create.insertInto(TABLE, TABLE.ID, TABLE.BLOB)
.values(1, new MyBlob(byteArray))
.execute();

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.

Cheers
Lukas

2013/4/2 <bizi...@gmail.com>:
> Hi both,
>
> 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,
> Peter

[Lukas Eder]

Hi Peter,

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.

Cheers
Lukas

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

[biziclop]

Of course and thanks for the suggestion.

I'm going to try the CustomField workaround and report back on the results.

[biziclop]

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.

Peter

[Lukas Eder]

Hi Peter,

2013/4/12 biziclop <bizi...@gmail.com>:

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

Thanks for the feedback.

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

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

Cheers
Lukas

[biziclop]

Hi Lukas,

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.

Peter

[Lukas Eder]

Hi Peter,

2013/4/13 biziclop <bizi...@gmail.com>:

> Hi Lukas,
>
> 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.

Yes, if it's not too much effort, I'd be curious to know more about
this problem.

Cheers
Lukas

[robert...@gmail.com]

Any update on this?

[Lukas Eder]

Hi Robert,

Thanks for your message. The discussion is from 2013 - I suspect that it might be quite likely it is outdated. Would you mind creating a new discussion on the user group with details on what you're currently looking for?

Thanks,

Lukas

2017-07-21 1:21 GMT+02:00 <robert...@gmail.com>:

Any update on this?

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