Image file to blob oracle

I'm working with Oracle 9i

I have read 4193 bytes into byte array ba

I have established an Connection conn

When I execute the following two statements, the blob appears to be OK ,

but the last line throws an exception "Unsupported Feature".

Blob blob = BLOB.createTemporary( conn, true, BLOB.DURATION_SESSION );

blob.setBytes(0,ba);

Is't there a WORKING example of how to read an image file and load it into a blob out there Somewhere?

I can't be the only one trying to do this...

BeaverCleavera at 2007-7-13 22:43:57 >

No, you are not the only one trying to store binary information in a BLOB. I have been using the following technique for quite some time and it works.

The odd thing about Oracle BLOBs is that you have to stream the data in, and out. On the one hand it is really clunky, but it makes some sense because you do not have to load the entire object into memory (e.g. streaming video etc).

I've created to convenience methods to assist with the task.

/** Reads and Oracle BLOB

*/

public static byte[] getOracleBlob(ResultSet result, String columnName) throws SQLException {

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

try {

oracle.sql.BLOB blob = ((oracle.jdbc.OracleResultSet)result).getBLOB(columnName);

InputStream inputStream = blob.getBinaryStream();

int bytesRead = 0;

while((bytesRead = inputStream.read()) != -1) {

outputStream.write(bytesRead);

}

inputStream.close();

outputStream.close();

} catch(IOException e) {

throw new SQLException(e.getMessage());

}

return outputStream.toByteArray();

}

/** Writes and Oracle Blob

*/

public static void setOracleBlob(ResultSet result, int order, byte[] data) throws SQLException {

try {

ByteArrayInputStream inputStream = new ByteArrayInputStream(data);

oracle.sql.BLOB blob = ((oracle.jdbc.OracleResultSet)result).getBLOB(order);

OutputStream outputStream = blob.getBinaryOutputStream();

int bytesRead = 0;

while((bytesRead = inputStream.read()) != -1) {

outputStream.write(bytesRead);

}

inputStream.close();

outputStream.close();

} catch(IOException e) {

e.printStackTrace(System.err);

throw new SQLException(e.getMessage());

}

}

To use the methods you do the following

public void create(String id, byte[] data, Connection connection) throws SQLException {

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

connection.setAutoCommit(false);

// Create the row

preparedStatement = connection.prepareStatement("INSERT INTO my_table(id, data) VALUES(?, empty_blob())");

preparedStatement.setString(1, id);

preparedStatement.executeUpdate();

preparedStatement.close();

// Insert the blob data

preparedStatement = connection.prepareStatement("SELECT data FROM my_table WHERE id=? FOR UPDATE");

preparedStatement.setString(1, id);

resultSet = preparedStatement.executeQuery();

if(resultSet.next())

setOracleBlob(resultSet, 1, data);

else

throw new SQLException("Could not find row to update");

resultSet.close();

preparedStatement.close();

} catch(SQLException e) {

throw e;

} finally {

// Close ResultSet

if(resultSet != null) {

try {

resultSet.close();

} catch(SQLException e) {

// Do nothing

}

}

// Close PreparedStatement

if(preparedStatement != null) {

try {

preparedStatement.close();

} catch(SQLException e) {

// Do nothing

}

}

}

To read the data, you simply use the getOracleBlob method as you would a getString or getInt method.

Hope this helps.

Message was edited by:

softnotes

softnotesa at 2007-7-13 22:43:57 >