InterBase® and Java®: storing and retrieving BLOB data

By Joe Shevland

This section describes how to store and retrieve binary data using BLOB (Binary Large OBject) columns in InterBase®, and assumes you have a working knowledge of Java®, JDBC and InterBase®.

The example given below shows a method that inserts an array of bytes into a BLOB column in the database. The PreparedStatement class is used so we can set the parameters independant of the actual SQL command string.

Example 2.0: Inserting a BLOB

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

...

public void insertBlob( int rowid, byte[] bindata ) {

// In this example I'm assuming there's an open, active
// Connection instance called 'con'.

// This examples uses an imaginary SQL table of the following
// form:
//
// CREATE TABLE blobs (
//    ROWID INT NOT NULL,
//    ROWDATA BLOB,
//
//    PRIMARY KEY (rowid)
// );

try {

ByteArrayInputStream bais = new ByteArrayInputStream(bindata);

String sql = "INSERT INTO blobs ( rowid, rowdata ) VALUES ( ?, ? )"; 

PreparedStatement ps = con.prepareStatement(sql);

// Set up the parameter index for convenience (JDBC column
// indices start from 1):
int paramindex = 1;

// Set the first parameter, the Row ID: 
ps.setInt(paramindex++, rowid);

// Now set the actual binary column data by passing the
// ByteArrayInputStream instance and its length:
ps.setBinaryStream(paramindex++, bais, bindata.length);

// Finally, execute the command and close the statement:
ps.executeUpdate();
ps.close();

} catch ( SQLException se ) {
System.err.println("Couldn't insert binary data: "+se);
} catch ( IOException ioe ) {
System.err.println("Couldn't insert binary data: "+ioe);
} finally {
con.close();
}
}

back to top of page

The example given below shows a method that retrieves an array of bytes from the database.

Example 2.0: Selecting a BLOB

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

... 

public byte[] selectBlob( int rowid ) { 

// In this example I'm assuming there's an open, active
// Connection instance called 'con'.

// This examples uses an imaginary SQL table of the following
// form:
//
// CREATE TABLE blobs (
//    ROWID INT NOT NULL,
//    ROWDATA BLOB,
//
//    PRIMARY KEY (rowid)
// );

try {

Statement sment = con.createStatement();

String sql = "SELECT rowid, rowdata FROM blobs WHERE rowid = " + rowid;

ResultSet rs = sment.executeQuery(sql);

byte[] returndata = null;

if ( rs.next() ) {

try {

// The ByteArrayOutputStream buffers all bytes written to it
// until we call getBytes() which returns to us an array of bytes:
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);

// Create an input stream from the BLOB column. By default, rs.getBinaryStream()
// returns a vanilla InputStream instance. We override this for efficiency
// but you don't have to:
BufferedInputStream bis = new BufferedInputStream( rs.getBinaryStream("fieldblob") );

// A temporary buffer for the byte data:
byte bindata[1024];
 
// Used to return how many bytes are read with each read() of the input stream:
int bytesread = 0;

// Make sure its not a NULL value in the column:
if ( !rs.wasNull() ) {
 
if ( (bytesread = bis.read(bindata,0,bindata.length)) != -1 ) {
 
// Write out 'bytesread' bytes to the writer instance:
baos.write(bindata,0,bytesread);

} else {

// When the read() method returns -1 we've hit the end of the stream,
// so now we can get our bytes out of the writer object:
returndata = baos.getBytes();
 
}
}

// Close the binary input stream:
bis.close();

} catch ( IOException ioe ) {
System.err.println("Problem retrieving binary data: " + ioe);
} catch ( ClassNotFoundException cnfe ) {
System.err.println("Problem retrieving binary data: " + cnfe);
}
}

rs.close();
sment.close();

} catch ( SQLException se ) {
System.err.println("Couldn't retrieve binary data: " + se);
} finally {
con.close();
}

return returndata;
}

This paper was written by Joe Shevland and is copyright Joe Shevland and IBPhoenix Inc.