How do I work with Unicode data when using PHP's ODBC extensions?

PHP extensions enable PHP to communicate with third-party libraries. Unified ODBC and PDO ODBC are PHP extensions that allow PHP to communicate with an ODBC library. These PHP extensions translate between PHP language-level function calls and ODBC API calls. Unified ODBC and PDO ODBC can be built against either an ODBC Driver Manager (which loads a database-specific ODBC driver based on information passed to it in a connection string) or an ODBC driver.

To the ODBC Driver Manager and ODBC driver, Unified ODBC and PDO ODBC are ODBC applications.

Unicode ODBC applications use Unicode data types and call Unicode versions of the ODBC API. The encoding form that ODBC expects for data used with Unicode API functions is UCS-2. Unified ODBC and PDO ODBC are not Unicode ODBC applications. They call the ANSI ODBC APIs (SQLxxxA APIs) rather than the Unicode ODBC API (SQLxxxW APIs).

You can still use non-Ascii data with Unified ODBC and PDO ODBC. However, you may have to convert the data to the encoding the target database expects from the one used on your PHP client platform.

Your ODBC driver may provide a mechanism for converting between character encodings. Alternatively, you can use the PHP function iconv. For example:

$value = iconv("UTF-8", "Windows-1252", $value);

In the following examples, Unified ODBC and PDO ODBC were built as shared objects against the unixODBC Driver Manager included with Easysoft's SQL Server ODBC driver. (The PHP configure line was this for Unified ODBC ./configure --with-unixODBC=shared,/usr/local/easysoft/unixODBC and this for PDO ODBC ./configure --with-pdo-odbc=shared,unixODBC,/usr/local/easysoft/unixODBC.) The target database was SQL Server. The character encoding on the client machine was UTF-8:

$ echo $LANG
en_GB.UTF-8

Example: Retrieving Cyrillic data from SQL Server with Unified ODBC

<?PHP

// The SQL Server collation for this table is Cyrillic_General_CI_AS. The associated code page is 1251. Use the
// SQL Server ODBC driver to convert the data from the SQL Server encoding to the client machine encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;Client_CSet=UTF-8;" . 
                    "Server_CSet=Windows-1251", "sa", "easysoft");
$rs = odbc_exec($con, "SELECT charcol FROM myCyrillicTable");

// Fetch and display the result set.
if (!$rs){
    exit("Error in SQL");
}

while (odbc_fetch_row($rs)){
    echo odbc_result($rs, "charcol");
}

odbc_close($con);

?>

Example: Retrieving Unicode data from SQL Server with Unified ODBC

<?PHP
// Use the SQL Server ODBC driver to convert the data from the SQL Server encoding to the client machine encoding. No
// need to specify the SQL Server encoding on the connection string — use the driver's default setting for
// the Unicode column encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;Client_CSet=UTF-8",
                    "sa", "easysoft");
$rs = odbc_exec($con, "SELECT ncharcol FROM myUnicodeTable");

// Fetch and display the result set.
if (!$rs){
    exit("Error in SQL");
}

while (odbc_fetch_row($rs)){
    echo odbc_result($rs, "ncharcol");
}

odbc_close($con);

?>

Example: Retrieving Cyrillic data from SQL Server with PDO ODBC

<?PHP

try {

    // The SQL Server collation for this table is Cyrillic_General_CI_AS. The associated code page is 1251. Use the
    // SQL Server ODBC driver to convert the data from the SQL Server encoding to the client machine encoding.
    $pdo = new PDO ("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;UID=sa;" . 
                    "PWD=password;Client_CSet=UTF-8;Server_CSet=WINDOWS-1251");

    $stmt = $pdo->prepare("SELECT charcol FROM myCyrillicTable");
    $stmt->execute();
    while ($row = $stmt->fetch()) {
      print_r($row);
    }
} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}
?>

Example: Retrieving Unicode data from SQL Server with PDO ODBC

<?PHP

try {

    // Use the SQL Server ODBC driver to convert the data from the SQL Server encoding to the client machine encoding.
    // No need to specify the SQL Server encoding on the connection string — use the driver's default setting for
    // the Unicode column encoding.
    $pdo = new PDO ("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;UID=sa;" . 
                    "PWD=password;Client_CSet=UTF-8");

    $stmt = $pdo->prepare("SELECT ncharcol from myUnicodeTable");
    $stmt->execute();
    while ($row = $stmt->fetch()) {
      print_r($row);
    }
} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}
?>

Example: Inserting Cyrillic data into SQL Server with Unified ODBC

Insert

<?PHP

// Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;Client_CSet=UTF-8;" . 
                    "Server_CSet=Windows-1251", "sa", "easysoft");
$success = odbc_exec($con, "INSERT INTO myCyrillicTable(charcol) VALUES('привет')");
odbc_close($con);

?>

Parameterised Insert

<?PHP

// Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;Client_CSet=UTF-8;" . 
                    "Server_CSet=Windows-1251", "sa", "easysoft");

$value = "привет";

$stmt = odbc_prepare($con, "INSERT INTO myCyrillicTable(charcol) VALUES(?)");

$success = odbc_execute($stmt, array($value));

odbc_close($con);

?>

Example: Inserting Unicode data into SQL Server with Unified ODBC

Insert

<?PHP

// Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;Client_CSet=UTF-8",
                    "sa", "easysoft");
$success = odbc_exec($con, "INSERT INTO myUnicodeTable(ncharcol) VALUES(N'Űńĩćōđě')");
odbc_close($con);

?>

Parameterised Insert

<?PHP

// Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
$con = odbc_connect("DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;Client_CSet=UTF-8",
                    "sa", "easysoft");

$value = "Űńĩćōđě";

$stmt = odbc_prepare($con, "INSERT INTO myUnicodeTable(ncharcol) VALUES(?)");

$success = odbc_execute($stmt, array($value));

odbc_close($con);

?>

Example: Inserting Cyrillic data into SQL Server with PDO ODBC

Insert

<?PHP
try {

  // Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
  $pdo = new PDO("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;UID=sa;" . 
                 "PWD=password;Client_CSet=UTF-8;Server_CSet=Windows-1251");

  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  $stmt = $pdo->prepare("INSERT INTO myCyrillicTable(charcol) VALUES('привет')");

  $stmt->execute();
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
?>

Parameterised Insert

<?PHP
try {

  $pdo = new PDO("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;UID=sa;" . 
                 "PWD=password");

  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  $value = "привет";


  // PDO ODBC always uses SQLPutData to send parameter data. The SQL Server ODBC driver
  // cannot use its iconv mechanism to convert data that is sent in parts, which is the
  // typical usage case for SQLPutData. Use the PHPconversion mechanism instead.
  $value = iconv("UTF-8", "Windows-1251", $value);

  $stmt = $pdo->prepare("INSERT INTO myCyrillicTable(charcol) VALUES(?)");
  $stmt->bindParam(1, $value, PDO::PARAM_STR);

  $stmt->execute();
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
?>

Example: Inserting Unicode data into SQL Server with PDO ODBC

Insert

<?PHP
try {

  // Use the SQL Server ODBC driver to convert the data from the client machine encoding to the SQL Server encoding.
  $pdo = new PDO("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;UID=sa;" . 
                 "PWD=password;Client_CSet=UTF-8");

  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  $stmt = $pdo->prepare("INSERT INTO myUnicodeTable(ncharcol) VALUES(N'Űńĩćōđě')");

  $stmt->execute();
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
?>

Parameterised Insert

<?PHP
try {

  $pdo = new PDO("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyUnicodeDB;UID=sa;" . 
                 "PWD=password;Client_CSet=Windows-1257");

  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  $value = "Ūńīćōdē";

  // For data that is being inserted into a Unicode SQL Server column:
  // 1) Use PHP to convert the character data from the client encoding to the 
  //    relevant Windows code page.
  // 2) Use the SQL Server ODBC driver to convert the data from the Windows
  //    encoding.
  $value = iconv("UTF-8", "Windows-1257", $value);

  $stmt = $pdo->prepare("INSERT INTO myUnicodeTable(ncharcol) VALUES(?)");
  $stmt->bindParam(1, $value, PDO::PARAM_STR);

  $stmt->execute();
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
?>

Example: Inserting Cyrillic data into SQL Server from an HTML form

Form

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                         "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>My Form</title>
</head>
<body>

<!-- Use the Unicode encoding for form submission -->
<form action="insert.php" method="post" accept-charset="utf-8">
Text: <input type="text" name="form_text">
<input type="submit">
</form>

</body>
</html>

Form script

<?PHP
try {
  $pdo = new PDO("odbc:DRIVER={Easysoft ODBC-SQL Server};Server=192.0.2.1:1433;Database=MyCyrillicDB;UID=sa;" .
                 "PWD=password");

  $value = $_POST["form_text"];
  $value = iconv("UTF-8", "WINDOWS-1251", $value);

  $stmt = $pdo->prepare("INSERT INTO myCyrillicTable(charcol) VALUES(?)");
  $stmt->bindParam(1, $value, PDO::PARAM_STR);
  $stmt->execute();
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
?>