ColdFusion: cfquery and the SQL insert statements

Written on 12 March 2011, 01:09pm

Tagged with: , ,

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')

This post presents two very useful and least documented features of the cfquery tag.

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.
documentation page

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.

Comments (1)

  1. hhhhhhhhhhhhh — April 28, 2014 at 14:42



Leave a response