more coldfusion 8 cfquery goodness
by now i suppose everyone knows about the new SQL goodness in coldfusion 8's cfquery. by using cfquery's optional result argument you can access identity (auto-increment) values if your database returns them (and of course you actually use them). one other useful bit of info returned in the result structure is recordcount which according to the docs returns the Number of records (rows) returned from the query. that doesn't sound all that interesting but what it really means is that it returns the number of rows affected by the cfquery's SQL, something akin to sql server's @@ROWCOUNT. so for UPDATEs and DELETEs (at least for sql server using the builtin datadirect and jTDS JDBC drivers that i tested with) you can very easily get at how many rows were deleted or updated without any extra SQL, etc. for instance:<cfquery datasource="jTDS" name="q" result="r">
DELETE a
WHERE ID >= 13050
</cfquery>
<cfdump var="#r#">
will return the number of rows deleted in r.recordcount along with the SQL used, execution time and whether this query is cached. sweet.
note that batching your SQL statements like this:
DELETE a
WHERE ID >= 13050
</cfquery>
<cfdump var="#r#">
<cfquery datasource="lab" name="q" result="r">
<cfloop index="i" to="10" from="1">
UPDATE a
SET testA='a'
</cfloop>
</cfquery>
will only return the affected row count for the last SQL statement (or first, haven't tested which yet). or if you wrap your SQL code in NOCOUNT blocks (in sql server this tells the db not to return row counts to the client):
<cfloop index="i" to="10" from="1">
UPDATE a
SET testA='a'
</cfloop>
</cfquery>
<cfquery datasource="lab" name="q" result="r">
SET NOCOUNT ON
UPDATE a
SET testA='a'
SET NOCOUNT OFF
</cfquery>
you won't get anything back for recordcount.
SET NOCOUNT ON
UPDATE a
SET testA='a'
SET NOCOUNT OFF
</cfquery>
posted by Paul Hastings @ 11:43 AM
1 Comments:
Excellent, this is something that has been very much missing to date.
Post a Comment
<< Home