ColdFusion, Railo & JSON

Just a quick blog post about a minor difference between ColdFusion and Railo when returning a query as JSON.

I've got a CFC that returns JSON data to AJAX calls which looks something like this:

<cfcomponent output="false" hint="returns data to an ajax call">

  <cffunction name="GetProductsByKeyword" 
    access="remote" 
    returntype="query" 
    returnformat="json" 
    output="false" 
    hint="returns a query object as json">
    
    <cfargument name="keyword" type="string" required="true">
    
    <cfset var qResult = "">
    
    <cfquery name="qResult" datasource="mydsn">
      select id, title
      from Products
      where title like <cfqueryparam value="%#arguments.keyword#%" cfsqltype="cf_sql_varchar">
    </cfquery>
    
    <cfreturn qResult>
  </cffunction>
  
</cfcomponent>

The URL used for my Ajax method is:

http://mysite.com/remote/AjaxLookup.cfc?wsdl&method=GetProductsByKeyword&keyword=abc&returnformat=json&queryformat=column

When you call this ColdFusion 9 the returned JSON looks like:

{"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"ID":[10,31],"TITLE":["Easy as ABC","ABC to XYZ","]}}

On Railo, I get this:

{"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"id":[10,31],"title":["Easy as ABC","ABC to XYZ","]}}

Did you spot the difference?

Railo is returning the database column names as lowercase in the DATA section of the JSON, whereas ColdFusion is using uppercase. As JavaScript is case-sensitive, then my AJAX call was failing.

The solution is simple; just use uppercase column names in the query like so:

<cfcomponent output="false" hint="returns data to an ajax call">

  <cffunction name="GetProductsByKeyword" 
    access="remote" 
    returntype="query" 
    returnformat="json" 
    output="false" 
    hint="returns a query object as json">
    
    <cfargument name="keyword" type="string" required="true">
    
    <cfset var qResult = "">
    
    <cfquery name="qResult" datasource="mydsn">
      select ID, TITLE
      from Products
      where title like <cfqueryparam value="%#arguments.keyword#%" cfsqltype="cf_sql_varchar">
    </cfquery>
    
    <cfreturn qResult>
  </cffunction>
  
</cfcomponent>

Now both ColdFusion and Railo will return:

{"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"ID":[10,31],"TITLE":["Easy as ABC","ABC to XYZ","]}}

• Posted in:
• ColdFusion
• Railo

3 comments

1. So which exactly is correct in its sesnce, CF9 or railo
   
   Comment by Misty – December 14, 2011
2. Personally, I think Railo is correct in this instance, but only if it is doing what I think it is. In a case insensitive language like CF I believe its interactions with case sensitive languages should always involve what the developer actually typed.
   
   In this case, you queried the columns as lower-case (or camelCase) and provided that the JDBC driver likely returns the query in that exact same way, CF should output the query in that exact same way. I generally find that manipulating a user's data (in this case the user is the developer and the data is the query they typed in) should be the last resort for any tool simply because it almost always will break the expectation of the user at some point and it is kind of indefensible from a "Why did it do this?" standpoint, knowing that CF has to integrate with case-sensitive languages..
   
   Comment by Mike Kelp – December 23, 2011
3. I don't think either is right or wrong particuarly it's more just about how they work, As long as it's consistent (for that engine) then fair enough.
   
   If I was to get pernickety, then I'd be inclined to say that they are both wrong. Adobe ColdFusion should leave the case intact (as Mike said), but Railo should also preserve the case in the array of column names (both ACF and Railo return them as uppercase).
   
   Comment by John Whish – December 24, 2011