在 ColdFusion 10 中使用 CF_SQL_NVARCHAR 的细节是什么? [英] What are the details for using CF_SQL_NVARCHAR in ColdFusion 10?

问题描述

有关更新数据库的 ColdFusion 10 文档有一个关于 ColdFusion 10 中与数据库相关的增强功能.该页面提到现在支持 CF_SQL_NVARCHAR 等，但没有关于它们的详细信息.此外，cfqueryparam 文档尚未更新包括他们的存在.

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.

ColdFusion 9 cfqueryparam 文档提到CF_SQL_VARCHAR 映射到 MSSQL 中的 varchar.除非 ColdFusion 管理员 数据源设置 具有String Format 设置启用.在这种情况下，CF_SQL_VARCHAR 映射到 nvarchar.这个记录不充分的功能是一个 hack，它可能导致性能问题 在 ColdFusion 中.

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.

所以很高兴他们引入了 CF_SQL_NVARCHAR，但最好了解它的工作原理.它只是 CF_SQL_VARCHAR 的别名，使它毫无意义?它总是将字符串作为 nvarchar 发送吗?如果是这样，CF_SQL_VARCHAR 是否总是发送 varchar?

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:

如果 String Format 启用 CF_SQL_VARCHAR 和 CF_SQL_NVARCHAR 都映射到 nvarchar.

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

如果 String Format 被禁用，则 CF_SQL_VARCHAR 映射到 varchar 并且 CF_SQL_NVARCHAR 映射到 nvarchar.

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

这意味着任何 CF10 之前的网站都可以迁移到 CF10 并正常工作，并且具有与 CF10 之前相同的性能注意事项.

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

新站点或重写所有查询以使 CF_SQL_VARCHAR 和 CF_SQL_NVARCHAR 与数据库设计相匹配的站点不会受到 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?

推荐答案

当你在等待更正式的东西时，我会投入我的 $0.02 ...

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

我做了一些挖掘，根据我的观察(使用 MS SQL 数据源)我相信:

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

• CF_SQL_NVARCHAR 不仅仅是 CF_SQL_VARCHAR 的别名.它映射到较新的 NVARCHAR jdbc 类型，它让您可以更精细地处理 unicode 值.

• 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 值始终被视为 nvarchar

CF_SQL_NVARCHAR 测试/结果:

如果启用数据源日志记录，您可以看到驱动程序调用特殊的 setNString 方法，只要使用 CF_SQL_NVARCHAR.所以最终该值作为 nvarchar 发送到数据库.(您可以使用 SQL Profiler 确认这一点)

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 测试/结果:

对于 CF_SQL_VARCHAR，它在技术上被标记为 varchar.但是，String Format 设置最终控制了数据库如何处理它.启用该设置时，将其作为 nvarchar 处理.当它被禁用时，它被视为 varchar.同样，您可以使用 SQL Profiler 验证这一点.

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 ','????? ??, ??? ??????? ????'

这篇关于在 ColdFusion 10 中使用 CF_SQL_NVARCHAR 的细节是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！