Connector/J 5.1.6 vs Connector/J 5.1.7

April 9, 2009

While deploying a Java based auto import system I ran into a very odd problem. The code was developed and tested just fine with Connector/J 5.1.6 then while pushing to prod there seemed to be a major issue, the import would run download everything parse it and then clear out the old data but then get stuck. I would then get a series of errors java.sql.SQLException: !Statement.GeneratedKeysNotRequested! well it turned out that the prod sever had Connector/J 5.1.7 installed instead of the 5.1.6 which test had installed. This turned out to be allowing for some wrong practices when it came to prepared statements. It was a pretty easy fix yet it took a bit of time to make sure it happened every where. So why there was a change and what happened is well discussed else where and the links to those will follow. But for now here the change that needs to happen.

PreparedStatement postinsert = connection.prepareStatement(sql);

Just changes to the following. But make sure you do the change for all prepared statements that will use generated keys, its easy to miss one and have the whole app fail because of it.

PreparedStatement postinsert = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);

So if you would like to read more about whats going on check out these resources:

Comments