Highlighted

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

Participant ,
Aug 24, 2020

Copy link to clipboard

Copied

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.

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

TOPICS
Database access

Views

91

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Participant ,
Aug 24, 2020

Copy link to clipboard

Copied

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.

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

TOPICS
Database access

Views

92

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 24, 2020 0
Participant ,
Aug 24, 2020

Copy link to clipboard

Copied

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

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 24, 2020 0
LEGEND ,
Aug 24, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 24, 2020 0
Adobe Community Professional ,
Aug 25, 2020

Copy link to clipboard

Copied

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

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2020 0
LEGEND ,
Aug 25, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2020 0
Participant ,
Aug 25, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2020 0
Participant ,
Aug 25, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2020 0
Participant ,
Aug 26, 2020

Copy link to clipboard

Copied

<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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 26, 2020 0
Participant ,
Sep 04, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 04, 2020 0