Re: BLOB manipulation - question

[Gianny Damour]

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