geronimo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dain Sundstrom <dsundst...@gluecode.com>
Subject Re: BLOB manipulation - question
Date Wed, 01 Dec 2004 22:29:43 GMT
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