How to: Convert to and from UTF-8 Data

  • Article

This topic describes how to manually send and retrieve UTF-8 encoded data with the SQL Server Driver for PHP. The procedures in this topic use the PHP iconv function. For more information, see the iconv documentation.

In version 1.0 of the SQL Server Driver for PHP, this was the only way to send or retrieve UTF-8 data. However, in version 1.1 your application no longer needs convert to and from UTF-8 data. For more information, see How to: Send and Retrieve UTF-8 Data Using Built-In UTF-8 Support.

Sending UTF-8 Encoded Data to the Server

This procedure describes how to send UTF-8 encoded data to the server.

To send UTF-8 encoded data to the server

  1. Make sure that the destination column is of type nchar or nvarchar.

  2. Convert the data to UTF-16LE encoding by using the PHP iconv function. For example, if $data is the variable that contains UTF-8 encoded data, the following code will convert it to UTF-16LE encoding:

    $data = iconv("utf-8", "utf-16le", $data);

  3. Specify the PHP type as SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY) in the parameters array. For example, if $data is the parameter being sent, the following code defines the PHP type in the parameter array:

    $params = array( 
                 array($data, 
                       null, 
                       SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY)
                       )
                );

Retrieving Data with UTF-8 Encoding

This procedure describes how to retrieve data with UTF-8 encoding. This procedure assumes that the data being retrieved has been stored on the server with UTF-16LE encoding (see the procedure above).

To retrieve data with UTF-8 encoding

  1. Use the sqlsrv_get_field function to retrieve the data. For more information about retrieving data with the sqlsrv_get_field function, see How to: Retrieve a Single Field.

  2. Specify the PHP type as SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY). For example, the following code specifies the PHP type:

    $data = sqlsrv_get_field($stmt, 0, 
                  SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY));

    For more information about specifying the PHP type, see How to: Specify PHP Data Types.

  3. Use the PHP iconv function to convert the data to UTF-8 encoding. For example, the following code converts $data to from UTF-16LE encoding to UTF-8 encoding:

    $data = iconv("utf-16le", "utf-8", $data);

Example

The following example demonstrates how to send and retrieve UTF-8 encoded data. The example updates the Comments column of the Production.ProductReview table for a specified review ID. The example also retrieves the newly updated data and displays it. Note that the Comments column is of type nvarcahr(3850). Also note that before data is sent to the server it is converted to UTF-8 encoding using the PHP utf8_encode function. (utf8_encode. is used for demonstration purposes only. In an application scenario you would begin with UTF-8 encoded data.)

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the browser when the example is run from the browser.

<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$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 for demonstration purposes only. In an application
scenario you would begin with UTF-8 encoded data. Before sending the
UTF-8 data to the server, it is converted to UTF-16LE with the PHP iconv
function. */
$reviewID = 4;
$comments = utf8_encode("[Insert comment here.]");
$comments = iconv("utf-8", "utf-16le", $comments);
$params1 = array(
                  array($comments,
                        null,
                        SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY)),
                  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. Note the PHPTYPE specification and the
use of the PHP iconv function to convert the data from UTF-16LE encoding
to UTF-8 encoding. */
if( sqlsrv_fetch($stmt2) )
{
    echo "Comments: ";
    $data = sqlsrv_get_field($stmt2, 
                             0, 
                             SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY));
    $data = iconv("utf-16le", "utf-8", $data);
    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);
?>

For information about storing Unicode data, see Working with Unicode Data.

See Also

Tasks

Example Application

Concepts

SQLSRV Constants

Other Resources

Retrieving Data
Updating Data (SQL Server Driver for PHP)
API Reference (SQL Server Driver for PHP)