Wednesday, September 22, 2004

UDF for Converting QueryBean objects to CF Query objects

I've blogged before about the difficulties of returning complex data in CFMX webservices
After many hours of banging my head on the desk, I've come up with a scheme that actually seems to work. I'll leave the details of it to another, more expansive post, because I've just written a function that I found extremely useful and I wanted to share it.

In my WS scheme, I'm making all my webservice methods pass back a generic object (wsgeneric.cfc, or a subclass of that) which has three properties
  • a status code
  • an error description (if an error occurs)
  • the actual returned data - which may be anything
One thing I've found a little irritating is that when the actual data is a query, you don't get an actual query BACK. What you get is a java QueryBean object, which doesn't have the nice convenient syntax for referring to its elements that a CF query has.

So, I whipped up a UDF for converting a QueryBean back to a CF query, and here it is:

<cffunction name="queryBeanToQuery" access="public" returntype="query" output="yes">
<cfargument name="objQueryBean" type="any" required="true"/>

<cfscript>

var qry_return = "";
var arrColumns = ArrayNew(1);
var arrRows = arrayNew(1);
var thisRow = 0;
var thisCol = 0;
var numRows = 0;
var thisVal = "";

if( objQueryBean.getClass() EQ "class coldfusion.xml.rpc.QueryBean" ){
arrColumns = objQueryBean.getColumnList();
numCols = arrayLen( arrColumns );
arrRows = objQueryBean.getData();
numRows = arrayLen( arrRows );
// create the return query object
qry_return = QueryNew( ArrayToList(arrColumns) );
// loop round each row
for( thisRow = 1; thisRow LTE numRows; thisRow = thisRow + 1 ){
QueryAddRow( qry_return );
// loop round each column
for( thisCol = 1; thisCol LTE numCols; thisCol = thisCol + 1 ){
// empty columns seem to give rise to undefined array elements!
try{
thisVal = arrRows[thisRow][thisCol];
}
catch(Any e) {
thisVal = "";
}
QuerySetCell( qry_return, arrColumns[thisCol], thisVal );
}
}
return qry_return;

} else {
writeOutput( "THATS not a query bean, it's a #objQueryBean.getClass()#!" );
qry_return = QueryNew("");
return qry_return;
}

</cfscript>
</cffunction>

It's not really production code, but it's saved me a lot of time, and hopefully someone out there will find it useful.

Enjoy!