A Funny Thing with ColdFusion - CFQuery Serialized Data

Page content

CFQuery SerializeJSON() and the structure returned

So this might not be the best programming at all, but recently I had a task, like many others. I needed to get data from the DB using ColdFusion and put it directly into a JavaScript plugin. The JavaScript plugin is a chart and graph one that the company bought years ago and no one has used until now.
This specific library is looking for pure data and then it will make the charts and such. No labels, no key->value pairs, no columns, just data. I want to take the data directly from ColdFusion (server side), possibly massage it there, and then in JavaScript (client side) stick right into the chart function and have it created. Some of the data can potentially be large and I don’t want users to experience any issues with processing the data if I was to massage it on the client side at all.

As a side note about “bad programming standards” to make the readers cringe, the ‘standard’ currently is to create an iFrame and then call the SSRS (MS SQL Server Reporting Services) server and report and have the iFrame refernce that report.

SO many issue with that, I won’t get into, least of all is interactivity with the response from the server, so there is hundreds of reports all doing the same exact thing. The other part that makes it cringe worthy for me (as SSRS and SSIS use to be my thing) is that no one really knows how to use SSRS (only 1 person has access and they need to make the reports for you) and none of the reports really look all that pretty. It kills the mojo or feel of a nice looking graph or chart.

That is why I am opting to go with the library that the company bought to make charts and graphs.

Thinking of it terms of a table, I just need the data portion, not the columns or headers. The goal is to do this as server side as possible, and while doing that I stumbled upon an interesting (funny because of my bad programming) thing from ColdFusion.

        <cfstoredproc datasource="#session.dsn#" returnCode="true" result="storesStatus" procedure="dbo.getStoreStatus" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@startDate"      NULL="#NOT(len(arguments.startDt))#"         value="#arguments.startDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@endDate"        NULL="#NOT(len(arguments.endDt))#"           value="#arguments.endDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@storeList"      NULL="#NOT(len(arguments.storeList))#"	   value="#arguments.storeList#" >

            <cfprocresult name="local.storeStatusSet" resultset="1" >
        </cfstoredproc>

Few things to note.
First, this is an example that is butchered to remove information from the client.
Second, I realize that I have the “result” parameter of the CFstoredProc and the CFProcResult set. There is a simple reason for that not covered here that is also obfuscated by the intentional butchering of the code and has to do with the “resultset=‘1’” parameter for the cfprocresult.
Third, a cfstoredproc will return a query or CFQuery data type as a result.

This is the interesting thing with ColdFusion and Queries and data.
One of the things I like about ColdFusion is its handling and use of queries and connection to the database. At this level from what I am showing you, you can see there are CFProcParam, or in a CFQUERY it would be CFQueryParam and you have to define the data type. ColdFusion does a great job enforcing this data type and limiting exposure to SQL Injection. Granted, there are ways around it, and some people could be passing something from the URL or FORM or ARGUMENTS scope directly thus mitigating any protection from potential SQL Injection. The intersting thing is the response though.
ColdFusion has a “query” data type, which gets confusing to talk about and is often just referred to as the “cfquery” data type so people know when you are talking about an actual query and the data type. This data type though is basically a table, and its great. It makes it really easy to work with and spit out some HTML and loop through and such. Often programmers won’t need much else than the result and a loop and things are done. The loop in ColdFusion (CFLoop) has a parameter to say that you are looping through a query, as opposed to an array, or list, or just a general loop with limits. It works great.

Now that we have the data set though from ColdFusion, it gets returned back to the clients JavaScript. I am not building a structure or doing a loop with the data, I just want to pass the raw data to the library and have the library do what it was built to do. Nothing else.

As with many other languages, there is a “serialize to JSON” method/function in ColdFusion. Awesome!
We serialize that and return it to the client….

        <!--- serialize in JSON format the data returned from the query --->
        <cfset local.response = SerializeJson(local.storeStatusSet)>

        <cfreturn local.response>

If we were to do a console.log() of the returned data, or do a <cfdump var="#local.response#"> (on the JSON serialized result) before doing the return we would see something interesing. We see that the “COLUMNS” and the “DATA” are broken up into two different objects in the JSON array. It is broken up into the “COLUMNS” object first, and then the “DATA” object. This is the default behavior of SerializeJSON() which can be altered to be a key->value pair instead by passing in a paramter. But that is not what I am looking for here. I am looking for just the “DATA” portion of that JSON Serialized CFQuery result.
This returned structure is great to work with and often not the issue at all. Often, we want to loop through those two things separatly or know the name->value pair relationship. Here, I just want data (I think I mentioned that), but below is a simple example of what I get.

{
  "COLUMNS": ["Issue", "Total"],
  "DATA": [
    ["Carts / Tools", 1],
    ["Dumpster", 1],
    ["Insurance", 2],
    ["Maintenance", 4],
    ["Pay Date", 1],
    ["Rate/Fee", 13],
    ["Security", 1],
    ["Staff", 1],
    ["Survey", 1],
    ["Systems", 3],
    ["Website", 2]
  ]
}

In ColdFusion we can’t can’t just the “DATA” portion of the CFQuery before we serialize it, simply because there is no “DATA” object in the CFQuery.

Ultimately, this is bad programming and the funny thing is I have to “Serialze” the CFQuery first, then access the “DATA” portion of the Serialized JSON (since its at this point that the “DATA” object is finally created), then I have to “Deserialize” that portion, only to then “Serizalize” it again in the result.
Ugly, but it give me the result of pure data being directly passed to the library as the library wants with minimal client processing.
Ugly code below.

        <!--- separating the "data" from the "columns" --->
        <cfset local.response1 = (DeserializeJSON(SerializeJson(local.storeStatusSet)).DATA) >

        <!--- serialize in JSON format the data returned from the query --->
        <cfset local.response = SerializeJson(local.response1)>

        <cfreturn local.response>

Now I get the following for my JSON result, which is what I am looking for.

[
  ["Carts / Tools", 1],
  ["Dumpster", 1],
  ["Insurance", 2],
  ["Maintenance", 4],
  ["Pay Date", 1],
  ["Rate/Fee", 13],
  ["Security", 1],
  ["Staff", 1],
  ["Survey", 1],
  ["Systems", 3],
  ["Website", 2]
]

These are just paired down examples, and in reality the code and result sets I am getting back are much more complicated, so any simplification helps.
Hopefully someone find this helpful.

Cheers!