If you use JDBC drivers for your ODBC source in ColdFusion, you may notice that you don’t return a new column entered into your database table using SELECT *. You may also get an error such as “Syntax error converting the varchar value ‘billboard’ to a column of data type int”.

I’ve known about this for awhile from Mark Kruger, but other people have asked me about this nuance.

Basically when you use SELECT * in your query, it caches the database schema.

Example Code:

<cfquery name="q1" datasource="#application.DSN#">
SELECT * FROM TABLE1
WHERE COLA = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="A">
</cfquery>

Three things can fix this:

1. Update your code to include the specific column names, such as “SELECT COLA, COLB”. This will also increase performance since SQL doesn’t have to lookup the table columns.
2. Change your query somehow, even adding a space
3. Restart the ColdFusion Service. This will flush out the schema cache.

After literally hours of tracking down what was causing a large code base to behave badly, I found an odd and frustrating features when doing a query of queries with <cfquery>. Take the following code as an example of what you would think works:

<cfquery name="q1" datasource="#application.DSN#">
 SELECT ' A2010 ' as v1
 FROM Seasons
 WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>

<cfquery name="q2" dbtype="query">
 SELECT v1
 FROM q1
 WHERE LOWER(v1) = '#Lcase(' A2010 ')#'
</cfquery>

The above code will return 10 results for q1 but none for q2 even though they are both matching ” 2010 “.

To fix the issue I tried using in the second query and even tried replacing the space with a #chr(32)# character code. Neither worked.

Changing the query to the following works:

<cfquery name="q1" datasource="#application.DSN#">
 SELECT ' A2010 ' as v1
 FROM Seasons
 WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>

<cfquery name="q2" dbtype="query">
 SELECT v1
 FROM q1
 WHERE LOWER(v1) = '#Lcase('A2010')#'
</cfquery>

Notice I removed the space before and after the 2010 in the second query. This will return all 10 results even though it is not an exact match.

ColdFusion is automatically trimming the whitespace of the values returned from q1.

The only solution I can find is to trim the value to match as well. This will provide for an inexact match, but will work for my purpose. This is very concerning because you could have a lack of results and not even know it without inspecting it.

The final code I’ve come up with for a “sorta-fix-workaround” is:

<cfquery name="q1" datasource="#application.DSN#">
 SELECT ' A2010 ' as v1
 FROM Seasons
 WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>

<cfquery name="q2" dbtype="query">
 SELECT v1
 FROM q1
 WHERE LOWER(v1) = '#Lcase(Trim(' A2010 '))#'
</cfquery>

The “ColdFusion Muse“, aka “My Boss”, has posted something I’d consider VERY important – this is a MUST read. This may save you frusteration, dollars, or more! He explains a potential vulnerability related to executing malicious code on your server without you knowing and can be very difficult to track down. After you read this, you may say duh – why didn’t I think of that? http://www.coldfusionmuse.com/index.cfm/2009/9/18/script.insertion.attack.vector

For years I’ve never been aware of a method to return only so many rows to the ColdFusion memory space using dbtype=”query”. I’ve always just ran the query then limited it down the code with things like <cfoutput query=”q” maxrows=”1″>.

In a MSSQL query, you can limit the query like: SELECT TOP 1 FROM TABLE

This will not work with a query of queries.

Today I found a solution…

Move the maxrows attribute to the cfquery tag. For example: <cfquery name=”q” dbtype=”query” maxrows=”1″> will return only the first row back to the memory space.

Let’s say you have an application requirement that uploads files to your web server and then lets your visitors either view or save them. They could be Documents, Spreadsheets, Photos, or anything else you can think of. However you want to be able to control who is able to access these files. How do you accomplish that with not allowing direct linking (ex. http://www.cfwebtools.com/files/xyz.docx)?

You can serve the files via a ColdFusion page. There are a few different methods to do this, and they really come down to MIME typing.

Option 1:
Allow the user to save or open any file.

<cfheader name="Content-Disposition" value="attachment; filename=""#getFileFromPath(filePath)#""">
<cfcontent file="#filePath#" type="application/octet-stream">

The cfheader of attachment and filename prompt the user to either save or externally open the document with the software and their computer. The client computer will determine which MIME type to use to associate with an application.

Option 2:

Allow the user to save or open only files with MIME types my server is aware of.

<cfset mimeType = getPageContext().getServletContext().getMimeType(filePath)>
<cfif IsDefined("mimeType")>
<cfheader name="Content-Disposition" value="attachment; filename=""#getFileFromPath(filePath)#""">
<cfcontent file="#filePath#" type="#mimeType#">
<cfelse>
This type of file is not supported.
</cfif>

Now – the MIME types supported are defined in your webserver, whether it’s IIS, Apache or other. Apache MIME types can be configured in the mime.types files in the httpd/conf directory. The getMimeTypes() method communicates with the web server and returns the associated MIME type for the file.

Now, be aware that there may be an issue with IIS communicating MIME types to ColdFusion on Windows 2K Server. I’ve had an application break using this method for additional types such as .docx extensions. I have not yet found a solution for it, and just use option #1 to work around it.

Option 3:
Allow the user to view supported types inside the browser such as MS Word documents and PDF files. If not supported in the browser, it will attempt to open the file externally.

<cfset mimeType = getPageContext().getServletContext().getMimeType(filePath)>
<cfif IsDefined("mimeType")>
<cfheader name="Content-Disposition" value="inline; filename=""#getFileFromPath(filePath)#""">
<cfcontent file="#filePath#" type="#mimeType#">
<cfelse>
This type of file is not supported.
</cfif>

You can also define the MIME type in the code by replacing the mimeType variable with the type such as “application/msword”.

Notice in the three examples I double up the quotes around the filename attribute in the cfheader tag. This resolves an issue that happens when you have spaces in the file name. Using no quotes will truncate the file name. Using double quotes will create an invalid tag. And using single quotes will just add single quotes to the file name. Therefore using doubled up (escaped) double quotes makes this work. Apparently in IE7 (and I’m assuming earlier versions) the spaces are converted into underscores (_). You could also use character codes (chr(34)) instead. Thanks to Ben Nadel for this solution.

These methods allow you to access any attached storage device on the server. Therefore you can save the files out of the web directory, making the file inaccessible without using your ColdFusion script. Imagine the possibilites like adding user authentication or IP restriction.

Additional resources:

Serving File Downloads with ColdFusion via Trunkful.com

Today I attempted to create a struct in ColdFusion 8 via shorthand using the syntax:

<cfscript>
struct = {key1=val1,key2=val2};
</cfscript>

My case was creating a state list with abbreviation for keys and the state name as the value. However I have determined that not all key names can be used. If it is a reserved ColdFusion word, it may not work. For example “IN” and “OR”.

What I want:

<cfscript>
var strStates = {IN="Indiana",OR="Oregon",UT="Utah"};
</cfscript>

But that leaves me with an “Invalid CFML construct found…”.

So as a workaround I did this:

<cfscript>
var strStates = {UT="Utah"};
strStates["IN"] = "Indiana";
strStates["OR"] = "Oregon";
</cfscript>

So much for shorthand. Has anyone found a better solution for this?