What are the details for using CF_SQL_NVARCHAR in ColdFusion 10?

Question

The ColdFusion 10 documentation on Updating Your Database has a section on Database-related enhancements in ColdFusion 10. That page mentions that there is now support for CF_SQL_NVARCHAR among others, but with no details about them. Additionally, the cfqueryparam documentation hasn't been updated to include their existence.

The ColdFusion 9 documentation for cfqueryparam mentions that CF_SQL_VARCHAR maps to varchar in MSSQL. This is true unless the ColdFusion Administrator datasource settings has the String Format setting enabled. In which case CF_SQL_VARCHAR maps to nvarchar. This poorly documented feature is a hack which can cause performance issues within ColdFusion.

So it's great that they have introduced CF_SQL_NVARCHAR, but it would be good to understand how it works. It is simply an alias for CF_SQL_VARCHAR making it pointless? Does it always send strings as nvarchar? If so, does CF_SQL_VARCHAR always send in varchar?

I would hope that for backward compatibility's sake it is implemented as such:

If String Format is enabled CF_SQL_VARCHAR and CF_SQL_NVARCHAR both map to nvarchar.

If String Format is disabled then CF_SQL_VARCHAR maps to varchar and CF_SQL_NVARCHAR maps to nvarchar.

This would mean any pre-CF10 sites can move to CF10 and work, with the same performance considerations pre-CF10.

New sites, or sites that rewrite all queries to match CF_SQL_VARCHAR and CF_SQL_NVARCHAR with the database design will not get the performance penalty that is unavoidable pre-CF10.

Can anyone confirm if this is the case; even better if with something official?

Nippy

Is anyone else experiencing a new issue with this where CF_SQL_VARCHAR is mapped to nvarchar even with string_format off? – Uno

Answer 1

While you are waiting for something more official, I will throw in my $0.02 ...

I did some digging and based on my observations (with an MS SQL datasource) I believe that:

• CF_SQL_NVARCHAR is not just an alias for CF_SQL_VARCHAR. It maps to the newer NVARCHAR jdbc type, which lets you to handle unicode values at a more granular level.

• CF_SQL_NVARCHAR values are always treated as nvarchar

• The handling of CF_SQL_VARCHAR depends on the String Format setting, same as in previous versions.

CF_SQL_NVARCHAR Test/Results:

If you enable datasource logging, you can see the driver invokes the special setNString method whenever CF_SQL_NVARCHAR is used. So ultimately the value is sent to the database as nvarchar. (You can confirm this with a SQL Profiler)

    // Query
    SELECT  ID
    FROM    Test
    WHERE   NVarcharColumn = <cfqueryparam value="#form.value#" cfsqltype="cf_sql_nvarchar">

    // Log 
    spy(...)>> PreparedStatement[9].setNString(int parameterIndex, String value)

    // Profiler
    exec sp_prepexec @p1 output,N'@P1 nvarchar(4000)',N'SELECT  ID
            FROM    Test
            WHERE   NVarcharColumn = @P1 ',N'Стоял он, дум великих полн'

CF_SQL_VARCHAR Test/Results:

In the case of CF_SQL_VARCHAR, it is technically flagged as varchar. However, the String Format setting ultimately controls how it is handled by the database. When the setting is enabled, it is handled as nvarchar. When it is disabled, it is treated as varchar. Again, you can verify this with a SQL Profiler.

Bottom line, everything I have seen so far says you are right on target about the implementation.

    // Query
    SELECT  ID
    FROM    Test
    WHERE   PlainVarcharColumn = <cfqueryparam value="#form.value#" cfsqltype="cf_sql_varchar">

    // Log
    spy(..)>> PreparedStatement[8].setObject(int parameterIndex, Object x, int targetSqlType)
    spy(..)>> parameterIndex = 1
    spy(..)>> x = ????? ??, ??? ??????? ????
    spy(..)>> targetSqlType = 12  (ie CF_SQL_VARCHAR)

    // Profiler (Setting ENABLED)
    exec sp_prepexec @p1 output,N'@P1 nvarchar(4000)',N'SELECT  ID
            FROM    Test
            WHERE   PlainVarcharColumn = @P1 ',N'Стоял он, дум великих полн'

    // Profiler (Setting DIS-abled)
    exec sp_prepexec @p1 output,N'@P1 varchar(8000)',N'SELECT  ID
            FROM    Test
            WHERE   PlainVarcharColumn = @P1 ','????? ??, ??? ??????? ????'

Fowlkes

"When the setting is enabled, it is handled as varchar. When it is disabled, it is treated as nvarchar." I presume you meant the opposite of that? – Nippy

Yep. Thanks for pointing it out. I was just fixing it when I saw your comment. – Fowlkes

@Leigh, how do you enable datasource logging on the driver? – Vitalism

Answer 2

Anyone who runs into this, try adding sendStringParametersAsUnicode=false to the db connection string in cf administrator.

Uno