cfc jquery load issue

Report · Oct 16, 2012

I am trying to load data from a database using cfc and jquery but can not sort it out due to some issue on client side.

here is what I have so far:

.cfc

SELECT ID,NAME

FROM table

</cfquery>

</cffunction>

</cfcomponent>

.cfm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

$(document).ready(function() {

$("#loadLink").click(loadQuery)

});

function loadQuery() {

$.ajax({

url: "loaddata.cfc?method=getData",

success:function(data){

alert('data that function gets from the server:' + '\n' + '\n' + data + '\n' + '\n' +

"everything is listed here");

var columnMap = {}

for (var i = 0; i < data.COLUMNS.length; i++) {

columnMap[data.COLUMNS] = i

}

var str = '<h1>People</h1>'

for (var i = 0; i < data.DATA.length; i++) {

str += '<b>'+data.DATA[columnMap.NAME]+'</b>'

str += ' has a cool score of '+data.DATA[columnMap.ID]+'<br/>'

}

$("#content").html(str)

}})

return false

}

</script>

</head>

<body>

<p>

<a href="" id="loadLink">Load Query</a>

</p>

</div>

</body>

</html>

as you can see this is a simple script (I found it on the web and slightly changed it for testing purposes), the final one should be a bit complex.

here is an error firebug throws every time

TypeError: data.COLUMNS is undefined
for (var i = 0; i < data.COLUMNS.length; i++) {

I know this is a javascript question but I would really appreciate your help on this one.

Report · Oct 16, 2012

Is your alert firing? If so, what does it show you? Can you post the output here?

From this line:

alert('data that function gets from the server:' + '\n' + '\n' + data + '\n' + '\n' +

"everything is listed here");

Report · Oct 16, 2012

yup every time and as I can see everything is normal on the server side

here is the output

data that function gets from the server:

<wddxPacket version='1.0'><header/><data><string>{"COLUMNS":["ID","NAME"],"DATA":[["1","John"],["2","Peter"],["3","Susan"]]}</string></data></wddxPacket>

everything is listed here

Report · Oct 16, 2012

Well there you go. That's not valid JSON format. It is valid between the <string> tags but not with the wddx stuff.

Try adding the returnformat on your cfc method and instead of returning "any" set it to return "string"

Let us know what that returns.

Also, you may no longer need to use the SerializeJson() function as I think the returnformat will do that for you. Test to verify.

Report · Oct 16, 2012

I've already tried that and everything else I could think off within the cfc and still cant sort it out.

I added &returnformat=json within url on the client side

url: "authenticationService.cfc?method=getData&returnformat=json",

and now it returns {"COLUMNS":["ID","NAME"],"DATA":[["1","John"],["2","Peter"],["3","Susan"]]}

regardless whats on the server side (returntype (string or any or query), and with or without serializeJSON), though for some reason I'm still getting the same error msg.

I think it should return [object Object] but have no idea how to make it work.

Report · Oct 16, 2012

Now that's better. You now have your JSON response exactly as documented here " converts a ColdFusion query into a row-oriented JSON Object with the following elements ...COLUMNS DATA..." : http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-79fa.html

Now at this point you need to parse the JSON and iterate over the data. You can Google something like "javascript parsejson array" for various ways to do this like here: http://stackoverflow.com/questions/9991805/javascript-how-to-parse-json-array

If you Google something like "javascript reference coldfusion json query object" you will get lots of varying examples on how to do that as well.

Hope that helps.

Report · Oct 18, 2012

thanks for your comments and for forcing me to learn, really appreciate that

i was able to sort it out and do some other things successfully

Now I would like to output data say

on first click records from 1 to 10

on second click records from 11-20

.etc

though I am not sure how to do it (again the problem is javascript).

in coldfusion component I should obviously use cfloop but not sure how to dynamically change startrow using javascript.

Report · Oct 18, 2012

Great! I'm glad you got it working and I purposely wanted to lead you and not just give you an answer. What fun would that be?

I might be oversimplifying things but it seems like you could just pass a value (javascript variable) containing the starting record to your cfc. When you get the data back just increment that variable based on the length (count of records) that was returned. Your cfc can handle the number of records to return each time or you could also pass that to your cfc from javascript. Is that what you are after?

Perhaps you are unaware but there is a "data" setting for the jQuery.ajax call that allows you to pass data to the server. And lucky for you, the data is in JSON format.

http://api.jquery.com/jQuery.ajax/

So you could do something like:

$.ajax({

url: "loaddata.cfc?method=getData",

data: { start: "1", records: "10" }

success:function(data){

alert('data that function gets from the server:' + '\n' + '\n' + data + '\n' + '\n' +

"everything is listed here");

var columnMap = {}

Report · Oct 18, 2012

I might be oversimplifying things but it seems like you could just pass a value (javascript variable) containing the starting record to your cfc. When you get the data back just increment that variable based on the length (count of records) that was returned. Your cfc can handle the number of records to return each time or you could also pass that to your cfc from javascript. Is that what you are after?

yup, that's exactly what i want to do but not sure how to handle it from client side

i already have dataType: 'json', within script which converts data back to a JavaScript object so I do not have to parse JSON manually.

Report · Oct 18, 2012

Yep, dataType is for formatting the result from the server but data is for passing data to the server. Check out the examples in the docs http://api.jquery.com/jQuery.ajax/

This should get you going with passing data from javascript to your cfc. Let me know how it goes.

Report · Oct 18, 2012

dataType - data duh, my bad

it passes data to the server correctly, though I've a different problem now on the server side.

my "simple" plan to cfloop over query, populate array and return json data has failed. whatever I try i can not return JSON format for some reason. definitely need a break, will try to sort it out later!

Report · Oct 18, 2012

You may not need to build an array and since the cfc can handle formatting a query object into JSON for you try to use that. There is a "maxrows" attribute to the cfquery tag. Maybe leverage that to get your 10 record chunks. You just need to be sure that your query parameter increments the records for you (first 10, then 11-20, then 21-30, etc). If your datasource does not support that maxrows attribute you could also write your query so that it only returns n records at a time.

See maxrows attribute of cfquery here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

Report · Oct 19, 2012

"query object into JSON" yup I'm currently using that

also i know about maxrows attribute, as I have been using it all over my websites as well as LIMIT within MySQL statement, of course, where it's possible to set it.

anyway I am missing the basic logic here, if maxrows is set (within <cfquery not <cfoutput query...) the query will actually return the first 10 rectords each time and given that cfquery tag does not support startrow I don't see how to make it work without cfloop.

Report · Oct 19, 2012

Yes you are correct. Okay, so here is what I was trying to say with this statement "You just need to be sure that your query parameter increments the records for you (first 10, then 11-20, then 21-30, etc)"

Again, this is probably over simplifying but hopefully you'll get the gist. I see that you have ID and NAME in your query example above. So on your first call to the cfc it will return the first 10 records:

SELECT ID,NAME

FROM table

ORDER BY ID

</cfquery>

1 Smith

2 Smith

3 Smith

4 Smith

5 Smith

6 Smith

7 Smith

8 Smith

9 Smith

10 Smith

When the user clicks for more you pass it the last record that you displayed and have the cfc return the next 10 records.

SELECT ID,NAME

FROM table

WHERE ID > 10

ORDER BY ID

</cfquery>

11 Smith

12 Smith

13 Smith

14 Smith

15 Smith

16 Smith

17 Smith

18 Smith

19 Smith

20 Smith

and so on. I'm not sure if that will work with your data or not but that is what I was thinking. Notice I added an order by clause and where clause to the query. You'll need to do something with these in order to make your 10 record chunks.

Hopefully that clarifies my thinking. Anyway it sounds like you are making good progress.

Report · Oct 19, 2012

we obviously have the same thinking (looking for the easiest possible solution), I thought of that first,
however this is never going to work because cfquery will be a bit more complex say

SELECT ID,NAME

FROM table

WHERE cat="cat1" and subcat="subcat2"

ORDER BY DATE

</cfquery>

this is still much simpler than what I'll use on production server.

so I can have IDs like this

5 Smith

210 Smith

355 Smith

800 Smith

etc

in this regard ID > of something is out of question

I thing looping over a cfquery is is the only solution, after which I need to do something

I\ve already tried to put data to array of struct and than serializeJSON but failed to sort it out

Report · Oct 22, 2012

now I have two working examples, the first works great and the second one is even better

Report · Oct 22, 2012

Great! Glad you got it working.

Adobe Community

cfc jquery load issue

1 Correct answer