You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@geronimo.apache.org by Gianny Damour <gi...@optusnet.com.au> on 2004/11/29 00:16:06 UTC
BLOB manipulation - question
Hi,
I am working on the support of Dependent Value Classes.
The implementation is rather simple: if a binding is not explicitely
defined for a CMP field class (see
org.tranql.sql.jdbc.binding.BindingFactory) and if the class implements
Serializable, then one assumes that the CMP field is a Dependent Value
Class. Such CMP fields are stored into BLOB columns.
The serialized object is stored into the BLOB via the
PreparedStatement.setBinaryStream(int parameterIndex,
java.io.InputStream x, int length) method. As a matter of fact, this
works with Derby. Yet, it seems that this is not the "correct" way. More
accurately, it seems that PreparedStatement.setBlob (int i, Blob x) is
the "correct" way.
Anyone knows if PreparedStatement.setBinaryStream is portable?
Thanks,
Gianny
Re: BLOB manipulation - question
Posted by Dain Sundstrom <ds...@gluecode.com>.
On Nov 28, 2004, at 3:16 PM, Gianny Damour wrote:
> Hi,
>
> I am working on the support of Dependent Value Classes.
>
> The implementation is rather simple: if a binding is not explicitely
> defined for a CMP field class (see
> org.tranql.sql.jdbc.binding.BindingFactory) and if the class
> implements Serializable, then one assumes that the CMP field is a
> Dependent Value Class. Such CMP fields are stored into BLOB columns.
I suggest that if the object is not explicitly defined you assume it is
serializable. The problem with checking for serializable is the field
type typically doesn't implement serializable. For example, people
often use java.lang.Object for a generic field type.
> The serialized object is stored into the BLOB via the
> PreparedStatement.setBinaryStream(int parameterIndex,
> java.io.InputStream x, int length) method. As a matter of fact, this
> works with Derby. Yet, it seems that this is not the "correct" way.
> More accurately, it seems that PreparedStatement.setBlob (int i, Blob
> x) is the "correct" way.
>
> Anyone knows if PreparedStatement.setBinaryStream is portable?
It's been a while, but last time I checked setBinaryStream didn't work
in oracle for streams bigger then 4k. IIRC, the right way to update a
blob is to get a blob object from a result set using getBlob and change
the new bytes into the blob (truncate it maybe?). At this point, I
don't remember what you do... maybe call updateBlob, or set it into an
update statement. Anyway, I remember the only portable way was to use
the LOG objects directly. I also remember that inserting a new LOB was
tricky.
Maybe someone that has worked with oracle recently can fill all of us
in on LOB support in the new oracle drivers.
-dain
Re: BLOB manipulation - question
Posted by Dain Sundstrom <ds...@gluecode.com>.
On Nov 28, 2004, at 4:49 PM, Jeremy Boynes wrote:
> Gianny Damour wrote:
>> Hi,
>> I am working on the support of Dependent Value Classes.
>> The implementation is rather simple: if a binding is not explicitely
>> defined for a CMP field class (see
>> org.tranql.sql.jdbc.binding.BindingFactory) and if the class
>> implements Serializable, then one assumes that the CMP field is a
>> Dependent Value Class. Such CMP fields are stored into BLOB columns.
>> The serialized object is stored into the BLOB via the
>> PreparedStatement.setBinaryStream(int parameterIndex,
>> java.io.InputStream x, int length) method. As a matter of fact, this
>> works with Derby. Yet, it seems that this is not the "correct" way.
>> More accurately, it seems that PreparedStatement.setBlob (int i, Blob
>> x) is the "correct" way.
>> Anyone knows if PreparedStatement.setBinaryStream is portable?
>
> LOBs in general are not very portable, especially with Oracle.
>
> The problem with setBlob() is that you need to have the driver create
> an instance for you - you can't just pass in a class that implements
> that interface. That usually involves executing the insert/update
> statement with vendor specific SQL and then obtaining a LOB locator
> either via another select or by using something like Oracle's
> insert/returning.
Looks like you can do this without Oracle's specific sql or classes
starting with 8.15 and the JDBC 3 drivers.
http://www.oracle.com/technology/tech/java/jroadmap/jdbc/
listing.htm#998520
I think there is still oracle specific sql for inserting a new empty
blob (empty_blob() maybe).
-dain
Re: BLOB manipulation - question
Posted by Jeremy Boynes <jb...@gluecode.com>.
Gianny Damour wrote:
> Hi,
>
> I am working on the support of Dependent Value Classes.
>
> The implementation is rather simple: if a binding is not explicitely
> defined for a CMP field class (see
> org.tranql.sql.jdbc.binding.BindingFactory) and if the class implements
> Serializable, then one assumes that the CMP field is a Dependent Value
> Class. Such CMP fields are stored into BLOB columns.
>
> The serialized object is stored into the BLOB via the
> PreparedStatement.setBinaryStream(int parameterIndex,
> java.io.InputStream x, int length) method. As a matter of fact, this
> works with Derby. Yet, it seems that this is not the "correct" way. More
> accurately, it seems that PreparedStatement.setBlob (int i, Blob x) is
> the "correct" way.
>
> Anyone knows if PreparedStatement.setBinaryStream is portable?
>
LOBs in general are not very portable, especially with Oracle.
The problem with setBlob() is that you need to have the driver create an
instance for you - you can't just pass in a class that implements that
interface. That usually involves executing the insert/update statement
with vendor specific SQL and then obtaining a LOB locator either via
another select or by using something like Oracle's insert/returning.
setBinaryStream() is the most portable way with the main exception being
Oracle's thin driver where this will not work if the stream is over 4K
in length.
--
Jeremy
Re: BLOB manipulation - question
Posted by Gianny Damour <gi...@optusnet.com.au>.
On 2/12/2004 11:52 AM, Dain Sundstrom wrote:
> Great news. I suggest that we declare that we only officially support
> the 10g driver. Maybe add a wiki page with supported drivers.
>
Good idea.
This is in progress in here:
http://wiki.apache.org/geronimo/Working_20with_20Enterprise_20JavaBeans
Thanks,
Gianny
Re: BLOB manipulation - question
Posted by Dain Sundstrom <ds...@gluecode.com>.
Great news. I suggest that we declare that we only officially support
the 10g driver. Maybe add a wiki page with supported drivers.
-dain
--
Dain Sundstrom
Chief Architect
Gluecode Software
310.536.8355, ext. 26
On Dec 1, 2004, at 3:30 PM, Gianny Damour wrote:
> On 2/12/2004 9:50 AM, Jeff Genender wrote:
>
>> Guys,
>>
>> Just an FYI...If you try the new Oracle driver from the 10g series
>> (the drivers are backward compatible), the BLOB problem appears to
>> have been fixed (finally).
>>
>>
>> Jeff
>
> Jeff, you are right!
>
> It seems that I did mess around with the Oracle driver versions :-(. I
> have tested one more time the insertion of a 5MB file followed by an
> update with a 1MB file with Oracle10g JDBC driver against an Oracle9i
> DB and it was successful.
>
> So, I assume that one does not need to decompose anymore.
>
> Thanks,
> Gianny
Re: BLOB manipulation - question
Posted by Gianny Damour <gi...@optusnet.com.au>.
On 2/12/2004 9:50 AM, Jeff Genender wrote:
> Guys,
>
> Just an FYI...If you try the new Oracle driver from the 10g series
> (the drivers are backward compatible), the BLOB problem appears to
> have been fixed (finally).
>
>
> Jeff
Jeff, you are right!
It seems that I did mess around with the Oracle driver versions :-(. I
have tested one more time the insertion of a 5MB file followed by an
update with a 1MB file with Oracle10g JDBC driver against an Oracle9i DB
and it was successful.
So, I assume that one does not need to decompose anymore.
Thanks,
Gianny
Re: BLOB manipulation - question
Posted by Jeff Genender <jg...@savoirtech.com>.
Guys,
Just an FYI...If you try the new Oracle driver from the 10g series (the
drivers are backward compatible), the BLOB problem appears to have been
fixed (finally).
Jeff
Dain Sundstrom wrote:
> Looks like it it time to implement some dialect specific back ends for
> TranQL. The original plan was to have an oracle specific sql generator
> to get around problems like blob insertion, blob updating, and
> differences in temporal types.
>
> -dain
>
> --
> Dain Sundstrom
> Chief Architect
> Gluecode Software
> 310.536.8355, ext. 26
>
> On Dec 1, 2004, at 2:21 PM, Gianny Damour wrote:
>
>> Dain, Jeremy, thanks for your replies.
>>
>> Here is a short description of what I have so far understood about LOB
>> manipulations:
>>
>> setBinaryStream
>> setBinaryStream does work with Derby, MySQL and Oracle (except where
>> the content is bigger than 4k). The JDBC specifications 3.0 mandate
>> that the method setBinaryStream may also be used to store BLOB.
>>
>> BLOB creation
>> * in the case of Oracle, the empty_blob() method is indeed to be used
>> to create an empty BLOB. I tried unsuccessfully other approaches
>> (setNull(index, Types.BLOB), setBinaryStream(index, null, 0) and
>> INSERT TABLE A (myBLOBColumn) VALUES ('')).
>> * in the case of MySQL a simple INSERT TABLE A (myBLOBColumn) VALUES
>> ('') works.
>>
>> BLOB update
>> BLOB updates are done either directly to the LOB itself or to a copy.
>> It is implementation dependent and the method
>> DatabaseMetaData.locatorsUpdateCopy() indicates which of this
>> implementation is supported by a driver.
>>
>> * Oracle works directly with the LOB. This means that to update a
>> BLOB, one just needs to do that:
>> statement.executeUpdate("CREATE TABLE A (a1 VARCHAR(10), a2 BLOB)");
>>
>> // execute this query to create a BLOB.
>> preparedStatement = connection.prepareStatement("INSERT INTO A (a1,
>> a2) VALUES ('a1', EMPTY_BLOB())");
>> preparedStatement .execute();
>>
>> // update it in place
>> preparedStatement = connection.prepareStatement("SELECT a2 FROM A
>> WHERE a1 = 'a1' FOR UPDATE");
>> resultSet = preparedStatement .executeQuery();
>> rs.next();
>> Blob blob = rs.getBlob(1);
>> OutputStream out = blob.setBinaryStream(1);
>> // update the Blob by writing new data via out.write()
>> // truncate Blob if required via blob.truncate(long);
>>
>> connection.commit();
>>
>> The above snippet works with Oracle10g JDBC driver and above. Prior to
>> this version, Oracle specific classes need to be used.
>>
>> * MySQL works with a copy of the LOB. This means that to update a
>> BLOB, one needs to update it via setBlob or updateBlob. The previous
>> snippet becomes:
>> // execute this query to create a BLOB.
>> preparedStatement = connection.prepareStatement("INSERT INTO A (a1,
>> a2) VALUES ('a1', '')");
>> preparedStatement .execute();
>>
>> // update the BLOB copy
>> preparedStatement = connection.prepareStatement("SELECT a2 FROM A
>> WHERE a1 = 'a1' FOR UPDATE");
>> resultSet = preparedStatement .executeQuery();
>> rs.next();
>> Blob blob = rs.getBlob(1);
>> OutputStream out = blob.setBinaryStream(1);
>> // update the Blob by writing new data via out.write()
>> // truncate Blob if required via blob.truncate(long);
>>
>> // update the BLOB itself
>> preparedStatement = c.prepareStatement("UPDATE A SET a2 = ? WHERE a1 =
>> 'a1'");
>> preparedStatement .setBlob(1, blob);
>> preparedStatement .execute();
>>
>> connection.commit();
>>
>> Based on these findings, I have started to implement a solution which
>> does this:
>> * use setBinaryStream if specified; or
>> * decompose INSERT and UPDATE statements into two or three statements
>> depending on a locatorsUpdateCopy configuration.
>>
>> Thanks,
>> Gianny
Re: BLOB manipulation - question
Posted by Dain Sundstrom <ds...@gluecode.com>.
Looks like it it time to implement some dialect specific back ends for
TranQL. The original plan was to have an oracle specific sql generator
to get around problems like blob insertion, blob updating, and
differences in temporal types.
-dain
--
Dain Sundstrom
Chief Architect
Gluecode Software
310.536.8355, ext. 26
On Dec 1, 2004, at 2:21 PM, Gianny Damour wrote:
> Dain, Jeremy, thanks for your replies.
>
> Here is a short description of what I have so far understood about LOB
> manipulations:
>
> setBinaryStream
> setBinaryStream does work with Derby, MySQL and Oracle (except where
> the content is bigger than 4k). The JDBC specifications 3.0 mandate
> that the method setBinaryStream may also be used to store BLOB.
>
> BLOB creation
> * in the case of Oracle, the empty_blob() method is indeed to be used
> to create an empty BLOB. I tried unsuccessfully other approaches
> (setNull(index, Types.BLOB), setBinaryStream(index, null, 0) and
> INSERT TABLE A (myBLOBColumn) VALUES ('')).
> * in the case of MySQL a simple INSERT TABLE A (myBLOBColumn) VALUES
> ('') works.
>
> BLOB update
> BLOB updates are done either directly to the LOB itself or to a copy.
> It is implementation dependent and the method
> DatabaseMetaData.locatorsUpdateCopy() indicates which of this
> implementation is supported by a driver.
>
> * Oracle works directly with the LOB. This means that to update a
> BLOB, one just needs to do that:
> statement.executeUpdate("CREATE TABLE A (a1 VARCHAR(10), a2 BLOB)");
>
> // execute this query to create a BLOB.
> preparedStatement = connection.prepareStatement("INSERT INTO A (a1,
> a2) VALUES ('a1', EMPTY_BLOB())");
> preparedStatement .execute();
>
> // update it in place
> preparedStatement = connection.prepareStatement("SELECT a2 FROM A
> WHERE a1 = 'a1' FOR UPDATE");
> resultSet = preparedStatement .executeQuery();
> rs.next();
> Blob blob = rs.getBlob(1);
> OutputStream out = blob.setBinaryStream(1);
> // update the Blob by writing new data via out.write()
> // truncate Blob if required via blob.truncate(long);
>
> connection.commit();
>
> The above snippet works with Oracle10g JDBC driver and above. Prior to
> this version, Oracle specific classes need to be used.
>
> * MySQL works with a copy of the LOB. This means that to update a
> BLOB, one needs to update it via setBlob or updateBlob. The previous
> snippet becomes:
> // execute this query to create a BLOB.
> preparedStatement = connection.prepareStatement("INSERT INTO A (a1,
> a2) VALUES ('a1', '')");
> preparedStatement .execute();
>
> // update the BLOB copy
> preparedStatement = connection.prepareStatement("SELECT a2 FROM A
> WHERE a1 = 'a1' FOR UPDATE");
> resultSet = preparedStatement .executeQuery();
> rs.next();
> Blob blob = rs.getBlob(1);
> OutputStream out = blob.setBinaryStream(1);
> // update the Blob by writing new data via out.write()
> // truncate Blob if required via blob.truncate(long);
>
> // update the BLOB itself
> preparedStatement = c.prepareStatement("UPDATE A SET a2 = ? WHERE a1 =
> 'a1'");
> preparedStatement .setBlob(1, blob);
> preparedStatement .execute();
>
> connection.commit();
>
> Based on these findings, I have started to implement a solution which
> does this:
> * use setBinaryStream if specified; or
> * decompose INSERT and UPDATE statements into two or three statements
> depending on a locatorsUpdateCopy configuration.
>
> Thanks,
> Gianny
Re: BLOB manipulation - question
Posted by Gianny Damour <gi...@optusnet.com.au>.
Dain, Jeremy, thanks for your replies.
Here is a short description of what I have so far understood about LOB
manipulations:
setBinaryStream
setBinaryStream does work with Derby, MySQL and Oracle (except where the
content is bigger than 4k). The JDBC specifications 3.0 mandate that
the method setBinaryStream may also be used to store BLOB.
BLOB creation
* in the case of Oracle, the empty_blob() method is indeed to be used to
create an empty BLOB. I tried unsuccessfully other approaches
(setNull(index, Types.BLOB), setBinaryStream(index, null, 0) and INSERT
TABLE A (myBLOBColumn) VALUES ('')).
* in the case of MySQL a simple INSERT TABLE A (myBLOBColumn) VALUES
('') works.
BLOB update
BLOB updates are done either directly to the LOB itself or to a copy. It
is implementation dependent and the method
DatabaseMetaData.locatorsUpdateCopy() indicates which of this
implementation is supported by a driver.
* Oracle works directly with the LOB. This means that to update a BLOB,
one just needs to do that:
statement.executeUpdate("CREATE TABLE A (a1 VARCHAR(10), a2 BLOB)");
// execute this query to create a BLOB.
preparedStatement = connection.prepareStatement("INSERT INTO A (a1, a2)
VALUES ('a1', EMPTY_BLOB())");
preparedStatement .execute();
// update it in place
preparedStatement = connection.prepareStatement("SELECT a2 FROM A WHERE
a1 = 'a1' FOR UPDATE");
resultSet = preparedStatement .executeQuery();
rs.next();
Blob blob = rs.getBlob(1);
OutputStream out = blob.setBinaryStream(1);
// update the Blob by writing new data via out.write()
// truncate Blob if required via blob.truncate(long);
connection.commit();
The above snippet works with Oracle10g JDBC driver and above. Prior to
this version, Oracle specific classes need to be used.
* MySQL works with a copy of the LOB. This means that to update a BLOB,
one needs to update it via setBlob or updateBlob. The previous snippet
becomes:
// execute this query to create a BLOB.
preparedStatement = connection.prepareStatement("INSERT INTO A (a1, a2)
VALUES ('a1', '')");
preparedStatement .execute();
// update the BLOB copy
preparedStatement = connection.prepareStatement("SELECT a2 FROM A WHERE
a1 = 'a1' FOR UPDATE");
resultSet = preparedStatement .executeQuery();
rs.next();
Blob blob = rs.getBlob(1);
OutputStream out = blob.setBinaryStream(1);
// update the Blob by writing new data via out.write()
// truncate Blob if required via blob.truncate(long);
// update the BLOB itself
preparedStatement = c.prepareStatement("UPDATE A SET a2 = ? WHERE a1 =
'a1'");
preparedStatement .setBlob(1, blob);
preparedStatement .execute();
connection.commit();
Based on these findings, I have started to implement a solution which
does this:
* use setBinaryStream if specified; or
* decompose INSERT and UPDATE statements into two or three statements
depending on a locatorsUpdateCopy configuration.
Thanks,
Gianny