Today is a good day to code

ColdFusion MX 6.1 Query Object Nightmare

Posted: December 31st, 1969 | Author: | Filed under: Uncategorized | No Comments »

ColdFusion MX 6.1 Query Object Nightmare

Picture of Irv Owens Web DeveloperFor the past week I have been trying to find a workaround for a query object nightmare in ColdFusion MX. I have an XML file that I am using to record changes in an application, and I am developing a log browser. Everything works fine, I can easily create the custom query using the following syntax:


“userSecurityLevel”, parsedXML.log.logRecord[i].blockNumbers.userSecurityLevel.XmlText
“confirmationNumber”, parsedXML.log.logRecord[i].blockNumbers.confirmationNumber.

The result is a nicely formed query object that I can perform queries on like this:

SELECT [id],[action],[user],[userSecurityLevel],[time],[date],
The brackets are to differentiate the field names from system operators
FROM recordSet
ORDER BY [#URL.value#]

That will return a nicely formed recordset, my output screen allows the user to sort by clicking on the row headers. I pass a URL variable over the top back to the page and sort the recordset in memory. Nice. Here's where the rub comes in. The problem starts when I want to add a where clause to that query of queries. It seems that since Java is strongly typed, many of my values are cast to other values. For example, I am generating a reference number for the transactions that has many hexadecimal numbers in it. Even though I have put quotes around it for my QuerySetCell it still thinks that it is a LONG or DOUBLE value. I have used the toString method and it still casts it to a LONG or DOUBLE. Nothing seems to be able to change it to a string.

What makes me angry is that instead of creating a patch or issuing a workaround, Macromedia has added the ability to give your column a CF_SQL type, which alleviates the problem… if you are using CF 7. The client is scheduled to upgrade to CF 7 soon, but not before this contract is over, or before the project is to launch so this does me no good. I am still looking for a workaround for CFMX 6.1, but I haven't found one yet, short of eschewing the ability to perform natural text searches for paging through miles and miles of logs, or allowing them to export to Excel and they can search that way. The latter is how I am leaning.

For laughs check out this post on Macromedia's livedocs on this issue. I love CF, but sometimes I think the move to Java screwed a bunch of the features up. I know that Macromedia wants to drive more paid upgrades, but they should really issue a patch for this like yesterday.

About Query of Queries – MM livedocs