If you need to run any date comparisons on a ColdFusion Query of Queries, you need the following:
In the original database query, select the values as datetime. DO NOT cast them as date. (This is with MSSQL, anyway).
In the follow-up query, use CFQUERYPARAM and the cf_sql_date type.
I spent waaay too long trying to select subsets of the original query based on dates, and whether I selected them as strings (for implicit conversion) or using query parameters, I just couldn’t get any results out of them. It turned out that since the original query had cast the datetime value as a date, I couldn’t make any comparisons in the query of queries.
Once I removed that cast (originally there to simply group timestamps by day, but I’d since changed the query to group by month), it worked.
select mydate, mystuff from mytable
<cfquery dbtype="query" name="followup">
select mystuff from mytable
where mydate = <cfsqlparam value="#somedate#"
Obviously this example is oversimplified, but you get the idea.
Every once in a while I encounter a problem with something so blindingly simple that
I can’t imagine I haven’t tried it before, and
I can’t believe no one else has documented the problem either.
ColdFusion 9 and later has a tag called <cfspreadsheet> which makes it dead simple to read, update, or create an Excel file. Want to plug the results of a query into a spreadsheet? Literally one tag with just three parameters.
There’s also function GetTempFile() that will create a guaranteed unique file on the server that you can then manipulate.
The obvious solution if you want to create a spreadsheet to download is to create a temp file, write to it with cfspreadsheet, then serve it up.
It turns out this won’t work. You’ll get a Null Pointer Exception in the logs. Not because of anything you’d expect, though…it turns out that cfspreadsheet is relying on the file extension, and since GetTempFile() gives you a file ending in .tmp instead of .xls, it isn’t sure what to do with the file.
Solution: Find some other way to name your temporary file, and give it an .xls extension.
Today I was trying to fix a problem in a section of a website that hadn’t been changed in roughly 5 years. The page in question retrieved data from a database and filled out an Acrobat form using FDF. Under some circumstances, Adobe Reader would generate an error message, “Expected a dict object.” Then it would freeze, and crash the web browser for good measure.
This site was built with ColdFusion, and used a then-freely-available library called PDFFormFiller.cfm (I can’t find any sign of it now) to generate the FDF code. After saving the offending FDF to a file (eliminating the browser as a factor), I started manually editing the code to see what happened.
The problem turned out to be parentheses appearing in the form data. FDF uses parentheses-delimited strings, and it was finding ) in the code and trying to parse what was left as FDF tokens. The solution was simple: just escape the parentheses as \( or \). Continue reading →