CF8 + MS JDBC 1.2 Driver - Generated key issues. Curiouser and Curiouser

In a previous entry I wrote about seeing some bizarre results when using CF8 and the MS JDBC 1.2 driver to obtain identity values. Dan G. Switzer, II pointed out two important CF8 bugs that might possibly be related to the strange behavior. He was right. The weird results are related to the second issue, with a slight twist.

Nathan Mische wrote about the second issue in his entry problems with CF 8's Generated Keys Feature. In summary, he points out that CF8's new ability to return generated keys is achieved by appending a SELECT SCOPE_IDENTITY() statement onto the end of insert statements. This happens even if you do not use cfquery's result attribute. His entry shows how you can use the SQL Profiler to verify this.

Since my results were slightly different, I decided to use the SQL Profiler to see what sql was actually sent to the database by the three (3) different drivers: CF8 Driver, MS SQL JDBC 1.0, and MS SQL JDBC 1.2. My test sql was a basic insert statement.

<cfquery name="insert" datasource="#dsn#" result="result">
INSERT INTO SomeTable ( Name )
 VALUES ( 'Test' )
</cfquery>

As you can see below, the results for the CF8 Driver show that select scope_identity() was appended to the sql, as Nathan Mische described.



The results for the JDBC 1.0 Driver show that nothing was appended to the query.



However, the results for the JDBC 1.2 driver show that select scope_identity() AS GENERATED_KEYS is added to the query. I am not certain why it adds the alias "GENERATED_KEYS".



But that does partially explain why the query below does not work as expected with JDBC 1.2.

<cfquery name="insert" datasource="#dsn#" result="result">
INSERT INTO SomeTable ( Name )
 VALUES ( 'Test' )
SELECT SCOPE_IDENTITY() AS NewRecordID
</cfquery>

The SQL Profiler shows that the actual sql sent to the database is:

 INSERT INTO SomeTable ( Name )
 VALUES ( 'Test' );
 SELECT SCOPE_IDENTITY() AS NewRecordID
 select SCOPE_IDENTITY() AS GENERATED_KEYS

As a result, CF seems to ignore the first select/column alias, and uses the second one instead. So if you dump the query, you can see the column name becomes GENERATED_KEYS.



The original issue blogged by Nathan Mische also explains another problem with the ColdFusion 8 driver. This problem was mentioned by an adobe forum member named sws. He/she observed that when you comment out the select statement, result.IDENTITYCOL becomes undefined. Run the following code.

<cfquery name="insert" datasource="#dsn#" result="result">
INSERT INTO SomeTable ( Name )
VALUES ( 'Test' )
-- SELECT SCOPE_IDENTITY() AS NewRecordID
</cfquery>

<cfdump var="#result#">
<cfif IsDefined("insert")>
 <cfdump var="#insert#">
<cfelse>
query not defined
</cfif>

The SQL Profiler shows that select SCOPE_IDENTITY() is appended to the end of the commented line. So that statement is never executed.



Consequently, result.IDENTITYCOL is not defined.



I hate to say it, but I am calling "bug" on all these behaviors.