View Full Version : ColdConfusion : Passing intelligent recordsets back and forth

11-21-2006, 02:23 PM
I'm having some problems understanding the way to pass data back and forth between ColdFusion and Flex. Quite possibly that's due to the fact that I've yet to get the blankety-blank Coldfusion program working on my computer. But anyway, let me try and state this as clearly as I can.

I have a database, it has of course rows and tables. I therefore have a select clause I wish to run on it, and have that information passed back to my flex program. The select clause creates an object of sorts. really it's creating a recordset. Let me give an example here, a Select clause of the form: SELECT Field1, Field2, Field3 FROM Table1 WHERE Field4 = #Employee#
So that's going to be one or more "records" in a recordset. Now I want to pass that data, that recordset back to Flex.

In the examples I've seen, or more exactly, in the prior CF code that has been written for this project I'm working on, it seems that the query data is being forced into and conformed to an array. Then that array is taken and reconformed into another array once it's returned to Flex.

What is the "proper" way to perform these actions?

Again, I essentially want to pass a recordset from CF to Flex. Is that possible without resorting to positional arrays?

I know I'm missing something, or quite possibly a few things. And they're probably rather simple. But it's just not getting thru. Am I asking for too much of the products (CF and Flex) by asking for a simple means of passing recordsets from the server to the client? Or am I essentially being stupid here and having mental blocks?

Any clarification on this would help a lot!

Thanks in advance, and I'll try and clarify my question as and if needed,

11-21-2006, 02:29 PM
Maybe recordset isn't exactly the right words but an object, a class that represents a recordset, and can be addressed by saying something like: recordset[3].Field1 instead of something like array[3][0]. Sorry about not being more clear on that point.

11-21-2006, 03:05 PM
That's the way I've been doing it. I create an array of structs (each representing a single row from the table), and pass that array back to Flex for use.

11-21-2006, 03:13 PM
Aha! An array of structs! The light kinda came on there, I think. So it's not just a multdimensional array you're passing is it? (which is what they seem to be doing and heck that can get as confusing as the dickens!) you've got your array of structs and it contains recordset[1].suchandsuchField right? And you can reference it as such. So then
In CF you return structarray and in Flex you take structarray and put it into an array of classes right?

Does that make sense, and does it sound like what you were saying?


11-21-2006, 03:17 PM
Righto -- sounds like you've got the jist of it! I'll paste an example from my app:

ClientController.cfc: This is what Flex makes a call to.

<cfcomponent name="ClientController">
<cffunction name="getAllClients" returntype="Client[]">
<cfargument name="username" required="true" />
<cfargument name="password" required="true" />
<cfargument name="clientID" required="false" />
<cfargument name="orderBy" required="false" />>

<cfset returnArray = ArrayNew(1)>

<cfinvoke component="#Request.componentPath#.UserController" method="validateLogin" returnvariable="validLogin">
<cfinvokeargument name="username" value="#arguments.username#" />
<cfinvokeargument name="password" value="#arguments.password#" />
<cfif validLogin>
<cfquery name="records" datasource="#Request.DSName#">
FROM Clients
<cfif isDefined('arguments.clientID') AND isNumeric(arguments.clientID)>
AND Clients.clientID = <cfqueryparam value="#arguments.clientID#" cfsqltype="cf_sql_integer" />
<cfif isDefined('arguments.orderBy')>
ORDER BY #arguments.orderBy#

<cfloop query="records">
thisClient = CreateObject("Component","Client");
ArrayAppend(returnArray, thisClient);

<cfreturn returnArray />

Client.cfc: This is an object representing a single row in the table. You can see it gets used in the ClientController.cfc to turn a query row into a Client object.

<cfcomponent name="Client">
<cfproperty name="clientID" type="numeric" default="" />
<cfproperty name="clientName" type="string" default="" />
<cfproperty name="contactName" type="string" default="" />
<cfproperty name="address" type="string" default="" />
<cfproperty name="city" type="string" default="" />
<cfproperty name="state" type="string" default="" />
<cfproperty name="zipcode" type="string" default="" />
<cfproperty name="phone" type="string" default="" />
<cfproperty name="fax" type="string" default="" />
<cfproperty name="email" type="string" default="" />
<cfproperty name="clientNotes" type="string" default="" />
<cfproperty name="isSAC" type="boolean" default="" />

<!--- SETTERS --->
<cffunction name="setClientID" output="false" access="public" returntype="void">
<cfargument name="val" type="numeric" required="true">
<cfset variables.clientID = arguments.val>

<cffunction name="setClientName" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.clientName = arguments.val>

<cffunction name="setContactName" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.contactName = arguments.val>

<cffunction name="setAddress" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.address = arguments.val>

<cffunction name="setCity" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.city = arguments.val>

<cffunction name="setState" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.state = arguments.val>

<cffunction name="setZipcode" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.zipcode = arguments.val>

<cffunction name="setPhone" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.phone = arguments.val>

<cffunction name="setFax" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.fax = arguments.val>

<cffunction name="setEmail" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.email = arguments.val>

<cffunction name="setClientNotes" output="false" access="public" returntype="void">
<cfargument name="val" type="string" required="true">
<cfset variables.clientNotes = arguments.val>

<cffunction name="setIsSAC" output="false" access="public" returntype="void">
<cfargument name="val" type="boolean" required="true">
<cfset variables.isSAC = arguments.val>

<!--- GETTERS --->
<cffunction name="getClientID" output="false" access="public" returntype="any">
<cfreturn variables.clientID>

<cffunction name="getClientName" output="false" access="public" returntype="any">
<cfreturn variables.clientName>

<cffunction name="getContactName" output="false" access="public" returntype="any">
<cfreturn variables.contactName>

<cffunction name="getAddress" output="false" access="public" returntype="any">
<cfreturn variables.address>

<cffunction name="getCity" output="false" access="public" returntype="any">
<cfreturn variables.city>

<cffunction name="getState" output="false" access="public" returntype="any">
<cfreturn variables.state>

<cffunction name="getZipcode" output="false" access="public" returntype="any">
<cfreturn variables.zipcode>

<cffunction name="getPhone" output="false" access="public" returntype="any">
<cfreturn variables.phone>

<cffunction name="getFax" output="false" access="public" returntype="any">
<cfreturn variables.fax>

<cffunction name="getEmail" output="false" access="public" returntype="any">
<cfreturn variables.email>

<cffunction name="getClientNotes" output="false" access="public" returntype="any">
<cfreturn variables.clientNotes>

<cffunction name="getIsSAC" output="false" access="public" returntype="any">
<cfreturn variables.isSAC>

And here's how I have Flex using it:

private function getAllClientsResult(event:ResultEvent):void
var tempArray:Array = event.result as Array;

clientArray = new Array(tempArray.length);

11-21-2006, 03:21 PM
Cool that sounds a more logical to my head now I can try and write the CF code (a bit with a blindfold on since I don't quite have it running here yet) and then put that data into my class(es) (Value Objects) that I've already done some work on in flex.
I can tell just by glancing over your examples that that's more logical.

Thanks much,

11-21-2006, 03:27 PM
Well, it works... but I have this nagging feeling there's a better way to do this.

A problem I've identified is that if I want to Strictly type the variables in Flex as being Client objects (rather than just being an object with whatever properties I pass in from ColdFusion), I need to create a separate Client.as file and convert the Array objects into that.

So yeah ... toy around with it and see how you like it :) And if you happen to come across improvements, post em :D

11-21-2006, 04:12 PM
Yeah I hear ya on that part. I've got/am building Value Objects (data classes) to hold all these things, as that seems to be the "way" to do it. And casting and such doesn't work for doing that. But then again it wouldn't really work for any database module, really even if it was "more tightly coupled" than in this server client scenario.

Hmm always room for improvement though


11-21-2006, 04:22 PM
One thing of note, and I didn't even think of doing it, is you're making a CFC data class as well as a Flex based data class (which I'm assuming that clientArray is an array of that data class)

11-21-2006, 04:28 PM
In the code posted above, clientArray is just a simple Array object:

[Bindable] private var clientArray:Array = new Array();

That's what I was getting at with my last post. If I wanted to make it a Client[], I would have to create yet another data class in Actionscript. Which reeks of evil to me -- as now I have to update my model in two separate places if I want to make a change. I haven't thought up a way around that... it doesn't seem ColdFusion can use my AS classes, and AS can't read my CF classes -- so it seems we have little choice in the matter.

11-21-2006, 06:01 PM
LOL This would be cool to have, though it doesn't totally solve the problem but it does go a long way towards helping.

Think of a...let's call it a code generator (hehe I guess just something else to go in my GenieRator, that I've done some work on but not much.) that takes a database (for starters) and shows you the fields in a table/tables.

It also lets you choose some of those fields. And maybe specify a few more fields as needed for your data class (and really it's data classes but I don't wanna quite give away the punch line) so then you have in this Wizard dialog in front of you a set of "fields" right? Each of them having a data type specified.

Now at the bottom of that Wizard dialog are two checkboxes. One says "Make As Data Class", the other says "Make CF Data Class" And beside each of those is an edit box allowing you to specify the directories (or maybe fully pathed file names) and finally at the bottom you have a button that says "Do it!" (and of course a Cancel button)

And voila, just by clicking "Do it!" you have two sets of "Strongly Typed Data Classes", each in their respective places and respective languages.

LOL God I'm such a geek huh? :D

I laid it out in a good bit of detail so that maybe someone might be able to write something like that. I might sometime if I find some time. In my mind at least, given a Windows (.NET) environment and using ADO it could be done reasonably "generically" and therefore use a wide range of databases (or in general data sources). Marc Clifton wrote something like this that takes an XML file and generates C# data classes. Here's the link in case anyone is interested Generate Classes From Declarative Code (http://www.codeproject.com/csharp/declarativeclassgenerator.asp) Hehe there's a bug in his implementation of it, at least in my mind, but I fixed it :p Yep, I think that settles it, I'm a geek!


11-21-2006, 07:12 PM
Well, you're among your own kind then :D I've done similar things before with CF forms and database scripts.

11-21-2006, 07:40 PM
Good, then you're the perfect man for the job, I'll expect it by Thursday (I'll be eating turkey and watching football LOL) Oh, and can we have a code converter built in there too? One that easily converts between ActionScript, CF, C#, C++ (and heck "C" anything after it) and java, oh and that language, whatever the bleep it is that Jaba the hut speaks. (Notice I'm not being mean and asking you for it to convert to something like Visual (anything but basic, so convoluted it's laughable) or Cobol. So yeah I think that should be feasibly done by say monday right? LOL (Ok, sorry did I sound like your boss a bit too much there?)

But I'm truly being silly now. I think I need Thanksgiving!

11-21-2006, 08:30 PM
Haha! We all need some turkey! :D

11-21-2006, 09:45 PM
damn.. that kinda looks like the long way around (having cf build the struct).... the way you do the client set up looks kinda helpful for some thing i was working on in the past.

my cf code always looks something like:

<cffunction name="getFacilityBySA" access="remote">
<cfargument name="reportName" type="string"/>
<cfargument name="tsa" type="string"/><!--- label SA or PCSA --->
<cfargument name="saValue" type="string"/><!--- name of service area or pcsa --->

<cfset var sv=""/>

<cfset var tbl="#getTableName(reportName)#"/>
<cfset var q=""/>
<cfset var sql=""/>

<cfif tsa eq 'SA'>
<cfset sv="Service_Area"/>
<cfset sv="PCSA"/>
<cfset sql="select
distinct FAC_NAME
from #tbl#
where ((#sv# = '#saValue#') and (FAC_NAME is not null))
order by FAC_NAME"/>
<cfquery name="q" datasource="#dsn#">

<cfset q="Error|"& cfcatch.detail & "|" & cfcatch.Message />
<cfreturn q />

so in this code... 'q' is the recordset (query object). i just return that directly to the calling object....

in flash i would do something like....

//clear the facility list box
//reload it with the data sent in from the cfc
var tmp=o.items;
}else if(details_rbg.selectedData=="team"){
}else if(details_rbg.selectedData=="provider"){
for(var i=0;i<tmp.length;i++){
}else if(details_rbg.selectedData=="team"){
//trace(tmp[i].FIRST_NAME + " " + tmp[i].LAST_NAME);
choiceList_lb.addItem({Facility:tmp[i].FAC_NAME,Provider:tmp[i].FIRST_NAME + " " + tmp[i].LAST_NAME, ProvID: tmp[i].PAN_PROV});
//var z=0;
var at:Array=rep._choiceList;
for(var n=0;n<tmp.length;n++){
if(tmp[n].FAC_NAME == at[0]) var z=n;
}else if(details_rbg.selectedData=="team"){
if(tmp[n].FAC_NAME==at[0] && tmp[n].TEAM ==at[1]) var z=n;
if(tmp[n].FAC_NAME==at[0] && tmp[n].PAN_PROV == at[1]) var z=n;
if(z != undefined) choiceList_lb.selectedIndex=z;


so flash recieves it from CF as an array.

but i can access all the fields like you are doing


note that all the field names are in CAPS.... you gotta do it this way, cause thats teh way flash remoting sends it....

well all that being said,
i ahve many reports built this way with flash remoting / coldfusion, but have yet to get the remoting to work with flex.... i will be watching your threads form now on tho cdh, cause i have a feeling you will lead me to the answer i want....

... now to go thru and re-read this thread again. thanks for this question.

11-21-2006, 09:57 PM
actually.... after re-reading this thread.
let me say just a bit more...

in my case i build my gui in flash. and build a flash as file that holds the class info, as well as handles the interaction with the cfc via remoting... if i were to do this properly, i would probably have a separate class deal with the remoting calls, and keep it separate from the data class.... but im a lazy pig.

any way. this has always worked well for me getting info back and forth from flash and cfcs, but i have yet to try to get it to work with a flex interface....

11-22-2006, 07:43 PM
So the general concept here is as follows:
1) A controller CFC that houses the functions that will be called by the Flex part of the application.
2) Separate Data Classes (or Value Objects) for each of the organized sets of data you wish to pass to the calling Flex module(s).
3) Comparable Data Classes in the Flex module.
4) The MXML/AS code that declares the RemoteObject (that object being the Controller CFC) and calls the methods/functions available.

Does that sound about like it? If so then cool, sounds kinda "cairngorm-ish" although I've yet to study cairngorm enough to really say that outright. It also sounds like some code generators could be devised to assist and genericize these tasks to a degree.

Just making sure with everyone that I'm on the right mental track here.


11-22-2006, 07:56 PM
That's exactly what I'm doing -- it just seemed the best way to do it to me.

I've never been much of a framework junkie though -- they seem to contribute more to confusion and code-bloat than the overall health of my applications. I know pretty much nill about Cairngorm, but I'm sure there's something out there that uses a similar design.

And yep, you could make generators for all of those components. I don't see any code in here that would be overly difficult to do given an object and it's attributes.

11-22-2006, 08:51 PM
Ok so I understood your points and I would say that I probably am and am not a "framework" junkie. When you've got a bunch of people working on a project and the very open possibility of them coming and going. A common reasonably understood pathway of solving problems assists to a large degree. Not that I'm not notorious for taking a "cowboy" tact and just da** it doing what works for the given moment. And in a way you've adapted at least pieces of your own framework. And they sound good to me. ;-)

Really if the separate pieces of a "framework" can't be taken and used and found viable as a piece unto themselves then they aren't good structural pieces are they? So maybe I'm the type that takes a framework, pulls it apart and goes "hmm how does this sucker work?" I dunno. LOL