問題描述

這里我使用的是 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);

...
?> 

這篇關于PHP sqlsrv 驅動程序和 PDO 驅動程序之間的數據類型差異的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！