Re: Unicode data w/ PDO DBLIB, MSSQL

Mike Matz wrote:

Is there currently any way to retrieve Unicode data from SQL Server? I'm working with SQL Server Express 2005, on a database that's been restored from an old SQL Server 2000 database. I'm writing a PHP script that imports data from the MSSQL database, processes it, and adds it to a MySQL database with a different schema. So far I've been unable to do this reliably, as there is a lot of multilingual, Unicode data in the MSSQL database. I'm trying to use the PDO method of database access, but could fall back on another method if this is impossible. I'm able to connect to my database using either ODBC or DBLIB MSSQL drivers. When using DBLIB, I get an error: $db = new PDO('mssql:host=\\\\.\pipe\sql\query;dbname=my_db', $user, $pass); $sth = $db->execute("SELECT * FROM Products"); $sth->execute(); print_r($sth->fetchAll()); gives me this exception: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 10007 Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. [10007] (severity 5) [(null)]' When using ODBC, I get very strange results. There are no errors or exceptions, but in every row, all columns following the first text/unicode column will be empty. For example, if I SELECT Product_ID, Short_Description, Product_Season (Short_Description being TEXT, possibly with unicode), Product_Season will be empty. However, if I SELECT Product_ID, Product_Season, Short_Description -- I'll get the Product_Season. Short_Description is empty in both cases (even if it's not empty when viewing with SQL Query Analyzer). Has anyone seen this behavior or have any ideas as to what I should try? Thanks, Mike

Mike, The ODBC issue is probably due to C thinking the 0's in the unicode mean 'end of string'. My suggestion is to pass that data to SQLServer as binary and convert it to nchar or nvarchar on the fly in the SQL engine its self using a convert statement. I come across trouble like this all the time :-( The trick is generally to avoid using strings to handle mixed language data, pass it around as binary all the time and life gets a lot easier. Also - I would highly recommend using ADODB to talk to SQL server not ODBC or the php specific stuff. For example, here is how to connect to a server $obj=new COM('ADODB.connection'); if(!$obj->pinned()) { $obj->open("Provider=SQLOLEDB.1;Server=$server;Database=$database;UID=$username;PWD=$password;");

            $obj->pin();

} and to get a result set

    /** This function returns a connected ADODB.RecordSet given a
      * connection and some SQL.
      */
    function ExecuteQuery($sql,&$connection)
    {
        $recordsAffected=0;
        return $connection->Execute($sql, $recordsAffected, 8);
    }

finally, to turn a PHP string to hex example:

        $datum='0x'.bin2hex($datum);
        $sql=
        "
        IF EXISTS(SELECT RowId FROM T_Data WHERE RowId=$id)
        BEGIN
            UPDATE T_Data SET Datum=$datum WHERE RowId=$id
        END
        ELSE
        BEGIN
            INSERT INTO T_Data (RowId,Datum) VALUES ($id,$datum)
        END
       ";
        ADODB::Execute($sql,$this->connection);

Please note that PHP strings can hold unicode but not all php string functions work with unicode data because some assume strings to be 0 terminated. Good luck AJ -- www.deployview.com www.nerds-central.com www.funkifunctions.com www.project-network.com