A reader asks:
Oh Jedi, please tell me the most effecient way to return 2 resultsets from a CFC? In a CFC I have a stored proceedure which returns 2 resultsets (2 queries). I need to send both of these back to the calling cfml page. At the moment I'm doing this by calling the CFC using dot notation and referencing the 1st resultset in the normal way and the 2nd resultset as obj.resultset because in the CF I've called it this.resultset (using the THIS scope). Thanks.
Your question could actually apply to just about anything. What if you needed to return an array and a string? A query and an array? An array and a weapon of mass destruction? For any complex result like this, I'd just consider a simple structure. You could place both resultsets in a structure with appropriately named keys. If your result is complex enough, you could consider returning a CFC. So your code could look like so....
<cfset result = myCFC.getComplexCrap()>
<cfset foo = result.getFoo()>
<cfset goo = result.getGoo()>
What's nice about the CFC approach is that your calling code doesn't have to know about the structure of the result. If you use a struct, you have to know ("I have keys Foo and Goo"). If your struct changed, all your calling code would have to be updated as well. The CFC approach keeps such details abstracted.
Archived Comments
Now THAT is good to know...
[Printing page for future reference....]
Mindful of the force, he is.
While that's a very valid question, and very useful technique, any method that is returning two or more distinct things is a good candidate for refactoring, since it's quite possibly doing two or more things. In most cases, a given method should just do a single thing (that is, be cohesive), and returning multiple results is a pretty strong indicator that it's doing multiple things.
Like all things, there are a million counter examples, but it's worth keeping in mind that the solution to the problem might not really be a solution, but just a cure for the symptoms of a real problem.
I disagree Barney - how could you refactor a stored proc that returns multiple resultsets? The whole beauty of a stored proc is that you're minimizing round trips to the database server with one efficient call.
That'd be one of the million counter examples.....
Personally, I'd avoid designing an app that way, but that's more personal preference. I greatly dislike having required business logic in both the DB and application code. But again, there's plenty of reasons for doing it that way.
A simpler example is returning a truncated result set and the number of total available rows for a paged display. getRows(criteria = myCrit, offset = 10, rows = 10) to return the second page of a 10-per-page result. Returns a result with a query (the data) and a totalRowsFound (the number of rows if truncation wasn't done), so you can render the pager with the right number of pages.
Ray, thanks for this method that I hadn't considered. But I'm wondering how to tell the CFC which function to execute and what the arguments are using your method?
Am I meant to do something like this:
<cfset result = myCFC.myFunction(arg1,arg2)>
Or do I have to called the CFC first using CFINVOKE or CFScript with CreateObject("component",etc) ?
I don't quite what you mean Gary. When you do foo.goo(), the CFC is foo, and the method is goo. That is basic syntax stuff. As for telling what args are in the method - that is on a case by case basic. Your business logic determines what arguments your CFC takes - determines how it works - determines what it returns, etc.
I think I may be misunderstanding your question.
Sorry for not being clear, Ray. Before I do the CFSETs that you use in your example, do I have to initiate/call the CFC first? CFCs take arguments so I'd need to pass some to it before I start doing your CFSET stuff, right? So do I pass the arguments before all your CFSETs within a CFINVOKE tag? If I'm still not explaining myself well can I include some example code in these comments?
It probably doesn't make sense to do so, but there is no reason you couldn't pass an array or query result sets back either. I agree with Barney that it does sound a little suspect though, and might be a good candidate for refactoring.
that should be "...array OF query..."
Oh yes. I was assuming you had an instance of the CFC first. In my case, myCFC was created by a createObject("component", "someCFC") type line.
Barney, Dave, I hear what you're saying, but I agree with what Todd says. In my current project I need 2 pieces of information from the db. One is a query with multiple rows and the other is a query with just 1 row (for headers in a table relating to the first query). They can be returned by either making 2 separate requests or getting both back at the same time using resultsets from an SP. This is very efficient and performance is top of my agenda.
I think if everything is broken down into separate CFCs and individual db requests then there's a danger of being too modular and the application takes a performance hit. It may not be noticeable with 1 page hit/second, but it will be for 5 or 10 times that.
OK - I hate to seem ignorant, but if I'm reading you right Gary, why don't you just do ONE query and use query.columnlist for your headers? I'm sure I must be reading something wrong....
Todd, it's not the column names I need in the 2nd query, it's organisational names which would be unfriendly (and illegal) to have as a column name. I can't combine the 2 queries using more joins as it's a complex schema and has a noticeable effect on the execution time. From a DBA perspective it has to be 2 queries. Thanks for your reply though and sorry I didn't make myself clear. (Must be the excitement of Christmas!)
I still don't get what Ray says. Here's some code.
<!--- CFC called myCFC.cfc --->
<CFCOMPONENT>
<CFFUNCTION access="public" name="goo">
<CFARGUMENT name="id">
<!--- Stored proc to grab 2 queries from db --->
<CFSTOREDPROC datasource="db1" procedure="getGoo">
<CFPROCPARAM type="In" cfsqltype="CF_SQL_INT" variable="@id" value="#arguments.id#">
<CFPROCRESULT name="results1" resultset="1">
<CFPROCRESULT name="results2" resultset="2">
</CFSTOREDPROC>
<CFRETURN results1>
</CFFUNCTION>
</CFCOMPONENT>
<!--- CFML page initiating CFC --->
<CFSET obj=CreateObject("component","myCFC")>
<!--- send ID as argument to function called goo --->
<CFSET goo=obj.goo("#id#")>
Goo is now a query containing rows from results1.
What I can't do using Ray's suggestions is to get back the rows from results2.
The only way I've been able to do this is to edit the CFC like so:
<CFPROCRESULT name="THIS.results2" resultset="2">
And the calling CFMLpage can access it like this:
<CFSET foo=obj.results2>
Is using the THIS scope in a CFC naughty? I'm sure Ray's method is more elegant but I can't get it to work with my example code.
Best practice dictates not to access the "this" scope directly. What about doing it this way:
<CFCOMPONENT>
<CFFUNCTION access="public" name="goo" returntype="array">
<CFARGUMENT name="id">
<!--- Stored proc to grab 2 queries from db --->
<CFSTOREDPROC datasource="db1" procedure="getGoo">
<CFPROCPARAM type="In" cfsqltype="CF_SQL_INT" variable="@id" value="#arguments.id#">
<CFPROCRESULT name="results1" resultset="1">
<CFPROCRESULT name="results2" resultset="2">
</CFSTOREDPROC>
<!--- assign the result sets to an array --->
<cfset results = ArrayNew(1) />
<cfset results[1] = results1 />
<cfset results[2] = results2 />
<cfreturn results />
</CFFUNCTION>
</CFCOMPONENT>
---------------------------------------------
Then your cfm:
<!--- CFML page initiating CFC --->
<CFSET obj=CreateObject("component","myCFC")>
<!--- send ID as argument to function called goo --->
<CFSET goo =obj.goo(id)>
---------------------------------------------
then your results are goo[1] and goo[2]
Dave, thanks. Can I ask why using "this." isn't good practice? I'll run some speed tests on using an array just to see what the performance hit is.
I'd like to hear from Ray to find out how his version works (which unfortunately I still don't understand)? Thank you.
Gary, here's an example of how you might adapt your code.
<cfcomponent displayname="Sproc.cfc">
<cffunction name="init" access="public" returntype="Sproc">
<cfset variables.instance = structNew()>
<cfset instance.results1 = QueryNew("")>
<cfset instance.results2 = QueryNew("")>
<cfreturn this>
</cffunction>
<cffunction name="setResults" returntype="void" access="public" output="false">
<cfargument name="results1" type="query" required="true">
<cfargument name="results2" type="query" required="true">
<cfset instance.results1 = arguments.results1>
<cfset instance.results2 = arguments.results2>
</cffunction>
<cffunction name="getResults1" returntype="query" access="public" output="false">
<cfreturn instance.results1>
</cffunction>
<cffunction name="getResults2" returntype="query" access="public" output="false">
<cfreturn instance.results2>
</cffunction>
</cfcomponent>
<!--- CFC called myCFC.cfc --->
<CFCOMPONENT>
<CFFUNCTION access="public" name="goo" returntype="Sproc">
<CFARGUMENT name="id" type="numeric" required="true">
<cfset var results1="">
<cfset var results2="">
<cfset var oSproc="">
<!--- Stored proc to grab 2 queries from db --->
<CFSTOREDPROC datasource="db1" procedure="getGoo">
<CFPROCPARAM type="In" cfsqltype="CF_SQL_INT" variable="@id" value="#arguments.id#">
<CFPROCRESULT name="results1" resultset="1">
<CFPROCRESULT name="results2" resultset="2">
</CFSTOREDPROC>
<cfset oSproc = createObject("component","Sproc").init()>
<cfset oSproc.setResults(results1,results2)>
<CFRETURN oSproc>
</CFFUNCTION>
</CFCOMPONENT>
<!--- CFML page initiating CFC --->
<CFSET obj=CreateObject("component","myCFC")>
<!--- send ID as argument to function called goo --->
<CFSET goo=obj.goo("#id#")>
<cfset q1=goo.getResults1()>
<cfset q2=goo.getResults2()>
Another example where this is appropriate besides stored procs is the cfsearch tag in CF7, which now returns a status struct in addition to the result recordset.
Hi Julian. I like the idea of what your code does and I'm sure I can use it elsewhere but I'm concerned about CF processing too many lines of code. I need to get the 2 queries back as quickly as possible as my site will be doing a lot of processing for a lot of users. The purists will tell me to buy more hardware if that's the case, but I can only buy so much.
thanks
I used to think the same way, Gary, but now code quality in terms of maintainability, reusability, encapsulation etc. is more important to me than the fastest excecution time I can possibly squeeze. Of course you may find later on that you do have a problem with performance, in which case the nice thing about web apps is that you can easily go back and refactor.
But I wouldn't assume that just because there are more lines of code or extra layers of objects it's going to be less efficient. Frequently the difference in speed is negligeable (look at your debugging: how many ms are really being added - frequently I see 0ms for quite lengthy function calls), but you'll probably find the improvement in code maintainability/extensibility makes a real difference.
It's down to judgement the end of the day, though, and you know your app better than me :-)