[cfaussie] RE: returning number of rows affected (updates, deletes)

[Stephen Milligan]

In SQL Server you would normally do this by supressing informational messages using NOCOUNT   e.g.   <cfquery datasource="#somedsn#' name="qInsertUser">   SET NOCOUNT ON   INSERT INTO users (firstname,surname) VALUES ('bob','cratchitt')   SET NOCOUNT OFF   SELECT @@IDENTITY AS newID </cfquery>   The problem being that by default the insert statement will return the number of rows affected. This gets supressed as an informational message (I don't remember whether that's at the ODBC/JDBC level, or by ColdFusion) and the rest of the information coming back is assumed to be junk even if it may contain a recordset.   If Informix has an equivalent of NOCOUNT then you should be able to do something similar.   Spike -------------------------------------------- Stephen Milligan Code poet for hire http://www.spike.org.uk Do you cfeclipse? http://cfeclipse.tigris.org   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Tuesday, June 15, 2004 4:24 PM To: CFAussie Mailing List Subject: [cfaussie] returning number of rows affected (updates, deletes) Hi all.   I’m looking for a way to return the number of rows affected on updates and deletes   kind of like this http://www.tek-tips.com/gfaqs.cfm/pid/183/fid/761 (Obtain "Rows Affected" by Execution of a Dynamic SQL Statement)   but the db isn’t SQLServer, I can only use CFQUERY (no stored procs) , etc.   eg: <cfquery name="test" datasource=”blah”>             update paststudent             set GIVEN_NAME = "Zoe-e"             WHERE GIVEN_NAME = "Zoe" and SCHOOL_SURNAME = "Zorpund" </cfquery> <cfoutput>#test.recordcount#</cfoutput>   which of course throws an error, since no actual records are returned.   I know the db (Informix) can return a struct containing things similar to SQLServer’s @@rowcount, @@error, etc, but how to access that from CF is the bit I’m stuck on.     any ideas?     thanx Barry Beattie CF Web Developer Alpha Business Systems [EMAIL PROTECTED]   Ph: +61 07 3216 0999   Unit 1, 31 Thompson Street Bowen Hills QLD 4006 www.alphabus.com.au   Virus Disclaimer: Please note that this email may contain computer-generated file(s) as an attachment.  Whereas we take every effort to protect our files from computer virus, we recommend that you check the file(s) with your own virus detection software prior to opening it.  If you would prefer to receive a hardcopy of the file please email or phone. Alpha Business Systems does not accept liability for the consequences of any computer viruses that may be transmitted with this email. Confidentiality: This email and any attached files are intended solely for the addressee(s) and may contain confidential and privileged information. If you received this email in error, please advise us by return email or phone and disregard the contents and immediately delete it and destroy any copies     --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/