ColdFusion queries and SQL comment

Here's one problem I experienced using a query in ColdFusion which was working perfectly in SQL server manager, but refused to work in ColdFusion.
The query was something like:

SELECT * --some comment
FROM users
WHERE user_id = 1

The query would only work if the comment is removed. The source of the problem is that ColdFusion removes all the line breaks in a query before executing. In this case only "SELECT *" would be executed which results (luckily) in a SQL error.
Good to know this before I tried the following query:

DELETE
FROM users --some comment
WHERE user_id = 1

Note: Normally you would use a SQL param and replace it with queryService.addParam(), this would result in an error because the param could not be found.

To work around this problem use /* and */ for SQL comment, or strip all your comments in SQL if you execute it through ColdFusion.

Coldfusion 9 problems

This is to make a note which problems I encountered with ColdFusion 9:

SpreadSheetRead has no spreadsheet to query possibility.
returns a query, but with the script function there is no option so it only returns an Excel object:
excelObj = SpreadSheetRead('c:\some.xls',1);

Next stop, the query object with double single quotes:
queryService = new Query();
queryService.setName("qUserExists");
queryService.setDataSource(application.settings.DSN);
queryService.addParam(name="userName", cfsqltype="cf_sql_varchar", value="someusername");
SaveContent variable="queryBody" {
WriteOutput("SELECT TOP 1 CASE ISNULL(U.firstname, '') WHEN '' THEN '*no firstname*' ELSE U.firstname END AS lastname ");
WriteOutput("FROM Users U ");
WriteOutput("WHERE U.Username = :userName ");
}
queryService.setSql(queryBody);
qUserExists = queryService.execute();

Don't mind the uglyness of the query, it is there to prove a bug. The query will return an error. If you see the details of the error and look at the executed query you will see that the two single quotes are replaced by one. So ISNULL(U.firstname, '') will be executed as ISNULL(U.firstname, ').

If someone knows a solution, please let me know.

UPDATE: The query issue is a known bug (80210) and is resolved in update 9.0.1