ColdFusion 8: Query results are undefined when using INSERT + SELECT FROM with SCOPE_IDENTITY()

UPDATE: This entry applies to the behavior of the built-in ColdFusion 8 MS SQL Server driver. If you are using the MS SQL JDBC driver, you may observe different behavior. You can read more about some of the differences in these two entries: CF8 + MS JDBC 1.2 Driver - .. And for my next trick, I will make this query disappear! and CF8 + MS JDBC 1.2 Driver - Generated key issues. Curiouser and Curiouser.

Recently I have seen a few questions in the forums about why the following MS SQL statement works under MX7, but no longer works with ColdFusion 8. The posters seemed to think it is a bug, but after thinking about it, I disagree. The statement below uses TOP to insert a single record from one table, into another table. Then returns the generated IDENTITY value as a column in the defined query.

<cfquery name="create" datasource="#dsn#">
INSERT INTO OtherTable ( Title, DateModified )
SELECT  TOP 1 Title, DateModified
FROM    MyTable
WHERE   MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID
</cfquery>

<cfif IsDefined("create")>
    <cfdump var="#create#">
<cfelse>
    query <b>create</b> is not defined.
</cfif>

If you run the statement with MX7, it successfully returns the inserted identity value in the declared query variable. However, in ColdFusion 8 the query variable "create" is always undefined. To make the query work with ColdFusion 8, you must wrap the statements in a set nocount on/off. Once you do that the query variable will be defined.

<cfquery name="create" datasource="#dsn#">
SET NOCOUNT ON

INSERT INTO OtherTable ( Title, DateModified )
SELECT   Title, DateModified
FROM     MyTable
WHERE    MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID

SET NOCOUNT OFF
</cfquery>

The change in behavior is clearly related to the new cfquery features introduced in ColdFusion 8. Even as far back as MX, most database connections were handled using JDBC. JDBC has the ability to access detailed information about each sql statement executed. For example it can access things like

• The output of PRINT statements


• ANSI warnings


• The number of rows affected by a statement


• IDENTITY values generated by an INSERT

However, prior to ColdFusion 8, cfquery did not provide a direct method for accessing some of these values. With the introduction of ColdFusion 8, you can now access "rows affected" and IDENTITY values using cfquery's result attribute. It is a great feature, but it was bound to change some of cfquery's behavior.

If you are familiar with JDBC, you already know that most sql statements return some type of result. UPDATE, INSERT and DELETE statements return the number of rows affected by the statement. Whereas SELECT statements also return a Resultset (ie data query). But all of these statements return some result. This was not an issue in MX because cfquery did not return the rows affected for UPDATE, INSERT and DELETE statements. So it could ignore the row counts, and only return resultsets. That is why the original query works with MX7.

However, ColdFusion 8 changed all that. It now makes the number of "rows affected" available in the result attribute. Which means cfquery can no longer ignore statements that do not return a resultset. Otherwise you would not be able to obtain the number of rows affected if your query contained only an UPDATE, INSERT or DELETE statement.

Since cfquery cannot return multiple results, it is clearly returning the first "result" it encounters. In this case the first "result" is from the INSERT statement.

<cfquery name="create" datasource="#dsn#" result="insertResults">
INSERT INTO OtherTable ( Title, DateModified )
SELECT  TOP 1 Title, DateModified
FROM    MyTable
WHERE   MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID
</cfquery>

<cfif IsDefined("create")>
    <cfdump var="#create#">
<cfelse>
    query <b>create</b> is not defined.
</cfif>

<cfdump var="#insertResults#">

If you use the result attribute, and dump the data, you will see that one (1) record was inserted. Since INSERT's do not return a resultset, the query variable "create" is undefined, which makes sense. It is also consistent with how INSERT's are treated in MX7.



However, when you use set nocount on/off, it suppresses the row count from the INSERT. So ColdFusion sees only one "result": the result of the SELECT statement. With only one result to process, it works and the query variable is defined.

Now, perhaps it is just the java in me ;) but this behavior makes sense to me and I do not consider it a bug. While I do not know what the designers of cfquery intended, I suspect they did not mean for it to be a substitute for stored procedures. The fact that cfquery can only return a single resultset tends to support that theory. While the ability to run multiple sql statements in a cfquery is great, I still do not expect it to behave like a full-fledged stored procedure or like cfstoredproc. I imagine if it were intended to do that, cfquery name and result would return an array of queries and row counts, not a single value. But again, that is just my opinion. I fully expect some people to disagree with me ;)

I may write up an entry later on how the results of multiple statements are processed using JDBC. Just to give some insight into the process (and my opinions on the topic ;-) So if that aspect interests you, stay tuned.

As always comments, suggestions, corrections are welcome ;)