Oracle8,blob, Java and more than 4000 bytes

[Bjarte Brandt]

Hello!

I have created a table with a BLOB field ( not LONG ), but I can't store
more than 4000 bytes in it.

PreparedStatement pstmt = con.prepareStatement("UPDATE PERSON SET
PHOTO = ? WHERE ID = 1000" );

pstmt.setBytes( 1, new byte[4000] );
<---------------------------------- :-)
pstmt.executeUpdate();

pstmt.setBytes( 1, new byte[4001] );
<----------------------------------- :-(
pstmt.executeUpdate();

Any answers out there?

This is the error message I get :

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert
into a L
ONG column

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:207)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:540)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1285)

at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:724
)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
a:916)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement
.java:963)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:132
3)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1358)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
edStatement.java:263)
at test.TestFrame.jbInit(TestFrame.java:150)
at test.TestFrame.<init>(TestFrame.java:28)
at test.Application1.<init>(Application1.java:10)
at test.Application1.main(Application1.java:27)

[Steve Dyrdahl]

On Thu, 27 Jan 2000, Bjarte Brandt wrote:

> Hello!
>
> I have created a table with a BLOB field ( not LONG ), but I can't store
> more than 4000 bytes in it.
>
> PreparedStatement pstmt = con.prepareStatement("UPDATE PERSON SET
> PHOTO = ? WHERE ID = 1000" );
>
> pstmt.setBytes( 1, new byte[4000] );
> <---------------------------------- :-)
> pstmt.executeUpdate();
>
> pstmt.setBytes( 1, new byte[4001] );
> <----------------------------------- :-(
> pstmt.executeUpdate();
>
>
> Any answers out there?
>

I will include a short example, but all of the information came from
the Oracle Technology Network (http://technet.oracle.com/). Membership is
free and absolutely a necessity if you plan to do any development with
Oracle.

http://technet.oracle.com/tech/java/sqlj_jdbc/index2.htm?Code&files/advanced/advanced.htm

The solution is not a general solution and will only work with Oracle.
However, I do not believe that there is a general solution to the problem
of writing large Blobs ( or clobs ) into Oracle using jdbc.

This example only deals with writing blobs to the database. Writing clobs
is slightly different as is reading blobs and clobs. Refer to the sample
code at the Technology Network for further examples.

Assume the following table:
CREATE table images
(
name VARCHAR2(24),
image BLOB,
CONSTRAINT pk1_images PRIMARY KEY( name )
);

import java.io.*;
import java.sql.*;

import oracle.sql.BLOB;
import oracle.jdbc.driver.OracleResultSet;

public class TestBlob
{
public void insertImage( String name,
byte[] image,
String url )
{
Connection c = null;
PreparedStatement ps = null;
OracleResultSet rs = null;
try
{
// Assuming the that jdbc driver has been registered
// and that url is a fully qualified url to the db.
c = DriverManager.getConnection( url );

// Auto commit must be turned off.
c.setAutoCommit( false );

// Insert just the name of the image.
ps = c.prepareStatement(
"INSERT INTO images ( name ) VALUES ( ? )" );
ps.setString( 1, name );
ps.executeUpdate();

// Prepare the blob column.
ps = c.prepareStatement(
"UPDATE images SET image = EMPTY_BLOB() WHERE name = ?" );
ps.setString( 1, name );

// Select the blob column for update.
// An OracleResultSet will be used.
ps = c.prepareStatement(
"SELECT image FROM images WHERE name = ? FOR UPDATE" );
ps.setString( 1, name );
rs = (OracleResultSet) ps.executeQuery();

// Throw an exception if the blob reference was not retrieved.
if( ! rs.next() )
throw new SQLException( "Unable to retrieve BLOB reference."
);

// The type returned is the BLOB type defined by Oracle.
BLOB b = (BLOB) rs.getBlob( 1 );

// Get an output steam from the blob and get an input stream
// from the byte array. Then simply read from one to the
// other. The BLOB.getBufferSize() returns what Oracle thinks
// the optimal buffer size should be.
OutputStream os = b.getBinaryOutputStream();
ByteArrayInputStream is = new ByteArrayInputStream( image );
byte[] buffer = new byte[ b.getBufferSize() ];
int bytesRead = 0;
while( ( bytesRead = is.read( buffer ) ) != -1 )
os.write( buffer, 0, bytesRead );
os.close();
is.close();

// Commit the changes.
c.commit();
}
catch( Exception e )
{
try{ c.rollback(); } catch(SQLException x){}
e.printStackTrace();
}
finally
{
try{ rs.close(); } catch(Exception x){}
try{ ps.close(); } catch(Exception x){}
try{ c.close(); } catch(Exception x){}