Returning multiple cfquery result back to AJAX call

Question

I'm building a webpage that upon webform submission will make an AJAX call. AJAX then calls a cfc, where there's a function that calls a SQL stored procedure passing the webform values as input arguments. Till here is fine, but I'm confused over how to return data back to the calling AJAX function - when there are multiple resultsets returned by the stored procedure.

To make it more clear, the stored procedure internally has many SELECT transactions and each of these are being captured as part of cfprocresult in the cfstoredproc. If it was only one cfprocresult, passing it back to AJAX seems straight forward, but how do I send multiple cfprocresult results back to the calling AJAX function?

Pseudo code below:

AJAX: —————————

    $.ajax(

        {

            type: “post”,

            url: "Contacts.cfc",

            data: {

                method: "retrieveCustomers",

                username: this.DOMReferences.Name.val(),



                },

            dataType: "json",





            success: function( objResponse ){



                if (objResponse.SUCCESS){



// rest of AJAX to process returned data here

Contacts.cfc —————————

<cfcomponent>
 <cffunction name="retrieveCustomers" returntype="query">

<cfstoredproc datasource="#application.dsn_spoon#" procedure="proc_getUsers_paged">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#username#">
      <cfprocresult name="qResult1" resultset="1">
      <cfprocresult name="qResult2" resultset="2">
      <cfprocresult name="qResult3" resultset="3">
</cfstoredproc>


// how do i return all 3 result sets back to calling AJAX function?

 </cffunction>
</cfcomponent>

NOTE: I've very little control to change the stored procedure logic. Existing webpage was using a refresh of the page itself while calling the cfstoredproc component, and not using AJAX. I'm rewriting the UI to use AJAX instead.

Thanks!

Answer 1

You could try something like this:

<cfcomponent>
<cffunction name="retrieveCustomers" returntype="struct" returnformat="json" access="remote">

<cfstoredproc datasource="#application.dsn_spoon#" procedure="proc_getUsers_paged">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#username#">
      <cfprocresult name="qResult1" resultset="1">
      <cfprocresult name="qResult2" resultset="2">
      <cfprocresult name="qResult3" resultset="3">
</cfstoredproc>

<cfset _results = structNew()>
<cfset _results.result1 = qResult1>
<cfset _results.result2 = qResult2>
<cfset _results.result3 = qResult3>

<cfreturn _results> 


</cffunction>
</cfcomponent>

The caveat here is that you may need to massage the qResults. Normally, I would loop through each query and build an array. But that's just personal preference. What I typically do when returning back a query is loop over it and build a new structure. This is because I may want to perform addition actions against each property.

...
<cfset _results = structNew()>
<cfset _results.result1 = arrayNew()>
<cfset _results.result2 = arrayNew()>
<cfset _results.result3 = arrayNew()>

<cfloop query="qResult1">
    <cfset _result = structNew()>
    <cfset _result.col1 = qResult1.col1>
    <cfset _result.col2 = qResult1.col2>
    <cfset _result.col3 = qResult1.col3>
    <cfset arrayAppend(_results.result1,_result)>
</cfloop>

<cfloop query="qResult2">
    <cfset _result = structNew()>
    <cfset _result.cola = qResult2.cola>
    <cfset _result.colb = qResult2.colb>
    <cfset _result.colc = qResult2.colc>
    <cfset _result.cold = qResult3.cold>
    <cfset arrayAppend(_results.result2,_result)>
</cfloop>

<cfloop query="qResult3">
....
</cfloop>

<cfreturn _results>

For me, this is cleaner and easier to work with. It may require more overhead and server side processing, but it's easier to maintain. And there is no parsing on the JS side because structure returned is in proper JSON formatting.