<CFQUERY> and INSERT INTO ... OUTPUT returns nothing in query's name (MSSQL Server)

dev support
1

I am upgrading existing app from CF11. As a pattern, we create records in the database throughout the app with a INSERT INTO ... OUTPUT ... VALUES (...). We did this to return the new primary key and possibly anything else we may want (like a computed column) in some cases. Here’s a post elsewhere using this pattern.

Here’s an example

<cfquery name="some_table_INS" datasource="#arguments.DSN#" result="result.metadata" >

BEGIN TRANSACTION
  DECLARE @tmpTableResults TABLE (ID INT);
  INSERT INTO some_table
   ( some_column )
  OUTPUT inserted.primary_key_column INTO @tmpTableResults
  VALUES
	( 'some value for the column' );
  SELECT * FROM @tmpTableResults;
COMMIT TRANSACTION

</cfquery>

In CF11, some_table_INS.ID would contain the new row key. Using Lucee, the some_table_INS variable only is an empty string.

I do find the key in the result.metadata.IDENTITYCOL and result.metadata.generatedKey variables so I can rewrite all of this to use those instead, but we have over 100 queries that use this pattern.

Can anyone spot anything that would prevent the some_table_INS variable (should be a query var, not a string) from being populated?

We are using the class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver' to talk to SQL Server 2014 EE.

OS: Docker image (Alpine Linux)
Java Version: 1.8.0_251
Tomcat Version: Apache Tomcat/9.0.36
Lucee Version: 5.3.6.61

2

I often need to get the id of a newly inserted table row, but using a temp table like you are doing seems a complicated way of doing it.

On an almost identical setup to yours, and with a column in the mssql table set up as unique identity, I do it like this:

<cfquery name="myquery">
		SET NOCOUNT ON
		INSERT INTO table (col1,col2,col3)
		VALUES (
			<cfqueryparam value="#val1#" cfsqltype="cf_sql_timestamp">,
			<cfqueryparam value="#val2#" cfsqltype="cf_sql_char">,
			<cfqueryparam value="#val3#" cfsqltype="cf_sql_integer">)
		SELECT SCOPE_IDENTITY() AS newId 
		SET NOCOUNT OFF
</cfquery>
<cfset newly_inserted_id = myquery.newId>

btw. I never tried it without the NOCOUNT, and tell the truth never really looked into why, or if it is really needed, but it always works as expected…

Richard

3

Right, it may be a little overkill for the PK alone but

If you have any table triggers in your app, the SCOPE_IDENTITY() options return something other than what you’re looking for. It returns the last identity value generated in any table in the current session. I consider them dangerous in that you may not be getting what you think you’re getting so I avoid using them.

4

For future self, i just remove the temp table from the query and retained the OUTPUT clause:

<cfquery name="some_table_INS" datasource="#arguments.DSN#" result="result.metadata" >

BEGIN TRANSACTION
  INSERT INTO some_table
   ( some_column )
  OUTPUT inserted.primary_key_column AS ID
  VALUES
	( 'some value for the column' );
COMMIT TRANSACTION

</cfquery>

Not sure why it’s necessary, but it was a rapid solution.

5

we do it like this…

<cfquery name="some_table_INS" datasource="#arguments.DSN#">
  INSERT INTO some_table ( some_column ) VALUES ( 'some value for the column' )
</cfquery>
<cfset newly_inserted_id=some_table_INS.generatedkey>
6

Yeah, there are many ways to skin this cat but i was really just questioning if this was a CF11 incompatibility or, was I doing something wrong that just happened to work.