How to convert a Coldfusion SQL query to a Javascript object

coldfusion · javascript · sql-server · json

Working with SQL databases is easy and straightforward in Coldfusion.

Looping and creating HTML output from queries is easy too.

When you want to work with SQL query data in Javascript it starts to get a little bit nasty. Coldfusion is able to turn a query into a JSON representation. So reading the data in Javascript works but it’s not easy to loop over the data. I want to show you how we turn those queries into easy to use Javascript objects. We do this not in Coldfusion but in Javascript for two reasons:

  • The amount of data to transfer from server to client is smaller when the client does the transformation.
  • On modern browsers Javascript is fast enough to handle even thousands of rows in milliseconds.

Step 1: Query the SQL database

We query the database and let Coldfusion turn the query into a JSON string.

Important: We use #SerializeJSON(GetData,true)# to force Coldfusion to return the format we want.

getusers.cfm:

<cfsetting showdebugoutput="no">
<cfheader name="Content-Type" value="application/json">

<cfquery name="GetData" datasource="WMForum">
  SELECT TOP 5 userid,username,firstname,lastname FROM Users
</cfquery>

<cfoutput>
#SerializeJSON(GetData,true)#
</cfoutput>

This returns a JSON string like this:

{
  "ROWCOUNT":5,
  "COLUMNS": ["USERID","USERNAME","FIRSTNAME","LASTNAME"],
  "DATA":
    {
      "USERID":[1,2,3,4,5],
      "USERNAME":["Patrick","amy","mmyers","pete45","jjj12"],
      "FIRSTNAME":["Patrick","Alice","Marc","Peter","Karl"],
      "LASTNAME":["Smith","Reed","Myers","Green","Nerb"]
    }
}

Step 2: Read the data via Ajax

users.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
  <script src="http://cdnjs.cloudflare.com/ajax/libs/json2/20110223/json2.js"></script>
</head>
<body>

<div id="main"></div>

<script>
  // Read the users
  $.ajax({
       url: "getusers.cfm",
       dataType: "json",
       success: function(data) {
         // Convert the object to a string to display it
        sData = JSON.stringify(data);
        $('#main').html('We received this:<br />' + sData);
       }
  });
</script>
</body>
</html>

Step 3: Turn the ugly query into a nice one

Now all we need is a function to turn this valid but hard to work with object into an easy one.

The CoffeeScript version:

queryToObject.coffee:

  # ## queryToObject
  # This function expects a Coldfusion query object generated with `SerializeJSON(query,true)`
  # and transforms it into a Javascript object like this:
  #
  #     [
  #      {id: 1, name: "Germany"},
  #      {id: 2, name: "Denmark"}
  #     ]
  queryToObject = (q) ->
    for i in [0...q.ROWCOUNT]
      r = {}
      for col in q.COLUMNS 
        r[col.toLowerCase()] = q.DATA[col][i]
      r

And the Javascript version compiled from the CS version:

queryToObject.js:

var queryToObject = function(q) {
  var col, i, r, _i, _len, _ref, _ref2, _results;
  _results = [];
  for (i = 0, _ref = q.ROWCOUNT; 0 <= _ref ? i < _ref : i > _ref; 0 <= _ref ? i++ : i--) {
    r = {};
    _ref2 = q.COLUMNS;
    for (_i = 0, _len = _ref2.length; _i < _len; _i++) {
      col = _ref2[_i];
      r[col.toLowerCase()] = q.DATA[col][i];
    }
    _results.push(r);
  }
  return _results;
};

Now let’s use this function on the data we receive before we output it:

users2.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
  <script src="http://cdnjs.cloudflare.com/ajax/libs/json2/20110223/json2.js"></script>
</head>
<body>

<div id="main"></div>

<script>
  // The queryToObject function
  var queryToObject = function(q) {
    var col, i, r, _i, _len, _ref, _ref2, _results;
    _results = [];
    for (i = 0, _ref = q.ROWCOUNT; 0 <= _ref ? i < _ref : i > _ref; 0 <= _ref ? i++ : i--) {
      r = {};
      _ref2 = q.COLUMNS;
      for (_i = 0, _len = _ref2.length; _i < _len; _i++) {
        col = _ref2[_i];
        r[col.toLowerCase()] = q.DATA[col][i];
      }
      _results.push(r);
    }
    return _results;
  };

  // Read the users
  $.ajax({
    url: "getusers.cfm",
    dataType: "json",
    success: function(data) {
      // Run the data thru queryToObject
      data = queryToObject(data);
      // Convert the object to a string to display it
      sData = JSON.stringify(data);
      $('#main').html('We now have this:<br />' + sData);
    }
  })
</script>
</body>
</html>

We now have this:

[
  {"userid":1,"username":"Patrick","firstname":"Patrick","lastname":"Smith"},
  {"userid":2,"username":"amy","firstname":"Alice","lastname":"Reed"},
  {"userid":3,"username":"mmyers","firstname":"Marc","lastname":"Myers"},
  {"userid":4,"username":"pete45","firstname":"Peter","lastname":"Green"},
  {"userid":5,"username":"jjj12","firstname":"Karl","lastname":"Nerb"}
]

UPDATE Oct 18th 2013: See how easy it is to work with trees in SQL once you have your data in this format.

This is a lot better and easier to work with. The traditional CFLOOP is easy to do with Javascript now:

users3.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
  <script src="http://cdnjs.cloudflare.com/ajax/libs/json2/20110223/json2.js"></script>
  <script src="http://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.2.2/underscore-min.js"></script>
</head>
<body>

<div id="main"></div>

<script>
  // The queryToObject function
  var queryToObject = function(q) {
    var col, i, r, _i, _len, _ref, _ref2, _results;
    _results = [];
    for (i = 0, _ref = q.ROWCOUNT; 0 <= _ref ? i < _ref : i > _ref; 0 <= _ref ? i++ : i--) {
      r = {};
      _ref2 = q.COLUMNS;
      for (_i = 0, _len = _ref2.length; _i < _len; _i++) {
        col = _ref2[_i];
        r[col.toLowerCase()] = q.DATA[col][i];
      }
      _results.push(r);
    }
    return _results;
  };

  // Read the users
  $.ajax({
    url: "getusers.cfm",
    dataType: "json",
    success: function(data) {
      var row, sData, _len, _i;
      // Run the data thru queryToObject
      data = queryToObject(data);
      // Convert the object to a string to display it
      sData = JSON.stringify(data);
      $('#main').html('We now have this:<br />' + sData);
      // List all lastnames we use underscore.js pluck to grab the lastnames from the object
      $('#main').append('<br /><br />These are the Lastnames: ');
      $('#main').append(_.pluck(data, "lastname").join(',')); 
      // Print all firstnames and lastnames
      $('#main').append('<h3>Firstname and Lastname:</h3>');
      // Loop over the query
      for (_i = 0, _len = data.length; _i < _len; _i++) {
        row = data[_i];
        $('#main').append(row.firstname + ' ' + row.lastname + '<br />');
      }
    }
  });
</script>
</body>
</html>
share