Re: Stored Procedures in a CFC
Dayum, dude... And I thought *I* was long-winded... cl! Someone who goes thru keyboards faster than I do! hehe Heh. I dunno... I've had this little jobber here for several years. I like it ... I actually got it at a little independant computer shop around the corner from the place where I lived with my ex in Orlando, primarily because it was so small. I was tired of (still am) seeing or working with desk-swallowing behemoths with keys or buttons for everything from email and irc to emptying my recycle bin. Although Tiff occasionally takes all the keys off to clean it, which may help with its lifespan. Though for a few months I thought it was broke because (I realized accidentally) she interposed the alt and windows keys on the left side, so I thought the left alt key was broke until I turned the machine on one day and got an unexpected response from ctrl+windows+del. I just happened to be skimming the code example and saw the isDefined thing. I saw it, and questioned it. Glad you weren't put out. Nah, no reason to be. I'm all for annoying people, except people you respect. Heh. I'll try and stay on your good side then. :) You definitely make some good points. Especially since I also find structKeyExists() painful and/or difficult to type. And I ALWAYS spell it strucktKeyExists the first time around, and it gives me many errors, and the errors say unto me variable undefined and yea do I say then, farging iceholes, I done did it agin'... Did you see in the newspaper the other day that Marone declared Fargan' War? I use full syntax in on-list examples because I fear that those who read it may inherit (or discover) bad habits in my code, and they may later accuse me of being silly, lazy, or, worse, utterly lacking in proper geek comportment. I often describe actual code as pseudocode just because it's incomplete, like if I put elipses in it with a description like div...some stuff.../div. I guess that's par for course for me -- I often tend to think and/or speak in a very open-ended fashion... sometimes. :) ENFP if Meyers/Briggs interests you at all. Though I've gotten to a point where I'm sort of picking my battles with regard to giving people advice on mailing lists -- or in general. It comes from noticing over the years that although people are grateful for advice which helps them solve a problem they've asked about, they often ignore or are actively put off by unsolicited advice, even if on my end it's given in good spirit. So if a guy says, why doesn't this piece of code work these days I try and stick more to the subject of why that code doesn't work than offering advice for general good habbits. Otherwise I just get frustrated when I offer advice left and right only to see it ignored. There are exceptions -- I still rant about people using arrays where I think they ought to use a structure. As far as the DB example, that's a return from a SQL Server built-in stored procedure, and it will ALWAYS return a minimum of 2 query objects... since my data access layer is wrapped in a CFC and it's a one-off call to a simple DAO to execute the sproc it seemed much more sensible to code one function-local struct and return the data to the caller than to write a complicated system (it's a base DAO and 3 extension classes as it is) wherein I have getters and setters for anywhere between 1 and 8 resultsets, possibly more than one sproc call (depending on which extension class I'm calling), and all the data (or, at least very most of the data) is always going to be needed. Since they're not long resultsets anyway, I elected to sacrifice a little bit of overhead on behalf of a simpler design. Ahh, see in that context of it being a built in sproc it make a lot more sense to me. :) Given your description I'm thinking you're using SQL Server's features to perform some of the db meta-data management I accomplish in the onTap framework with JDBC methods. Incidentally, since I was returning the data in a struct, I discovered that I had a situation wherein my tableDAO may return either 7 OR 8 resultsets, depending on the structure of the table. The issue that ensued is very difficult to explain understandably so bear with me. snip What I ended up doing was adding a private method that reconstructs the return value on the fly, looping over the struct generated by the sproc call and keying off unique column names in the datasets to reorder the struct. As I write this, I begin to think it's probably not the best way, but... it worked. And it proved to me that there is definite value in API-based coding. I hope that made sense. I didn't find that hard to follow at all... Possibly because I'm familiar with the need to perform similar snafu-correction myself. The onTap framework was originally designed for CF5 and when MM released MX some of the changes to the behavior of functions caused a lot of the framework functions to break. Specifically the fact
RE: Stored Procedures in a CFC
No, not you, the other Jared! Wow, how embarrassed must you be right now?! :OD -Original Message- From: Jared Rypka-Hauer - CMG, LLC [mailto:[EMAIL PROTECTED] Sent: 04 March 2005 03:43 To: CF-Talk Subject: Re: Stored Procedures in a CFC Well Ade, thankee... Preachy doesn't float with me, it elicits an instant bye-bye, talk to the hand reaction. So I try to avoid it, though sometimes I fear I fail anyway. Thanks for the kudo. (And glad to oblige, that's what community's all about!) Laterz, J On Fri, 4 Mar 2005 01:36:02 -, Adrian Lynch [EMAIL PROTECTED] wrote: Very good point Jared. I did think of that but I couldn't at the time think of a reason why looping through the structure would be of any use but it's obvious when you think about it. You don't 'need' to know the names of the keys. SNIPPAGES Anyway, it's 01:30 and my bed looks very inviting. Sorry if my ramblings seem to contradict each other that's probably because they do! And d'you know what I like about Isaac's and Jared's posts? They don't in any way seem preachy. Which to me makes them damn fine posts :O) Ade -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197424 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: stored procedures in a CFC - short delay
I'm planning to respond to this thread, but it's going to have to wait until at least when I get back from the office this evening. I just got busy last night with some new framework features to encapsulate and/or search keywords for reuse in queries and since it's all downloaded here at the apartment and I'm not liable to have time to respond via the web interface at the office, it'll have to wait a bit. Anyway, just wanted to let everyone know I'm not ignoring you. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197432 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: Stored Procedures in a CFC
Hehe, you mean the Subway Jared, or the Galleria of Jewelry Jared, The Pretender Jared, or maybe some abstract class of Jared? I'm pretty hard to embarrass... and even when it happens, I usually give myself some credit anyway, for having done whatever it was, because nobody else was doing it! (PS - There's no other Jared here (leastways on this thread), Ade... so, well, about that feeling of being embarrassed, I'm not so sure... eh, never mind) ;) Laterz, J On Fri, 4 Mar 2005 10:35:48 -, Adrian Lynch [EMAIL PROTECTED] wrote: No, not you, the other Jared! Wow, how embarrassed must you be right now?! :OD -Original Message- From: Jared Rypka-Hauer - CMG, LLC [mailto:[EMAIL PROTECTED] Sent: 04 March 2005 03:43 To: CF-Talk Subject: Re: Stored Procedures in a CFC Well Ade, thankee... Preachy doesn't float with me, it elicits an instant bye-bye, talk to the hand reaction. So I try to avoid it, though sometimes I fear I fail anyway. Thanks for the kudo. (And glad to oblige, that's what community's all about!) Laterz, J On Fri, 4 Mar 2005 01:36:02 -, Adrian Lynch [EMAIL PROTECTED] wrote: Very good point Jared. I did think of that but I couldn't at the time think of a reason why looping through the structure would be of any use but it's obvious when you think about it. You don't 'need' to know the names of the keys. SNIPPAGES Anyway, it's 01:30 and my bed looks very inviting. Sorry if my ramblings seem to contradict each other that's probably because they do! And d'you know what I like about Isaac's and Jared's posts? They don't in any way seem preachy. Which to me makes them damn fine posts :O) Ade -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197442 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: Stored Procedures in a CFC
But, I do see you're using NOT isDefined(variables.var). I've been using NOT structKeyExists(variables,varName) (which I didn't know about until Sean yelled at me for using the other method... ;)) and I can say that it dramatically improves clarity, performance, and readability. It doesn't use the isDefined() search path, it checks the scope (or any struct you specify, actually) and immediately reports an answer. I've noticed improvements of up to 50-60% in response times for pages/code that are dependent on the isDefined/structKeyExists check process. In my own code I use structKeyExists() _nearly_ exclusively. Which is not to say that there aren't exceptions. The exception in general is when either a) I don't actually know what the name of the variable will be and as a result would have no data to pass to the structkeyexists() function -- this is an unfortunate circumstance which arrises occasionally when I want to conditionally check for the existence of a variable within a function given the name of the variable -- some variable names are fine in an isdefined() call, other quite common and perfectly valid variable syntaxes produce an exception (I think isdefined(myarray[1]) is all it takes)... I would _MUCH_ rather cf simply return false in _any_ case that the value is a string which can't be evaluated (rather than a complex object) in much the same way that isNumeric() will not error when given a complex object for examination. That not being the case, I end up being forced to resort to try-catch -- which is an awful way to manage that because the exception is a waste of resources - but I digress - or b) when I know that the syntax for the variable will be valid in an isdefined() call, but I'm not certain all the structures will exist: cfif isdefined(request.tapi.db.keys.exported) -- now in general, I know that request.tapi exists, but the db library may or may not have been loaded... further if none of the db.keys functions (iirc there are 3 of them) have been loaded, the db.keys structure won't exist either, so the alternative syntax for this would be: cfif structkeyexists(request.tapi,db) and structkeyexists(request.tapi.db,keys) and structkeyexists(request.tapi.db.keys,exported) Asside from being three times as much code, it also needs the structkeyexists() call three times, which means that asside from the extra code, the 50-60% increase in performance would likely be more than negated in this instance and the isdefined() call would actually be more efficient and save some time. Although as a general rule, I don't consider mechanical efficiency on the scale of a function like isdefined() or iif() a high priority. These functions add mere milliseconds (if that) to execution times, so when I'm working on making an application efficient I focus on much more productive methods such as database structure/indexes and incremental caching of complex process results which actually save orders of magnitude more time in execution and have far fewer points of impact in the application code. So the reason I actually use structkeyexists() in the majority of my code is not for the purpose of efficiency, it's simply for the purpose of the function being more precise -- of being a more exacting descrption of exactly what it is I want to know in my application. When I'm typing out examples for cf-talk or elsewhere I frequently revert back so isdefined() mostly because it's fewer keystrokes, and this will sound really silly... but I find the word structkeyexists to be a particularly annoying word to need to type, particularly repetitively... the position of the keys on the qwerty keyboard is just such that structkeyexists rather than sort of rolling easily off of my fingertips is actually a nearly painful excercise for my hands to work through. I wonder if that's had an impact on the adoption of a programming language features in the past? People not using it just because they find it annoying to type. Just FYI. /comments destination=S. Isaac Dealey As far as the original question... I've got one situation where I'm doing this: cfset var returnData = structNew() cfstoredprocedure... cfprocresult name=returnData.descriptor resultset=1 / cfprocresult name=returnData.columns resultset=2 / /cfstoredprocedure cfreturn returnData / I don't see a reason to split them up. If the stored procedure is a unified method for accessing data, and the data is always going to be returned in a block, why on earth would it make sense to divide them up into many getters/setters? I can see this making sense in situations where you may or may not return all the data from an sproc call, but in my case I'm *always* going to return *all* the result sets. It's not better encapsulation to divide it, IMO, in this case... it's a waste of effort. It would be difficult for me to say without knowing exactly what those resultsets contain and how their used if I could
RE: Stored Procedures in a CFC
Isaac, you've given me an answer for something I'd given up on, I wrap my SPs in functions some of the time, but because I don't want to call the SP twice on one page I've always assigned to a local variable and then accessed the resultset via that variable. For example: cfset qUser = GetUser(userID) cfoutput #qUser.Username# #qUser.Email# /cfoutput What I would rather do, and this is purely for aesthetic reasons on my part, is this: cfoutput #GetUser(userID).Username# #GetUser(userID).Email# /cfoutput And using your example of checking in the VARIABLES scope for the recordset before calling the SP I can do that without two or more calls to the DB. Glad I could help... Although my own CFC's tend to look a bit different... I'd have probably actually used a user CFC for the login and called it like this (again, pseudocode): cfset user = createObject(user).init(userid) cfoutput #user.getProperty(username)# #user.getProeprty(email)# /cfoutput In this setup, my init() function would clear all properties of the object (cfset structclear(variables.properties)) then check to make sure the userid is valid (exists in the database) and if the userid is valid then it would initialize some identifying variables for the user or maybe go and get property data for persistent storage if I happen to be using a DAO type system. I tend not to go and fetch all the object data at once, particularly if the object happens to have a lot of data and/or a lot of calculations involved in its data and instead resort to incrementally caching the object data within the object when it's requested. For a moment, looking at the variable name getUser I was thinking this was a CFC designed specifically for a login page, but now I'm thinking not... GetUser just contains methods that get data from the db based on a userid for reporting purposes? ... seems like an odd abtraction, but that's my best guess... However, now I've thought about it, I'm beginning to favour the former. How's that for fickleness?! :) We all got our quirks. Yes, but, you do need to know the public interface of an object which includes what it returns if anything. So for this example you need to know that putting the car in reverse and hitting the gas will back the car up. You could argue that knowing exactly what a function returns has no baring on it being less black boxed, regardless of it being a resultset, or a struct of queries. At what point does it become too open: - you know it returns a structure? - you know it returns a structure with a key of myResultset? - you know it returns a structure with a key that references a query? - you know it returns a structure with a key that references a query that has a column UserID? I don't have a firm opinion on this, but if it's not the first line then I don't think it would be anything other than the last. This is true -- although I think the general thinking (and don't quote me, because I'm just sort of passing on what I think I've heard/read here) is that the more complex the result of the function is, the less cohesive and therefore less generally useful the method is. In other words, if I create a method that returns several queries in a structure along with some associated string and array values, the likelyhood that all that data will be valid and useful in more than one place is not very high. Granted, in this instance we're only talking about a structure that contains two queries, so it's nothing that complicated, but the _theory_ says essentially that less is more. As an example, I have a cfc I'm working on where I work now which has a couple functions in it like this (and please, no critiques -- I'm putting lipstick on a pig as it is with this application): cffunction name=getCommissionTypes access=public returntype=string output=false hint=returns a comma delimited list of commission type codes cfreturn IBA,IBB,OBB,RBB /cffunction cffunction name=describeCommission access=public returntype=string output=false hint=returns a human readable description for a given commission type cfargument name=ctype type=string required=true cfswitch expression=#ctype# cfcase value=IBAcfset ctype = In House Agent/cfcase cfcase value=IBBcfset ctype = In House Broker/cfcase cfcase value=OBBcfset ctype = Outside Broker/cfcase cfcase value=RBBcfset ctype = Referring Broker/cfcase /cfswitch cfreturn ctype /cffunction One of the alternatives to this that I considered was simply having a single function which would return a structure containing the descriptions with the commission type codes as their keys. I decided against it because there are actually a number of circumstances in which it's useful to have a list of the commission types without necessarily needing the human-readable names, and rather than return a structure it's likely easier for everyone just to quickly get a comma delimited list and a
Re: Stored Procedures in a CFC
Dayum, dude... And I thought *I* was long-winded... cl! Someone who goes thru keyboards faster than I do! hehe I just happened to be skimming the code example and saw the isDefined thing. I saw it, and questioned it. Glad you weren't put out. I'm all for annoying people, except people you respect. You definitely make some good points. Especially since I also find structKeyExists() painful and/or difficult to type. And I ALWAYS spell it strucktKeyExists the first time around, and it gives me many errors, and the errors say unto me variable undefined and yea do I say then, farging iceholes, I done did it agin'... I use full syntax in on-list examples because I fear that those who read it may inherit (or discover) bad habits in my code, and they may later accuse me of being silly, lazy, or, worse, utterly lacking in proper geek comportment. As far as the DB example, that's a return from a SQL Server built-in stored procedure, and it will ALWAYS return a minimum of 2 query objects... since my data access layer is wrapped in a CFC and it's a one-off call to a simple DAO to execute the sproc it seemed much more sensible to code one function-local struct and return the data to the caller than to write a complicated system (it's a base DAO and 3 extension classes as it is) wherein I have getters and setters for anywhere between 1 and 8 resultsets, possibly more than one sproc call (depending on which extension class I'm calling), and all the data (or, at least very most of the data) is always going to be needed. Since they're not long resultsets anyway, I elected to sacrifice a little bit of overhead on behalf of a simpler design. Incidentally, since I was returning the data in a struct, I discovered that I had a situation wherein my tableDAO may return either 7 OR 8 resultsets, depending on the structure of the table. The issue that ensued is very difficult to explain understandably so bear with me. In a full response from the sproc, I'd have an 8-element struct, with a query in each. If the sproc only returned 7 results, it always dropped what I had defined as resultset 6. When that happened, CF dropped the 8th name from the struct, but left out resultset 6, which means that resultset 7 ended up in a struct element with the key name that should have been reserved for resultset 6, and resultset 8 ended up in the struct under a key name that should have been set aside for result set 7. What I ended up doing was adding a private method that reconstructs the return value on the fly, looping over the struct generated by the sproc call and keying off unique column names in the datasets to reorder the struct. As I write this, I begin to think it's probably not the best way, but... it worked. And it proved to me that there is definite value in API-based coding. I hope that made sense. Anywho, thanks for responding. Laterz, J On Fri, 4 Mar 2005 20:01:10 -0500, S. Isaac Dealey [EMAIL PROTECTED] wrote: In my own code I use structKeyExists() _nearly_ exclusively. Which is not to say that there aren't exceptions. The exception in general is when either a) I don't actually know what the name of the variable will be and as a result would have no data to pass to the structkeyexists() function -- this is an unfortunate circumstance which arrises occasionally when I want to conditionally check for the existence of a variable within a function given the name of the variable -- some variable names are fine in an isdefined() call, other quite common and perfectly valid variable syntaxes produce an exception (I think isdefined(myarray[1]) is all it takes)... I would _MUCH_ rather cf simply return false in _any_ case that the value is a string which can't be evaluated (rather than a complex object) in much the same way that isNumeric() will not error when given a complex object for examination. That not being the case, I end up being forced to resort to try-catch -- which is an awful way to manage that because the exception is a waste of resources - but I digress - or b) when I know that the syntax for the variable will be valid in an isdefined() call, but I'm not certain all the structures will exist: snippages -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197569 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:
RE: Stored Procedures in a CFC
I use structures to pass back multiple things from functions, but I'm not so sure you're right about not using an array. An array maps to the cfprocresult's resultset attribute(as it could equally to it's name attrib) but with the array you don't need to know the name used only the number of the resultset. I hope that makes sense, I've just had a not so powerful power nap and I tell ya, it hasn't done anything for my thinking just this minute. Ade Well my comments weren't really with regard to the procedure itself per se... I realize that the stored procedure only offers the number of the result set to inform you about what's in it (unless you get creative with output variables), but rather, if I'm just looking at a piece of code and I see this: cfset mydata = cfc.getData() cfoutput query=mydata[1].../cfoutput That's going to make a heck of a lot _less_ sense to me reading it as this: cfset mydata = cfc.getData() cfset qeury=mydata.products.../cfoutput The latter is rather self-documenting -- you know simply from reading it what it is... The former, having only a number is obscure and leads to programmers wondering what the hell is going on and then needing to go investigate further in order to understand it. This is in general a frequent problem with code that I see submitted to the cf-talk list (or in other places), I think particularly with programmers who come to CF from other languages where associative arrays are less friendly to use, like Java or ASP, where rather than using the extraordinarily simple and self-explanatory CF structure, they resort to an array and a collection of numeric variables. I see this frequently in Java class documentation on the Sun site, where the class has public properties like STATUS_AVAILABLE = 1 : STATUS_CANCELLED = 2 : and then in order to actually know what the object is doing you have to compare method results to these public properties instead of simply using the method result. And this sort of thing I _think_ often translates when people come to CF from other languages to ugly codeblocks like this: cfset productname = 1 cfset qty = 2 cfset unitcost = 3 cfset totalcost = 4 cfset product = ArrayNew(1) cfset shoppingcart[arraylen(shoppingcart)] = product cfset product[productname] = myquery.productname cfset product[qty] = form.quantity cfset product[unitcost] = myquery.unitcost cfset product[totalcost] = product[qty] * procut[unitcost] Instead of something more efficient, elegant, legible and bulletproof like this: cfset product = queryToStruct(myquery) cfset product.qty = form.quantity cfset product.totalcost = product.qty * product.unitcost Granted that I have taken the liberty of using a function to convert the query into a structure in this example (a good habbit anyway), which also eliminates a good bit of the code, but just simply not having the local variables for productname, qty, unitcost and totalcost just to be able to know where the item is in the array is a subtle but powerful thing. Consider for a moment if you hand off the original code using the array to another programmer who'd hever seen the code before or never worked with ColdFusion and they need to debug it. You might expect if they've read about ColdFusion a little they might use cfdump to display the contents of the shopping cart... and to their shock and dismay they would be met with a huge list of completely arbitrary and meaningless numbers, each associated with an arbitrary string which may or may not be what they think it is. Then they'd have to start comparing the local variables productname, qty, unitcost and totalcost to those numbers in order to figure out what's what. If they're bright, they'll figure this out right away and they'll start making the association as they flip back and forth between their browser and the code and they'll probably figure it out in a reasonably short period of time, but honestly, why put them through the trouble? Why make it more difficult for them - why make them have to think about that association of the arbitrary number 2 to the intuitively obvious name qty at all when it's _easier_ to just use the CF native structure and let them know _instantaneously_ what everything is from looking at the dump output? Anyway -- that's the end of my rant. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message:
Stored Procedures in a CFC
Hi there, I have a SP wrapped in a CFC. The SP returns two record sets, which i use cfprocresult to reference. How do i then return the 2 queries via the CFC? Cheers Andy J www.andyjarrett.co.uk ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197229 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: Stored Procedures in a CFC
Good question, I believe you will have to enter the two resultset objects into a single CF complex object (such as a Structure) and return that as a single value. -Original Message- From: Andy Jarrett [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 09:01 To: CF-Talk Subject: Stored Procedures in a CFC Hi there, I have a SP wrapped in a CFC. The SP returns two record sets, which i use cfprocresult to reference. How do i then return the 2 queries via the CFC? Cheers Andy J www.andyjarrett.co.uk ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197230 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: Stored Procedures in a CFC
Good question, I believe you will have to enter the two resultset objects into a single CF complex object (such as a Structure) and return that as a single value. That would be my choice. I think a structure makes more sense for that as this code: cfset data = myCFC.getQueries() cfloop query=data.getUsers.../cfloop cfloop query=data.getRoles.../cfloop Is much more legible (and self-documenting) than this code: cfset data = myCFC.getQueries() cfloop query=data[1].../cfloop cfloop query=data[2].../cfloop s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197237 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: Stored Procedures in a CFC
I use structures to pass back multiple things from functions, but I'm not so sure you're right about not using an array. An array maps to the cfprocresult's resultset attribute(as it could equally to it's name attrib) but with the array you don't need to know the name used only the number of the resultset. I hope that makes sense, I've just had a not so powerful power nap and I tell ya, it hasn't done anything for my thinking just this minute. Ade -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 13:50 To: CF-Talk Subject: RE: Stored Procedures in a CFC Good question, I believe you will have to enter the two resultset objects into a single CF complex object (such as a Structure) and return that as a single value. That would be my choice. I think a structure makes more sense for that as this code: cfset data = myCFC.getQueries() cfloop query=data.getUsers.../cfloop cfloop query=data.getRoles.../cfloop Is much more legible (and self-documenting) than this code: cfset data = myCFC.getQueries() cfloop query=data[1].../cfloop cfloop query=data[2].../cfloop s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197238 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: Stored Procedures in a CFC
I would do it differently (then returning a struct/array with both recordset). Please give comments/suggestions/whatevers as I'm just thinking out loud. In essence, your SP returns two recordsets which don't hold the same data, they could be related but they are not the same. Why not create two methods for your CFC each returning one of these recordsets. They would both use another private method which would run the SP and then store its results in private variables. It would also ensure that the SP isn't run again and again if it's been run recently. Two me that's better encapsulation. The Application doesn't have to know how the CFC gets the recordsets all it knows is that by calling either of the methods it will get back the data it needs. -- Marc ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197245 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: Stored Procedures in a CFC
Well, If the method called can call one proc and is returning two record sets which are required - sure they are not calling the same data sets but they may be required - and one hit to the DB is better than separate calls - if there is certainly good reason and better performance over your idea I would be happy to try it out! Also the SP would probably not get called again and again internally for as long as the Query/Query plan is cached on SQL Server. -Original Message- From: Marc Campeau [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 15:24 To: CF-Talk Subject: Re: Stored Procedures in a CFC I would do it differently (then returning a struct/array with both recordset). Please give comments/suggestions/whatevers as I'm just thinking out loud. In essence, your SP returns two recordsets which don't hold the same data, they could be related but they are not the same. Why not create two methods for your CFC each returning one of these recordsets. They would both use another private method which would run the SP and then store its results in private variables. It would also ensure that the SP isn't run again and again if it's been run recently. Two me that's better encapsulation. The Application doesn't have to know how the CFC gets the recordsets all it knows is that by calling either of the methods it will get back the data it needs. -- Marc ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197249 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: Stored Procedures in a CFC
I'm not sure about others, but I was talking about the call to the function that contains the two resultset SP. No matter how many layers you put between the SP call at some point you need to call a function that will run that SP. The cfstoredproc tag will return both queries regardless, you could construct the function to return one of the callers choosing, but from a simplistic approach returning both in either an array or a structure seems more useful. The point I was making was that you either reference them as: function()[1] and function()[2] or: function().resultsetOne and function().resultsetTwo one, the structure, exposes more about the function than the other, the array. I understand what you're suggesting in your post, something like: component getData SP resultset1 resultset2 /SP return resultset /getData getFirst return getData().resultset1 (or return getData()[1]) /getFirst getSecond return getData().resultset2 (or return getData()[2]) /getSecond /component But within getFirst() and getSecond() you still need to decide how you call getData and access the two resultsets, struct or array fashion. Not that it's hugely important! :O) Ade -Original Message- From: Marc Campeau [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 15:24 To: CF-Talk Subject: Re: Stored Procedures in a CFC I would do it differently (then returning a struct/array with both recordset). Please give comments/suggestions/whatevers as I'm just thinking out loud. In essence, your SP returns two recordsets which don't hold the same data, they could be related but they are not the same. Why not create two methods for your CFC each returning one of these recordsets. They would both use another private method which would run the SP and then store its results in private variables. It would also ensure that the SP isn't run again and again if it's been run recently. Two me that's better encapsulation. The Application doesn't have to know how the CFC gets the recordsets all it knows is that by calling either of the methods it will get back the data it needs. -- Marc ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197256 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: Stored Procedures in a CFC
The cfstoredproc tag will return both queries regardless, you could construct the function to return one of the callers choosing, but from a simplistic approach returning both in either an array or a structure seems more useful. The point I was making was that you either reference them as: function()[1] and function()[2] or: function().resultsetOne and function().resultsetTwo one, the structure, exposes more about the function than the other, the array. I understand but I feel like they both expose too much to the application. ;) I usually prefer structs becauses they're more literate but by hiding the resultsets in the CFC it doesn't matter whether you use an array, struct or whatever. I would do this: component private datasets = structNew(); private isLoaded = false; private function loadData() if not isLoaded sp result1 result2 /sp datasets.result1 = result1; datasets.result2 = result2; isLoaded = true; /if return; /function private function getResult1() loadData(); return datasets.result1; /function private function getResult2() loadData(); return datasets.result2; /function /component -- Marc ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197280 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: Stored Procedures in a CFC
Right, but in your getResult1() and getResult2() you access the key named in loadData(). That's what I'm saying(asking whether it's) bad. Would array syntax not hide better what loadData() does? -Original Message- From: Marc Campeau [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 17:04 To: CF-Talk Subject: Re: Stored Procedures in a CFC The cfstoredproc tag will return both queries regardless, you could construct the function to return one of the callers choosing, but from a simplistic approach returning both in either an array or a structure seems more useful. The point I was making was that you either reference them as: function()[1] and function()[2] or: function().resultsetOne and function().resultsetTwo one, the structure, exposes more about the function than the other, the array. I understand but I feel like they both expose too much to the application. ;) I usually prefer structs becauses they're more literate but by hiding the resultsets in the CFC it doesn't matter whether you use an array, struct or whatever. I would do this: component private datasets = structNew(); private isLoaded = false; private function loadData() if not isLoaded sp result1 result2 /sp datasets.result1 = result1; datasets.result2 = result2; isLoaded = true; /if return; /function private function getResult1() loadData(); return datasets.result1; /function private function getResult2() loadData(); return datasets.result2; /function /component -- Marc -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.6.0 - Release Date: 02/03/2005 ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197284 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: Stored Procedures in a CFC
On Thu, 3 Mar 2005 13:54:45 -, Adrian Lynch [EMAIL PROTECTED] wrote: I use structures to pass back multiple things from functions, but I'm not so sure you're right about not using an array. An array maps to the cfprocresult's resultset attribute(as it could equally to it's name attrib) but with the array you don't need to know the name used only the number of the resultset. But returning an array causes the array to be *copied* (not a huge deal with only two entries but still...). An array doesn't give you nice names - it effectively requires calling code to know which order the two entries are in. It's much better from a maintenance point of view to use a struct with well-named keys. That way if you change the implementation of the CFC method, you can still return the two queries even if you move away from a SP with two result sets... (so the struct is better encapsulation because it hides the SP implementation). -- Sean A Corfield -- http://www.corfield.org/ Team Fusebox -- http://www.fusebox.org/ Breeze Me! -- http://www.corfield.org/breezeme Got Gmail? -- I have 49, yes 49, invites to give away! If you're not annoying somebody, you're not really alive. -- Margaret Atwood ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197323 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: Stored Procedures in a CFC
I think for readability returning the values in a structure is going to do a.k.a S.Isaac cfset data = myCFC.getQueries() cfloop query=data.getUsers.../cfloop cfloop query=data.getRoles.../cfloop But the idea of going through a function to get the different recordsets, and referencing via the array would mean that there is no need to know the name of the queries? defently beneficial. Cheers everyone for you help Andy On Thu, 3 Mar 2005 17:17:48 -, Adrian Lynch [EMAIL PROTECTED] wrote: Right, but in your getResult1() and getResult2() you access the key named in loadData(). That's what I'm saying(asking whether it's) bad. Would array syntax not hide better what loadData() does? -Original Message- From: Marc Campeau [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 17:04 To: CF-Talk Subject: Re: Stored Procedures in a CFC The cfstoredproc tag will return both queries regardless, you could construct the function to return one of the callers choosing, but from a simplistic approach returning both in either an array or a structure seems more useful. The point I was making was that you either reference them as: function()[1] and function()[2] or: function().resultsetOne and function().resultsetTwo one, the structure, exposes more about the function than the other, the array. I understand but I feel like they both expose too much to the application. ;) I usually prefer structs becauses they're more literate but by hiding the resultsets in the CFC it doesn't matter whether you use an array, struct or whatever. I would do this: component private datasets = structNew(); private isLoaded = false; private function loadData() if not isLoaded sp result1 result2 /sp datasets.result1 = result1; datasets.result2 = result2; isLoaded = true; /if return; /function private function getResult1() loadData(); return datasets.result1; /function private function getResult2() loadData(); return datasets.result2; /function /component -- Marc -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.6.0 - Release Date: 02/03/2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197324 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: Stored Procedures in a CFC
I don't see how using structures over arrays improves encapulation. Granted standardising the naming of the resultsets will help but how can it be better in having to know the names of two variables inside another function/cfc method? Good call on the value/reference point though, that always slips me by. Ade -Original Message- From: Sean Corfield [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 20:19 To: CF-Talk Subject: Re: Stored Procedures in a CFC On Thu, 3 Mar 2005 13:54:45 -, Adrian Lynch [EMAIL PROTECTED] wrote: I use structures to pass back multiple things from functions, but I'm not so sure you're right about not using an array. An array maps to the cfprocresult's resultset attribute(as it could equally to it's name attrib) but with the array you don't need to know the name used only the number of the resultset. But returning an array causes the array to be *copied* (not a huge deal with only two entries but still...). An array doesn't give you nice names - it effectively requires calling code to know which order the two entries are in. It's much better from a maintenance point of view to use a struct with well-named keys. That way if you change the implementation of the CFC method, you can still return the two queries even if you move away from a SP with two result sets... (so the struct is better encapsulation because it hides the SP implementation). -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.6.0 - Release Date: 02/03/2005 ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197329 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: Stored Procedures in a CFC
I think for readability returning the values in a structure is going to do a.k.a S.Isaac cfset data = myCFC.getQueries() cfloop query=data.getUsers.../cfloop cfloop query=data.getRoles.../cfloop But the idea of going through a function to get the different recordsets, and referencing via the array would mean that there is no need to know the name of the queries? defently beneficial. Cheers everyone for you help Another option is if your object has persistence and that persistence is maintained via a caching mechanism, that you could simply execute the stored procedure on an as-needs basis, and then use separate methods to return the individual record-sets from the stored procedure... as an example: cfcomponent cffunction name=getSP access=private cfargument name=refresh type=boolean default=false cfif refresh or not isdefined(variables.spr) cfset variables.spr = structnew() cfstoredproc procedure=mySP ... cfprocresult name=variables.spr.set1 result=1 cfprocresult name=variables.spr.set2 result=2 /cfstoredproc /cfif cfreturn variables.spr /cffunction cffunction name=getSet1 access=public cfreturn getSP().set1 /cffunction cffunction name=getSet2 access=public cfreturn getSP().set2 /cffunction /cfcomponent In this example, the caching mechanism is internal to the CFC, which is useful if you expect the methods might be called multiple times during a single page request (or within a single custom tag call) -- although it doesn't address the more wide-scale issue of caching the object incrementally in memory for the duration of a session or the application for true scalability. In general, I tend to view the multiple result sets returned from a stored procedure as being somewhat antithetical to the idea of a method or a function in general -- that is that a function accepts potentially multiple inputs to produce a singular output. I think of them as being like a funnel. The stored procedure is a many-to-many process, accepting potentially multiple inputs and producing potentially multiple outputs. More like a tree which has roots branching out at the bottom (input) and branches and leaves at the top (output). And in general what I know of encapsulation says that things should largely fallow the model of functions -- the black box (component) doesn't require you to know how the internals work -- you provide multiple inputs and get back a singular result. Put the car in reverse (input 1) and step on the gas (input 2) and the car backs up (singular result). You may know how the engine works to accomplish this, but that knowledge is irrespective of your ability to produce the singular desired result of backing the car. In a lot of cases we do end up returning structures from functions, but in general my thinking is that if it's possible to return a structure, then in most cases, particularly when the content of the structure is varied and complex such as queries, there should also be a consistent syntax to produce any of the individual values in that structure independent of the structure itself, whether that's getNameOfStructureKey() or getProperty(nameOfStructureKey) (my preference). At this point the function which returns the structure is merely a convenience afforded for the sake of processes which utilize a large amount of the returned data at once, rather than needing only certain discrete bits of it. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=48229DE=1 http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.sys-con.com/story/?storyid=48229DE=1 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197331 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: Stored Procedures in a CFC
Isaac, A quick comment on your CFC snippet... First of all, thanks for using real code instead of psuedo-code. I know sometimes the shorthand is nice for the writer, but it's hell on the reader. I do it myself, although lately I've been more inclined to jump into HomeSite+ and write it up and then copy/paste. It's actually faster that way than sending pseudo-code, more accurate, can be tested, and gives the reader the ability to copy/paste themselves and run the code. But, I do see you're using NOT isDefined(variables.var). I've been using NOT structKeyExists(variables,varName) (which I didn't know about until Sean yelled at me for using the other method... ;)) and I can say that it dramatically improves clarity, performance, and readability. It doesn't use the isDefined() search path, it checks the scope (or any struct you specify, actually) and immediately reports an answer. I've noticed improvements of up to 50-60% in response times for pages/code that are dependent on the isDefined/structKeyExists check process. Just FYI. /comments destination=S. Isaac Dealey As far as the original question... I've got one situation where I'm doing this: cfset var returnData = structNew() cfstoredprocedure... cfprocresult name=returnData.descriptor resultset=1 / cfprocresult name=returnData.columns resultset=2 / /cfstoredprocedure cfreturn returnData / I don't see a reason to split them up. If the stored procedure is a unified method for accessing data, and the data is always going to be returned in a block, why on earth would it make sense to divide them up into many getters/setters? I can see this making sense in situations where you may or may not return all the data from an sproc call, but in my case I'm *always* going to return *all* the result sets. It's not better encapsulation to divide it, IMO, in this case... it's a waste of effort. My other question is this: Why would you write an sproc that returns more data than you need? That's another waste of resources. I would also submit that it's a symptom of low cohesion. If your needs are such that an sproc is required and the results are variable, I'd use internal logic in the sproc to return the data that's necessary. Now, I can see situations wherein you may have a CFC in a shared scope that contains query data in it's instance data... if you don't need all the data all the time but are going to need it at various points, sure, that makes sense. getDataThis() and getDataThat() would be sensible. If we're talking about a DAO/Gateway that deals with data processing for an event, that's where I have questions... I'm mostly wondering why would you want to do that anyway? Laterz, J On Thu, 3 Mar 2005 15:59:36 -0500, S. Isaac Dealey [EMAIL PROTECTED] wrote: I think for readability returning the values in a structure is going to do a.k.a S.Isaac cfset data = myCFC.getQueries() cfloop query=data.getUsers.../cfloop cfloop query=data.getRoles.../cfloop But the idea of going through a function to get the different recordsets, and referencing via the array would mean that there is no need to know the name of the queries? defently beneficial. Cheers everyone for you help Another option is if your object has persistence and that persistence is maintained via a caching mechanism, that you could simply execute the stored procedure on an as-needs basis, and then use separate methods to return the individual record-sets from the stored procedure... as an example: -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197383 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: Stored Procedures in a CFC
On Thu, 3 Mar 2005 20:51:45 -, Adrian Lynch [EMAIL PROTECTED] wrote: I don't see how using structures over arrays improves encapulation. Granted standardising the naming of the resultsets will help but how can it be better in having to know the names of two variables inside another function/cfc method? Good call on the value/reference point though, that always slips me by. Ade I don't really see how it aids enapsulation either, but I do know that you don't need to know the names of your collection elements in order to loop it: cfloop collection=#dataStruct# item=i #i# = #dataStruct[i]#br /cfloop This gets access to your collection (structure variable) the same way an index loop would get you access to an array: cfloop from=1 to=#arrayLen(myArray)# index=i #myArray[i]#br /cfloop However, because of the structure's properties as an associative array, you have the extra readability and clarity when using cfoutput in a loop, cfdump, or any other method to reveal your variable's contents. Add to that the byRef (pass) vs. byVal (copy) difference between the two and I think there's compelling reason to use structs where possible instead of arrays. Aside from that, now that we have the ability to use array-style notation on structs, from a basic coding perspective there's no real difference other than the aforementioned benefits. And I don't think the numerical aspects of an array's indexing versus string indexes in a struct provide enough of a performance boost to justify the sacrifice of readability (which amounts to maintainability) and clarity. Laterz, J -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197386 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: Stored Procedures in a CFC
Very good point Jared. I did think of that but I couldn't at the time think of a reason why looping through the structure would be of any use but it's obvious when you think about it. You don't 'need' to know the names of the keys. Isaac, you've given me an answer for something I'd given up on, I wrap my SPs in functions some of the time, but because I don't want to call the SP twice on one page I've always assigned to a local variable and then accessed the resultset via that variable. For example: cfset qUser = GetUser(userID) cfoutput #qUser.Username# #qUser.Email# /cfoutput What I would rather do, and this is purely for aesthetic reasons on my part, is this: cfoutput #GetUser(userID).Username# #GetUser(userID).Email# /cfoutput And using your example of checking in the VARIABLES scope for the recordset before calling the SP I can do that without two or more calls to the DB. However, now I've thought about it, I'm beginning to favour the former. How's that for fickleness?! And in general what I know of encapsulation says that things should largely fallow the model of functions -- the black box (component) doesn't require you to know how the internals work -- you provide multiple inputs and get back a singular result. Put the car in reverse (input 1) and step on the gas (input 2) and the car backs up (singular result). You may know how the engine works to accomplish this, but that knowledge is irrespective of your ability to produce the singular desired result of backing the car. Yes, but, you do need to know the public interface of an object which includes what it returns if anything. So for this example you need to know that putting the car in reverse and hitting the gas will back the car up. You could argue that knowing exactly what a function returns has no baring on it being less black boxed, regardless of it being a resultset, or a struct of queries. At what point does it become too open: - you know it returns a structure? - you know it returns a structure with a key of myResultset? - you know it returns a structure with a key that references a query? - you know it returns a structure with a key that references a query that has a column UserID? I don't have a firm opinion on this, but if it's not the first line then I don't think it would be anything other than the last. In a lot of cases we do end up returning structures from functions, but in general my thinking is that if it's possible to return a structure, then in most cases, particularly when the content of the structure is varied and complex such as queries, there should also be a consistent syntax to produce any of the individual values in that structure independent of the structure itself, whether that's getNameOfStructureKey() or getProperty(nameOfStructureKey) (my preference). At this point the function which returns the structure is merely a convenience afforded for the sake of processes which utilize a large amount of the returned data at once, rather than needing only certain discrete bits of it. I see your point with this. Better to have: #user.getProperty(username)# #user.getProperty(password)# #user.getProperty(email)# than: #user.getUsername()# #user.getPassword()# #user.getEmail()# as a way of dealing with structures or queries. The former could deal with ever increasing/decreasing resultset rows, add a new column, user.getProperty(hairColour), take a column away and you don't have redundant methods that need removing. But, if you know the name of a struct key/query column, what's wrong with using it directly without going through a method, getProperty(someKeyOrColumn)? Ok, it is more encapulated, but there are ways around it that I wouldn't consider to be too much work to implement. If you start off with returning a query and all code that accesses it uses query.column you could always maintain the returning of a query even if it changes to something else. If they had a query to begin with, give a query even when it's actually a structure/array/simple value. I do like the getProperty() idea. Anyway, it's 01:30 and my bed looks very inviting. Sorry if my ramblings seem to contradict each other that's probably because they do! And d'you know what I like about Isaac's and Jared's posts? They don't in any way seem preachy. Which to me makes them damn fine posts :O) Ade -Original Message- From: Jared Rypka-Hauer - CMG, LLC [mailto:[EMAIL PROTECTED] Sent: 03 March 2005 23:39 To: CF-Talk Subject: Re: Stored Procedures in a CFC On Thu, 3 Mar 2005 20:51:45 -, Adrian Lynch [EMAIL PROTECTED] wrote: I don't see how using structures over arrays improves encapulation. Granted standardising the naming of the resultsets will help but how can it be better in having to know the names of two variables inside another function/cfc method? Good call on the value/reference point though, that always slips me by. Ade I don't really see how it aids enapsulation either, but I do
Re: Stored Procedures in a CFC
Well Ade, thankee... Preachy doesn't float with me, it elicits an instant bye-bye, talk to the hand reaction. So I try to avoid it, though sometimes I fear I fail anyway. Thanks for the kudo. (And glad to oblige, that's what community's all about!) Laterz, J On Fri, 4 Mar 2005 01:36:02 -, Adrian Lynch [EMAIL PROTECTED] wrote: Very good point Jared. I did think of that but I couldn't at the time think of a reason why looping through the structure would be of any use but it's obvious when you think about it. You don't 'need' to know the names of the keys. SNIPPAGES Anyway, it's 01:30 and my bed looks very inviting. Sorry if my ramblings seem to contradict each other that's probably because they do! And d'you know what I like about Isaac's and Jared's posts? They don't in any way seem preachy. Which to me makes them damn fine posts :O) Ade -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197417 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