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

Using database default values in ColdFusion ORM

I stumbled upon a problem when I was using ORM on a SQL table which has a default value (column not null, default value 0). The problem is that ORM does not accept a value which isn't set because then it will insert it as NULL.

Example:
/**
* @persistent true
* @entityName UserError
* @table UserErrors
* @schema dbo
*/
component output="false"
{
property name="user_id" column="user_id" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="error_count" column="error_count" type="numeric" ormtype="int"; //column with default value
}

The step for me was to force that you can set this value as NULL, which caused an error:
property name="error_count" column="error_count" type="numeric" ormtype="int" notnull="false" ;

Instead of an ORM error you get a database error. So the next try is to tell ORM not to use this:
property name="error_count" column="error_count" type="numeric" ormtype="int" persistent="false" ;

This worked for the insert because the value was set correctly in the database. The problem is that you cannot retrieve the value after it is inserted.
So the next step is to modify the data before it is inserted. In the component I added the folowing function:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(0);
}
Now I get an error converting char to int. This gives me a clue that the preInsert function is executed. So now I have to force ColdFusion to use int for the Error_count default value:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(javacast("int",0));
}

Now it is working as a charm.
The only problem is that you have two places where your database defaults exists. This is something to keep in mind if you are changing databse default values. It is working for me, but it is not ideal.
If I find a better solution I will post it here. Suggestions are welcome in the comments.