Category: ColdFusion

Posted on

New Repo: CFC for CSV

As I mentioned before, I’ve been working with SiteImprove’s API recently. As part of my task, I had to stitch all the data retrieved together in a large file which could be opened with and manipulated by Excel, Google Sheets, etc. Using the cfspreadsheet tag/functions seemed like overkill, so I wrote something “quick & dirty” to convert an array of structures into a .csv file.

Although the solution I came up with worked, it was tied to the headers/columns of the SiteImprove report. This bothered me, so I came up with a CFML component which transforms queries into comma-separated-value output for a more generic, reusable approach.

Posted on

A Reminder About CFHTTP and JSON

If you’re using this to retrieve JSON:

    <cfhttp 
        authtype="basic" 
        username=#UserName# 
        password=#APIkey#
        url="https://api.example.com/v2/getThings?page=1&page_size=10"
    >

And find your response is JSON wrapped in a bare-bones HTML document, rather than just the JSON you were expecting, you might need to add a cfhttpparam accept tag to specifically reference application/json, like so:

    <cfhttp 
        authtype="basic" 
        username=#UserName# 
        password=#APIkey#
        url="https://api.example.com/v2/getThings?page=1&page_size=10"
    >
        <cfhttpparam type="header" name="Accept" value="application/json">
    </cfhttp>
Posted on

Converting GetCEData Array Of Structs Into A ColdFusion Query

a very large 879 pound pumpkin on display at the Virginia State Farm

If you’ve worked with CommonSpot’s ADF before, you’ve likely used the getCEData function. It’s a convenient way to retrieve data from custom elements, with powerful filtering options to zero in on only the records you want.

Unfortunately, getCEDAta does not give you any way to control the sequence in which these records are returned (e.g. ORDER BY State, City, ZipCode). Since it returns its results in an array of structs, you need to convert the results over to a query if you want to sort them by more than one field.

This conversion process isn’t difficult. It’s nothing more than creating a query, looping through the array, adding a new row to the query, and populating the current record with a series of QuerySetCell calls. The most tedious bit was manually specifying the column names on a case by case basis, so I finally wrote a function with two nested cfloops, the first which iterates through the array, and the second to loop through the column names dynamically.

Here it is, if you are interested:

I’m hoping a future version of getCEData will allow us to specify the format in which the results are returned, similar to the findAll() function of the CFWheels framework, so we can get a query back instead of an array of structs.