Php pdo unixOBDC to sqlserver 2008: String data, length mismatch when execute prepared stmt

Question

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 => '[email protected]',
             1 => 2
         ),
))

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

Connection code is

        // 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);

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?

UPDATED 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"
    )
 );

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.

Benny Hill · Accepted Answer · 2013-12-16 15:56:34Z

0

The error

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

is telling you that this line:

$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
    );
}

edited Dec 16, 2013 at 15:56

answered Dec 16, 2013 at 14:14

Benny Hill

6,2804 gold badges42 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Eugeny Over a year ago

No, the error occurs only when I apply $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Benny Hill Over a year ago

@Eugeny - I have updated my answer. You cannot use the setAttribute() function, you have to set that option when you create the PDO object.

Eugeny Over a year ago

Unfortunately, the same error. It does not allow to set any attribute except for PDO::ATTR_ERRMODE even when PDO is being created.

Benny Hill Over a year ago

@Eugeny - can you edit your question and show your updated code? Please include the part where you're trying to set PDO::ATTR_EMULATE_PREPARES.

Eugeny Over a year ago

Regarding this link and this link and other else - this is a driver's bug.

|

Collectives™ on Stack Overflow

Php pdo unixOBDC to sqlserver 2008: String data, length mismatch when execute prepared stmt

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related