Returning a structure containing an array of Structures from ColdFusion Webservice

Encountered this issue when I was trying to return a structure containing an Array of structures. The structure I returned contained the array but with empty structures. Though it was accessed from another Coldfusion system, the web service interprets the complex data structure in a different way.

There are couple of useful documents at the adobe help docs on how to handle the complex data types in case of web services.

This is how the code looked when I encountered the issue.

1. <cfcomponent>
2. 
   
3.     <cffunction name="testStructReturn" access="remote" returntype="struct">
4.          
5.           <cfset var resultStruct = StructNew() />
6.           <cfset resultStruct.testArray = ArrayNew(1) />
7.           <cfset var tempStruct = StructNew() />
8. 
   
9.           <cfset var qryResult = QueryNew("") />
10. 
    
11.           <cfquery name="qryResult" datasource="testDSN">
12.                Select first_name, 
13.                           last_name 
14.                from employee
15.           </cfquery>
16. 
    
17.          <cfset resultStruct.companyName = "zzz company" />
18.          <cfset resultStruct.companyLocation = "Gandhi Bazaar, Tirunelveli - 2" />
19.          
20.          <cfloop from="1" to="#qryResult.RecordCount#" index="i">
21. 
    
22.                <cfset tempStruct = StructNew() />
23.                <cfset tempStruct.firstName = qryResult[i][0] />
24.                <cfset tempStruct.lastName = qryResult[i][1] />
25.                 
26.                 <cfset ArrayAppend(resultStruct.testArray, tempStruct) />
27.           
28.           </cfloop>
29. 
    
30.           <cfreturn resultStruct />
31.     </cffunction>
32. </cfcomponent>

The above code returned the resultStruct with the array, but empty structures within it. To resolve this. I had to change this line of code 26 to read like this..

<cfset ArrayAppend(resultStruct.testArray, serializeJSON(tempStruct)) />.

Happy coding!

Creating excel report by manipulating the Query result set

There was a situation where in I had to generate a report of team members of different teams, where in a single record should be there for each team with team member names in the same row. But then the database has 4 records for each team.

It was a bit of a challenge for me. Usually in these situations I would use the group attribute to loop through, but I used this query of queries to solve this.

<cfset filename = expandPath("./teamsReport.xls")>
  
 
  <cfset s = spreadsheetNew()>
  <cfset spreadsheetAddRow(s, "Team Id, Member 1,  Member 2,  Member 3 ,  Member 4 ")>
   
   <!--- Loop through the Query result set--->
  <!--- -Create a query header-->
    <cfset qryListTeam = QueryNew("Team_Id,Member_1,Member_2, Member_3,Member_4","Integer,VarChar, VarChar, VarChar,VarChar") />
    <cfset i=0 />
    <cfset j=1 />

Make a Query Of Queries call which would fetch the  4 records of the team.

      <cfoutput query="teamList" group="TEAM_ID">
      
            <cfset queryAddRow(qryListTeam,1) />
            <cfset querySetCell(qryListTeam,"Team_Id",#TEAM_ID#) />
                <cfinvoke component="tag_report" method="listTeamMembers" returnvariable="teamMemberList">
                      <cfinvokeargument name="team_id" value="#TEAM_ID#" >
                      <cfinvokeargument name="teamList" value="#teamList#">
             </cfinvoke>
      
 Build the  qryListTeam which we had already declared.

        <cfset i = 1 />
      
  <cfloop query="#teamMemberList#" startrow="1" endRow="#teamMemberList.RecordCount#">
                <cfif i eq 1>
                    <cfset querySetCell(qryListTeam,"Member_1",#MEMBER_NAME#) />
                    </cfif>
                <cfif i eq 2>
                    <cfset querySetCell(qryListTeam,"Member_2",#MEMBER_NAME#) /> 
                 </cfif>
                <cfif i eq 3>
                    <cfset querySetCell(qryListTeam,"Member_3",#MEMBER_NAME#) />
                 </cfif>
                <cfif i eq 4>
                    <cfset querySetCell(qryListTeam,"Member_4",#TA_MEMBER_NAME#) />
                    </cfif>
                <cfset i++ />
        </cfloop>
    </cfoutput>

    <!--- format header --->   
     <cfset spreadsheetFormatRow(s,
            {
                bold=true,
                fgcolor="lemon_chiffon",
                fontsize=14
            },
            1)>

Now using the custom built Query result set is ready to be used in our spreadsheet.

  <cfset spreadsheetAddRows(s, qryListTeam)>
  <cfset spreadsheetWrite(s, filename, true)>
    <cfheader name="content-disposition" value="attachment; filename=TeamsReport.xls">
   <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">