Easysoft ODBC-SQL Server Driver
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 /
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);
Examples
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. (I.e 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
// 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
// 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
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
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
// 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
// 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
// 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
// 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
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
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 PHP conversion 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
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
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">
<title>My Form</title>
</head>
<!-- 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
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();
- Products
- Easysoft ODBC-SQL Server Driver
- Languages
Knowledge Base Feedback
