Skip to main content
Z
ZNB
Inspiring
August 24, 2020
Answered

coldfusion retrieve the index of a record that has just been inserted.

  • August 24, 2020
  • 3 replies
  • 1086 views

Hello,

I would like to retrieve the index of a record that has just been inserted.

<CFQUERY name="Com_impr" datasource="#session.dbname#">
  INSERT INTO Impr_commandes
	(SiteName,
	ICImp_ID,
	ICClient_ID,
	ICLivre_ID,
	ICProd_ID,
	CDate_crea,
	ICQuantite
        )
  VALUES 
	('#SiteName#',
	#Impp#,
	#get_Clients.Client_ID#,
	#get_Livre.Livre_ID#,
	#get_Prod.Prod_ID#,
	'#DateFormat(now(),"dd/mm/yyyy")#',
	1
     	)
  SELECT Scope_Identity() AS [IC_ID];
</CFQUERY>

This buggy!
Is this the best method?
Thank you in advance for your help.

This topic has been closed for replies.
Correct answer ZNB

<CFQUERY name="Com_impr" datasource="#session.dbname#" result="queryResult">
INSERT INTO Impr_commandes
(SiteName,
ICImp_ID,
ICClient_ID,
ICLivre_ID,
ICProd_ID,
CDate_crea,
ICQuantite
)
VALUES
('#SiteName#',
#Impp#,
#get_Clients.Client_ID#,
#get_Livre.Livre_ID#,
#get_Prod.Prod_ID#,
'#DateFormat(now(),"dd/mm/yyyy")#',
1
)
</CFQUERY>

In your query, above, I added result="queryResult" which is where CF returns the information about the query. You would then use one of the below values to get your new id column value, the one you use depends on which databsae you are using. This is all in the CFQuery documentation.

 

queryResult.identitycol // MSSQL
queryResult.rowid // Oracle
queryResult.sys_identity // Sybase
queryResult.serial_col // Informix
queryResult.generated_key // MySQL


Hello,
Sorry but I couldn't test before ;-(
The method is good and easy to implement.
I do not know.
thanks again

3 replies

WolfShade
WolfShade
Legend
August 25, 2020

Another thing I just thought of.  You say you'd like to retrieve the index of a record just inserted.  Do you actually mean a unique ID?  We use a varchar with a max length of 36 for unique IDs, and have ColdFusion create a UUID and insert that as the unique ID for the record.  That way, we don't need to retrieve it.  CF created it, we just use that elsewhere.

 

HTH,

 

^ _ ^

Z
ZNBAuthor
Inspiring
August 26, 2020

Hello,
Thank you for your answer John1231.
Do you have an example because I don't see how to apply it.
Thank you in advance

Z
ZNBAuthor
Inspiring
August 26, 2020

Hello,

My approach is simple.

I created a record that I want to send a link to so I need its id.

Thanks in advance.

WolfShade
WolfShade
Legend
August 24, 2020

I tried to do a search of my past posts, looking for a question I asked, similar to yours.  Can't find it.

 

At first, the response by John1231 seemed like the standard.  However, one user stated that the result method doesn't always return what is expected.  There is another way, by inserting the insert query and a follow-up select query, but this method is also unreliable.  But if you find success with John1231's suggestion, please report here how well it worked.

 

V/r,

 

^ _ ^

D
Community Expert
Dave WattsCommunity Expert
Community Expert
August 25, 2020

There's nothing unreliable about doing this within a CFTRANSACTION block with the default "read isolation" level.

 

Dave Watts, Eidolon LLC

Dave Watts, Eidolon LLC
J
John123
Participating Frequently
August 24, 2020

Check out the documentation for CFQuery and look at the RESULT attribute, which returns a bunch of information about the query just executed and includes information on the generated key. Some of the attributes

 

result.identitycol // MSSQL
result.rowid // Oracle
result.sys_identity // Sybase
result.serial_col // Informix
result.generated_key // MySQL

 

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices