用于Microsoft SQL Server的PHP ODBC PDO驱动程序返回空数组或false

Question

I have this code:

$dbh = new PDO('odbc:MSSQLServer', 'user', 'pass');

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

$sth = $dbh->prepare($sql);

$sth->execute();

$result = $sth->fetch(PDO::FETCH_ASSOC);        

var_dump($result);

If:

$sql = "select seri_cdser from tsica_alun_matr";

The output is:

array(1) {
    ["seri_cdser"]=>
    string(4) "3EMM"
}

But if:

$sql = "select * from tsica_alun_matr";

The output is:

SQLSTATE[22003]: Numeric value out of range: 0 [Microsoft][SQL Server Native Client 11.0]Numeric value out of range (SQLFetchScroll[0] at /usr/local/src/PDO_ODBC/odbc_stmt.c:372)

Sometimes an empty array is returned too, apparently the driver is instable.

The table tsica_alun_matr has 13 columns.

Someone knows if it is a bug with PDO driver for MS SQL Server?

Answer 1

I found a comment in PHP docs:

http://php.net/manual/pt_BR/book.pdo.php

That says:

1. The second error I had was:

   Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22003]: Numeric value out of range: 0 [Microsoft][SQL Native Client]Numeric value out of range (SQLFetchScroll[0] at ext\pdo_odbc\odbc_stmt.c:372)' in (YOUR_TRACE_HERE) <<< Another meaningless error "Numeric value out of range"...

-> I was actually returning a date datatype (datetime or smalldatetime) "as is", that is, without converting it to varchar before including it in the result set... I don't know if PDO is responsible for converting it to a PHP datatype, but it doesn't. Convert it before it reaches PHP.

So, as exist datetime and smalldate fields i have changed my PDO query as below, converting datetime and smalldatetime to varchar. This page helps to choose the best converstion format.

$sth = $dbh->prepare("
    select
        alun_cdal,
        seri_cdser,
        CONVERT(VARCHAR(23), matp_dat_Mat, 121) as matp_dat_Mat,
        alun_nmal,
        alun_sexo,
        CONVERT(VARCHAR(23), alun_dnsc, 121) as alun_dnsc
    from 
        tsica_alun_matr");

Now my code is working fine!