geronimo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Genender <jgenen...@savoirtech.com>
Subject Re: BLOB manipulation - question
Date Wed, 01 Dec 2004 22:50:36 GMT
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

Mime
View raw message