PHP ODBC PDO driver for Microsoft SQL Server returning empty array or 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?

Westwick · Accepted Answer · 2013-08-31 19:37:26Z

1

My connection string looks different when using PDO with MSSQL. Not sure if this will help but this is how I do it:

$c = new PDO("sqlsrv:Server=x.x.x.x;Database=db_name", "username", "password");

This might help with debugging too:

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

answered Aug 31, 2013 at 19:37

Westwick

2,5173 gold badges32 silver badges51 bronze badges

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

2 Comments

Marcio Mazzucato Over a year ago

Are you using Windows or Linux? I am using an ODBC driver on Linux to connect to MS SQL Server

Marcio Mazzucato Over a year ago

Thanks for your help, i found the solution and posted as answer!

Marcio Mazzucato · Accepted Answer · 2013-09-02 02:59:04Z

I found a comment in PHP docs:

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

That says:

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!

Collectives™ on Stack Overflow

PHP ODBC PDO driver for Microsoft SQL Server returning empty array or false

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related