AJAX to do Real Time DB Polls like Twitter's "Real Time Results" in Coldfusion

one quirky thing to watch

returned objects are always lower case

var resp = obj.myid + obj.mytime
and not

var resp = obj.myId + obj.myTime

Thanks Sid,

     Good to see you again; thanks I will try to whip something up and see what happens.

sorry it took me so long to get back to this that you even had to opened a new question...

attached is a working example (i.e. prrof of concept).

it uses a query stored in session scope, so make sure sessions are enabled in your application.

put all the files in same folder, remove .txt extensions from them (EE does not allow uploading .cfm files, even inside a zip archive...).
javascript code is commented, but do ask if something is unclear.

Azadi

Thanks a lot azadisaryev; I'll give this a try now.  No worries; I understand that people get busy; I just had a tight deadline; thanks again!

Thanks; I got the sample project working dandy, now I am just trying to apply it to my current project.  I'm having a little trouble modifying the query.cfm because my DB is already setup (and I'm not creating it at page launch with querynew as you are in this sample).  Basically I don't know how to associate session.qSearch with my prebuild DB instead of the one your creating here (or if its even necessary).

Still trying to figure it out on my own over here as well; thanks!

>> Basically I don't know how to associate session.qSearch with my prebuild DB instead of the one your creating here (or if its even necessary).

it is not necessary - you can (and should) use your own query. and you do not need to put it in session scope either - that was just for my example to work without extra db setup.

just remember to change the result variables used in the checkResultsCB js function - they use the query's column names:
in my example the query returned columns id and name, and the js function used r.DATA.id and r.DATA.name.
you will need to change that to use columns returned by your query.
also remember that column names are case-sensitive in a way - they should be referenced in checkResultsCB function in same case as they are in the query sql in the cfc function.

Azadi

Thanks again!  I am very close to getting it working.  Everything is okay except the part where the notice for new results displays; I narrowed it down, keeping in mind what you said about case sensitivity; but I notice that the alert in the below code does not show up (wheras a similar alert in your original code does, or if i put like alert("Yo") instead)...something goes wrong here; its almost like the CFC function fails for some reason; i removed the query and tried it separately and it worked so I don't think its just a SQL error.

Thanks!

checkResultsCB = function(r) {
	// callback for checkResults function
	var ttlres = r.ROWCOUNT; //ROWCOUNT is part of json object returned by cfc function call
	if (ttlres > newResults) {
		var tr = ''; //this will hold html for new rows for the results table
		alert(r.DATA.RecordID[0]);
		for (var i=0; i<ttlres; i++) {
			if (r.DATA.RecordID[i] > curMaxID) tr += '<tr class="newresult"><td width="20">'+r.DATA.RecordID[i]+'</td><td>'+r.DATA.message[i]+'</td></tr>';
		} //only create rows for results which do not already have rows created for them
		curMaxID = r.DATA.RecordID[0]; //reset curMaxID to max id value returned by current cfc call. our query returns data ordered desc by id, so the first id in the result is the max id
		newResults = ttlres; //reset newResults var to ttlres
		$('#resultstable tbody').prepend(tr); //add new result rows to top of results table
		$('#newresults').find('#numres').html(newResults).end().fadeIn(); //show new results notification
		$('#numrowsadded').html('');
	}
}; 

//relevant function in cfc 
	<cfargument name="maxID" type="numeric" required="no" default="0">
		<cfset var q = "">
		<cfquery name="q" datasource="#application.datasourceName#">
		SELECT
        	RecordID, message
		FROM 
        	DwitterStatusUpdates
		WHERE RecordID > <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.maxID#">
		ORDER BY RecordID DESC
		</cfquery>
		<cfreturn q />
	</cffunction>
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

hmm.. nothing wrong jumps out at me in that code...

you say when you change alert(r.DATA.RecordID[0]); in the above code to alert('Yo!'); you do see the alert then?
if so, there's probably something wrong with the data returned by cfc call and we need to do some debugging...
the best tool for this is Firefox+Firebug add-on - if you are not already using them you should start now.

for starters, add console.log(r); as the first line in the checkResultsCB function, reload the page with Firebug open, copy the output from Firebug's console tab and post it here.

Azadi

Yes, if i changed that alert to 'Yo' it would indeed appear.  

Okay while using firebug; the output, before I add any new rows; is as follows:

//10 seconds after page load with no new records added:
Object ROWCOUNT=0 COLUMNS=[2] DATA=Object 

//after one press of the add new button
Object ROWCOUNT=3 COLUMNS=[2] DATA=Object
r.DATA.RecordID is undefined
anonymous(Object ROWCOUNT=3 COLUMNS=[2] DATA=Object)DwitterNew (line 66)
anonymous()cfajax.js (line 487)
anonymous()cfajax.js (line 473)
anonymous()cfajax.js (line 172)
anonymous()cfajax.js (line 125)
[Break on this error] alert(r.DATA.RecordID[0]);\r\n 
//after second press of add new button (similar for subsequent presses
Object ROWCOUNT=8 COLUMNS=[2] DATA=Object

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Here is some more info from that output after I press the add new button (new to firebug):

//in the "Response" tab 
{"ROWCOUNT":5,"COLUMNS":["RECORDID","MESSAGE"],"DATA":{"RECORDID":[47,46,45,44,43],"MESSAGE":["New Message","New Message","New Message","New Message","New Message"]}}

                                              

1:
2:
3:

need to see what's in the DATA object.

in firebug console tab you should see lines like GET http://... appear every 10 seconds.
click on one of those to expand it, then click on Response tab and copy the response text and post here.

(if you can't see the http requests listed in the console tab - click on the little arrow on the tab title and select "Show XMLHTTPRequests" option)

you should also be able to click on the {ROWCOUNT=... lines to see the full object in the DOM tab. next to DATA object there look at the names of properties returned - is there one called RecordID ? is it in the same case as you are using or different?

Azadi

I think I have pasted that in the post right above your current one (I double posted so maybe you only saw the first one).  I see the RecordID there; but (like the message one too) is in all caps; nowhere do I have write it in all caps in the code.  Do you need something else besides what is above?

ha, you beat me to it!

>> "DATA":{"RECORDID":[47,46,45,44,43],"MESSAGE":["New Message","New Message","New Message","New Message","New Message"]}}

as you can see, the column names are returned in UPPERCASE.
you need to use RECORDID and MESSAGE (all uppercase) instead of RecordId and message in the js code.

i think the difference is that my data was returned from a QoQ, while yours is returned from a real db query - thus mine was being returned in the case as i had the column names in the QoQ's SQL, but yours is converted to all uppercase.

Azadi

Yeah I gotcha; I guess what was throwing me is that I reference lower case "message" in my code and it didn't give a problem like this; i case the case sensitivity only matters sometimes depending on what you are doing with it?  In the meantime I will change that to all caps and see what happens!

just to be clear:

change your
alert(r.DATA.RecordId[0]);

to:
alert(r.DATA.RECORDID[0]);

and also change
if (r.DATA.RecordID[i] > curMaxID) tr += '<tr class="newresult"><td width="20">'+r.DATA.RecordID[i]+'</td><td>'+r.DATA.message[i]+'</td></tr>';

to:
if (r.DATA.RECORDID[i] > curMaxID) tr += '<tr class="newresult"><td width="20">'+r.DATA.RECORDID[i]+'</td><td>'+r.DATA.MESSAGE[i]+'</td></tr>';

as well as curMaxID = r.DATA.RecordID[0]; to curMaxID = r.DATA.RECORDID[0];

Azadi

Yeah that worked!  The strange thing I am noting here is that r.Data.message works as lower case OR upper case... why did it not matter for message and it did for recordID?

cf returns structures create with dot notation (i.e. somestructure.somekey) to all uppercase. same goes for returning queries which are a special case of structures.

structures created using array notation (somestructure['somekey']) will be returned in the case the key was defined.

this is not an issue when your returned data is consumed by cf code, which does not care about the case, but it makes all the difference when returned data is consumed by javascript which is case-sensitive.

i didn;t know that QoQ were treated differently by cf (or maybe it's not QoQs but queries created using querynew() function... will need to test it out) and that threw me off a bit, even though i knew that cf returns most things in uppercase.

Azadi

>> why did it not matter for message and it did for recordID?

my guess would be because message was in all lower-case, while RecordID was in mixed-case... but i will need to run some tests to say for sure, and to see how exatly the case matters (i.e. what would have happened if you had it named as recordID...)

Azadi

Hmm okay; well at least it was interesting!  

I have some follow up questions on this project here; simple ones (I think) that I may somewhat know the answer to already.  Should I post them here; or should I close this out and start a new question.  You've been very helpful and I want to make sure you get the proper points ;)

You could also post a quick summary on the other thread and I'll close that one out as well.

keep posting them here - it will be easier to follow the code and conversation in one place than having to jump from one question to another.  it will probably also be better for someone looking for this solution later...

i also think you can close the first question you had opened on this subject (the one i had neglected for too long) and request full points refund for that one. explain that you opened a new question on exactly same subject and link to this question in the reason to close the first one.

so, what's the next question? :)

Azadi

K sounds good, thanks! ;)

Okay; as we are now well aware the records currently consist of RecordID and Message; this was a simplification of what I am working on. There will actually be another field "category" that could be one of a few things (lets say 3).  So for instance, this table might have records that look like

1, myMessage, category1
2, myMessage, category2
3, myMessage, category3
4, myMessage, category2

and so on.  So basically the page is going to have a a few separate sections (corresponding to the categories), and I want this kind of update noticing of new records happening in each section, but only for message with the category of that section.  I hope that makes sense.  Now; I think I understand what I have to do; basically make 3 separate database searches instead of one (one for each category) and handle it all similarly.  I just want to make sure this is the most efficient way; as if say there are 20 questions; would it be a problem to make these 20 search calls every 10 seconds?  Or is there some kind of magic SQL way to make one DB call and then parse it into 20 different result sets (that then are what is used to do the update noticing) etc.  

That was a little convoluted but I hope it made sense!

I said 20 "questions" up there by mistake: i meant 20 categories lol

my first thought would be that, yes, you need to make 20 separate calls to the cfc - one for each category...

but then... all you really need is a separate new results counter for each section, and you can return all data in one query in one call. of course, you won't be able to use the very handy ROWCOUNT variable - you will have to create a custom counter for each category in js.

let me adjust my sample code to include multiple categories - you can then have a look at the code to see how to do it.

Azadi

Okay; thanks.  I'm thinking that I might get stuck having to make seperate DB calls for each category in case I want to do more specific things.  Like for instance lets say on page start up I'd want to start out displaying the 10 most recent message for each category; I might have to do that doing each separate call and ordering them be RecordID (or timestame) DESC and limiting to 10...but then again maybe that can be done with SQL Parsing magic too.  

Also; in truth the number of categories will be variable; so my plan is to, on page start up, pool the DB to get the categories; and then in all this subsequent stuff just looping over the categories and doing all this other stuff.

Actually; maybe not just on page start up for checking the categories; but at every interval (in case for some reason a category gets added or dropped while the page is sitting there.  Sorry for my rapid fire posts; just want to get any information up here before you dive in; don't want to lead you in circles.

Lol, looks like I lost you again.

i am with you!
had some urgent work (again), but i am testing an application that uses multiple categories right now. will post code later today or tomorrow.

Azadi

Your alive!  Thanks for getting back; I'm always busy too so I understand ;)  

I started doing something myself too, but I'm sure you can come up with a more streamlined way of doing it then I am doing; thanks again!

I basically just was doing a query to get the distinct people in the room; and then looped through them all doing sql queries for each person, to see if they have new records and changing their section accordingly.  Again this may be clunky compared to the way are doing.

Oops; i meant to say categories instead of people; hopefully you knew what I was saying (the categories obviously correspond to company members lol).

Wow, really impressive!  Definitely more streamlined then what I was doing, lol.  I am working on integrating into my project now; so far so good; I'll write back with a status update soon.

Still here; just got tied up with other work.  So far so good; I'm just trying to modify it to make new rows in a single table for each new category as opposed to separate tables.

post your tables structure if you get stuck creating a single query.

Azadi

I got everything working with my DB data perfectly; just wrestling with formatting changes ugh ;)  Now my boss wants it so there is just one table, with a row in it for each category, and only 1 record (the newest one) showing at a time with a link to display older records which will make some older records appear below that in "subrows"  

Oiy!  

I'm trying to figure out how to adapt based on your extremely well commented code; but your java kung fu is far superior to mine so its been slow going.  I'm currently focusing on this:

var tr = '<tr class="newresult"><td width="20">'+r.DATA.ID[i]+'</td><td>'+r.DATA.NAME[i]+'</td></tr>'; // create html for new result row...

as that is clearly related.  

@andrewaiello:

your new requirement is rather different from your original question...
i will be happy to help you with it if it is still relevant and if you open a new question for it.

@angelIII:

i believe i have provided a solution to the original question, which has been acknowledged by the OP in his last comment. in case the OP does not finalize the question in time and you need to close it, i will appreciate if you kept that in mind.

Azadi