Re: BLOB manipulation - question
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
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
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
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
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
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
