No thanks to Adobe May 14, 2009Posted by faelix in moron-ocity.
Tags: adobe, ColdFusion, insert, sql, update
So, we encountered a problem at work.
The error message that we eventually found, after enabling db activity logging, which was also poorly documented, was “java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Auto-generated keys were not requested, or the SQL was not a simple INSERT statement.”
And the sql query it reported as being a problem was … an UPDATE. So, we knew it wasn’t a simple INSERT (duh).
So, we googled the error message. And we got this:
Patch Available to Disable Auto-generatedkeys in Datasources (ColdFusion 8.0, 8.0.1). It says that version 8.0.1 already has the patch, which we’re running, and you just have to add line to the configuration to turn it off. Come -on- Adobe! Did you even TEST it? We did. And it does bugger all. It STILL came up with the errors.
So, what did we end up doing? We added a query that does SET NOCOUNT ON. Why? Because as a last resort, we looked at
Problems with CF 8’s Generated Keys Feature, which actually only focuses on INSERT statements.
Amazingly, adding SET NOCOUNT ON actually prevents the coldfusion code from automatically trying to request the auto-generated keys.
Come on Adobe – test your code a bit better, and test the instructions you provide.