字符串数据，SQLSRV PHP Driver 中的右截断(“甜甜圈洞") [英] String data, right truncation in SQLSRV PHP Driver ("donut hole")

问题描述

我正在使用 Microsoft 的 sqlsrv PHP 驱动程序(在 Windows 2003 服务器上)将大量文本写入 text 列.

I'm writing large-ish amounts of text to a text column using Microsoft's sqlsrv PHP driver (on a Windows 2003 server).

有一个奇怪的甜甜圈洞"错误，如果文本长度很小，则保存成功，如果文本足够大，也保存成功.在这中间，我们得到以下错误:

There's an odd "donut hole" error, where if the length of the text is fairly small, it is saved successfully, and if the text is sufficiently large, it is also save successfully. In between, we get the following error:

SQLSTATE: 22001
code: 0
message: [Microsoft][SQL Server Native Client 10.0]String data, right truncation 

下限(触发错误)似乎是大约 4011 个字符，上限是 8024.数据库中还有一些其他列可能会将其推到 2 的幂边界(4096 和 8192).

The lower limit (to trigger the error) appears to be around 4011 characters, and the upper limit 8024. There are a few other columns in the database that might push it over the power-of-two boundary (4096 and 8192).

这与正在运行的 sql 无关.作为测试用例，我们运行了以下内容:

This happens regardless of the sql being run. As a test case, we ran the following:

$connectionInfo = array("UID" => "REDACTED", "PWD" => "REDACTED", "Database"=>"REDACTED", "CharacterSet" => "UTF-8");

$conn = sqlsrv_connect("SQLSERVER", $connectionInfo);

$stmt = sqlsrv_prepare($conn, "SELECT LEN(?)", array(&$large_string));

if (!sqlsrv_execute($stmt))
    print_r(sqlsrv_errors());

sqlsrv_free_stmt($stmt);

sqlsrv_close($conn);

我们发现了另一个表现出相同行为的表(使用 varchar(max) 列).

We've found another table (using varchar(max) columns) that exhibits the same behavior.

当使用带有 varchar 列的 UTF-8 编码时会发生这种情况，但在使用标准编码时不会发生这种情况.

This occurs when using UTF-8 encoding with a varchar column, but not when using the standard encoding.

推荐答案

如果您将附加参数与参数一起输入，错误就会消失:

If you feed additional parameters in with the parameter, the bug disappears:

$stmt = sqlsrv_prepare($conn, "SELECT LEN(?)", array(array(&$large_string, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('UTF-8'));

这可能与将 UTF-8 值传递到 varchar(相对于 nvarchar)列的事实有关.

This may be related to the fact that it is passing a UTF-8 value into a varchar (versus nvarchar) column.

这篇关于字符串数据，SQLSRV PHP Driver 中的右截断(“甜甜圈洞")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！