In my last blog post, CFMobile Example – Accessing remote data from mobile application, I explained how to access data returned by a remote CFC. The CFC queried a database table, but returned array of struct/object to cfclient. 

In a comment on that blog post, Tayyab posted a problem, where in he returned a query object from CFC and tried to access it from cfclient. I thought instead of posting the answer as a comment, it deserved a separate post.
This is what he asked - 

These are the 2 most simplest coldfusion files. One is a cfc and the other a CFM but when I wrap the cfm into <cfscript> tag I get a blank page.

I think he meant wrapping CFML code in <cfclient>. The code in two files he posted were -

listCustomers.cfm

<cfinvoke component="customer" method="retrieveCustomers" returnvariable="allCustomers">
</cfinvoke>
<table width="100%" border="1" cellspacing="0" cellpadding="3">
	<tr>
		<td>
			First Name
		</td>
		<td>
			Last Name
		</td>
		<td>
			Email
		</td>
	</tr>
	<cfoutput query="allCustomers">
		<tr>
			<td>
				#firstName#
			</td>
			<td>
				#lastName#
			</td>
			<td>
				#email#
			</td>
		</tr>
	</cfoutput>
</table>

customer.cfc

<cfcomponent displayname="Customer" hint="ColdFusion Component for Customers">
	<cfset this.dsn = "books">
	<!--- This function retrieves all customers from the database --->
	
	<cffunction name="retrieveCustomers" hint="Gets all customer from the database" returntype="query" 
	            access="remote">
		<cfquery name="getCustomers" datasource=#this.dsn#>
			select * from Customers
		</cfquery>
		<cfreturn getCustomers>
	</cffunction>
	
</cfcomponent>

If you just wrapped code in listCustomers.cfm in <cfclient> block then nothing would be displayed.

The reason is that when you return CF Query from server to cfclient, it is serialized to a JavaScript Object, which is not really a CF Query object. This is also in fact different object than the query object that you get by executing client side query (see Creating database mobile application with ColdFusion Splendor) .


Loop iteration and query column access would not work on this object the way it works on the server side, or on the client side query object. You can see the structure of deserialized query object by logging the object to JavaScript console - insert <cfset console.log(books)> just after <cfinvoke> in listCustomers.cfm, run the page with JavaScript console of the browser open and observe its value.

So you need to access query rows and columns differently for CF query returned from the server. I have created a similar example as above - not using Customer database, but using cfbookclub data source that is shipped with Splendor. I have two files now - book.cfc and listBooks.cfm.

book.cfc

<cfcomponent displayname="Book" hint="ColdFusion Component for Books">
	<cfset this.dsn = "cfbookclub">
	<!--- This function retrieves all books from the database --->
	
	<cffunction name="getBooks" hint="Gets all books from the database" returntype="query" 
	            access="remote">
		<cfquery name="books" datasource=#this.dsn#>
			select * from books
		</cfquery>
		<cfreturn books>
	</cffunction>
	
</cfcomponent>

listBooks.cfm

<script >
	//Helper function to get value at given row and column of the query object
	function getQueryColumnValue (queryObj, rowNum, colName)
	{
				var defaulValue = "";
				
				if (typeof queryObj.__QUERY__ == 'undefined')
					return defaulValue;
				
				rowNum--;
				if (rowNum >= queryObj.length)
					return defaulValue;
					
				var row = queryObj.DATA[rowNum];
				
				var index = queryObj.COLUMNS.indexOf(colName);
				
				if (index < 0)
					return defaulValue;
					
				
				return row[index];
	}	
</script>

<cfclient>
	<cfinvoke component="book" method="getBooks" returnvariable="books">
	</cfinvoke>
	
	<table width="100%" border="1" cellspacing="0" cellpadding="3">
		<tr>
			<td>
				Title
			</td>
			<td>
				Description
			</td>
		</tr>
		<cfoutput >
			<cfloop index="i" from="1" to="#books.recordCount#">
				<tr>
				<td>
					#getQueryColumnValue(books,i,"TITLE")#
				</td>
				<td>
					#getQueryColumnValue(books,i,"BOOKDESCRIPTION")#
				</td>
				</td>
			</cfloop>
		</cfoutput>
	</table>
</cfclient>

Notice that I have created a helper function in JavaScript block to get value from query object at given a row and column. Also cfloop does not treat the value returned by CFC as query; it now iterates it using index. And lastly, query column names are all in upper case, because column names are upper-cased on the server.

We could probably simplify it a bit, but as of now this is how you can access server side CF query data in cfclient. If you are going to run the above example, make sure that cfbookclub data source is created on the server.

-Ram Kulkarni

11 Comments to “CFMobile - How to display CF query data returned from remote CFC”

  1. Tayyab Hussain
    Dear Ram- Now I understand, that was awesome and so very well and simply explained . Thanks for all the help.
  2. Raymond Camden
    I'm confused - why wouldn't you use the new struct format for returned queries? This makes the query much simpler to work with in client-side code. While this feature is pretty much broken in the beta, it *is* the preferred way (imo) of returning queries to JS in CF11. Your workaround is unnecessary and really shouldn't be recommended (again, imo ;).
  3. Ram Kulkarni
    I agree and in fact when I tried the code Tayyab had posted, I used the new struct format. But then realised that we use the framework that we had developed for cfajaxproxy and that is the way it serializes query. I would like to see the new struct format supported for communication between cfclient and server, but not sure if it will be done.

    BTW, as I also mentioned, client side query follow the new struct format.
  4. Raymond Camden
    "BTW, as I also mentioned, client side query follow the new struct format." I skimmed - but is this in relation to using WebSQL?

    "But then realised that we use the framework that we had developed for cfajaxproxy and that is the way it serializes query."

    If cfajaxproxy's JS stuff does *not* support the new format, that would be a huge mistake. That should be a two second fix to the JS file - allow struct and just pass it on the call. (That's assuming the JS function does validation on row/query now, if it doesn't, than the only fix is to document it.)
  5. Ram Kulkarni
    I haven't verified it using cfajaxproxy, but I think the way query was serialized is not changed.

    And since the structure of the new query object is different, changing cfajaxproxy's implementation would break existing apps.

    Having said all this, I am no expert in cfajaxproxy, so I might be wrong.
  6. Raymond Camden
    Respectfully, you are wrong. :) cfajaxproxy lets you decide the queryformat. Given X is an object set up by cfajaxproxy, you can do

    x.setQueryFormat(...)

    Right now the docs say row, column, but afaik struct should work too. Will test on CF11. No wait - I can't - since this feature is broken in beta. What I'll do though is file a bug report to ensure this does work. Otherwise the feature is incomplete.
  7. Raymond Camden
    I just checked the source code - and it looks like validation is enforced and struct is blocked. Filing a bug report now. With struct not working at all via Ajax in the beta, this feature doesn't look like it was tested well, which is unfortunate.
  8. Hemszz
    I need to understand the concept of Sessions in Coldfusion with Queries. Please can Anyone Help me out...?
  9. Marc
    Hello,

    How can i read a barcode in one of the input field ?

    Regards,

    Marc
  10. Brian
    Do you know why the example will not produce data from mysql db? It is producing the rows but the data is blank. My CFC is displaying data correctly. Will this only work for the Apache Derby Embedded?
  11. Ashok Ramkumar
    Do you know why the example will not produce data from Oracle DB? Query would not hit the oracle server. Works fine with out CFCLIENT.
    Will this only work for the Apache Derby Embedded?

Leave a Comment

Leave this field empty: