What are the details for using CF_SQL_NVARCHAR in ColdFusion 10?
coldfusion, coldfusion-10
Solution
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 ','????? ??, ??? ??????? ????'
Problem
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?