Sunday, March 23, 2008

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.

5 comments:

Anonymous,  March 24, 2008 at 7:33 AM  

Hi,

Before you call bug on those behaviors, read known issues on CF 8 release notes at http://www.adobe.com/support/documentation/en/coldfusion/8/releasenotes.pdf

Search Number: 70007
Description:
When using Microsoft SQL Server, complex SQL INSERT
statements such as the following do not return a result set:
INSERT INTO orders (Name)
SELECT first FROM customers
WHERE last = 'flintstone';
SELECT @@Identity as newid
To avoid this issue, you can wrap two separate cfquery SQL
statements in a cftransaction block to retrieve the identity field.
Simple inserts (those that do not include a sub-select for the
values) work correctly, although Adobe recommends that you use
the autogenerated key feature of the cfquery RESULT attribute
instead of using database-specific SQL.

I hope it helps your issue.

Shaji

cfSearching March 24, 2008 at 8:17 AM  

@Shaji,

Thanks. Yes, I had read that issue already. But I do not think it covers the problems mentioned here. It says

Simple inserts (those that do not include a sub-select for the values) work correctly

So a simple INSERT / VALUES statement should work. But it does not.. not exactly. The issue also does not explain why the column is being renamed "generated_keys".

Brian,  April 11, 2008 at 5:08 AM  

I get the same buggy results with a simple insert statement and JDBC 1.2 SQL Server 2005 and CF8.

Stu,  November 24, 2008 at 3:54 AM  

Worth mentioning that adding a locking hint - eg: insert into MyTable with (rowlock) - causes the same behaviour. Even if the SQL statement is otherwise "simple".

Stu,  November 24, 2008 at 4:01 AM  

Actually, ignore my previous comment! Of course a SQL insert wouldn't have a locking hint - oops.

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep