Re: blob without largeobject api

Here's how we do it:

First, in your table, use the OID type (this matches to the BLOB type in
Oracle):

raw_data OID NOT NULL,

The JDBC SQL is something like

INSERT INTO yourtable (raw_data) VALUES (?);

(With Oracle, this is different in that you need to insert an empty_blob()
and then update it)

Then we created a wrapper blob object and create the BLOB object from a byte
array, but you can also stream it if that's important to you:

YoByteBlob attBlob = new YoByteBlob( (byteArrayOfData );
int num = stmt.executeUpdate();

And yes, you do need to wrap this in a transaction, but that should be
standard operating procedure for anything but toy applications in my opinion
since who wants to deal with the troubles of out of sync data.

Retrieving it is pretty straightforward, too:

java.sql.Blob b = resultSet.getBlob(1);
YoByteBlob ybb = new YoByteBlob(b);

David

Our YoByteBlob wrapp class (below) has been cleared to release on this list
(in the end, it's a shame a simple Blob implementation was not provided as
part of the JDBC spec). Note that this wrapper doesn't allow for streaming
of blobs, so it may not meet your needs as it does our (our blobs go through
compression and encryption before being stuffed in the db):

// Copyright (c) 2002-2003 Yozons, Inc. All rights reserved.

// This file is proprietary and confidential.

//

package com.yozons.jdbc;

import java.sql.SQLException;

/**

* Screwy wrapper class so that we can insert a Blob into the database from a
byte array.

* Includes more screwy stuff for Oracle specific updating of a blob (the
only way to insert a new blob).

*

* @author David Wall

*/

public class YoByteBlob

implements java.sql.Blob

{

byte[] bytes = null;

/**

* Creates a YoByteBlob using the specified byte array.

*/

public YoByteBlob(byte[] b)

{

bytes = b;

}

// My own constructor for taking a Blob of input and returning as an array

public YoByteBlob(java.sql.Blob b)

{

java.io.InputStream is = null;

try

{

is = b.getBinaryStream();

bytes = new byte[(int)b.length()];

is.read(bytes);

}

catch( java.sql.SQLException e )

{

bytes = null;

}

catch( java.io.IOException e )

{

bytes = null;

}

finally

{

try

{

if ( is != null )

is.close();

}

catch( Exception e ) {}

}

}

public long length()

throws java.sql.SQLException

{

return bytes.length;

}

// My own API call for simplicity

public byte[] getBytes()

{

return bytes;

}

public byte[] getBytes(long pos, int length)

throws java.sql.SQLException

{

if ( pos == 0 && length == bytes.length )

return bytes;

try

{

byte[] newbytes = new byte[length];

System.arraycopy(bytes, (int)pos, newbytes, 0, length);

return newbytes;

}

catch( Exception e )

{

throw new java.sql.SQLException("Could not get subset of array for
YoByteBlob");

}

}

public java.io.InputStream getBinaryStream()

throws java.sql.SQLException

{

return new java.io.ByteArrayInputStream(bytes);

}

public int setBytes(long pos,

byte[] bytes,

int offset,

int len)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported setBytes() for YoByteBlob");

}

public int setBytes(long pos,

byte[] bytes)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported setBytes() for YoByteBlob");

}

public java.io.OutputStream setBinaryStream(long pos)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported setBinaryStream() for
YoByteBlob");

}

public void truncate(long len)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported truncate() for YoByteBlob");

}

public long position(byte[] pattern, long start)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported position() for YoByteBlob");

}

public long position(java.sql.Blob pattern, long start)

throws java.sql.SQLException

{

throw new java.sql.SQLException("Unsupported position() for YoByteBlob");

}

/**

* Routine used to put the "real" object into an Oracle database, which
requires

* creating an empty blob, then retrieving it again and updating it from
there.

*/

public void updateOracleBlob(java.sql.Blob b)

throws java.sql.SQLException

{

java.io.OutputStream outstream = null;

try

{

if ( b == null )

throw new SQLException("YoByteBlob.updateOracleBlob() blob was null");

if ( ! (b instanceof oracle.sql.BLOB) )

throw new SQLException("YoByteBlob.updateOracleBlob() blob not an
oracle.sql.BLOB object; is: " +

b.getClass().getName() );

if ( bytes == null )

throw new SQLException("YoByteBlob.updateOracleBlob() no blob bytes to
write");

oracle.sql.BLOB blob = (oracle.sql.BLOB)b;

outstream = blob.getBinaryOutputStream();

int bufSize = blob.getBufferSize();

int pos = 0;

int remaining = bytes.length;

while ( remaining > 0 )

{

int numOut = Math.min(bufSize,remaining);

outstream.write(bytes, pos, numOut);

pos += numOut;

remaining -= numOut;

}

}

catch( java.io.IOException e )

{

throw new java.sql.SQLException("YoByteBlob.updateOracleBlob() I/O failure:
" + e.getMessage());

}

finally

{

try

{

if ( outstream != null )

outstream.close();

}

catch( java.io.IOException e )

{

throw new java.sql.SQLException("YoByteBlob.updateOracleBlob() close I/O
failure: " + e.getMessage());

}

}

}

}