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">

Escape pound sign(#) in Coldfusion

There came a scenario, where in the query string which carried the address of the users to another application had a # sign, which broke the system. Pound sign(#), having a special meaning in coldfusion, I had pretty tough time solving it. The solution is just simple.

eg If the address goes like this

    <cfset var address1 = #447, North Avenue />(Probably this is coming from the database)

    Doubling the hash would work if it's displayed in the cfm, wrapped in <cfoutput></cfoutput>. In this case I had to use this

    <cfset queryString = queryString & Replace(address1,"##","CHR(35)","all") />

•   ## - Here pound sign is escaped by doubling it.
• CHR(35) is the ASCII equivalent of  pound(#) sign.

New line string not appearing in Javascript(Jquery) alert

The error message had multiple lines. But the new line character I had inserted in the string was not recognized.

Eg.
To have a successful authentication, please ensure you have fulfilled the requisites.\nBecome a member by paying the membership amount.\nNot all services come free.

But the newline character was not at all recognized. Instead it alerted the string along with \n.

The reason being while the string reaches ajax from the controller, '\n' becomes '\\n'. The solution for this issue is this...

alert(msg.replace(/\\n/g,"\n"));

This would alert the message as intended.

Renewal of session and the expected behaviour of CFLOGIN

Lately I used CFLOGIN to manage user's session and its so powerful. It comes up with so many luxury of setting up the user roles, and at any point of time, IsUserLoggedIn(), GetAuthUser() comes in handy.

CFLOGOUT makes life much more easier to logout a user. But then it doesn't do everything like clearing session values, etc. In fact, clearing the session values have to be handled by us for the right behaviour of the application.

The real glitch is the way cflogin and session handles the timeout. There are two rules.

1. If the session expires, cflogin expires.
2. If the cflogin expires, session doesn't have to expire and  rather it doesn't expire.

So careful steps have to be taken while setting the session time out in app.cfc and idletimeout in cflogin. 

Now, if a user is given an option to renew a session, a soft refresh using ajax post would do. ie an ajax post to the server would be considered as a call to the server and the session would be considered as renewed.

But cflogin doesn't recognize that. In that case, a hard refresh of the page is needed to make a call to the application/web server. That would let the cflogin renew its session too.

FTP made easier with CFFTP - Downloading zip file using cfftp

CFFTP is a powerful feature in coldfusion which allows file operations without hassles. To start with it might throw challenges but then it's not hard to master.

To get a file from a server using FTP. Adobe docs have very detailed explanation of this tag. You can find this here.

1. Open a connection.
                This is not a necessary option but then I'd choose this way.

                  <cfftp action="open" username="webuser" connection="ftpConnection"
                   password="secret" server="testftp.com" stoponerror="Yes" secure="true"
                   timeout="300"/>

                Timeout is optional, I'd rather specify it.

2. GetFile

                Let me give an example for GetFile operation.

                Get the current path using the GetCurrentDir action. This would help us to avoid errors like file not found.

                <cfftp connection=MyFTPConnection action="GetCurrentDir" stoponerror="Yes">


               <cfftp action="getFile" connection="ftpConnection"
                   localfile="/home/data/downloadedFile.zip"
                   remotefile="#cfftp.returnvalue#/gallery/images.zip"
                   transfermode="auto" failifexists="no" timeout="300" passive="true"/>

3. Close the connection

               <cfftp action="close" connection="MyFTPConnection" stoponerror="Yes"/>


Now the required operation can be performed on that. Let me know if any of you have any questions.

Add a Date/time Column with a default date to a table in MySQL

This is again a mundane task which any one of us might have been required to do. Please find the simple DDL.

If the requirement is to alter table Users with a pwdchangedate with a default date of today, here goes the DDL

ALTER TABLE Users
    ADD pwdchangedate
    DATETIME
    NOT NULL
    DEFAULT now();