dcsimg

How do I extract a BLOB from a database?

Created Nov 29, 1999

Lennart Jorelid

A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You extract the data in two steps:

  1. Call the getBlob method of the Statement class to retrieve a java.sql.Blob object
  2. Call either getBinaryStream or getBytes in the extracted Blob object to retrieve the java byte[] which is the Blob object.

Note that a Blob is essentially a pointer to a byte array (called LOCATOR in database-talk), so the java.sql.Blob object essentially wraps a byte pointer. Thus, you must extract all data from the database blob before calling commit or

<div align="center">

private void runGetBLOB() 
{
     try
     {   // Prepare a Statement:
         PreparedStatement stmnt = conn.prepareStatement("select aBlob from BlobTable");

         // Execute
         ResultSet rs = stmnt.executeQuery();

         while(rs.next())
         {
            try
            {
               // Get as a BLOB
               Blob aBlob = rs.getBlob(1);
               byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
            }
            catch(Exception ex)
            {
               // The driver could not handle this as a BLOB...
               // Fallback to default (and slower) byte[] handling
               byte[] bytes = rs.getBytes(1);
            }
         }

       // Close resources
       rs.close();
       stmnt.close();

     }
     catch(Exception ex)
     {
       this.log("Error when trying to read BLOB: " + ex);
     }
}

</div>