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>
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.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: 126.96.36.199