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 On 2 April 2013 21:04, biziclop <[email protected]> wrote: > Of course and thanks for the suggestion. > > I'm going to try the CustomField workaround and report back on the results. > > > On 2 April 2013 20:42, Lukas Eder <[email protected]> wrote: > >> 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 <[email protected]>: >> > 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 <[email protected]>: >> >> 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 >> >> >> >> On Wednesday, 27 March 2013 15:20:21 UTC, digulla wrote: >> >>> >> >>> 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 >> >> >> >> -- >> >> 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 [email protected]. >> >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> >> >> >> -- >> 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 [email protected]. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > -- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
