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.