php pdo unixOBDC到sqlserver 2008:字符串数据，执行准备好的stmt时长度不匹配 [英] Php pdo unixOBDC to sqlserver 2008: String data, length mismatch when execute prepared stmt

问题描述

我具有以下设置:Apache/php 5.3/带有odbc的pdo，并在服务器上安装了适用于Linux的Microsoft SQL Server ODBC驱动程序1.0.

I have the following setup: Apache / php 5.3 / pdo with odbc with installed Microsoft SQL Server ODBC Driver 1.0 for Linux on server.

我的脚本在尝试执行一条语句时出现以下堆栈跟踪错误:

My script rises an error with the following stacktrace when trying to execute a statement:

(UTC) 2013-12-16 12:07:40: Thrown exception log ->
'Error message: SQLSTATE[22026]: String data, length mismatch: 0 [Microsoft][SQL Server Native Client 11.0]String data, length mismatch (SQLExecute[0] at /tmp/pear/temp/PDO_ODBC/odbc_stmt.c:133)
 Arisen in Core_Db->select(array (
      0 =>
         'SELECT  *
          FROM    TMS.dbo.TEST_user
          WHERE   email = ? AND status_id = ?',
      1 => 
         array (
             0 => '[e-mail]',
             1 => 2
         ),
))

为了进行测试，我在带有pdo sqlsrv的Windows上使用了php 5.3，那里没有任何问题.

For testing I use php 5.3 on windows with pdo sqlsrv and nothing went wrong there.

连接代码为

        // for unixODBC (production)
        if (DB_DRIVER == 'odbc') {
            $this->_link = new PDO(
                "odbc:DRIVER={SQL Server Native Client 11.0};SERVER=" . DB_HOST . ";"
                . "PORT=" . DB_PORT . ";DATABASE=" . DB_NAME . ";PROTOCOL=TCPIP;UID=" . DB_LOGIN . ";"
                . "PWD=" . DB_PASSWD . ";"
            );
        // for sqlsrv (development)
        } else {
            $this->_link = new PDO(
                "sqlsrv:Server=" . DB_HOST . "," . DB_PORT . ";Database=" . DB_NAME,
                DB_LOGIN,
                DB_PASSWD
            );
        }
        $this->_link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

关于此问题，有一些建议: ODBC和SQL Server 2008:无法使用准备好的语句?.但我无法检查.当我尝试在生产环境中添加属性时，脚本会引发以下错误:

There is some recommendation regarding this issue: ODBC and SQL Server 2008: Can't use prepared statements?. But I can't check it. When I try to add an attribute in production, the script rises the following error:

(UTC) 2013-12-16 13:19:44: SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes

有人知道如何解决此问题吗?

Does anyone know how to resolve this problem?

更新了2013-12-18

// unixODBC connection
$this->_link = new PDO(
    "odbc:DRIVER={SQL Server Native Client 11.0};SERVER=xxx.xxx.xxx.xxx;"
    . "PORT=1433;DATABASE=TMS;PROTOCOL=TCPIP;",
    DB_LOGIN,
    DB_PASSWD,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // this attr is recognized by ODBC
        PDO::ATTR_EMULATE_PREPARES => false // but if this is added ODBC throws PDOException with message "driver does not support setting attributes"
    )
 );

在使用prepared语句时，字符串从ODBC到客户端的传输会导致字符串损坏.所以我只猜这是在PDO :: ATTR_EMULATE_PREPARES中.

String's transfer from ODBC to Client leads to string's corruption while prepared statement is used. So I only guess the matter is in PDO::ATTR_EMULATE_PREPARES.

推荐答案

错误

(UTC)2013-12-16 13:19:44:SQLSTATE [IM001]:驱动程序不支持 此功能:驱动程序不支持设置属性

(UTC) 2013-12-16 13:19:44: SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes

告诉您这一行:

$this->_link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

无效.相反，请执行以下操作:

is invalid. Instead, do this:

$driver_options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_EMULATE_PREPARES => FALSE);

if (DB_DRIVER == 'odbc'){ // for unixODBC (production)
    $dsn = 'odbc:DRIVER={SQL Server Native Client 11.0};SERVER='. DB_HOST .';PORT='. DB_PORT .';DATABASE='. DB_NAME .';PROTOCOL=TCPIP;';
    $this->_link = new PDO($dsn, DB_LOGIN, DB_PASSWD, $driver_options);
}else{ // for sqlsrv (development)
    $this->_link = new PDO(
        "sqlsrv:Server=" . DB_HOST . "," . DB_PORT . ";Database=" . DB_NAME,
        DB_LOGIN,
        DB_PASSWD
    );
}

这篇关于php pdo unixOBDC到sqlserver 2008:字符串数据，执行准备好的stmt时长度不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！