ColdFusion: How to get the number of rows affected by a query

I’ve used ColdFusion for a great many years, a substantial number of them with Microsoft SQL Server. Not surprisingly, I’ve built up a significant collection of code snippets that I frequently reuse. One of the handiest is the use of @@rowcount to retrieve the number of rows affected by an insert, update or delete query. This can useful if we need to check, say, that only one row was updated or that a row was deleted as expected. For example:

<cfquery name="myQuery" datasource="myDatasource">
    set nocount off
    update ...
    select @@rowcount as numberOfRowsUpdated
    setnocount on
</cfquery>

<cfif myQuery.numberOfRowsUpdated GT 1>
    throw an error ...
</cfif>

As you can see, the number of rows updated by this query will, in this case, be returned in the variable myQuery.numberOfRowsUpdated. Simple enough, and this has worked for as long as I can remember (certainly before the pre-MX days).

Today, however, I had to do the same thing for a query that may run against MySQL or Oracle databases. My first impulse was to look for the equivalents of @@rowcount – for MySQL this is ROW_COUNT() and for Oracle it’s SQL%ROWCOUNT. Again, it’s easy enough to write a single query with a simple condition that returns the appropriate dbms variable depending on which database we are querying but it feels like a clumsy hack. Luckily, the ColdFusion cfquery tag (from version 7 onwards) offers a more elegant approach – the result property.

In my experience, the result property of cfquery is rarely used, which is a pity as it offers us quite a few benefits that are not immediately obvious from the documentation. By adding this property to our update query like so:

<cfquery name="myQuery" datasource="myDatasource" 
                        result="myQueryResult">
    update ...
</cfquery>

a number of properties are returned to us in the structure myQueryResult. The property we’re interested in is recordcount. When the query is a select it is set to the number of rows returned (as it usually is). However, for insert, update or delete queries it is set to the number of rows affected by the query i.e. the number of rows inserted, updated or deleted. Testing this value is no more difficult than in the first example:

<cfif myQueryResult.recordcount GT 1>
    throw an error ...
</cfif>

Altogether a simpler, cleaner and more reliable cross-platform method of getting at this value.

3 thoughts on “ColdFusion: How to get the number of rows affected by a query

  1. Wow thank you! I’ve been developing ColdFusion apps for years and I’ve never known you could get the number of rows affected in this manner! Previously I ran a select statement followed by my delete statement to get the number of rows deleted. Your method has saved me many lines of unnecessary code! Thank you!

  2. This is good info! thanks for posting this. Have you tried using this with the CFtransaction tag. EX: If the rows affected would be more than 1 rollback? Just a though….

  3. Running CF9 (I know) I found that if you use cfQueryParams in your update SQL the results set will always report a recordCount of 1.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s