RE: CF9 cfquery not giving same insert results as CF7
> As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no > effect on > these. The only thing that does is prevent extra > network traffic by > stopping the extra reporting of how many rows were affected > for each > piece of the query. In CF8 it did have an effect on cfquery's in some situations. Specifically the simple INSERT followed by SELECT scope_identity() scenario. I do not about CF9. Though I will say the results in CF8 varied greatly depending on a number of conditions (statement used, driver version, etcetera). A lot of the issues were related to how the generated keys feature was implemented. http://www.mischefamily.com/nathan/index.cfm/2008/1/28/Problems-with-CF-8s-Generated-Keys-Feature ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330259 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF9 cfquery not giving same insert results as CF7
I don't know what CF9 is doing under the hood. What I do is put all the queries in stored procedures. At the beginning of the procedure I always put SET NOCOUNT ON and I always end with SET NOCOUNT OFF. If you have a procedure that is doing a lot of stuff it can reduce a lot of back and forth and network traffic. I had one procedure that did a lot of data pulls and combining of data, without using the nocount, the amount of messages about xx rows affected was way larger than the actual data that was returned. Sometimes it's the other way around. When I'm testing something I leave them off so I can see what is happening. Think of it sort of like turning off debug in production for cf. It's not really critical data, but if you don't do it you get a lot of overhead. Steve -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: Friday, January 29, 2010 9:37 AM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 Thanks. This gives me a bit of extra information on top of the research I've done on the topic. Now comes a few extended questions. :) 1. When a cfquery has a result attribute defined, it will return data about the query and in the case of an insert, the id of the inserted item. The assumption is that under the hood CF is using scope_identity() (for MSSQL). Do you have any idea if this is the case? Just trivia and something I should test anyway. 2. From what you say of NoCount, I assume that there would be a minor performance benefit for sql insert, update, and delete operations to use it, correct? Thanks -- Michael Dinowitz ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330256 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
Thanks. This gives me a bit of extra information on top of the research I've done on the topic. Now comes a few extended questions. :) 1. When a cfquery has a result attribute defined, it will return data about the query and in the case of an insert, the id of the inserted item. The assumption is that under the hood CF is using scope_identity() (for MSSQL). Do you have any idea if this is the case? Just trivia and something I should test anyway. 2. From what you say of NoCount, I assume that there would be a minor performance benefit for sql insert, update, and delete operations to use it, correct? Thanks -- Michael Dinowitz On Fri, Jan 29, 2010 at 9:24 AM, DURETTE, STEVEN J (ATTASIAIT) wrote: > > Use SCOPE_IDENTITY(). @@Identity returns the id for the last record > inserted. If an another insert into that table occurs between your > insert and the select @@identity (2 different people making inserts into > the db for example) you will get the id for the other record that was > inserted. > > Scope identity only pulls the last record for the current scope. Sort of > like putting something in a transaction. It all sticks together. > (however putting a query in a transaction with @@identity will not solve > the problem above). > > As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no effect on > these. The only thing that does is prevent extra network traffic by > stopping the extra reporting of how many rows were affected for each > piece of the query. > > Of course I'm assuming that this is MSSQL. > > Ste ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330255 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF9 cfquery not giving same insert results as CF7
Use SCOPE_IDENTITY(). @@Identity returns the id for the last record inserted. If an another insert into that table occurs between your insert and the select @@identity (2 different people making inserts into the db for example) you will get the id for the other record that was inserted. Scope identity only pulls the last record for the current scope. Sort of like putting something in a transaction. It all sticks together. (however putting a query in a transaction with @@identity will not solve the problem above). As for the SET NOCOUNT ON and SET NOCOUNT OFF, they have no effect on these. The only thing that does is prevent extra network traffic by stopping the extra reporting of how many rows were affected for each piece of the query. Of course I'm assuming that this is MSSQL. Steve -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: Thursday, January 28, 2010 9:10 PM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 I've found a query that used Select @@Identity as NewAgentID without having a Set NoCount on or off. This query still worked and did not have to be altered at all. That brings up the question if NoCount is needed and if it is needed, do both the @@identity and the SCOPE_IDENTITY() methods need it? What is the difference between the two methods? Select NewAgentID = SCOPE_IDENTITY() Select @@Identity as NewAgentID Thanks -- Michael Dinowitz On Wed, Jan 27, 2010 at 8:19 PM, Brook Davies wrote: > > I am curious, does the query use the NOCOUNT and @@identity SQL commands?? > > > SET NOCOUNT ON > insert into users_printprofiles > ( > ) > values( > ) > select newid=@@identity > SET NOCOUNT OFF > > > Brook > > > -Original Message- > From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] > Sent: January-27-10 1:26 PM > To: cf-talk > Subject: Re: CF9 cfquery not giving same insert results as CF7 > > > OK, so it turns out that this is a known issue that came into effect between > CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > >>I just ran into this and while it's new to me, I'm sure it's old news >>to others here. I'm looking at someones code from CF 7 where they have >>a cfquery inserting a record. The cfquery tag only has a name and a >>datasource. Immediately after the tag, there is a cfset that makes use >>of the query's name and the name of the identity field of the table >>that the data was inserted into. >> >> >>insert ... >> >> >> >>In CF 7, the newid would be the id of the newly inserted record. In CF >>9 all I get is an error. If I use a result attribute rather than a >>name attribute, I get a structure with the identity result buried in >>it. >> >>So here's the question. If it worked in 7 and not in 9, is there a >>simple fix to make it work like it did in 7 again? I'd rather not have >>to recode a whole slew of insert statements. >> >>Thanks >> >>-- >>Michael > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330254 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
I've found a query that used Select @@Identity as NewAgentID without having a Set NoCount on or off. This query still worked and did not have to be altered at all. That brings up the question if NoCount is needed and if it is needed, do both the @@identity and the SCOPE_IDENTITY() methods need it? What is the difference between the two methods? Select NewAgentID = SCOPE_IDENTITY() Select @@Identity as NewAgentID Thanks -- Michael Dinowitz On Wed, Jan 27, 2010 at 8:19 PM, Brook Davies wrote: > > I am curious, does the query use the NOCOUNT and @@identity SQL commands?? > > > SET NOCOUNT ON > insert into users_printprofiles > ( > ) > values( > ) > select newid=@@identity > SET NOCOUNT OFF > > > Brook > > > -Original Message- > From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] > Sent: January-27-10 1:26 PM > To: cf-talk > Subject: Re: CF9 cfquery not giving same insert results as CF7 > > > OK, so it turns out that this is a known issue that came into effect between > CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > >>I just ran into this and while it's new to me, I'm sure it's old news >>to others here. I'm looking at someones code from CF 7 where they have >>a cfquery inserting a record. The cfquery tag only has a name and a >>datasource. Immediately after the tag, there is a cfset that makes use >>of the query's name and the name of the identity field of the table >>that the data was inserted into. >> >> >>insert ... >> >> >> >>In CF 7, the newid would be the id of the newly inserted record. In CF >>9 all I get is an error. If I use a result attribute rather than a >>name attribute, I get a structure with the identity result buried in >>it. >> >>So here's the question. If it worked in 7 and not in 9, is there a >>simple fix to make it work like it did in 7 again? I'd rather not have >>to recode a whole slew of insert statements. >> >>Thanks >> >>-- >>Michael > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330250 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF9 cfquery not giving same insert results as CF7
I am curious, does the query use the NOCOUNT and @@identity SQL commands?? SET NOCOUNT ON insert into users_printprofiles ( ) values( ) select newid=@@identity SET NOCOUNT OFF Brook -Original Message- From: Michael Dinowitz [mailto:mdino...@houseoffusion.com] Sent: January-27-10 1:26 PM To: cf-talk Subject: Re: CF9 cfquery not giving same insert results as CF7 OK, so it turns out that this is a known issue that came into effect between CF 7 and CF 8. I can do one of three things here. 1. replace the name attribute with the result attribute: becomes 2. add a result attribute of the same name to the cfquery becomes 3. use a 'generic' result value to the cfquery becomes The problem that I see is that I don't know everywhere that the query results will be used. Is anything returned to the 'name' variable of the query? If not, then will removing it cause some other unforeseen effect? (experiments so far say no) Will the use of both a name and a result conflict? (experiments so far say no) How will all this interact with Fusebox? Is the results of a query in an act_ page supposed to be 'exposed' to the dsp_ page? If anyone has a clue or two on which way I should go on this, please let me know. I've got to fix this in almost 700 places (the client site is a royal mess of backed up and copied code) and a single, standard approach is what I need. Luckily, the RegEx to find all of the locations took 60 seconds to write. Literally. The hard is fast, the easy is slow. How standard for me. :) -- Michael >I just ran into this and while it's new to me, I'm sure it's old news >to others here. I'm looking at someones code from CF 7 where they have >a cfquery inserting a record. The cfquery tag only has a name and a >datasource. Immediately after the tag, there is a cfset that makes use >of the query's name and the name of the identity field of the table >that the data was inserted into. > > >insert ... > > > >In CF 7, the newid would be the id of the newly inserted record. In CF >9 all I get is an error. If I use a result attribute rather than a >name attribute, I get a structure with the identity result buried in >it. > >So here's the question. If it worked in 7 and not in 9, is there a >simple fix to make it work like it did in 7 again? I'd rather not have >to recode a whole slew of insert statements. > >Thanks > >-- >Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330198 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
Depending on the database, you could also try just adding: SELECT identityfield = SCOPE_IDENTITY() to the end of the insert queries, this one is SQL Server specific though. ~Mahcsig On Wed, Jan 27, 2010 at 1:25 PM, Michael Dinowitz < mdino...@houseoffusion.com> wrote: > > OK, so it turns out that this is a known issue that came into effect > between CF 7 and CF 8. I can do one of three things here. > > 1. replace the name attribute with the result attribute: > becomes > > 2. add a result attribute of the same name to the cfquery > becomes result="AddAgent"> > > 3. use a 'generic' result value to the cfquery > becomes result="QueryResult"> > > The problem that I see is that I don't know everywhere that the query > results will be used. > Is anything returned to the 'name' variable of the query? If not, then will > removing it cause some other unforeseen effect? (experiments so far say no) > Will the use of both a name and a result conflict? (experiments so far say > no) > How will all this interact with Fusebox? Is the results of a query in an > act_ page supposed to be 'exposed' to the dsp_ page? > > If anyone has a clue or two on which way I should go on this, please let me > know. I've got to fix this in almost 700 places (the client site is a royal > mess of backed up and copied code) and a single, standard approach is what I > need. Luckily, the RegEx to find all of the locations took 60 seconds to > write. Literally. > > The hard is fast, the easy is slow. How standard for me. :) > > -- > Michael > > >I just ran into this and while it's new to me, I'm sure it's old news > >to others here. I'm looking at someones code from CF 7 where they have > >a cfquery inserting a record. The cfquery tag only has a name and a > >datasource. Immediately after the tag, there is a cfset that makes use > >of the query's name and the name of the identity field of the table > >that the data was inserted into. > > > > > >insert ... > > > > > > > >In CF 7, the newid would be the id of the newly inserted record. In CF > >9 all I get is an error. If I use a result attribute rather than a > >name attribute, I get a structure with the identity result buried in > >it. > > > >So here's the question. If it worked in 7 and not in 9, is there a > >simple fix to make it work like it did in 7 again? I'd rather not have > >to recode a whole slew of insert statements. > > > >Thanks > > > >-- > >Michael > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330197 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF9 cfquery not giving same insert results as CF7
OK, so it turns out that this is a known issue that came into effect between CF 7 and CF 8. I can do one of three things here. 1. replace the name attribute with the result attribute: becomes 2. add a result attribute of the same name to the cfquery becomes 3. use a 'generic' result value to the cfquery becomes The problem that I see is that I don't know everywhere that the query results will be used. Is anything returned to the 'name' variable of the query? If not, then will removing it cause some other unforeseen effect? (experiments so far say no) Will the use of both a name and a result conflict? (experiments so far say no) How will all this interact with Fusebox? Is the results of a query in an act_ page supposed to be 'exposed' to the dsp_ page? If anyone has a clue or two on which way I should go on this, please let me know. I've got to fix this in almost 700 places (the client site is a royal mess of backed up and copied code) and a single, standard approach is what I need. Luckily, the RegEx to find all of the locations took 60 seconds to write. Literally. The hard is fast, the easy is slow. How standard for me. :) -- Michael >I just ran into this and while it's new to me, I'm sure it's old news >to others here. I'm looking at someones code from CF 7 where they have >a cfquery inserting a record. The cfquery tag only has a name and a >datasource. Immediately after the tag, there is a cfset that makes use >of the query's name and the name of the identity field of the table >that the data was inserted into. > > >insert ... > > > >In CF 7, the newid would be the id of the newly inserted record. In CF >9 all I get is an error. If I use a result attribute rather than a >name attribute, I get a structure with the identity result buried in >it. > >So here's the question. If it worked in 7 and not in 9, is there a >simple fix to make it work like it did in 7 again? I'd rather not have >to recode a whole slew of insert statements. > >Thanks > >-- >Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CF9 cfquery not giving same insert results as CF7
I just ran into this and while it's new to me, I'm sure it's old news to others here. I'm looking at someones code from CF 7 where they have a cfquery inserting a record. The cfquery tag only has a name and a datasource. Immediately after the tag, there is a cfset that makes use of the query's name and the name of the identity field of the table that the data was inserted into. insert ... In CF 7, the newid would be the id of the newly inserted record. In CF 9 all I get is an error. If I use a result attribute rather than a name attribute, I get a structure with the identity result buried in it. So here's the question. If it worked in 7 and not in 9, is there a simple fix to make it work like it did in 7 again? I'd rather not have to recode a whole slew of insert statements. Thanks -- Michael ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330190 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
