Monday, February 18, 2008

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 ;)

12 comments:

Anonymous,  March 10, 2008 at 2:21 PM  

Thanks for the post. MX8 was giving me this error. This really helped.

Anonymous,  May 7, 2008 at 8:22 AM  

in your last example, i think it is a bad example. if you do the "insert into...select.." statement without the TOP 1, and that statement inserts 300 rows into OtherTable from MyTable, the recordcount from the result struct will still return 1, even though 300 rows were inserted. it is seeing the recordcount from the last select statement.

also, the set nocount on/off is backwards in your example. it should be turned off first, then back on.

cfSearching May 7, 2008 at 6:48 PM  

@Anonymous wrote:
the result struct will still return 1, even though 300 rows were inserted. it is seeing the recordcount from the last select statement.

Yes, but that is what is supposed to happen. When you execute multiple statements they return multiple results "of a sort". However, cfquery can only return one of them. That is the problem here.

Essentially what you are asking for is not possible. You cannot say to cfquery "give me the record count from statement 1, _but_ I want the data from statement 2". It just does not work that way. If you think about, it would not make sense to mix and match results in that manner anyway. Though what might be nice is if cfquery were capable of returning an array of results. But for now, it is not.



also, the set nocount on/off is backwards in your example. it should be turned off first, then
back on.


No, the order is correct. You must SET NOCOUNT ON first to suppress the record count from the insert. Only then does CF return a query containing the identity value.

cfSearching May 7, 2008 at 7:23 PM  

Of course if you really needed to insert 300 records, you probably would not be using SELECT SCOPE_IDENTITY() anyway. Not much point in returning one (1) id out of 300 ;-)

In that situation, a better option would be to use SQL Server 2005's OUTPUT. That would allow you to grab all of the generated identity values. Not just one.

Marianne June 26, 2008 at 2:38 PM  

Dear cfsearching -
Thanks so much for your help on the Adobe Forums - I read both your comment and your article here, and got everything working great!
I was having some problems using SCOPE_IDENTITY() with Coldfusion8 but it works perfectly now.

cfSearching June 28, 2008 at 9:25 AM  

@marianne,

I am glad I could help :-)

Just remember you can also use CF8's "result" attribute to obtain identity values from a simple INSERT. So there is an alternative to using SCOPE_IDENITY().

Dan,  September 22, 2008 at 10:48 AM  

Man, I just wasted an hour trying to debug this. Thanks for the heads up!

cfSearching September 22, 2008 at 3:24 PM  

@Dan,

Glad it was helpful. Though I am sorry you cannot get the hour back ;)

Joseph Gooch,  September 26, 2008 at 2:18 PM  

Thank you for your work!

However, I have to disagree on your analysis, mainly because if I do:

UPDATE (some update statment)
SELECT @@ROWCOUNT as ROWs

It works as it does in CFMX7. It returns the second resultset with the ROWS value and you're good to go.

I don't see any reason why an INSERT statement wouldn't behave the same way. The resultset isn't being defined from the INSERT anyway, so it's not like there are conflicting resultsets. (The insert is only returning counts and metadata, not a ResultSet)

Seems like if a select can return Metadata in "result" and also data in "name", INSERT/UPDATe should be able to do the same, and do it *consistently*.

(I have issue with the behavior changing, but moreso that UPDATE and INSERT work differently.)

Joseph Gooch,  September 29, 2008 at 9:27 AM  

It appears this only happens
1) If you're using INSERT...SELECT. INSERT..VALUES seems to be fine...
2) If the INSERT statement is the first statement in the query block.


Adding a DECLARE @ROWS int before the INSERT seems to resolve the problem, even if you never reference it.

cfSearching September 30, 2008 at 10:45 AM  

@Joseph Gooch,

Yes, you should really read the later entries on this topic. (Some of them are linked at the top of the page.) It seems there are several insert "quirks" ;). Apparently due to how the new IDENTITYCOL feature was implemented. It seems CF tacks a "SELECT SCOPE_IDENTITY() .." statement onto the end of simple insert statements. It gets event more interesting when you start using different drivers ;)

Anonymous,  May 5, 2010 at 3:51 AM  

Thanks a lot -

It is working fine in Coldfusion 9 like 7 (no need to wrap the query with SET NOCOUNT) so it seems that it is only a problem in Coldfusion 8.

Ben

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep