Re: Update, Delete, Insert recordcount?
Mainly, it is a performance thing...when you have multiple statements returning their counts, it takes longer to get to that one statement that actually has the results you want.It may be milliseconds, but in some systems, that can add up...so it's just good practice. Also, I have read that not having set nocount on can mess up CF's ability to parse out an error message from the messages that are returned, as it gets confused by the multiple count messages that may precede the errorbut I haven't confirmed that one myself. But there is probably no problem using it on a single statement. On 1/22/06, Adrian Lynch [EMAIL PROTECTED] wrote: Why multiple statements, why not single statements? Ade -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: 22 January 2006 01:08 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? Also always a good idea to use SET NOCOUNT around multiple SQL statements to suppress informational messages... cfquery name=yourQuery datesource=yourDS SET NOCOUNT ON UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT AS RowsUpdated SET NOCOUNT OFF /cfquery On 1/21/06, Adrian Lynch [EMAIL PROTECTED] wrote: My name's not Bobby, but here goes: cfquery name=yourQuery datesource=yourDS UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT 'RowsUpdated' /cfquery cfoutput #yourQuery.RowsUpdated# /cfoutput Untested, and for SQL Server, but might be ok for Access. Adrian ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230215 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
Cheers. I can't say I've ever used it in cfquery, but it's in my SP templates. I could have sworn there was another reason for using it but I can't think what it is right now. Ade -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: 23 January 2006 12:21 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? Mainly, it is a performance thing...when you have multiple statements returning their counts, it takes longer to get to that one statement that actually has the results you want.It may be milliseconds, but in some systems, that can add up...so it's just good practice. Also, I have read that not having set nocount on can mess up CF's ability to parse out an error message from the messages that are returned, as it gets confused by the multiple count messages that may precede the errorbut I haven't confirmed that one myself. But there is probably no problem using it on a single statement. On 1/22/06, Adrian Lynch [EMAIL PROTECTED] wrote: Why multiple statements, why not single statements? Ade -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: 22 January 2006 01:08 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? Also always a good idea to use SET NOCOUNT around multiple SQL statements to suppress informational messages... cfquery name=yourQuery datesource=yourDS SET NOCOUNT ON UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT AS RowsUpdated SET NOCOUNT OFF /cfquery On 1/21/06, Adrian Lynch [EMAIL PROTECTED] wrote: My name's not Bobby, but here goes: cfquery name=yourQuery datesource=yourDS UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT 'RowsUpdated' /cfquery cfoutput #yourQuery.RowsUpdated# /cfoutput Untested, and for SQL Server, but might be ok for Access. Adrian ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230217 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Update, Delete, Insert recordcount?
Jim Wright wrote: I have read that not having set nocount on can mess up CF's ability in the past (ie pre MX) you needed it to pump multiple t-sql statements into 1 cfquery. the intermediate resultset counts returned to cf would otherwise confuse it. i forget who exactly figured that out but i think it was a team allaire member, maybe around the time of cf3. it's been a good practice since then (even longer in the sql server world, you'll see it recommended for use in sp that deal w/monster thumping tables all the time). ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230218 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
Why multiple statements, why not single statements? Ade -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: 22 January 2006 01:08 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? Also always a good idea to use SET NOCOUNT around multiple SQL statements to suppress informational messages... cfquery name=yourQuery datesource=yourDS SET NOCOUNT ON UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT AS RowsUpdated SET NOCOUNT OFF /cfquery On 1/21/06, Adrian Lynch [EMAIL PROTECTED] wrote: My name's not Bobby, but here goes: cfquery name=yourQuery datesource=yourDS UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT 'RowsUpdated' /cfquery cfoutput #yourQuery.RowsUpdated# /cfoutput Untested, and for SQL Server, but might be ok for Access. Adrian ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230188 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Update, Delete, Insert recordcount?
Hmm, in CFMX 7, the result attribute of cfquery gives the number of records returned. So it is only useful for SELECT statement. It should definitely give the number of records affected insteads so it would be useful for all four insert, select, update and delete statements. I hope they will change it in the next version. Johnny Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230179 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Update, Delete, Insert recordcount?
SQL can do it in a coupld of databases I think in sql server its @@rowcount? Bobby, Could you offer an example for dave? He mostly uses Access. Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230180 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
My name's not Bobby, but here goes: cfquery name=yourQuery datesource=yourDS UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT 'RowsUpdated' /cfquery cfoutput #yourQuery.RowsUpdated# /cfoutput Untested, and for SQL Server, but might be ok for Access. Adrian -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: 21 January 2006 18:26 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? SQL can do it in a coupld of databases I think in sql server its @@rowcount? Bobby, Could you offer an example for dave? He mostly uses Access. Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230181 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Update, Delete, Insert recordcount?
Also always a good idea to use SET NOCOUNT around multiple SQL statements to suppress informational messages... cfquery name=yourQuery datesource=yourDS SET NOCOUNT ON UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT AS RowsUpdated SET NOCOUNT OFF /cfquery On 1/21/06, Adrian Lynch [EMAIL PROTECTED] wrote: My name's not Bobby, but here goes: cfquery name=yourQuery datesource=yourDS UPDATE yourTable SET YourColumn = 'Something' SELECT @@ROWCOUNT 'RowsUpdated' /cfquery cfoutput #yourQuery.RowsUpdated# /cfoutput Untested, and for SQL Server, but might be ok for Access. Adrian -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: 21 January 2006 18:26 To: CF-Talk Subject: Re: Update, Delete, Insert recordcount? SQL can do it in a coupld of databases I think in sql server its @@rowcount? Bobby, Could you offer an example for dave? He mostly uses Access. Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230185 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Update, Delete, Insert recordcount?
Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230099 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
What type of database is it? ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Friday, January 20, 2006 11:21 AM To: CF-Talk Subject: Update, Delete, Insert recordcount? Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230100 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
SQL Server you should be able to use @@rowcount. -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: 20 January 2006 16:21 To: CF-Talk Subject: Update, Delete, Insert recordcount? Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230101 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Update, Delete, Insert recordcount?
Well, I was speaking generally, but the main use for it at the moment would be an as/400 iseries database set up through odbc. Although I could definately see use for it using mysql as well. On 1/20/06, Bobby Hartsfield [EMAIL PROTECTED] wrote: What type of database is it? ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Friday, January 20, 2006 11:21 AM To: CF-Talk Subject: Update, Delete, Insert recordcount? Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230102 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Update, Delete, Insert recordcount?
there I go with control + enter again... anyway... SQL can do it in a coupld of databases I think in sql server its @@rowcount? Update blah set blah = blah; select @@rowcount as rowsaffected; something like that anyway You could always SELECT just before an update/delete query using the same WHERE clause to know how many records are going to be updated or deleted. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Friday, January 20, 2006 11:21 AM To: CF-Talk Subject: Update, Delete, Insert recordcount? Hey guys, Is there any way that you make coldfusion give you how many rows were affected from Update Delete and insert statements? Even if its not in the recordcount variable (which I have to assume would not be possible)? -- Ryan Guill BlueEyesDevelopment [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230103 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
