RE: cf8 query weirdness
I found this thread on HoF. I'm running into the same issue, I'm doing an INSERT INTO SELECT and no resultset is available from the query. Was there any word on whether this was a bug or not? Thanks. Adrian http://www.adrianlynch.co.uk/ -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: 15 February 2008 23:01 To: CF-Talk Subject: RE: cf8 query weirdness It is a bug because it works differently from cf7. And the issue seems to be not that cf is returning the wrong resultset (ie rowcount instead of the identity), it's that it's not returning any resultset at all. If rowcounts cause these kinds of issues, wouldn't we have issues with pretty much every query? Russ -Original Message- From: C S [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 5:07 PM To: CF-Talk Subject: Re: cf8 query weirdness What is this nocount and why do I have to use it. Also can we confirm that this is a cf8 bug? Drivers process the results of each statement. Including those that only return a rowcount like updates or inserts: ie (1) row inserted. Cfquery is only supposed to return one resultset. Rowcounts can interfere with that. Using set nocount avoids that problem. That is not a bug IMO. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304092 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cf8 query weirdness
I remember getting this error but not sure if and how I resolved it (I am no longer getting this error). I am currently doing: Select IsNull(SCOPE_IDENTITY(), 0) as someid HTH Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299134 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cf8 query weirdness
That would help if for some reason no row was inserted. In my case, however, a row is being inserted, but it just doesn't work. I believe I'm running the latest version of CF8 (8,0,0,176276). Any other suggestions? Russ -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 4:11 PM To: CF-Talk Subject: Re: cf8 query weirdness I remember getting this error but not sure if and how I resolved it (I am no longer getting this error). I am currently doing: Select IsNull(SCOPE_IDENTITY(), 0) as someid HTH Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299136 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cf8 query weirdness
That seemed to work, but I don't think for the reason you specified. I have also gotten it to work this way My original query was something like this: cfquery name=someqry datasource=somedns Insert into sometable (somefields) Select somefields from someothertable Select SCOPE_IDENTITY() as someid Where someID=cfqueryparam cfsqltype=cf_sql_integer value=#arguments.someId# /cfquery I have changed it to the following: cfquery name=someqry datasource=somedns Declare @someid integer Set @someid=cfqueryparam cfsqltype=cf_sql_integer value=#arguments.someId# Insert into sometable (somefields) Select somefields from someothertable Select SCOPE_IDENTITY() as someid Where [EMAIL PROTECTED] /cfquery I think the cf engine has somewhere code similar to the following: If qrytext startswith insert or qrytext startswith update or qrytext startswith delete //null the query variable Qrynameref=null; Basically this is a bug in CF... how do we get them to fix it? I don't think we can roll out our app to CF8 until this is fixed... we potentially have dozens of these. Russ -Original Message- From: C S [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 4:16 PM To: CF-Talk Subject: Re: cf8 query weirdness Any other suggestions? Wrap the whole thing in a set nocount on/set nocount off to prevent rowcounts from interfering with the query results. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299139 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cf8 query weirdness
Why do you have both selects? The first one may be interfering. I assume you want the result set returned by the second? ~Brad -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 3:09 PM To: CF-Talk Subject: cf8 query weirdness I have something like that following (which I believe works on our cf7 servers). ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299140 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cf8 query weirdness
Any other suggestions? Wrap the whole thing in a set nocount on/set nocount off to prevent rowcounts from interfering with the query results. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299137 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cf8 query weirdness
My original query was something like this: cfquery name=someqry datasource=somedns Insert into sometable (somefields) Select somefields from someothertable Select SCOPE_IDENTITY() as someid Where someID=cfqueryparam cfsqltype=cf_sql_integer value=#arguments.someId# /cfquery Is there a typo in there? You have got the WHERE clause after the SCOPE_IDENTITY(). I would not expect that to work even with MX7. Do you mean this? SET NOCOUNT ON Insert into sometable (somefields) Select somefields from someothertable Where someID=cfqueryparam cfsqltype=cf_sql_integer value=#arguments.someId# Select SCOPE_IDENTITY() as someid SET NOCOUNT OFF ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299143 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cf8 query weirdness
Ok, I get it. The sometable stuff had thrown me off. You could call a stored proc instead and send out the identity column as an output parameter, but I'm guessing you don't want to have to re-write all that... ~Brad -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 3:44 PM To: CF-Talk Subject: RE: cf8 query weirdness The first select is part of the insert statement, the second select just gets the identity out. Russ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299146 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cf8 query weirdness
You could call a stored proc instead and send out the identity column as an output parameter, but I'm guessing you don't want to have to re-write all that... Yes, but an insert / select .. from table should work just by adding set nocount. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299147 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cf8 query weirdness
What is this nocount and why do I have to use it. Also can we confirm that this is a cf8 bug? Russ -Original Message- From: C S [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 4:44 PM To: CF-Talk Subject: Re: cf8 query weirdness You could call a stored proc instead and send out the identity column as an output parameter, but I'm guessing you don't want to have to re-write all that... Yes, but an insert / select .. from table should work just by adding set nocount. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299150 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cf8 query weirdness
Personally, I see zero value in stored procedures. In fact I believe they're a detriment, as you can't' easily store them in source control. Russ -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 4:49 PM To: CF-Talk Subject: RE: cf8 query weirdness Ok, I get it. The sometable stuff had thrown me off. You could call a stored proc instead and send out the identity column as an output parameter, but I'm guessing you don't want to have to re-write all that... ~Brad -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 3:44 PM To: CF-Talk Subject: RE: cf8 query weirdness The first select is part of the insert statement, the second select just gets the identity out. Russ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299151 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cf8 query weirdness
The first select is part of the insert statement, the second select just gets the identity out. Russ -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 4:35 PM To: CF-Talk Subject: RE: cf8 query weirdness Why do you have both selects? The first one may be interfering. I assume you want the result set returned by the second? ~Brad -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 3:09 PM To: CF-Talk Subject: cf8 query weirdness I have something like that following (which I believe works on our cf7 servers). ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299141 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cf8 query weirdness
What is this nocount and why do I have to use it. Also can we confirm that this is a cf8 bug? Drivers process the results of each statement. Including those that only return a rowcount like updates or inserts: ie (1) row inserted. Cfquery is only supposed to return one resultset. Rowcounts can interfere with that. Using set nocount avoids that problem. That is not a bug IMO. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299152 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cf8 query weirdness
It is a bug because it works differently from cf7. And the issue seems to be not that cf is returning the wrong resultset (ie rowcount instead of the identity), it's that it's not returning any resultset at all. If rowcounts cause these kinds of issues, wouldn't we have issues with pretty much every query? Russ -Original Message- From: C S [mailto:[EMAIL PROTECTED] Sent: Friday, February 15, 2008 5:07 PM To: CF-Talk Subject: Re: cf8 query weirdness What is this nocount and why do I have to use it. Also can we confirm that this is a cf8 bug? Drivers process the results of each statement. Including those that only return a rowcount like updates or inserts: ie (1) row inserted. Cfquery is only supposed to return one resultset. Rowcounts can interfere with that. Using set nocount avoids that problem. That is not a bug IMO. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299156 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cf8 query weirdness
It is a bug because it works differently from cf7. Not in my opinion. cfquery is only supposed to return one resultset. So to me that makes CF7's behavior a fluke/wrong. Yes, you can run multiple statements inside a cfquery, but I do not think it was intended as a replacement for stored procedures where you can run multiple statments and return multiple resultsets. And the issue seems to be not that cf is returning the wrong resultset (ie rowcount instead of the identity), it's that it's not returning any resultset at all. It is not returning a CF _named_ query. That is different than not returning any results. There is nothing that says a cfquery statement has to return a resultset (ie query with data). An UPDATE statement does not. I think you may be confusing the two. The first insert returns a rowcount. CF is treating that as a result. But since a rowcount is not a resultset (ie query with data), that is why your query variable is undefined. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299159 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
