If you look at the OracleAdapter code in Cayenne and related classes, you'll see how bad we have to bend things to ensure users can seamlessly work with Oracle CLOB/BLOB. So that's the answer to the last question :-) Insert/update/delete/select should work with LOB columns.
I guess we just overlooked the aspect of using LOBs as sp parameters. Andrus On Sep 6, 2011, at 2:41 PM, Joseph Senecal wrote: > Hi Andrus, > > Currently the code is passing in a byte[], Cayenne is converting that into a > MemoryBlob. I have no problem with passing in a BLOB instead, but when I > looked up how to create a BLOB I got stuck. It requires a java.sql.Connection > as a parameter, and I haven't been able to find a way to return that from > Cayenne. As far as I know, the connection might not be selected for an > ObjectContext until I perform the query. > > I checked the original code, which calls JDBC directly. There it just calls > setBytes on a Statement passing in the byte[], using a separate connection > just for the stored procedure. That should still work as a workaround. > > I'm going to see if I can get permission to move the stored procedure > functionality to Java since I think that will provide better performance. But > will I run into the same problem using Blobs as an oracle column in a record? > > Joe Senecal > > On Sep 4, 2011, at 9:58 AM, Andrus Adamchik wrote: > >> Hi Joe, >> >> Oracle JDBC driver is messed up. Standard JDBC types are not working they >> way you'd expect them, and then a bunch of custom types to deal with. This >> looks like one of the examples. >> >> We may need to investigate if we can better abstract this particular case in >> Cayenne, but just a guess - what if you use oracle.sql.BLOB instead of >> org.apache.cayenne.util.MemoryBlob to call the stored procedure? I hope that >> should fix it in the interim. >> >> Andrus >> >> >> On Sep 2, 2011, at 6:39 PM, Joseph Senecal wrote: >> >>> I'm trying to pass a Blob to a stored procedure. The parameter is defined >>> in the CayenneModeler as an IN parameter of type BLOB. I'm passing an array >>> of bytes to ProcedureQuery.addParameter. When the code runs it gets an cast >>> exception "org.apache.cayenne.util.MemoryBlob cannot be cast to >>> oracle.sql.BLOB". >>> >>> I'm using version 3.1M2. Any suggestions as to what I'm doing wrong? >>> >>> Joe Senecal >>> >> > >
