Coldfusion 9 64bit, MSSQL 2008 R2 & GENERATED_KEYS

Report · Jun 27, 2011

We recently upgraded our hosting environment to Coldfusion 9 64bit. This has caused us no end of problems, but I can't seem to find an answer to the following at all.

Take this code for example. The table is pretty basic with two colums (testID and testName) where the testID column is an auto increment primary key INT column.

<cffunction name="testInsert" returntype="query">
     <cfquery datasource="#DSN_NAME#" name="testQuery">
          INSERT INTO TestTable (
               testName   
          ) VALUES (
               'Test'
          )
          
          SELECT SCOPE_IDENTITY() AS MyID
     </cfquery>
     
     <cfreturn testQuery />
</cffunction>

I would expect it to return a result set with a column called MyID; this is what our old Coldfusion 8 32bit server did with our MSSQL 2005 databases.

Since we have upgraded to Coldfusion 9 64bit and MSSQL 2008 R2, it no longer does this. Instead, it returns a query with the column called GENERATED_KEYS. It does this for pretty much everything involving a generated identity: NEWID(), SCOPE_IDENTITY() and @@IDENTITY.

You can see the result of the above query below:

This is more than painful since we host hundreds of websites which are now all broken. How do we stop this behaviour? If I alias a column I would expect it to be called that in the result set.

Thanks in advance,

Anna.

Report · Jun 28, 2011

I don't usually give advice I haven't first tested but amn't in a position to do so @ the mo', so I'll just fling it out there with this as a caveat emptor.

Is this an artefact of CF, or is it an artefact of the new JDBC drivers CF9 uses. What happens if you revert the drivers to the ones CF8 uses?

This would not be a permanent fix, but would give you some breathing space whilst updating all your code for the new regime.

I would also consider this a bug, because Adobe claim they hold backwards compat as sacrosanct (although this is only when it's convenient for them, really). You should probably raise it as such: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html.

--

Adam