Working with BLOBs

Showing 1-9 of 9 messages
Working with BLOBs digulla 3/27/13 8:20 AM
Hi Lukas,


I posted some code, maybe you can have a look at it.

Regards,

A. Digulla
Re: Working with BLOBs Lukas Eder 3/28/13 12:00 AM
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.
>
>
Re: Working with BLOBs Lukas Eder 4/2/13 5:55 AM
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
Re: Working with BLOBs Lukas Eder 4/2/13 12:42 PM
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>:
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.

Peter
Re: Working with BLOBs Lukas Eder 4/13/13 4:03 AM
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
Re: Working with BLOBs Biziclop 4/13/13 12:17 PM
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
Re: Working with BLOBs Lukas Eder 4/14/13 10:20 AM
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