在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文档尚未更新，以包含它们的存在。 / p>

cfqueryparam的ColdFusion 9文档提到 CF_SQL_VARCHAR 映射到MSSQL中的 varchar 。这是真的，除非ColdFusion管理员数据源设置具有字符串格式设置已启用。在这种情况下， CF_SQL_VARCHAR 映射到 nvarchar 。这种记录不良的功能是可以导致性能问题的黑客在ColdFusion中。

因此，他们引入了 CF_SQL_NVARCHAR 了解它是如何工作的。它只是 CF_SQL_VARCHAR 的别名，使其毫无意义？它总是发送字符串 nvarchar ？如果是， CF_SQL_VARCHAR 总是发送 varchar ？

我希望为了向后兼容性，它被实现为：

如果字符串格式启用 CF_SQL_VARCHAR 和 CF_SQL_NVARCHAR 都映射到 nvarchar 。

如果字符串格式被禁用，则 CF_SQL_VARCHAR 映射到 varchar 和 CF_SQL_NVARCHAR 映射到 nvarchar 。

这意味着任何CF10之前的网站都可以迁移到CF10并工作，在CF10之前具有相同的性能考虑。

新网站或网站重写所有查询以匹配 CF_SQL_VARCHAR 和 CF_SQL_NVARCHAR 与数据库设计将不会获得不可避免的性能损失pre- CF10。

任何人都可以确认是否是这种情况;

解决方案

当你正在等待更多的官方，我会扔在我的$ 0.02 ...

我做了一些挖掘，并根据我的观察（与MS SQL数据源）我相信：

• CF_SQL_NVARCHAR 不仅仅是 CF_SQL_VARCHAR 的别名。它映射到较新的 NVARCHAR jdbc类型，可让您




• CF_SQL_NVARCHAR 值总是被视为 nvarchar



• CF_SQL_VARCHAR 的处理取决于String Format 设置，与以前的版本相同。

CF_SQL_NVARCHAR测试/结果：

如果您启用数据源记录，您可以看到驱动程序调用特殊的 CF_SQL_NVARCHAR ，就可以使用<20java.lang.String％29> setNString 因此最终该值将作为 nvarchar 发送到数据库。 （您可以使用SQL Profiler确认）

  //查询
 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验证这一点。

底线，到目前为止，我所看到的一切都说明您对目标的实施是正确的。

  //查询
 SELECT ID 
 FROM Test 
 WHERE PlainVarcharColumn =< cfqueryparam value =＃form。值＃cfsqltype =cf_sql_varchar> 
 
 // Log 
 spy（..）>> PreparedStatement [8] .setObject（int parameterIndex，Object x，int targetSqlType）
 spy（..）> parameterIndex = 1 
 spy（..）>> x = ??，??? ?? ？ 
 spy（..）>> targetSqlType = 12（即CF_SQL_VARCHAR）
 
 // Profiler（设置ENABLED）
 exec sp_prepexec @ p1 output，N'@ P1 nvarchar（4000）'，N'SELECT ID 
 FROM Test 
 WHERE PlainVarcharColumn = @ P1'，N'Стоялон，думвеликихполн'
 
 // Profiler（设置DIS-abled）
 exec sp_prepexec @ p1 output，N '@ P1 varchar（8000）'，N'SELECT ID 
 FROM Test 
 WHERE PlainVarcharColumn = @ P1'，' ??，??? ?? '
  

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?

解决方案

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

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