Hey Geoff, is there a reason why the 3 resultSets are all part of the stored proc?
I'm not having a go at you, but trying to get a better picture of some of your initial design decisions. For us, since all 3 resultsets run off the same initial values and are all related ( while it looks like "parent, children, grandchildren", grandchild is actually a product of parent as well) and are gathered together it makes sence to get them all at one time(although it's an asp.net app and C#, the idea still holds). >From there the three resultsets populate the internal data of a Factory object and data transfer objects are "budded" off that. factory = createFactory(id) (this can be cached) parent = factory.GetParent() arrayChildren = factory.GetChildren() child = factory.GetChild(child_id) arrayGrandChildren = factory.GetGrandChildren() grandchild = factory.GetGrandChild(grandchild_id) firstchild = factory.GetFirstChild() that would also work with non-hierarchical data provided it makes sence to relate (lump) them all together. because it *is* hierarchical we can go further factory = createFactory(id) parent = factory.GetParent() arrayChildren = parent.GetChildren() child = parent.GetChild(child_id) arrayGrandChildren = child.GetGrandChildren() grandchild = child.GetGrandChild(grandchild_id) firstchild = parent.GetFirstChild() we *had* to do this (get lots of data up front and work off that). the (excessive) db hits (previously) were killing the app. and besides, the data logically belonged in the same domain. just 2c worth, nothing more barry.b On 4/12/06, Phillip Senn <[EMAIL PROTECTED]> wrote: > Geoff, > > What you're doing is learning the syntax of the language and wondering what > is the best practice. I'm in the same boat as you are. > > If I had time, I would study other people's code like Ray Camden's blogcfc, > and I would take time to look into frameworks such as Fusebox and Model Glue > to see how they do cfcs. > > What you want to do is encapsulate your ideas. "This goes in, this comes > out". Use the hint keyword when defining your function. If it starts to > feel like a run-on sentence, that might be an indication that it's doing too > much. > > Prove that your function is consistent with others by showing an example of > some other language function that returns three resultsets. > > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Geoff Parkhurst > Sent: Tuesday, April 11, 2006 6:12 AM > To: [email protected] > Subject: [CFCDev] Multiple stored proc resultsets > > I have a stored proc which returns 3 different resultsets as follows: > > <cfstoredproc datasource="#application.ds#" procedure="someproc"> > <cfprocparam dbvarname="prodid" value="#val(somevalue)#" > cfsqltype="cf_sql_integer" type="in"/> > <cfprocresult name="variables.prodinfo" resultset="1"/> > <cfprocresult name="variables.relatedprods" resultset="2"/> > <cfprocresult name="variables.images" resultset="3"/> > </cfstoredproc> > > I would like to stick this in a CFC, but I'm not sure what my returntype > should be... > > Would this be suitable: > > <cffunction name="productdetails" access="public" returntype="struct"> > <cfargument name="somevalue" type="numeric" required="yes"/> > <cfset var something=structnew()/> > > <!--- test arguments.somevalue a bit more to see if it's a positive > integer etc ---> > > <cfstoredproc datasource="#variables.ds#" procedure="someproc"> > <cfprocparam dbvarname="prodid" > value="#arguments.somevalue#" cfsqltype="cf_sql_integer" type="in"/> > <cfprocresult name="something.prodinfo" resultset="1"/> > <cfprocresult name="something.relatedprods" resultset="2"/> > <cfprocresult name="something.images" resultset="3"/> > </cfstoredproc> > <cfreturn something/> > </cffunction> > > I don't really know if this stored proc is faster than 3 individual > queries... I have a nagging feeling I should really have three cffunctions, > each responsible for returning a query on their own to keep things discreet > and modular, but I'm not sure. > > Any thoughts greatly appreciated. > Cheers, > Geoff > > > > > ---------------------------------------------------------- > You are subscribed to cfcdev. To unsubscribe, send an email to > [email protected] with the words 'unsubscribe cfcdev' as the subject of the > email. > > CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting > (www.cfxhosting.com). > > An archive of the CFCDev list is available at > www.mail-archive.com/[email protected] > > > > > ---------------------------------------------------------- > You are subscribed to cfcdev. To unsubscribe, send an email to > [email protected] with the words 'unsubscribe cfcdev' as the subject of the > email. > > CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting > (www.cfxhosting.com). > > An archive of the CFCDev list is available at > www.mail-archive.com/[email protected] > > > ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
