ColdConfusion : Passing intelligent recordsets back and forth

[CDHBookingEdge]

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,
Christopher

[CDHBookingEdge]

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.

[meddlingwithfir]

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.

[CDHBookingEdge]

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?

Christopher

[meddlingwithfir]

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#" />
		</cfinvoke>
		<cfif validLogin>
			<!--- BAD LOGIN, DO NOT GIVE INFO --->
		<cfelse>
			<cfquery name="records" datasource="#Request.DSName#">	
				SELECT *
				FROM Clients
				WHERE 0=0
				<cfif isDefined('arguments.clientID') AND isNumeric(arguments.clientID)>
					AND Clients.clientID = <cfqueryparam value="#arguments.clientID#" cfsqltype="cf_sql_integer" />
				</cfif>
				<cfif isDefined('arguments.orderBy')>
					ORDER BY #arguments.orderBy#
				</cfif>
				;
			</cfquery>
			
			<cfloop query="records">
				<cfscript>
					thisClient = CreateObject("Component","Client");
					thisClient.setClientID(records.clientID);
					thisClient.setClientName(records.clientName);
					thisClient.setContactName(records.contactName);
					thisClient.setAddress(records.address);
					thisClient.setCity(records.city);
					thisClient.setState(records.state);
					thisClient.setZipcode(records.zipcode);
					thisClient.setPhone(records.phone);
					thisClient.setFax(records.fax);
					thisClient.setEmail(records.email);
					thisClient.setClientNotes(records.clientNotes);
					thisClient.setIsSAC(records.isSAC);
					ArrayAppend(returnArray, thisClient);
				</cfscript>
			</cfloop>
		</cfif>
		
		<cfreturn returnArray />
	</cffunction>
</cfcomponent

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>

	<cffunction name="setClientName" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.clientName = arguments.val>
	</cffunction>
	
	<cffunction name="setContactName" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.contactName = arguments.val>
	</cffunction>
	
	<cffunction name="setAddress" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.address = arguments.val>
	</cffunction>
	
	<cffunction name="setCity" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.city = arguments.val>
	</cffunction>
	
	<cffunction name="setState" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.state = arguments.val>
	</cffunction>
	
	<cffunction name="setZipcode" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.zipcode = arguments.val>
	</cffunction>
	
	<cffunction name="setPhone" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.phone = arguments.val>
	</cffunction>
	
	<cffunction name="setFax" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.fax = arguments.val>
	</cffunction>
	
	<cffunction name="setEmail" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.email = arguments.val>
	</cffunction>
	
	<cffunction name="setClientNotes" output="false" access="public" returntype="void">
		<cfargument name="val" type="string" required="true">
		<cfset variables.clientNotes = arguments.val>
	</cffunction>
	
	<cffunction name="setIsSAC" output="false" access="public" returntype="void">
		<cfargument name="val" type="boolean" required="true">
		<cfset variables.isSAC = arguments.val>
	</cffunction>

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

	<cffunction name="getContactName" output="false" access="public" returntype="any">
		<cfreturn variables.contactName>
	</cffunction>
	
	<cffunction name="getAddress" output="false" access="public" returntype="any">
		<cfreturn variables.address>
	</cffunction>
	
	<cffunction name="getCity" output="false" access="public" returntype="any">
		<cfreturn variables.city>
	</cffunction>
	
	<cffunction name="getState" output="false" access="public" returntype="any">
		<cfreturn variables.state>
	</cffunction>
	
	<cffunction name="getZipcode" output="false" access="public" returntype="any">
		<cfreturn variables.zipcode>
	</cffunction>
	
	<cffunction name="getPhone" output="false" access="public" returntype="any">
		<cfreturn variables.phone>
	</cffunction>
	
	<cffunction name="getFax" output="false" access="public" returntype="any">
		<cfreturn variables.fax>
	</cffunction>
	
	<cffunction name="getEmail" output="false" access="public" returntype="any">
		<cfreturn variables.email>
	</cffunction>
	
	<cffunction name="getClientNotes" output="false" access="public" returntype="any">
		<cfreturn variables.clientNotes>
	</cffunction>
	
	<cffunction name="getIsSAC" output="false" access="public" returntype="any">
		<cfreturn variables.isSAC>
	</cffunction>
</cfcomponent>

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);
			}

[CDHBookingEdge]

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,
Christopher

[meddlingwithfir]

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

[CDHBookingEdge]

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

Christopher

[CDHBookingEdge]

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)

[meddlingwithfir]

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.