CF8, MS-SQL, scope_identity()

[Phil Lepanto]

In CFMX 6-7, I used to write the following:

<cfquery dbname=name dbtype=odbc name=myInsertQuery>
insert into myTable (field1,field2,field3) select top 1 'VALUE1' as value1,value2,value3 from myOtherTable where something = something
select scope_identity() as NEWVAL from myTable
</cfquery>
<cfoutput>#myInsertQuery.NEWVAL#</cfouput>

This would work perfectly fine. Now, in CF8, this no longer works. A slightly less complicated insert query that doesn't involve selecting from another table does return a scope_identity value (as well as the new result struct in CF8 queryname.identitycol), but the more complex one that I demostrate above does not.

Has anyone else experienced this? Could this be a result of running MS-SQL2000 instead of MS-SQL2005?

I have submitted this on the bug reports and on the cfquery livedoc, but haven't received any response todate.

-Phil

[Newsgroup_User]

have you tried using the RESULT attribute of CFQUERY and then accessing
the [result_name].IDENTITYCOL variable?

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Phil Lepanto]

Yes, I have. You know what's really interesting about that? That the [result_name] struct comes back as not defined. The only thing that comes back is the [query_name] struct.

Please let me know if you are unable to duplicate my errors.

[-__cfSearching__-]

When you used the RESULT attribute, did you remove the scope_identity() call?

If you choose to use scope_identity(), wrap the whole query in a set nocount on/set nocount off. That will prevent rowcounts from interfering with the cfquery results.

[Phil Lepanto]

Yes, I had thought that perhaps scope_identity might be the issue. However, the query below will also yield a result.identitycol IS NOT DEFINED error

[-__cfSearching__-]

It does work when you use a VALUES clause. Just not when using SELECT .. FROM . I do not know if that is deliberate or a bug.

You could always go back to using scope_identity(). Just wrap it in a set nocount on/off.

[Phil Lepanto]

Thanks for all your help on this.

I agree that if you use explict values, as opposed to trying to select values from another table, then everything works properly. However, when inserting values from a select query, whether set nocount on/off is used or not, the identitycol and the scope_identity() values are not available.

Are you seeing the same behavior in your tests?

[-__cfSearching__-]

No. When I use set nocount on/off in conjunction with scope_identity the identity value is available in the returned query (not the "result" variable).

Here is an example of what works for me. The dump returns:

query
RESULTSET query
NewIDValue
1 12

[Phil Lepanto · Accepted solution]

Ah, OK, when I wrapped the whole query in nocount rather than just the second component [select scope_identity() as testid from myOtherTable] then the resultname.testid value came back in the result struct AND in the queryname.struct.

So, I am still calling "BUG" on this one if anyone from Adobe reads this, but this is the fix that I needed to get my code running again.

[sws]

Some strange stuff....

The following works in CF7 with the built-in MSSQL or JDBC 1.0 driver. It also works in CF8 with the built-in MSSQL driver.

insert into myTable (myField)
values ('myValue');
select scope_identity() as myAliasName



If I'm using the JDBC 1.0 driver in CF8, I must wrap the query with the "SET NOCOUNT" statements for it to work:

SET NOCOUNT OFF;
insert into myTable (myField)
values ('myValue');
select scope_identity() as myAliasName;
SET NOCOUNT ON:


Now, when using the JDBC v1.2 driver in CF8, the SET NOCOUNT statements do not work. The insert works but the query is undefined. result.RECORDCOUNT=0.

If you remove the SET NOCOUNT statements, myAliasName is undefined but the query will be defined and you will get a value for queryName.GENERATED_KEYS. result.RECORDCOUNT=1. It seems the alias is ignored.

If you now remove the select scope_identity() statement so that you only have the INSERT statement, the query will be undefined but now the result variable will contain a GENERATED_KEYS value. Also, RECORDCOUNT=1 even though the query is undefined. (Note: you must remove the scope_identity() statement. If you just comment it out with SQL comments, the insert will work but GENTERATED_KEYS will be undefined and RECORDCOUNT=0).


I'm not sure yet what to make of this info. I'm using CF8 in development and CF7 in production. If I use the built-in Microsoft SQL Server driver for both environments, I don't need to make any changes to my code. That's what I'm doing for the time being.

[-__cfSearching__-]

Well the original issue was with insert/select statements, not insert/values. In my tests of


insert into myTable (myField)
values ('myValue');
select scope_identity() as myAliasName;


The query was always defined with all three drivers: JDBC 1.0, JDBC 1.2 and the CF8 built in driver. I did not have to use set nocount. Though personally I do not quite agree with that behavior. Arbitrarily ignoring the row counts of insert statements in some queries but not in others, just feels wrong to me.

sws wrote:
> Also, RECORDCOUNT=1 even though the query is undefined.


Insert statements return recordcounts too. RECORDCOUNT=1 is likely the result of the insert. ie 1 record was inserted.

The only strange result I observed was when using the ms 2005 driver. As you described, it ignores the alias and returns the identity values in a column named GENERATED_KEYS. I think the problem may be more than just ignoring the alias. I think the entire select statement is ignored or replaced. For example, this set of statements should return two records


INSERT INTO TestTable ( FieldName )
VALUES ('test value');

SELECT SCOPE_IDENTITY() AS MyAliasName
UNION ALL
SELECT SCOPE_IDENTITY() AS MyAliasName


However, the resulting query contains only 1 record.

COLUMNLIST GENERATED_KEYS
RECORDCOUNT 1

query
RESULTSET query
GENERATED_KEYS
1 33

Clearly something is happening behind the scenes to cause the subsequent select to be ignored/replaced with the GENERATED_KEYS.

[-__cfSearching__-]

I have been working solely with MySql the last few weeks. So it just hit me what is really wrong with this picture 😉 From the documentation

result_name.GENERATED_KEY
MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.

result_name.IDENTITYCOL
SQL Server only. The ID of an inserted row.

So .. why is a query run against an MS SQL database returning GENERATED_KEY at all?

[-__cfSearching__-]

Possibly. Then again, I have not read the documentation on this one thoroughly 😉 If you have, and feel it is a bug, you might submit a bug report

http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform

EDIT: By "bug" I mean IDENTITYCOL not working with insert / select .. from table . The requirement for using set nocount on/off is not a bug IMO.

[BrianO]

I get the same result with SQL Server 2005, JDBC driver and CF 8 when using Insert statements. Wacky.
Brian