Sep 10, 2004

Great Exceptions Descriptions Are Gold...

Last night, I was implementing a database method that would update a field within a database. The database I am presently working with is MySQL, using the MySQL Connector/J JDBC driver. I have been using this configuration for quite sometime, but generally my interaction with the database are more disconnected, in the sense that I load some information, do some potentially lengthy processing on it, and update the database, so I generally implement this as two separate methods.

The application in question, however, does not have this same lengthy processing period, and so it lends it self rather well to JDBC's ResultSet update feature. I implemented it, and give it a whirl, and I got an exception:

com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
  at com.mysql.jdbc.UpdatableResultSet.generateStatements(UpdatableResultSet.java:1817)
  at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1938)
  at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:1642)
  at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:1684)

OK. So that is pretty indicative, I thought, of the problem, complete with a little checklist.

  1. The first criteria is that the statement must be updatable via ResultSet.CONCUR_UPDATABLE, so I double checked, and sure enough, that is what I had:

    statement = connection.createStatement( java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE );

  2. The second item must only select from a single table, as I discuss next.

  3. The third criteria is that you must select the Primary Keys. This is where my problem was. My table looked something like:

    | Field        | Type       | Null | Key | Default | Extra |
    | UserName     | varchar(8) |      | PRI |         |       |
    | Handle       | varchar(8) |      | PRI |         |       |
    | Data         | longblob   |      |     |         |       |
    ... more ...

    However, I had mistakenly written my query as SELECT Data FROM Reports WHERE UserName="user" AND Handle="handle";, instead of SELECT UserName,Handle,Data FROM Reports WHERE UserName="user" AND Handle="handle";

Such an example of an exception is the exact opposite of the ClassCastException I recently mentioned, that required a Google to resolve. It contained all the information I needed in order to resolve the issue. Such exceptions are the best.

Filed In