Get Primary Key after using CFINSERT - ColdFusion

Question

When I use CFINSERT the form data is inserted into my database because the field names match the column names.

MY QUESTION: How can I get the primary key of the row I just added using the CFINSERT?

I know I cant use "Result='variable'" similar to a standard cfquery so what is the best way to get the primary key?

If I run the following query directly after my cfinsert it should return the pervious PK:

<cfquery name="getID" datasource="#mydsn#" result="#result#">
select Max(id) as NewID from myTablename;
</cfquery>

Is this the best way to accomplish what I am trying to do?

Shiv

What database system are you using? Also, are you determined to use <cfinsert>? Maybe <cfquery> with one INSERT statement to insert the data and one SELECT statment to grab the new ID would be a useful alternative. – Venosity

I just found this: <cfquery name="getID" datasource="#mydsn#" result="#result#"> select Max(id) as NewID from myTablename; </cfquery> – Shiv

Which I think will work if I run it after my cfinsert. I am using a MSSQL server to answer your question. – Shiv

That's not good because it is a race condition - when two forms are submitted at the same time, then both times the SELECT MAX(id) will return the same value. – Venosity

I thought that might be an issue. Do you have any recommendations? – Shiv

That depends on your database system. SQL Server has SCOPE_IDENTITY to solve this particular problem. Other DB systems use different functions. So, it depends. – Venosity

I am not sure if that can be combined with cfinsert since I am not actually writing the query to use Scope_Identity. – Shiv

That's why I asked whether you are determined to use <cfinsert> or not. You see, if you only had answered the two questions in my first comment... – Venosity

Sorry I had answered it but it looks like it got cut off between my second and third comment when I clicked return by accident. YES I want to use cfinsert because I have over 120+ inputs and this will save me time. – Shiv

I still don't know your database system so you kind of not really answered the other question. Really, it's 10 comments later now. – Venosity

Which I think will work if I run it after my cfinsert. I am using a MSSQL server to answer your question. – Denoteone 15 mins ago Was that not enough information? – Shiv

Hm, okay, that slipped through. Sorry. So, instead of MAX(id) use SELECT SCOPE_IDENTITY() as NewId in a cfquery. – Venosity

I will do that. If you put it in an answer I can mark it as closed. – Shiv

I'm not entirely sure if that avoids the race condition, because if you do a cfinsert and a separate cfquery that's two batches (Docs: "two statements are in the same scope if they are in the same stored procedure, function, or batch"). Does SCOPE_IDENTITY even return a value when used like that? – Venosity

possible duplicate of SQL - Inserting a row and returning primary key – Viscus

@Viscus Not really a duplicate because the circumstances are special to how this can be used in ColdFusion. – Venosity

Don't use cfinsert.....ever. – Dextrous

Answer 1

Since you are on SQL Server you can use the SCOPE_IDENTITY() function to safely grab the last inserted identity value in the current scope.

The documentation says

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

When used in two separate ColdFusion tags (<cfinsert> followed by <cfquery>) then that's two batches and SCOPE_IDENTITY() will not work anymore. Therefore the INSERT and the SELECT statement will have to be part of the same batch. Unfortunately this cannot be achieved with <cfinsert>.

You said you had many fields in your form post, so I would do something like this:

<cfset fieldNames = "all,relevant,field,names,from,http,post">
<cfset fieldTypes = "INTEGER,VARCHAR,VARCHAR,DATETIME,INTEGER,VARCHAR,VARCHAR">
<cfset fieldNullable = "false,true,true,true,false,true,false">
<cfset fieldCount = ListLen(fieldNames)>

<!--- default "" for any fields missing from the HTTP POST --->
<cfloop from="1" to="#fieldCount#" index="i">
  <cfparam name="FORM.#ListGetAt(fieldNames, i)#" default="">
</cfloop>

<cfquery name="insert" datasource="#yourdatasource#">
  INSERT YourTable (#fieldNames#)
  VALUES (
    <cfloop from="1" to="#fieldCount#" index="i">
      <cfif i gt 1>,</cfif>
      <cfset val = FORM[ListGetAt(fieldNames, i)]>
      <cfset type = "CF_SQL_#ListGetAt(fieldTypes, i)#">
      <cfset null = ListGetAt(fieldNullable, i) eq "true" and val eq "">
      <cfqueryparam value="#val#" cfsqltype="#type#" null="#null#">
    </cfloop>
  )

  SELECT SCOPE_IDENTITY() as NewId
</cfquery>

<cfdump var="#insert#">

Venosity

If you are going to use cfquery, the result attribute is simpler. The use of the loop is clever though. – Shipp

Ah, you're right, insert.IDENTITYCOL is actually simpler. – Venosity

then that's two batches True, though FWIW wrapping multiple sql statements in a cftransaction should ensure they keep the same connection. Then you can obtain the scope_identity() value safely. That said agreed using standard INSERT + query.GENERATEDKEY (db agnostic) is preferred. – Matthia

@Matthia I thought about recommending <cftransaction> but I am not sure if that is guaranteed to work. In my experience SCOPE_IDENTITY returns nothing when there was no insert in the current batch. – Venosity

@Venosity - My understanding is scope_identity applies to the current session. Wrapping multiple statements in a cftransaction ensures the same session and that guarantees it will work. So the result should be the same as if you executed those two statements in a single SSMS query window, with no go in between. – Matthia

Lacking an SQL Server for testing I did not want to make uncertain recommendations. If you can verify that this works, go ahead and post it as an alternative answer. – Venosity

Edit: @Venosity - Understood. My comments were more informational about cftransaction and sessions for others reading the thread in the future :) I can verify it works as expected, but I do not think it is worth a separate answer. Compared to the simplicity of using query.GENERATEDKEY or a single query with SELECT SCOPE_IDENTITY() as NewId using cftransaction with multiple cfquery's would be an unnecessary complication in this case. Plus I do not want to encourage the use of the legacy cfinsert tag ;-) A standard INSERT is the way to go. – Matthia

I don't think using <cftransaction> is necessary to ensure the same DB session. I suppose it would enable one to roll back the transaction in the (very unlikely) event that SCOPE_IDENTITY() doesn't return anything. – Littlest

@DavidFaber - Yes, logically it seems like a single request would maintain the same db connection, but... I have never seen it documented that it is "guaranteed". So it may work without it, but AFAIK the only way to guarantee you maintain the same connection is with cftransaction. – Matthia

Also, the SQL Server documentation clearly states that it has to be the same batch, the same connection is not enough. – Venosity

True. Though I think same connection should suffice here. My understanding is a "batch" consists of one or more statements separated by the TSQL keyword GO. Since GO is not supported outside of MS clients, I am thinking same connection and batch should be the equivalent in this scenario. – Matthia

@Matthia "A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution." (ref) - GO separates multiple statements that are sent at the same time into batches. It does not make a batch out of multiple statements sent at different times. Trusting that multiple CF statements will execute in the same batch is asking for trouble. – Venosity

Answer 2

The best way to deal with this is to get the primary key from the generatedKey from the query's result struct.

<cfquery name="myQuery" result="queryResult" datasource="#myDSN#">
    INSERT INTO some_table
    (column_one) 
    VALUES 
    (<cfqueryparam value="#stringForColOne#" cfsqltype="CF_SQL_VARCHAR">)
</cfquery>
<cfoutput>
    Generated Key from SQL Insert = #queryResult.generatedKey#
</cfoutput>

See https://wikidocs.adobe.com/wiki/display/coldfusionen/cfquery#cfquery-Usage

Tsai