• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1384
  • Last Modified:

Stored procedure multiple recordsets only returning first recordset in CFML

I've only started to learn about cfstoredproc, so I may be making a simple and obvious error (but not to me)!

I have a trivial mysql stored procedure [named "test"] which returns two recordsets:
BEGIN
	SELECT 'Melbourne', 'Victoria'; 

	SELECT 'Hobart', 'Tasmania'; 
END

Open in new window

When I call this directly in mysql (using Navicat editor), it works perfectly and shows two separate results:

Result1:  "Melbourne", "Victoria"
Result2: "Hobart", "Tasmania"


I have then set up a CFC function to call this mysql stored procedure, as follows:
<cffunction name="getTest" access="remote">
    <cfstoredproc datasource="mysqlTracker" procedure="test">
        <cfprocresult name="q">
    </cfstoredproc>
    <cfreturn q>
</cffunction>

Open in new window

I then call this cfc function using jquery getJSON, as follows:
$.getJSON('cfc/AR.cfc?method=getTest&returnformat=JSON', function(json) {
			
});

Open in new window

However, when I inspect the returned JSON using Firebug, I see only the first recordset and not both as I would have expected (and want):

{"COLUMNS":["MELBOURNE","VICTORIA"],"DATA":[["Melbourne","Victoria"]]}

If I change the order of the mysql SELECT statements in the mysql stored procedure to:
BEGIN
	SELECT 'Hobart', 'Tasmania'; 

	SELECT 'Melbourne', 'Victoria'; 
END

Open in new window

Firebug then shows the returned JSON as (only):

{"COLUMNS":["HOBART","TASMANIA"],"DATA":[["Hobart","Tasmania"]]}

even though two results are shown calling the stored procedure in mysql (Navicat) -- with the results now being reversed (as expected) ie Result 1 is now old Result2 and Result2 is now old Result1.

How do I get CFML/jquery to show both recordsets?
0
ShanghaiD
Asked:
ShanghaiD
  • 3
  • 2
1 Solution
 
_agx_Commented:
First, I'm not sure you really need to return multiple resultsets. With that example it seems like you should be using UNION or UNION ALL to merge the SELECT results together into a single resultset:

* Note, I've added column aliases "City" and "State".

          SELECT 'Melbourne' AS City, 'Victoria' AS State;
          UNION
          SELECT 'Hobart', 'Tasmania';

If you really DO need multiple resultsets, you'd have to use multiple <cfprocresult> tags to capture each one.  Of course you'd also have to figure out how to package them properly with JSON too.

      <cffunction name="getTest" access="remote">
          <cfset var q1 = "">
          <cfset var q2 = "">
          <cfstoredproc datasource="mysqlTracker" procedure="test">
              <cfprocresult name="q1" resultSet="1">
              <cfprocresult name="q2" resultSet="2">
            </cfstoredproc>
           ... package the results and return as json ...
     </cffunction>
0
 
_agx_Commented:
Heading to bed for some much needed sleep :)  Later!
0
 
ShanghaiDAuthor Commented:
Excellent!   resultSet=" " is what I was missing.  I DO need multiple recordsets -- the trivial example was not a real world one where the two recordsets are getting (a) detailed listing and (b) grouped total listing for the same data.

My full solution -- thanks to your help -- is:

<cffunction name="getTest" access="remote">
  <cfset struct1=StructNew()>
  <cfset struct1.q1="">
  <cfset struct1.q2="">
  <cfstoredproc datasource="mysqlTracker" procedure="test">
    <cfprocresult name="q1" resultset="1">
    <cfprocresult name="q2" resultset="2">
  </cfstoredproc>
  <cfset struct1.q1=q1>
  <cfset struct1.q2=q2>
  <cfreturn struct1>
</cffunction>
0
 
_agx_Commented:
Looks good. Don't forget to VAR scope all local variables as well (including query variables). Since you're returning a structure you could probably shorten it to:

<cffunction name="getTest" access="remote">
  <cfset VAR struct1=StructNew()>
  <cfstoredproc datasource="mysqlTracker" procedure="test">
    <cfprocresult name="struct1.q1" resultset="1">
    <cfprocresult name="struct1.q2" resultset="2">
  </cfstoredproc>
  <cfreturn struct1>
</cffunction>
0
 
ShanghaiDAuthor Commented:
Thanks for this!  I've learnt something new!  (again!).
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now