Tag Archives: ColdFusion

Dates and ColdFusion Query of Queries

Argh.

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.

<cfquery name="realquery">
select mydate, mystuff from mytable
</cfquery>

<cfquery dbtype="query" name="followup">
select mystuff from mytable
where mydate = <cfsqlparam value="#somedate#"
                   cfsqltype="cf_sql_date" />
</cfquery>

Obviously this example is oversimplified, but you get the idea.

cfspreadsheet + GetTempFile() = NullPointerException

Every once in a while I encounter a problem with something so blindingly simple that

  1. I can’t imagine I haven’t tried it before, and
  2. 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.

<cfset filename=GetTempFile(GetTempDirectory()) >
<cfspreadsheet action="write" overwrite="true" filename="#filename#" query="myquery">
<cfheader name="Content-disposition" value="attachment;filename=MyFile.xls">
<cfcontent type="application/msexcel" file="#filename#">
<cffile action="delete" file="#filename#">

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.

“Expected dict” Errors in FDF Acrobat Forms

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