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?

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

score 4 · Answer 1 · answered Aug 04 '09 at 17:28

4

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);
}

answered Aug 04 '09 at 17:28

DreadPirateShawn

8,164
4
49
71

score 2 · Answer 2 · answered Aug 04 '09 at 17:32

2

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.

answered Aug 04 '09 at 17:32

dfa

114,442
31
189
228

The single jar files are ojdbc5.jar (JDK 5) or ojdbc6.jar (JDK 6). – Brian Aug 05 '09 at 02:08
The last release supporting ojdbc6 is Oracle Database 12c Release 1 (12.1.0.1): http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html – Vadzim Dec 25 '17 at 10:57

score 1 · Answer 3 · answered Aug 04 '09 at 17:29

1

Try...

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

...instead?

answered Aug 04 '09 at 17:29

cagcowboy

30,012
11
69
93

1

I would use rs.getBinaryStream() instead, it's more reliable – Dec 08 '10 at 08:09

score 1 · Answer 4 · answered Aug 04 '09 at 17:33

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.

Personally, I've found that XYZ is always better, no matter the cost. Seriously, dude. — DreadPirateShawn, Aug 04 '09 at 18:14

score 0 · Answer 5 · answered Apr 04 '13 at 19:44

When JDBC returns a ResultSet from an Oracle database it always returns an OracleResultSet. If you are typing it as a ResultSet, java upcasts it to the standard SQL ResultSet. OracleResultSet overrides most of the data type methods, because Oracle datatypes are not standard SQL types. In other words, that worked because you cast the rs as an OracleResultSet, and used it's getBlob method.

ResultSet.getBlob() Exception

5 Answers5