Working with a ColdFusion Query in jQuery

This post is more than 2 years old.

This morning one of the ColdFusion support forums I subscribe to asked about how one could work with a ColdFusion query sent over Ajax to jQuery. I whipped up a quick example of one way (remember - like ColdFusion - there are many ways to do things in jQuery) you could do it. To start off, I'll show the CFC I'll be using to feed data to the Ajax-based front end. Notice how nothing in it is Ajax-specific. Outside of the fact that it has remote access, this is just a vanilla script-based CFC.

component {

remote query function getArt(string search="") {

	var q = new com.adobe.coldfusion.query();
    q.setDatasource("cfartgallery");
    q.setSQL("select artid, artname, price, description from art where description like :search or artname like :search");
    q.addParam(name="search",value="%#arguments.search#%",cfsqltype="cf_sql_varchar");
    return q.execute().getResult();
	    
}

}

The component has one method, getArt. It allows for an optional search search. When run we query against the art table that ships with the ColdFusion samples and returns the id, name, price, and description of each piece of art. Now let's look at the front end.

<html>

<head> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> <script>

$(document).ready(function() {

$("#searchbtn").click(function(e) {
	var search = $.trim($("#search").val());
	
	$.post("test.cfc?method=getArt&returnFormat=json&queryFormat=column", {"search":search}, function(res,code) {
		if(res.ROWCOUNT &gt; 0) {
			var s = "&lt;table border='1'&gt;&lt;tr&gt;&lt;th&gt;Name&lt;/th&gt;&lt;th&gt;Price&lt;/th&gt;&lt;th&gt;Description&lt;/th&gt;&lt;/tr&gt;";
			for(var i=0; i&lt;res.ROWCOUNT; i++) {
				s += "&lt;tr&gt;&lt;td&gt;" + res.DATA.ARTNAME[i] + "&lt;/td&gt;";
				s += "&lt;td&gt;" + res.DATA.PRICE[i] + "&lt;/td&gt;";
				s += "&lt;td&gt;" + res.DATA.DESCRIPTION[i] + "&lt;/td&gt;";
				s += "&lt;/tr&gt;";
			}
			s += "&lt;/table&gt;";
		} else {
			var s = "Sorry, nothing matched your search.";
		}
		$("#results").html(s);
			
	},"json");
	
	e.preventDefault();
});

});

</script> </head>

<body>

<form> <input type="text" id="search"> <input type="submit" id="searchbtn" value="Search"> </form> <div id="results"></div>

</body>

</html>

Before we go up to the JavaScript, let's take a quick look at the layout on the bottom. I've got a search text field, a button, and a blank div. Obviously the text field is where you will enter search terms. The button will fire off the event. And lastly, the blank div will be used to draw out the results.

Now let's go back to the code. The first thing we do is grab (and trim) the search text. We want to allow you to get data even if you don't enter anything so we don't leave the method if the value is empty. Next we do the actual POST operation. I could have used a GET here since the amount of text you end up sending will be small, but POST just seems safer to me.

Now - I want to point out something interesting about the URL. First - we use returnFormat=json to tell ColdFusion to automatically convert the result into JSON. The second argument is a bit different. When ColdFusion serializes queries it can do so in two ways. The queryformat attribute let's us pick one. The default value is row. When you row, the result looks like this:

Notice that the result contains a COLUMNS array and a DATA array. The DATA array is setup so that the Nth item in the array relates to the Nth COLUMNS value. So to get the ARTNAME for example, you have to know that ARTNAME is array item 1. Totally workable - you can translate the COLUMNS array into a structure. But I thought it might be easier if we switched to the column format. Check out how that looks:

This structure is a a bit simpler. Now our DATA value is a struct where each key is a column that is an array of values. Notice how in the JavaScript I can simply loop from 1 to the included ROWCOUNT value.

If you want to see this in action, hit the big Demo button below. As always, I recommend using a tool like Firebug, Charles, or Chrome's Dev Tools, so you can see the data flowing in the background.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Edward Beckett posted on 11/9/2010 at 7:15 PM

Can't seem to get the demo to work ...

Comment 2 by Raymond Camden posted on 11/9/2010 at 7:18 PM

What browser? Tested 2 Chromes and it worked fine. It's pretty simple JS. What did Firebug tell you? Or Chrome Dev Tools? Or Chalres. INFO MAN!!! :>

Comment 3 by andy matthews posted on 11/9/2010 at 7:27 PM

You should also offer up an alternative to the default query return (which totally blows) by converting the query to an array of structs. WAY easier to manage.

Comment 4 by Sam Farmer posted on 11/9/2010 at 7:33 PM

Demo works fine for me. I searched for "ch". Are you getting an error?

Comment 5 by Raymond Camden posted on 11/9/2010 at 7:36 PM

Andy - what's wrong with the return I have up there? It was easy enough to work with. Or am I missing something?

Comment 6 by Raymond Camden posted on 11/9/2010 at 7:37 PM

@Edward: I should point out - the search is case sensitive. So try a super simple search, or type nothing too.

Comment 7 by andy matthews posted on 11/9/2010 at 7:50 PM

Nothing wrong with it. But I detest the way that CF returns query results. It's more "efficient", but much harder to understand and deal with. Just personal opinion of course.

Comment 8 by Raymond Camden posted on 11/9/2010 at 7:51 PM

Ok, now I'm gonna fight with you. :) You really think the code above (the code specifically to handle the looping) is difficult? I agree that an array of structs is even simpler - but what's there now looks pretty easy too.

Comment 9 by Jim Rector posted on 11/9/2010 at 7:55 PM

Could the lack of a closing form tag be causing issues for some browsers?

Comment 10 by Raymond Camden posted on 11/9/2010 at 7:58 PM

Maybe - sorry about that. Fixed.

Comment 11 by Marco van den Oever posted on 11/9/2010 at 8:06 PM

Great example, only i can't see how i can convert the cfc to non script one :( I have to as then i understand how to change it so it uses a database etc of my choice, i am on Railo so don't have the sample db...

Comment 12 by Raymond Camden posted on 11/9/2010 at 8:09 PM

Marco, I can write it you want to, but it's just a cfcomponent tag, a cffunction tag, and a cfquery tag. That's it.

Comment 13 by Robert posted on 11/9/2010 at 8:39 PM

This json you speak of. Can I make it work in CF7? My boss refuses to schedule an upgrade to CF 9 but I sure would like to use ajax at least for some experience.

Comment 14 by Raymond Camden posted on 11/9/2010 at 8:40 PM

Yes, ColdFusion can do anything:

http://www.coldfusionjedi.c...

Comment 15 by Robert posted on 11/9/2010 at 8:46 PM

Cool, thanks Ray! And "No Duh" to me for not searching.

Comment 16 by Marco van den Oever posted on 11/9/2010 at 8:55 PM

When i return my query in the cfc and debug with firebug i see in the html output :

[Deleted by Raymond]

Comment 17 by Raymond Camden posted on 11/9/2010 at 8:57 PM

Ouch. Marco, I'm going to edit your comment and remove that horribly large dump. Please repost with a pastebin url. Also - I don't see a question in there. You didn't say what you were posting about.

Comment 18 by Marco van den Oever posted on 11/9/2010 at 9:00 PM

That was very messy :) Sorry. Well my question is, nothing is shown on the page and when i view the html in firebug i get that output, so what is going wrong???

Comment 19 by Raymond Camden posted on 11/9/2010 at 9:02 PM

Well obviously your data is different. So please post (via pastebin) your data + the code you use to work with it. The code I have above is very specific to the data returned.

Comment 20 by Marco van den Oever posted on 11/9/2010 at 9:21 PM

Ohww if i only would have a 'normal' cfc..... :(

http://pastebin.com/FkgKD3xi

Comment 21 by Raymond Camden posted on 11/9/2010 at 9:29 PM

And can you also pastebin the sample result?

Comment 22 by Marco van den Oever posted on 11/9/2010 at 9:33 PM

Yes added it...

Comment 23 by Raymond Camden posted on 11/9/2010 at 9:36 PM

Not seeing it at the pastebin url above.

Comment 24 by Marco van den Oever posted on 11/9/2010 at 9:47 PM
Comment 25 by Raymond Camden posted on 11/9/2010 at 9:49 PM

Interesting. The case of COLUMNS doesn't match DATA. It's "Title", not "TITLE". Case matters in JavaScript.

Comment 26 by Brian Lang posted on 11/9/2010 at 11:26 PM

Out of curiosity, why is the search not case-insensitive? I've never had to worry about that with CF7 and MySQL.

Comment 27 by Raymond Camden posted on 11/9/2010 at 11:58 PM

It's a Derby db. I think that's the default.

Comment 28 by Brian Lang posted on 11/9/2010 at 11:59 PM

Ah. Never worked with one of those before.

Comment 29 by Raymond Camden posted on 11/10/2010 at 12:00 AM

It's what ColdFusion uses for it's sample DBs.

Comment 30 by Marco van den Oever posted on 11/10/2010 at 2:54 PM

Hello again :) About your comment:

Interesting. The case of COLUMNS doesn't match DATA. It's "Title", not "TITLE". Case matters in JavaScript.

Do you, or anyone else, have any idea how to solve this?

Comment 31 by Marco van den Oever posted on 11/10/2010 at 3:02 PM

Mmmm i have set a manual query from an example from the net:

<cfscript>
results = QueryNew("LastName,FirstName,Title,ISBN");
newRow = QueryAddRow(results);
QuerySetCell(results, "LastName", "Acker" );
QuerySetCell(results, "FirstName","Angie" );
QuerySetCell(results, "Title","Growing Fruit Trees " );
QuerySetCell(results, "ISBN","ISBN222" );
// etc.
</cfscript>

And now it works.....

Comment 32 by Marco van den Oever posted on 11/10/2010 at 3:41 PM

Whats interesting now is that the case of COLUMNS also not matching the case of DATA, see the html output:

http://pastebin.com/9mkYJDPP

Comment 33 by Marco van den Oever posted on 11/10/2010 at 3:52 PM

It all works now, feel free to remove some of my spamming :)
I am ashamed to admit that it didn't worked because i was using a db table that was empty....

Thanks again for you time and example!

Comment 34 by Grant Eagon posted on 11/10/2010 at 5:11 PM

@Ray,
Are you using jquery version 1? Why? Also, what's the ColdFusion forum you were mentioning? I'm trying to get more active in the community since <a href="http://www.coldfusionjedi.c...">your recent PHP post</a>.

Comment 35 by Raymond Camden posted on 11/10/2010 at 5:15 PM

I'm using the Google CDN version. By using "1" it asks Google to use the _latest_ version 1 build, so 1.X.

As for CF Forums - we've got some here (pretty low traffic), but the big one is cf-talk at houseoffusion.com.

Comment 36 by andy posted on 11/11/2010 at 12:15 AM

Hi Ray. Thanks for the great post. A question though: What would a Coldfusion error look like if say, the database disappeared because of a db server reboot? I'm trying to do more projects using jQuery and making calls to CFCs but am not sure how to do the CF/jQuery error checking.

Thanks for your time.
-A-

Comment 37 by Raymond Camden posted on 11/11/2010 at 12:56 AM
Comment 38 by Marlene posted on 11/11/2010 at 2:15 AM

I'm trying my darndest to learn how to integrate CF with JQuery so your blog post was timely. I tried to duplicate your example locally. I DO have the CFArtgallery database.

Your example CFC seems to be a shorthand version - not working for me. Is the full cfc posted somewhere for me to copy?

Comment 39 by Raymond Camden posted on 11/11/2010 at 2:31 AM

Nope, that is a full CFC. It is script based. It works in CF9. Are you running CF8 perhaps?

Comment 40 by Marco van den Oever posted on 11/11/2010 at 3:16 PM

Marlene, try this: http://pastebin.com/ewXnrnWf

Comment 41 by Marlene posted on 11/11/2010 at 8:07 PM

Yes, I am still running CF8...haven't yet upgraded but didn't realize there was that significant a difference.

And thanks, Marco, I'll try your example.

Comment 42 by Marlene posted on 11/11/2010 at 8:46 PM

Using Marco's example for CF8 I created a new CFC and applied the Field Titles to the JQuery. I used a different database since I discovered that CFArtGallery was not an Access db but Derby.

I tried to catch all the details (like case) and verified the query is correct but the jQuery search is still not working for me. Could someone take a look and see if you can spot any glaring mistakes? Thanks.

http://pastebin.com/HizcUDz6

Comment 43 by Raymond Camden posted on 11/12/2010 at 1:13 AM

What do you see when using Firebug?

Comment 44 by Marlene posted on 11/12/2010 at 1:42 AM

Here's the Firebug content:

http://pastebin.com/XXj38GGV

Comment 45 by Phillip Senn posted on 11/12/2010 at 1:58 AM

That's interesting that adding & queryFormat=column to the $.post will add a ROWCOUNT field and change the DATA fieldnames from numeric to their corresponding column names.
This is the only thing I could find in the documenation:
"You use the setQueryFormat function to specify whether to return a JSON format query as an object with an array of column names and an array of row arrays, or as an object that corresponds to the WDDX query format."

I changed it to an $.ajax command because that adds an error handler.
http://pastebin.com/D2Da6NJh

Thank you Ray!

Comment 46 by Raymond Camden posted on 11/12/2010 at 2:16 AM

@Marlene: That's the Firebug report for the -main- request. You want to look at the XHR tab.

Comment 47 by Marlene posted on 11/12/2010 at 2:42 AM

Okay - now I really feel like a dolt! I didn't find an actual TAB for xhr but I did find a link under DOM>jQuery>AjaxSettings>xhr and all it said was function ()

Is that the xhr tab you're talking about?

Comment 48 by andy matthews posted on 11/12/2010 at 7:47 PM

@phillip...

One of the other benefits of converting an array to a struct is being able to keep casing in you column names. You'll notice that in Ray's example, the column names are converted to uppercase. This is a known issue with ColdFusion and one that I heartily disagree with. Anyway, when I want to convert a query to an array of structs, I use a method similar to this:

http://www.bennadel.com/blo...

Be aware that the difference in key casing is caused when using dot notation vs bracket notation. Heres a simple example of two variables returned via an AJAX call:

VARIABLES.someVariable = SOMEVARIABLE
vs
VARIABLES['someVariable'] = someVariable in JavaScript

Comment 49 by Phillip Senn posted on 11/12/2010 at 8:21 PM

@Marlene,

You have ?method=getMerch&amp;returnFormat=json&amp;queryFormat=column
and I think you need:
?method=getMerch&returnFormat=json&queryFormat=column

Comment 50 by Raymond Camden posted on 11/12/2010 at 8:29 PM

@andy: Not so sure I see that as a big deal for query column names. To me, thats just meta data.

Comment 51 by Raymond Camden posted on 11/12/2010 at 8:39 PM

Marlene, try this video:

http://insideria.com/2009/0...

Comment 52 by Marlene posted on 11/12/2010 at 9:23 PM

thanks Phil - the &amp; appeared when I pasted the code copy into paste bin. The actual code in the .cfm file is as you suggested.

And Ray, thanks for the video link - I'll definitely review it. I've been reading books and took some Lynda.com classes but they normally use PHP in their examples instead of ColdFusion, so I'm trying to find how to integrate dynamic data with CF. Will also check in to upgrading to CF9 - it looks like that's essential.

Sorry to bother everyone with my JQuery newbie problems.

Comment 53 by Raymond Camden posted on 11/12/2010 at 11:05 PM

Don't worry about jQuery newbie-ness. I love jQuery and any way I can help I will. :)

Comment 54 by Marlene posted on 11/19/2010 at 8:13 PM

okay - the jquery newbie is back! I have upgraded to CF9 and tried again to use your examples and also Marco's example in pastebin.

Am I missing something here? The blog example - test.cfc - is not wrapped in in CF tags like <cfcomponent>. Are the tags not required or just not on the blog example?

BTW - all 3 volumes of the Forta/Camden CF9 book should arrive today! Maybe they'll answer my questions and I'll be able to throw out my old CFMX reference books!

Comment 55 by Raymond Camden posted on 11/19/2010 at 8:15 PM

CF9 allows for script based CFCs. You don't have to write them that way - but I prefer it and tend to use them when blogging to encourage others to try it out.

Comment 56 by Marlene posted on 11/19/2010 at 8:36 PM

Don't the scripts need <script> tags?

Comment 57 by Raymond Camden posted on 11/19/2010 at 8:39 PM

Nope, not in ColdFusion 9. :) You can write CFCs completely in script.

Comment 58 by Phillip Senn posted on 12/18/2010 at 12:27 AM

$.post("test.cfc?method=getArt&returnformat=json&queryFormat=column'

I'd like to use

$.ajax({
type: "POST"
,url: 'test.cfc'
,data:
{'method': 'getArt'
,'returnformat':'json'
,'queryFormat':'column'

Is that the right syntax?

Comment 59 by Raymond Camden posted on 12/18/2010 at 2:08 AM

Looks good to me. Don't forget your response handler.

Comment 60 by Sue posted on 4/17/2012 at 7:57 PM

I am just learning jquery and ajax. Your examples have been very good for learning and doing! I have made a similiar little app. using this example that works. I want to make a row in the ajax displayed table a link. If I put a class or id in the tr, td or anything I get an error. How would I go about making a row clickable?

Thanks for any advise.

Comment 61 by Raymond Camden posted on 4/17/2012 at 10:23 PM

It depends on how you are generating the table. Got a URL we can look at?

Comment 62 by Sue posted on 4/18/2012 at 12:31 AM

I just put it on jsfiddle but not in the right boxes yet

http://jsfiddle.net/suewe/t...

Comment 63 by Sue posted on 4/18/2012 at 12:38 AM

There are lots of problems with it. The basic idea is to have a menu on the left from a cfquery which is working. Then click on a link and display a table with ajax which is working. But then make the last row in each 4 some of rows a link so I can call another ajax and open a dialog window. I have the ajax and it gives me the right response (with a hard coded value) but I can't get the link or get it into the window. If that makes any sense.

Comment 64 by Raymond Camden posted on 4/18/2012 at 6:38 AM

Not quite sure I get what you mean. I can say that I don't see a link in your code - I mean where you generate your table. Why didn't you include the link?

Comment 65 by Sue posted on 4/18/2012 at 5:59 PM

Sorry I took it out because it was causing the table not to display. I put it back in.

In your example above, could the table cell:
s += "<td>" + res.DATA.DESCRIPTION[i] + "</td>"; be made to be clicked?

Comment 66 by Raymond Camden posted on 4/18/2012 at 6:03 PM

Well, you can add click support to anything - if you want to add a JavaScript event handler. In your case, I'd add a class to the td, like rayrules, and then add a click event handler using a selector like "td.rayrules"

Comment 67 by Sue posted on 4/18/2012 at 6:12 PM

When ever I add a class or id I get this error in firebug and the table wont display.

missing ; before statement
[Break On This Error]

s += "<td class="rayrules">" + response.DATA.PROD_SUPER_ID[i] + "</td>" + "</tr>";

thanks very much for the response. Sue

Comment 68 by Raymond Camden posted on 4/18/2012 at 6:37 PM

You aren't understanding how strings work in JavaScript. Notice the quotes? You can't use quotes inside a quoted string, ie, this is bad:

var name = "Raymond "Foo" Camden";

You have to escape them - or use single quotes on the outside:

var name = "Raymond \"Foo\" Camden";
var name2 = 'Raymond "Foo" Camden';

This should have shown aup as syntax error in the console.

Comment 69 by Sue posted on 4/18/2012 at 7:17 PM

Thank you very much that was it for the table and putting in a class. I am trying to learn javascript and jquery together (with books and tutorials) which isn't ideal but something I need to do for my work. I'm trying to figure out how to call the next ajax with the click. I'll keep working on this now. Maybe I will send another post if absolutely necessary. I will send the results when I'm finished. I really appreciate your time.

Comment 70 by Raymond Camden posted on 4/18/2012 at 7:56 PM

Woot - glad you got it working. :)

Comment 71 by Sue posted on 5/1/2012 at 1:04 AM

Hi,

I've made changes to this page and it's working except for displaying the second ajax call in the dialog. It alerts correctly but wont display in the dialog or in just a div. I think the 2nd ajax is not in the correct success function location. Do you have any ideas or could you direct me to examples of how to use success function or complete function? Thanks! I put code here: http://jsfiddle.net/suewe/t...

Comment 72 by Raymond Camden posted on 5/1/2012 at 1:23 AM

Not sure. It looks ok to me. What about simplifying it a bit and doig $("#StorForm").html("<b>Ray</b>"); just to ensure that works right.

Comment 73 by Sue posted on 5/1/2012 at 1:42 AM

Hi,

Yes, that works. I'm having trouble getting my response into html or ? It alerts ok and I've tried putting it in a table like your example which works for me in the 1st ajax call but seems to conflict somehow with the second one. When I try to move things around to what I think is the success function it stops working altogether.

Comment 74 by Raymond Camden posted on 5/1/2012 at 2:06 AM

If you console.dir(t), what do you see?

Comment 75 by Sue posted on 5/1/2012 at 2:42 AM

ReferenceError: t is not defined

Comment 76 by Raymond Camden posted on 5/1/2012 at 6:46 AM

Try moving var t = []; inside the callback.

Comment 77 by Sue posted on 5/1/2012 at 6:48 PM

Hi,

When I do this I get in firebug:

0 "Sorry, no selection."

I moved it around and this was the only undefined response I got.

Comment 78 by Sue posted on 5/1/2012 at 7:02 PM

This alerts what I want but how to access?

alert(jqXhr.responseText);

Comment 79 by Raymond Camden posted on 5/1/2012 at 7:11 PM

1) That makes no sense. Can you update the jSFiddle?

2) Not what I get what you mean. "access" ? You just _did_ access it in your Alert.

Comment 80 by Sue posted on 5/1/2012 at 7:35 PM

Hi,
I updated the jsFiddle. I meant by access to say display in the div not just alert.

thank you

Comment 81 by Raymond Camden posted on 5/1/2012 at 8:00 PM

You didn't move the t line though. It is still outside of your callback.

Comment 82 by Raymond Camden posted on 5/1/2012 at 8:01 PM

Another issue is - you need to tell jQuery you got JSON back. As the fourth argument to your $.post call, add "json". So the syntax is:

$.post("url", "data", yourcallback, "json");

Comment 83 by Sue posted on 5/2/2012 at 12:27 AM

Hi,

Thanks for your help. The link:

http://jsfiddle.net/suewe/t...

The $('#StoreForm').html(data); is finally in the dialog. Only thing now is how to put the values in form inputs or into a div.

jqxhr.always(function() {

$('#StoreForm').html(data);

Comment 84 by Raymond Camden posted on 5/2/2012 at 12:45 AM

Well, that's no different then setting any other form field.

$("#theidoftheformfield").val("....");

Comment 85 by Phillip Senn posted on 5/6/2013 at 6:17 PM

This is a post from 2010, and I use queryformat=column all the time. I'm just wondering if anything has changed to get the column names in their original case instead of them always being in uppercase.

Comment 86 by Raymond Camden posted on 5/6/2013 at 6:28 PM

I'd just convert it to an array of structs. Make a utility function for it and you can reuse it for all your service methods.