<< InterBase and Java: making the connection | Database technology articles | Java UDF functional specification >>

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^®.

Storing BLOB data

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

Retrieving BLOB data

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.

See also:
InterBase^® and Java^®: making the connection
Java^® UDF functional specification

back to top of page
<< InterBase and Java: making the connection | Database technology articles | Java UDF functional specification >>