PHP sqlsrv 驱动程序和 PDO 驱动程序之间的数据类型差异

2021-12-26 00:00:00 binary php pdo sql-server sqlsrv

这里我使用的是 sqlsrv:

$conn = sqlsrv_connect("192.168.1.102,1433", array("Database"=>"RF_User", "UID"=>"rfo-gcp", "PWD" => ""));

$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";

$stmt = sqlsrv_query($conn, $tsql, array("test"));

$result = sqlsrv_fetch_array($stmt);

var_dump($result);

结果:array(2) { [0]=>object(DateTime)#1 (3) { ["date"]=>string(26) "2020-04-19 20:40:00.000000" ["timezone_type"]=>int(3) [时区"]=>string(3) "UTC" } ["birthdate"]=>object(DateTime)#1 (3) { ["date"]=>string(26) "2020-04-19 20:40:00.000000" ["timezone_type"]=>int(3) [时区"]=>string(3) "UTC" } }

这里我使用的是PDO:

$conn = new PDO("sqlsrv:Server=192.168.1.102,1433; Database=RF_User;", "rfo-gcp", "");

$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = cast(? as varchar(13))";     

$stmt = $conn->prepare($tsql);

$stmt->execute(array("test"));

$result = $stmt->fetch(PDO::FETCH_ASSOC);

var_dump($result);

结果:array(1) { ["birthdate"]=>string(19) "2020-04-19 20:40:00" }

如果您注意到，我必须在 PDO 代码上使用 cast(? as varchar(13)).没有它不会返回任何行.在 sqlsrv 上，我不必使用 CAST() 函数.为什么是这样?另外，数据库上的id列是一个BINARY(13)，那么为什么我要强制转换id 到 varchar 而不是 binary(使用二进制转换它也找不到行)?

If you notice, I had to use cast(? as varchar(13)) on the PDO code. Without it would not return any row. On the sqlsrv I didn't have to use the CAST() function. Why is this? Also, the id column on the database is a BINARY(13), so why do I have to cast the id to varchar and not to binary (with binary cast it also doesn't find the row)?

推荐答案

为什么日期和时间值的返回方式不同?

其实这只是一个设定.

当您使用 PDO_SQLSRV(正如 在文档中提到)，日期和时间类型(smalldatetime、datetime、date、time、datetime2 和 datetimeoffset)默认以字符串形式返回.PDO::ATTR_STRINGIFY_FETCHES 和 PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE 属性都没有任何影响.为了将日期和时间类型检索为 PHP DateTime 对象，请将连接或语句属性 PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE 设置为 true(默认为 false).

When you use PDO_SQLSRV (as is mentioned in the documentation), date and time types (smalldatetime, datetime, date, time, datetime2, and datetimeoffset) are by default returned as strings. Neither the PDO::ATTR_STRINGIFY_FETCHES nor the PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE attribute has any effect. In order to retrieve date and time types as PHP DateTime objects, set the connection or statement attribute PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE to true (it is false by default).

当您使用 SQLSRV 驱动程序时(同样来自 文档)、smalldatetime、datetime、date、time、datetime2 和 datetimeoffset 类型将作为 PHP DateTime 对象返回.可以通过在连接字符串或语句级别设置 'ReturnDatesAsStrings' 选项来更改此行为.

When you use SQLSRV driver (again from the documentation), smalldatetime, datetime, date, time, datetime2, and datetimeoffset types will be returned as PHP DateTime objects. This behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level.

$conn = sqlsrv_connect(
   "192.168.1.102,1433", 
    array(
       "ReturnDatesAsStrings"=>true,
       "Database"=>"RF_User", 
       "UID"=>"rfo-gcp", 
       "PWD" => ""
   )
);

请注意，某些功能取决于 SQL Server 的 PHP 驱动程序版本.

Note that some of the features depend on the version of PHP Driver for SQL Server.

如何转换参数值?

在语句中使用 CAST() 和 CONVERT() 函数并将参数值与字符串值绑定应该可以工作.当然，您可以在绑定参数时指定参数数据类型.

Using CAST() and CONVERT() functions in the statement and binding parameter value with string value should work. Of course, you can specify the parameter data type, when you bind a parameter.

对于 PDO_SQLSRV，您应该为 PDOStatement::bindParam().

对于 SQLSRV，您可以使用扩展的 $params syntax 指定 SQL Server数据类型，当您调用 sqlsrv_query()sqlsrv_execute() 时.

For SQLSRV you may use the extended $params syntax to specify the SQL Server data type, when you make a call to sqlsrv_query()sqlsrv_execute().

我可以重现这个问题(PHP 7.1.12、PHP Driver for SQL Server 4.3.0+9904、SQL Server 2012)，解决方案是使用:

I'm able to reproduce this issue (PHP 7.1.12, PHP Driver for SQL Server 4.3.0+9904, SQL Server 2012) and the solution is to use:

$params = array($id, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_BINARY);          // SQLSRV
$stmt->bindParam(1, $id, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY); // PDO_SQLSRV

表格:

CREATE TABLE tbl_rfaccount (id binary(13), birthdate datetime)
INSERT INTO tbl_rfaccount (id, birthdate) VALUES (CONVERT(binary(13), 'Test'), GETDATE())

PHP:

<?php
...

// 
$id = "Test";

// SQLSRV
$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";
$params = array($id, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_BINARY);
$stmt = sqlsrv_query($conn, $tsql, $params);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
$result = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
var_dump($result);

// PDO_SQLSRV
$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";     
$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $id, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($result);

...
?>

相关文章