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

[ZNB]

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.

[John123]

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

[WolfShade]

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,

^ _ ^

[Dave Watts]

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

Dave Watts, Eidolon LLC

[WolfShade]

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,

^ _ ^

[ZNB]

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

[ZNB]

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.

[John123]

<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

[ZNB · Accepted solution]

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