ColdFusion: cfquery and the SQL insert statements
Written on 12 March 2011, 01:09pm
Say you have a
cfquery statement which does a simple insert in a table in your database:
<cfquery name="query_name" result="result_name"> INSERT INTO mytable(id,name) VALUES(1,'foo') </cfquery>
This post presents two very useful and least documented features of the
1. How to get the newly inserted ID
The ColdFusion 9 documentation offers several solutions depending on your database:
result_name.IDENTITYCOL SQL Server only. The ID of an inserted row.
result_name.ROWID Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.
result_name.SYB_IDENTITY Sybase only. The ID of an inserted row.
result_name.SERIAL_COL Informix only. The ID of an inserted row.
result_name.GENERATED_KEY MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.
What is not documented is that you can use the generic result_name.GENERATEDKEY to get the newly inserted id.
<cfdump var="#result_name.GENERATEDKEY#"> <!--- returns the newly inserted ID --->
2. How to get the number of inserted rows
The same documentation page offers the following information about the result_name.RECORDCOUNT key:
result_name.recordcount Number of records (rows) returned from the query.
The information is helpful for the SELECT queries, the ones returning records from database. But what happens with the result_name.RECORDCOUNT when we have an INSERT query?
The answer is: it returns the number of rows affected by the query.
<cfdump var="#result_name.RECORDCOUNT#"> <!--- returns 1 --->
Considering the simple insert on top of this page, the result_name.RECORDCOUNT will return 1. But if you have an INSERT that adds multiple rows in the table, the result_name.RECORDCOUNT will return the number of inserted rows.
The same goes for UPDATE and DELETE SQL statements. So if your query will delete 10 records from the database, the result_name.RECORDCOUNT will be 10.
And it looks like it’s been like that since ColdFusion 7.
Written by Dorin Moise (Published articles: 243)