July 22, 2018

Pages


Search Site


Topics


Links


Archives

Working with a cfajaxproxy, ColdFusion recordset as JSON

May 28 2009 by Marc Funaro



Something new I picked up today; hopefully it will help simplify things for others.

1.? Assume a cfc called "Person" that has a remote function as follows:

<cffunction name="getPersonById" access="remote" output="false" returnType="Query" hint="Returns a ColdFusion Recordset containing a Person.">
??? <cfargument name="personId" required="true" type="Numeric" hint="The ID of the person to retrieve." />
??? <cfset var rsPerson = "" />
??? <cfquery name="rsPerson" datasource="#request.dsn#">
??? ??? SELECT
??? ??? ??? personId,
??? ??? ??? firstName,
??? ??? ??? lastName
??? ??? FROM
??? ??? ??? Person
??? ??? WHERE
??? ??? ??? personId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.personId#">
??? </cfquery>
??? <cfreturn rsPerson />
</cffunction>



2.? Assume you have a page that uses the above controller in a cfajaxproxy:

<cfajaxproxy cfc="controller.Person" jsclassname="PersonController">


3.? Assume you have some javascript that then uses this controller to get a person by a url-passed ID, using a callback function when we get a response from the server:

<script type="text/javascript">
??? pc = new PersonController();
??? pc.setCallbackHandler(pcListener);
??? var personId = <cfoutput>#url.personId#</cfoutput>
??? // NOTE:? THE NEXT LINE IS THE IMPORTANT CODEFOR THIS BLOG POST.
??? // As it exists here, this line is OPTIONAL because the default is already 'row'.
??? // we will be changing it shortly.
??? pc.setQueryFormat('row');
??? pc.getPersonById(personId);
</script>




Given the above three assumptions, the JSON-formatted response from getPersonById might look like this:

{"COLUMNS":["PERSONID","FIRSTNAME","LASTNAME"],"DATA":[[1000.0,"Frank","Zappa"]]}

Assume we want to write the retrieved person's information to the javascript console. In order to do so, you would have to have the following callback function AND an additional function (as provided in the ColdFusion documentation) to get to your recordset data:

<script type="text/javascript">

??? // Function to find the index in an array of the first entry
??? // with a specific value.
??? // It is used to get the index of a column in the column list.
??? // This is straight out of the CF8 documentation
??? Array.prototype.findIdx = function(value){
??? ??? for (var i=0; i < this.length; i++) {
??? ??? ??? if (this[i] == value) {
??? ??? ??? ??? return i;
??? ??? ??? }
??? ??? }
??? }

??? // this is the function that is called when a response is received from getPersonById(int)
??? // note that all column names are coming in as UPPERCASE; that's a painful ColdFusion inconsistency
??? function pcListener(response){
??? ??? var personId = response.DATA[0][response.COLUMNS.findIdx('PERSONID')];
??? ??? var firstName = response.DATA[0][response.COLUMNS.findIdx('FIRSTNAME')];
??? ??? var lastName = response.DATA[0][[response.COLUMNS.findIdx('LASTNAME')]];
??? ??? var str = firstName + ' ' + lastName + ' is the person with ID ' + personId;
??? ??? console.log(str)
??? }

</script>


This listener is verbose; having to find the data by finding the index of the column name in a separate array is painful to look at.? Plus we have that extra function in there... something we'd probably want as a global function if we're doing a lot of this stuff.? Also, for my aging brain, this is just a lot to remember. (There may be a better way to deal with that JSON string -- I simply had been using the example provided in the documentation.? Anyone?)


HOWEVER, if just prior to your call to the remote function, you setQueryFormat('column') like so:

<script type="text/javascript">
??? pc = new PersonController();
??? pc.setCallbackHandler(pcListener);
??? var personId = <cfoutput>#url.personId#</cfoutput>
??? // NOTE:? THE NEXT LINE IS NOW CHANGED from 'row' to 'column'
??? pc.setQueryFormat('column');
??? pc.getPersonById(personId);
</script>


Then the JSON response now looks like this (notice that "DATA": line now contains individual arrays of values for each column):

{"ROWCOUNT":1,"COLUMNS":["PERSONID","FIRSTNAME","LASTNAME"],"DATA":{"PERSONID":[1000.0],"FIRSTNAME":["Frank"],"LASTNAME":["Zappa"]}}


With this new JSON response, you don't need the additional Array.prototype.findIdx function, and all you would need in your callback handler is this:

<script type="text/javascript">
??? // this is the function that is called when a response is received from getPersonById(int)
??? // note that all column names are coming in as UPPERCASE; that's a painful ColdFusion inconsistency
??? function pcListener(response){
??? ??? var person = response.DATA;
??? ??? var str = person.FIRSTNAME + ' ' + person.LASTNAME + ' is the person with ID ' + person.PERSONID;
??? ??? console.log(str)
??? }
</script>


Because the response was formatted differently ('column' vs.? 'row') I am now more comfortable removing the separate variable declarations.? This, combined
with the removal of the Array.prototype.findIdx function, makes the code much more legible.? The JSON response WILL be a little larger as a result, since it
is essentially specifying the query column names twice (once in a separate array, once in an array of structs), but I don't consider this to be an issue at all.

Finally, we also get a ROWCOUNT passed back, so for recordsets containing multiple rows, we get a nice count we could use, for example with some jQuery DOM manipulation.

FOR ONE OR MORE ROWS:

Here's a JSON response containing a recordset that has multiple rows, using setQueryFormat('column'):

{"ROWCOUNT":4,"COLUMNS":["PERSONID","FIRSTNAME","LASTNAME"],"DATA":{"PERSONID":[1000,1001,1002,1003],"FIRSTNAME":["Frank","Nora","Bob","Andy"],"LASTNAME":["Zappa","Jones","Dylan","James"]}}

Again, each column in the DATA object has an Array containing multiple values.

In this case, your listener might access these multiple rows like this:

<script type="text/javascript">
??? // this is the function that is called when a response is received from getPersonById(int)
??? // note that all column names are coming in as UPPERCASE; that's a painful ColdFusion inconsistency
??? function pcListener(response){
??? ??? var rows = response.ROWCOUNT;
??? ??? var person = response.DATA;
??? ??? for (var r=0; r<rows; r++) {
??? ??? ??? var curPersonStr = person.FIRSTNAME[r] + ' ' + person.LASTNAME[r] + ' is the person with ID ' + person.PERSONID[r];
??? ??? ??? console.log(curPersonStr);
??? ??? }
??? }
</script>


This works for one or more rows.

As always -- if you found this helpful or pointless, or if you found an error or an easier way to do all this, please feel free to comment!


Posted in ColdFusion | snippets |

2 responses to “Working with a cfajaxproxy, ColdFusion recordset as JSON”

  1. Jessi Says:

    This is *wonderful*!!! You have saved me hours and hours of hunting on how to get to the data returned from a CFC call. The explanation as to why the Array.... didn't quite make sense to me until you explained it, and the column method is much more user friendly to my mind. Thanks so much!
  2. miked Says:

    Time saved = priceless
    Thanks!

Comments now closed