RE: [ACFUG Discuss] Query or Stored Proc calling
Wow, I had sent this a long time before it arrived (sent at 10:12) well before Ajas replied to Derrick and Teddy. Sorry. Don't know why it got held up (as did a few messages of mine to the list, which all arrived around 12). Odd. /charlie From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Charlie Arehart Sent: Thursday, February 04, 2010 10:12 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Query or Stored Proc calling Ajas, given your experience with CF, I'm a little confused how to reply. If this was a brand new user, I'd say that they have things backward and even then still misunderstood. First, the main difference is that the code in blue would execute once for each query result, whereas the code in red would execute only once, for the first record in the result set. Also, you go on to refer to things in terms of "executing the query" more than once, but that never happens in either example. The query is executed only once (unless the cfquery itself us somehow in another loop). The difference is only a matter of whether the query's results are looped over (first example) or not (second example). Second, it seems that you're maybe thinking that the use of the queryname before the column name has some affect on how many records are processed, but it does not. It's simply that if (in the first example) you're in a CFQUERY output loop, the queryname is optional (though recommended) as CF can figure out that the varname is a reference to a query column name because the queryname becomes a sort of default scope for the life of the query loop. But in the plain cfoutput (the second example, which is not a loop), then the queryname is not a part of the normal scope search, so CF would not know that the variable references were query columns, so you must provide them. Finally, none of this changes if it's a cfstoredproc/cfprocresult or a cfquery. Does that help? Or is there something more to your question that I'm not seeing? /charlie From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Ajas Mohammed Sent: Wednesday, February 03, 2010 10:21 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Query or Stored Proc calling Hi, I came across code like this select col1,col2,col3 from tbl where condition Now, instead of using col1 col2 col3 , the code uses getSomething.col1 getSomething.col2 getSomething.col3 The code in blue would do ONLY ONE CALL to query and display results. My take is that, the code in red is executing the query every time col1 thru col3 are referenced with query name. Is this correct? Also if getSomething was a cfprocresult name, the stored proc would be executed for every reference to stored proc name.colname right? Thanks, http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Query or Stored Proc calling
I offered my reply before seeing yours here, Derrick. Doh. But thanks for the confidence that I'd have something to offer. :-) As to your last point, I don't know. I mean, sure, those who move to more OO-like development will tend to consider options where query results are generated in a CFC method, and in that case they may return the results in "an actual structure of some kind (list, array, structure)", as you say, but I don't know about asserting that it's "always better". More of a case of different strokes, in my experience. :-) But I get your drift. /charlie From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Derrick Peavy Sent: Thursday, February 04, 2010 10:00 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Query or Stored Proc calling Ajas: I am not an expert on how CF actually executes, but I am pretty darn sure that the assumption you have is wrong. getSomething.col1 getSomething.col2 getSomething.col3 Is not re-executing the query. Once the query is run once, assuming the query is not in a loop itself, then it's done. It only runs once. If the original coder found that there was always only a single record, then there is no harm in the code you have. However, it's not great practice. If there is any chance of ever having more than one record from that query then your first code snip is best ( col1 col2 col3 ), and in general, is a better idea. I'm sure that Charlie or someone else can chime in on how CF handles the record set in memory and how it's referenced, etc., My guess would be that it's always better to use structured code or an actual structure of some kind (list, array, structure) to reference multiple record within a query. I think the question is also similar to the old, which is better, CFLOOP or CFOUTPUT. _ Derrick Peavy - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Query or Stored Proc calling
Ajas, given your experience with CF, I'm a little confused how to reply. If this was a brand new user, I'd say that they have things backward and even then still misunderstood. First, the main difference is that the code in blue would execute once for each query result, whereas the code in red would execute only once, for the first record in the result set. Also, you go on to refer to things in terms of "executing the query" more than once, but that never happens in either example. The query is executed only once (unless the cfquery itself us somehow in another loop). The difference is only a matter of whether the query's results are looped over (first example) or not (second example). Second, it seems that you're maybe thinking that the use of the queryname before the column name has some affect on how many records are processed, but it does not. It's simply that if (in the first example) you're in a CFQUERY output loop, the queryname is optional (though recommended) as CF can figure out that the varname is a reference to a query column name because the queryname becomes a sort of default scope for the life of the query loop. But in the plain cfoutput (the second example, which is not a loop), then the queryname is not a part of the normal scope search, so CF would not know that the variable references were query columns, so you must provide them. Finally, none of this changes if it's a cfstoredproc/cfprocresult or a cfquery. Does that help? Or is there something more to your question that I'm not seeing? /charlie From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Ajas Mohammed Sent: Wednesday, February 03, 2010 10:21 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Query or Stored Proc calling Hi, I came across code like this select col1,col2,col3 from tbl where condition Now, instead of using col1 col2 col3 , the code uses getSomething.col1 getSomething.col2 getSomething.col3 The code in blue would do ONLY ONE CALL to query and display results. My take is that, the code in red is executing the query every time col1 thru col3 are referenced with query name. Is this correct? Also if getSomething was a cfprocresult name, the stored proc would be executed for every reference to stored proc name.colname right? Thanks, http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] Query or Stored Proc calling
Thanks Teddy and Derrick. Good to know that I was wrong in my assumption. I felt getSomething.col1 getSomething.col2 getSomething.col3 is an overkill but apparently not. Thanks, http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives. On Thu, Feb 4, 2010 at 10:22 AM, Teddy R. Payne wrote: > Ajas, > I am not sure what you are looking for here. > > Given your example: > > >select col1,col2,col3 from tbl where condition > > > If you perform: > > #getSomething.col1# > > You will get back "col1" from the first record. The call to the database > does not occur in the cfoutput. The call happens in the and will > stay available for the life of template execution. > > As for stored procedures, you can call stored procedures within > using syntax for your associated RDMS(EXEC...etc). I have not been a fan of > that practice as I typically favor . If you need multiple > result sets, has a "resultset" attribute that you can assign. > "Resultset" is an integer and it refers to the logically produced data sets > in your stored procedure from top to bottom in that order. > > As with above, does not call the stored procedure. The stored > procedure would be called within or and stored in > the variables scope unless otherwise created. can "bind" to a > query using the "query" attribute of to the resultset of a > storedprocedure or a query by the reference to the name of the result set or > query name, but only in the sense of outputting the information stored in > the returned data that is stored in the variable. > > > Teddy R. Payne, ACCFD > Google Talk - teddyrpa...@gmail.com > > > > > On Wed, Feb 3, 2010 at 10:20 PM, Ajas Mohammed wrote: > >> Hi, >> >> I came across code like this >> >> >>select col1,col2,col3 from tbl where condition >> >> >> Now, instead of using col1 col2 >> col3 , >> the code uses getSomething.col1 getSomething.col2 >> getSomething.col3 >> >> The code in blue would do ONLY ONE CALL to query and display results. >> >> My take is that, the code in red is executing the query every time col1 >> thru col3 are referenced with query name. Is this correct? Also if >> getSomething was a cfprocresult name, the stored proc would be executed >> for every reference to stored proc name.colname right? >> >> Thanks, >> >> >> http://ajashadi.blogspot.com >> We cannot become what we need to be, remaining what we are. >> No matter what, find a way. Because thats what winners do. >> You can't improve what you don't measure. >> Quality is never an accident; it is always the result of high intention, >> sincere effort, intelligent direction and skillful execution; it represents >> the wise choice of many alternatives. >> > >
Re: [ACFUG Discuss] Query or Stored Proc calling
Ajas, I am not sure what you are looking for here. Given your example: select col1,col2,col3 from tbl where condition If you perform: #getSomething.col1# You will get back "col1" from the first record. The call to the database does not occur in the cfoutput. The call happens in the and will stay available for the life of template execution. As for stored procedures, you can call stored procedures within using syntax for your associated RDMS(EXEC...etc). I have not been a fan of that practice as I typically favor . If you need multiple result sets, has a "resultset" attribute that you can assign. "Resultset" is an integer and it refers to the logically produced data sets in your stored procedure from top to bottom in that order. As with above, does not call the stored procedure. The stored procedure would be called within or and stored in the variables scope unless otherwise created. can "bind" to a query using the "query" attribute of to the resultset of a storedprocedure or a query by the reference to the name of the result set or query name, but only in the sense of outputting the information stored in the returned data that is stored in the variable. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Wed, Feb 3, 2010 at 10:20 PM, Ajas Mohammed wrote: > Hi, > > I came across code like this > > >select col1,col2,col3 from tbl where condition > > > Now, instead of using col1 col2 /> col3 , > the code uses getSomething.col1 getSomething.col2 > getSomething.col3 > > The code in blue would do ONLY ONE CALL to query and display results. > > My take is that, the code in red is executing the query every time col1 > thru col3 are referenced with query name. Is this correct? Also if > getSomething was a cfprocresult name, the stored proc would be executed > for every reference to stored proc name.colname right? > > Thanks, > > > http://ajashadi.blogspot.com > We cannot become what we need to be, remaining what we are. > No matter what, find a way. Because thats what winners do. > You can't improve what you don't measure. > Quality is never an accident; it is always the result of high intention, > sincere effort, intelligent direction and skillful execution; it represents > the wise choice of many alternatives. >
Re: [ACFUG Discuss] Query or Stored Proc calling
Ajas: I am not an expert on how CF actually executes, but I am pretty darn sure that the assumption you have is wrong. getSomething.col1 getSomething.col2 getSomething.col3 Is not re-executing the query. Once the query is run once, assuming the query is not in a loop itself, then it's done. It only runs once. If the original coder found that there was always only a single record, then there is no harm in the code you have. However, it's not great practice. If there is any chance of ever having more than one record from that query then your first code snip is best (query="getSomething"> col1 col2 col3 cfoutput>), and in general, is a better idea. I'm sure that Charlie or someone else can chime in on how CF handles the record set in memory and how it's referenced, etc., My guess would be that it's always better to use structured code or an actual structure of some kind (list, array, structure) to reference multiple record within a query. I think the question is also similar to the old, which is better, CFLOOP or CFOUTPUT. _ Derrick Peavy derr...@derrickpeavy.com 404-786-5036 “Innovation distinguishes between a leader and a follower.” -Steve Jobs "A good deal that used to be a great deal, is not nearly as good as an awful deal that was once a horrible deal." - Dan Gilbert, http://bit.ly/8gUruX _ On Feb 3, 2010, at 10:20 PM, Ajas Mohammed wrote: Hi, I came across code like this select col1,col2,col3 from tbl where condition Now, instead of using col1 col2 col3 , the code uses getSomething.col1 getSomething.col2 getSomething.col3 The code in blue would do ONLY ONE CALL to query and display results. My take is that, the code in red is executing the query every time col1 thru col3 are referenced with query name. Is this correct? Also if getSomething was a cfprocresult name, the stored proc would be executed for every reference to stored proc name.colname right? Thanks, http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
[ACFUG Discuss] Query or Stored Proc calling
Hi, I came across code like this select col1,col2,col3 from tbl where condition Now, instead of using col1 col2 col3 , the code uses getSomething.col1 getSomething.col2 getSomething.col3 The code in blue would do ONLY ONE CALL to query and display results. My take is that, the code in red is executing the query every time col1 thru col3 are referenced with query name. Is this correct? Also if getSomethingwas a cfprocresult name, the stored proc would be executed for every reference to stored proc name.colname right? Thanks, http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
