pdo-odbc doesn't work whit bind values, nvarch

问题:

There is a column url(nvarchar(200), not null)

<?php
//
$pdo = new PDO('odbc:mssql', 'xxx', 'yyy');
$pdo->setAttribute(PDO::ATTR_PERSISTENT, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

// plain sql query: WORKS FINE!
$sth = $pdo->prepare("SELECT COUNT(*) FROM pagina WHERE url = '/webito'");
$sth->execute();

// using bindValue: ERROR!
$sth = $pdo->prepare("SELECT COUNT(*) FROM pagina WHERE url = :unique_value");
$sth->execute(array('unique_value' => '/webito'));

Returning error:

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 402 [FreeTDS][SQL Server]The data types nvarchar and text are incompatible in the equal to operator. (SQLExecute[402] at /builddir/build/BUILD/php-5.4.15/ext/pdo_odbc/odbc_stmt.c:254) in /root/php/test.php on line 13

Is this a bug?

Using: php 5.4.15, unixodbc 2.2.14, freetds 0.91, sql-server-2012, centos-x64 6.4

Update:

Seems to be a bug. I found this patch, but works only with ODBC Driver 11 for SQL Server (I tried with FreeTDS, no luck). I managed to install PHP from source with this patch applied and changed from FreeTDS to ODBC Driver 11 for SQL Server; now is working.

• php 5.4.15
• unixODBC 2.3.0
• ODBC Driver 11 for SQL Server
• sql-server-2012
• centos-x64 6.4

回答1:

Having done a fair bit of research into this it seems pdo_odbc is buggy on 64-bit architectures: it's built with 32-bit SQLLEN and SQLULEN sizes. Microsoft's driver used to be built this way, which is probably why PHP followed suit. MS have since started to follow the ODBC spec properly, but apparently PHP hasn't.

The patch referenced in the question fixes one such problem in the PHP source, but seemingly not all such problems. Using the MS driver and a patched PHP I was still unable to run prepared statements.

I actually found the same problem when using Easysoft's driver, and talking through the issues with them discovered that pdo_odbc is the culprit. They were able to provide me with a 64-bit driver built using 32-bit sizes, and that works fine.

Until 64-bit PHP is patched to use 64-bit SQLLEN and SQLULEN sizes it looks like the best Free solution is to use 32-bit PHP and ODBC drivers.

回答2:

same error here:

PDOStatement::execute(): SQLSTATE[22001]: String data, right truncated: 0 [Microsoft][SQL Server Native Client 11.0]Die Zeichenfolgedaten wurden rechts abgeschnitten (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:254)

Win7 64bit, PHP 5.4.12, SQL Server Express 2012, ODBC 11 for SQL Server