ResultSet.getBlob() Exception

Question

The Code:

ResultSet rs = null;

try { 
    conn = getConnection();
    stmt = conn.prepareStatement(sql);
    rs = stmt.executeQuery();

    while (rs.next()) {
        Blob blob = rs.getBlob("text");
        byte[] blobbytes = blob.getBytes(1, (int) blob.length());
    String text = new String(blobbytes);

The result:

java.sql.SQLException: Invalid column type: getBLOB not implemented for class oracle.jdbc.driver.T4CClobAccessor
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:357)
at oracle.jdbc.driver.Accessor.getBLOB(Accessor.java:1299)
at oracle.jdbc.driver.OracleResultSetImpl.getBLOB(OracleResultSetImpl.java:1280)
at oracle.jdbc.driver.OracleResultSetImpl.getBlob(OracleResultSetImpl.java:1466)
at oracle.jdbc.driver.OracleResultSet.getBlob(OracleResultSet.java:1978)

I have class12_10g.zip in my class path. I've googled and have found essentially only one site on this particular problem, and it wasn't helpful at.

Does anyone have any ideas on this?

Answer 1

Not sure about making the Blob object work -- I typically skip the Blob step:

rs = stmt.executeQuery();

while (rs.next()) {
   byte[] blobbytes = rs.getBytes("text");
   String text = new String(blobbytes);
}

Answer 2

Try...

  PreparedStatement stmt = connection.prepareStatement(query);
  ResultSet rs = stmt.executeQuery();
  rs.next();
  InputStream is = rs.getBlob(columnIndex).getBinaryStream();

...instead?

Answer 3

try to use the latest version of the drivers (10.2.0.4). Try also the drivers for JDK 1.4/1.5 since classes12 are for JDK 1.2/1.3.

Answer 4

I have a utility method in a DAO superclass of all my DAOs:

protected byte[] readBlob(oracle.sql.BLOB blob) throws SQLException {

    if (blob != null) {
        byte[] buffer = new byte[(int) blob.length()];
        int bufsz = blob.getBufferSize();
        InputStream is = blob.getBinaryStream();
        int len = -1, off = 0;
        try {
            while ((len = is.read(buffer, off, bufsz)) != -1) {
                off += len;
            }
        } catch (IOException ioe) {
            logger.debug("IOException when reading blob", ioe);
        }
        return buffer;
    } else {
        return null;
    }
}

// to get the oracle BLOB object from the result set:
oracle.sql.BLOB blob= (oracle.sql.BLOB) ((OracleResultSet) rs).getBlob("blobd");

Someone will now say "why didn't you just do XYZ", but there was some issue at the time that made the above more reliable.

Answer 5

A little background: We were converting one of our databases from MySQL to Oracle. Within the MySQL DB, one of the fields is a longtext which is treated as a BLOB in the code. The SQL developer workbench by default converts longtext to CLOB (make sense to me) but the code was expecting Blob. I guess the error wasn't that nice: oracle.jdbc.driver.T4CClobAccessor (though it does mention Clob).

When I tried the following:

rs = stmt.executeQuery();

while (rs.next()) {
   byte[] blobbytes = rs.getBytes("text");
   String text = new String(blobbytes);
}

it threw an unsupported exception - all I had to do in the first place was compare the types in the newly created Oracle DB with what the code was expecting (unfortunately I just assumed they would match).

Sorry guys! Not that I've put much thought into it, now I have to figure out why the original developers used BLOB types for longtext