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?

What database system are you using? Also, are you determined to use ``? Maybe `` with one INSERT statement to insert the data and one SELECT statment to grab the new ID would be a useful alternative. — Tomalak, Apr 21 '15 at 09:17
I just found this: select Max(id) as NewID from myTablename; — Denoteone, Apr 21 '15 at 09:18
Which I think will work if I run it after my cfinsert. I am using a MSSQL server to answer your question. — Denoteone, Apr 21 '15 at 09:18
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. — Tomalak, Apr 21 '15 at 09:21
I thought that might be an issue. Do you have any recommendations? — Denoteone, Apr 21 '15 at 09:22
That depends on your database system. SQL Server has [`SCOPE_IDENTITY`](https://msdn.microsoft.com/en-us/library/ms190315.aspx) to solve this particular problem. Other DB systems use different functions. So, it depends. — Tomalak, Apr 21 '15 at 09:25
I am not sure if that can be combined with cfinsert since I am not actually writing the query to use Scope_Identity. — Denoteone, Apr 21 '15 at 09:28
That's why I asked whether you are determined to use `` or not. You see, if you only had answered the two questions in my first comment... — Tomalak, Apr 21 '15 at 09:29
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. — Denoteone, Apr 21 '15 at 09:31
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. — Tomalak, Apr 21 '15 at 09:33
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? — Denoteone, Apr 21 '15 at 09:35
Hm, okay, that slipped through. Sorry. So, instead of `MAX(id)` use `SELECT SCOPE_IDENTITY() as NewId` in a cfquery. — Tomalak, Apr 21 '15 at 09:37
I will do that. If you put it in an answer I can mark it as closed. — Denoteone, Apr 21 '15 at 09:38
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? — Tomalak, Apr 21 '15 at 09:42
possible duplicate of [SQL - Inserting a row and returning primary key](http://stackoverflow.com/questions/8479315/sql-inserting-a-row-and-returning-primary-key) — da_didi, Apr 21 '15 at 09:47
@da_didi Not really a duplicate because the circumstances are special to how this can be used in ColdFusion. — Tomalak, Apr 21 '15 at 10:02

score 4 · Answer 1 · edited Dec 15 '16 at 19:33

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

Tomalak · Accepted Answer · 2015-04-21T10:04:13.793

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#">

edited Apr 21 '15 at 10:04

answered Apr 21 '15 at 09:58

Tomalak

332,285
67
532
628

3

If you are going to use cfquery, the result attribute is simpler. The use of the loop is clever though. – Dan Bracuk Apr 21 '15 at 11:53
Ah, you're right, `insert.IDENTITYCOL` is actually simpler. – Tomalak Apr 21 '15 at 12:29
1

*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. – Leigh Apr 21 '15 at 14:20
@Leigh I thought about recommending `` 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. – Tomalak Apr 21 '15 at 14:25
@Tomalak - 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. – Leigh Apr 21 '15 at 15:42
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. – Tomalak Apr 21 '15 at 15:46
Edit: @Tomalak - 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. – Leigh Apr 21 '15 at 17:42
I don't think using `` 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. – David Faber Apr 22 '15 at 13:20
1

@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. – Leigh Apr 22 '15 at 18:22
Also, the SQL Server documentation clearly states that it has to be *the same batch*, the same connection is not enough. – Tomalak Apr 22 '15 at 19:25
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. – Leigh Apr 22 '15 at 22:02
@Leigh *"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](https://technet.microsoft.com/en-us/library/ms175502%28v=sql.105%29.aspx)) - `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. – Tomalak Apr 23 '15 at 05:30

Get Primary Key after using CFINSERT - ColdFusion

2 Answers2