如何使用内置的 UTF-8 支持发送和检索 UTF-8 数据
在 SQL Server Driver for PHP 的 1.1 版中添加了对 UTF-8 字符的支持。
在 SQL Server Driver for PHP 的 1.0 版中,您必须使用 iconv 函数手动转换为 UTF-8 和从 UTF-8 进行转换。有关详细信息,请参阅如何转换为 UTF-8 数据和从 UTF-8 数据转换。
将 UTF-8 编码数据发送到服务器或者检索 UTF-8 编码数据:
- 确保源或目标列的数据类型为 nchar 或 nvarchar。
- 在参数数组中将 PHP 类型指定为
SQLSRV_PHPTYPE_STRING('UTF-8')
。或者,将"CharacterSet" => "UTF-8"
指定为连接选项。
在您将某一字符集指定为连接选项的一部分时,驱动程序假定其他连接选项字符串使用相同的字符集。服务器名称和查询字符串也假定为使用相同的字符集。
请注意,您可以将 UTF-8 或 SQLSRV_ENC_CHAR 传递到 CharacterSet(但不能传递 SQLSRV_ENC_BINARY)。默认编码为 SQLSRV_ENC_CHAR。
示例
下面的示例演示了如何通过在进行连接时指定 UTF-8 字符集,发送和检索 UTF-8 编码数据。该示例对 Production.ProductReview 表中指定审核 ID 的 Comments 列进行了更新。此示例还检索了最近更新的数据,并将这些数据予以显示。请注意,Comments 列的类型为 nvarcahr(3850)。同时请注意,在相应数据发送到服务器之前,会使用 PHP utf8_encode 函数将这些数据转换为 UTF-8 编码格式。此示例仅针对演示目的设计。在实际应用场景中,数据一开始便已为 UTF-8 编码格式。
此示例假定本地计算机上已安装 SQL Server 和 AdventureWorks 数据库。当从浏览器运行此示例时,所有的输出都将写入该浏览器。
<?php
// Connect to the local server using Windows Authentication and
// specify the AdventureWorks database as the database in use.
//
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ( $conn === false ) {
echo "Could not connect.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Set up the Transact-SQL query.
//
$tsql1 = "UPDATE Production.ProductReview
SET Comments = ?
WHERE ProductReviewID = ?";
// Set the parameter values and put them in an array. Note that
// $comments is converted to UTF-8 encoding with the PHP function
// utf8_encode to simulate an application that uses UTF-8 encoded data.
//
$reviewID = 3;
$comments = utf8_encode("testing 1, 2, 3, 4. Testing.");
$params1 = array(
array( $comments, null ),
array( $reviewID, null )
);
// Execute the query.
//
$stmt1 = sqlsrv_query($conn, $tsql1, $params1);
if ( $stmt1 === false ) {
echo "Error in statement execution.<br>";
die( print_r( sqlsrv_errors(), true));
}
else {
echo "The update was successfully executed.<br>";
}
// Retrieve the newly updated data.
//
$tsql2 = "SELECT Comments
FROM Production.ProductReview
WHERE ProductReviewID = ?";
// Set up the parameter array.
//
$params2 = array($reviewID);
// Execute the query.
//
$stmt2 = sqlsrv_query($conn, $tsql2, $params2);
if ( $stmt2 === false ) {
echo "Error in statement execution.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Retrieve and display the data.
//
if ( sqlsrv_fetch($stmt2) ) {
echo "Comments: ";
$data = sqlsrv_get_field( $stmt2, 0 );
echo $data."<br>";
}
else {
echo "Error in fetching data.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Free statement and connection resources.
//
sqlsrv_free_stmt( $stmt1 );
sqlsrv_free_stmt( $stmt2 );
sqlsrv_close( $conn);
?>
有关存储 Unicode 数据的信息,请参阅 Working with Unicode Data(使用 Unicode 数据)。
下面的示例与第一个示例类似,但不是对连接指定 UTF-8 字符集,而是显示如何对列指定 UTF-8 字符集。
<?php
// Connect to the local server using Windows Authentication and
// specify the AdventureWorks database as the database in use.
//
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ( $conn === false ) {
echo "Could not connect.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Set up the Transact-SQL query.
//
$tsql1 = "UPDATE Production.ProductReview
SET Comments = ?
WHERE ProductReviewID = ?";
// Set the parameter values and put them in an array. Note that
// $comments is converted to UTF-8 encoding with the PHP function
// utf8_encode to simulate an application that uses UTF-8 encoded data.
//
$reviewID = 3;
$comments = utf8_encode("testing");
$params1 = array(
array($comments,
SQLSRV_PARAM_IN,
SQLSRV_PHPTYPE_STRING('UTF-8')
),
array($reviewID)
);
// Execute the query.
//
$stmt1 = sqlsrv_query($conn, $tsql1, $params1);
if ( $stmt1 === false ) {
echo "Error in statement execution.<br>";
die( print_r( sqlsrv_errors(), true));
}
else {
echo "The update was successfully executed.<br>";
}
// Retrieve the newly updated data.
//
$tsql2 = "SELECT Comments
FROM Production.ProductReview
WHERE ProductReviewID = ?";
// Set up the parameter array.
//
$params2 = array($reviewID);
// Execute the query.
//
$stmt2 = sqlsrv_query($conn, $tsql2, $params2);
if ( $stmt2 === false ) {
echo "Error in statement execution.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Retrieve and display the data.
//
if ( sqlsrv_fetch($stmt2) ) {
echo "Comments: ";
$data = sqlsrv_get_field($stmt2,
0,
SQLSRV_PHPTYPE_STRING('UTF-8')
);
echo $data."<br>";
}
else {
echo "Error in fetching data.<br>";
die( print_r( sqlsrv_errors(), true));
}
// Free statement and connection resources.
//
sqlsrv_free_stmt( $stmt1 );
sqlsrv_free_stmt( $stmt2 );
sqlsrv_close( $conn);
?>
另请参见
任务
概念
其他资源
检索数据
更新数据 (SQL Server Driver for PHP)
API 参考 (SQL Server Driver for PHP)