如何:使用內建 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 編碼的資料給伺服器:
- 請確定來源或目的地資料行的類型為 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 資料表的 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 資料的詳細資訊,請參閱<使用 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)