ColdFusion Query Page returning JSON

Question

So I am trying to repurpose an old Coldfusion page for an internal DBA tool to track long running queries.

The page currently does a query as a <CFQUERY name="GetLongRunners"> and then displays the data in a table format:

<table>
    <tr>
        <td>#GetLongRunners.spid#</td>
        <td>#GetLongRunners.Database#</td>
    </tr>
</table>

etc, etc

My goal is to strip out all of the display stuff, and be able to simply post to this page, and then receive JSON back with this same data. I played around with:

jsonData = SerializeJSON(#GetLongRunners#)

along with trying to be more specific with #GetLongRunners.database# and it does not seem to be working. The page shows 'jsonData = SerializeJSON(' and I get an error:

Complex object types cannot be converted to simple values.

I'm very new to ColdFusion, and am trying to encapsulate this functionality as much as I can, so I really only have to worry about posting to this page, and then I can get the raw JSON to work with in the front end.

All examples I found, built the queries in a very different way, and it seems to be outside my scope to do it this way.

I am picturing a simple way to serialize the query output as json and send it back as a JSON object. Either that, or build an array by looping through the results of the query, and then serializing that.

Any ideas?

Thanks!

score 1 · Answer 1 · edited Dec 02 '15 at 19:51

1

<cfsetting enablecfoutputonly="true">

<!--- logic/calculation --->
<cfset jsonData = SerializeJSON( GetLongRunners )>

<!--- lastly, output the result --->
<cfoutput>#jsonData#</cfoutput>

edited Dec 02 '15 at 19:51

Leigh

28,765
10
55
103

answered Dec 02 '15 at 19:47

Henry

32,689
19
120
221

1

Just found out the hard way that this box is still on CF 7 >. – Jacob Dec 02 '15 at 20:00
1

There are CFCs around to serialize Coldfusion objects to JSON like http://svn.riaforge.org/cfjson/trunk/cfjson.cfc You might want to loop over the query and build an array, that might be easier to handle on the Javascript side. – Bernhard Döbler Dec 02 '15 at 20:30
1

@Jacob - Ouch. Try the [JSONUtil.cfc mentioned here](http://stackoverflow.com/questions/25190654/how-to-parse-json-returned-in-coldfusion/25252656#25252656). (Edit) I agree with Bardware about queries. The default structure is a bit unwieldy, but ... get it working with a plain vanilla query object first. Then tweak it. – Leigh Dec 02 '15 at 20:30

score 1 · Accepted Answer · edited May 23 '17 at 11:59

If you are on CF7, try the JSONUtil.cfc. Just download the CFC and install it somewhere under the web root. Then it is ready to use.

Inside your .CFM script, run the query, create an instance of the JSONUtil, and invoke the serializeJSON() method. That is it.

<cfsetting enablecfoutputonly="true">
<cfquery name="GetLongRunners">.... your query here .... </cfquery>
<cfset util = createObject("component", "path.to.JSONUtil")>
<cfcontent type="application/json" reset="true"> 
<cfoutput>#util.serializeJSON(GetLongRunners)#</cfoutput>

As Henry noted, extra white space can be problem when using .cfm files in this way. Be sure to include the cfsetting at the very top of your script. It will suppress output from everything except content wrapped in <cfoutput> tags.

Truthfully I would use a CFC for this instead, but one thing at a time. If you are interested, this thread has an example of using a remote function as well as converting a query object into a more friendly format ie array of structures.

ColdFusion Query Page returning JSON

2 Answers2