Using CFHTTP to Build a Query

Sorry I have been lazy about posts recently. I’ve been in Boston, meeting with various product teams. All I can say is that there is some cool stuff on the horizon.

Anyway, did you know that CFHTTP can automatically turn a comma-delimited file into a query object for you? Let’s say you have a file called data.txt that looks like this:

firstName, lastName, emailAddress
Christian, Cantrell,
Mike, Chambers,
Baby, Blue,

The following use of CFHTTP will parse the data above into a query stored in the variable “myQuery”:

<cfhttp method="GET" url="http://localhost/tests/cfhttp/data.txt" name="myQuery">

You can use the columns attribute of the CFHTTP tag to specify a different set of column headers, and you can use the firstrowasheaders attribute to include the first row as data rather than column headers. And, of course, your comma-delimited file doesn’t have to be static; the delimited values can be dynamically generated by any means.

3 Responses to Using CFHTTP to Build a Query

  1. Wow, I never knew that. Thanks for the tip Christian.

  2. Bas says:

    Note: this only works if your columns do not contain a carriage return!You get a “Incorrect number of columns in row”For example: if you have a webapplication with database that has a memo-field (html textfield) and you have one or more carriage returns in it, these are (ofcourse) also stored in the database.Now if you cfhttp to a webpage that returns these database-results in a html-page, you get a page with extra carriage returns at the position of the carriage return from the memo-field.Every line in the http-response must be one row!

  3. ken says:

    Do you know of anyway around this bug?