sqlsrv PDO Exception

[MarcusFulbright]

I'm running php 5.5.20 in windows with the sqlsrv extensions enabled (thread safe). When connecting to the DB with an ExtendedPdo object, I get the following error:
PDOException: SQLSTATE[IMSSP]: The given attribute is only supported on the PDOStatement object.

I'm sure that this is a problem with the ExtendedPdo object because the following code using Vanilla PDO works just fine:

    $pdo = new \PDO(
         'sqlsrv:Server=my_server ; DataBase=My_DB',
         $userName,
         $password
     );

    $smt = $pdo->prepare('Select Top 10 * From My_table');
    $stmt->execute();
    $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// yields the appropriate associative array of results

When I try to accomplish the same thing with ExtendedPdo it doesn't work:

    $pdo = new ExtendedPdo(
        'sqlsrv:Server=My_Server ; DataBase=My_DB',
        $userName,
        $password
    );
    $stmt = 'Select Top 10 * From My_table';
    $result = $pdo->fetchAll($stmt);

//yields the error: 
//PDOException: SQLSTATE[IMSSP]: The given attribute is only supported on the PDOStatement object.

With the extended PDO this error seems to get triggered as soon as it tries to establish a connection to the DB. Calling connect(), prepare(), and fetch*() yields exactly the same behavior. Am I doing something wrong or is there a compatibility issue with ExtendedPdo and sqlsrv?

Update

After reading the doc block over the ExtendedPdo Constructor I realized that I could just pass in an already instantiated PDO object. I have tested this and it seems to work fine. Code for illustration:

   $pdo = new \PDO(
        'sqlsrv:Server=My_Server ; DataBase=My_DB',
        $userName,
        $password
    );
    $extended = new ExtendedPdo($pdo);
    $stmt = 'Select Top 10 * from My_table';
    $result = $extended->fetchAll($stmt);
// yields the appropriate array of results.  

While this does work and will make my life a little bit easier, it'd be nice to take advantage of ExtendedPdo's lazy connection feature which means there is still a bug somewhere.

[harikt]

@MarcusFulbright could you do a trace .

Go to https://github.com/auraphp/Aura.Sql/blob/2.1.0/src/ExtendedPdo.php#L321 . Add a try catch and see if the error is happening over perform method.

protected function fetchAllWithCallable(
    $fetch_type,
    $statement,
    array $values = array(),
    $callable = null
) {
    try {
        echo "Performing, internally calling prepare";
        $sth = $this->perform($statement, $values);
        echo "Performed";
        if ($fetch_type == self::FETCH_COLUMN) {
            $data = $sth->fetchAll($fetch_type, 0);
        } else {
            $data = $sth->fetchAll($fetch_type);
        }
    } catch (\Exception $e)
        echo " Exception from performing " . $e->getMessage();
    }
    var_dump($data);
    exit;
    return $this->applyCallableToFetchAll($callable, $data);
}

If the error occurred after displaying Performed it is not from prepare I believe. As you mentioned it can connect, then can prepare I wonder what may be the exact reason for if you trace how it works, it works the same way.

[harikt]

aah it seems you need to turn of self::ATTR_EMULATE_PREPARES . Try turning off .

<?php
$pdo = new ExtendedPdo(
    'sqlsrv:Server=My_Server ; DataBase=My_DB',
    $userName,
    $password,
    array(),
    array(        
        ExtendedPdo::ATTR_EMULATE_PREPARES => false,
    );
);

I am looking at http://stackoverflow.com/questions/11462893/getting-yii-to-play-nicely-with-sql-server

[MarcusFulbright]

@pmjones came into IRC yesterday and dropped some wisdom that got me going in the right direction. The problem has something to do with attributes that ExtendedPdo sets by default. Apparently my trouble came from ERRMODE, not EMULATE_PREPARES. Setting ERRMODE to warning or silent (its default value) gets everything to work. However, while set to warning, I got this message: PHP Warning: PDO::setAttribute(): SQLSTATE[IMSSP]: <<Unknown error>>: -39 The given attribute is only supported on the PDOStatement object in \vendor\aura\sql\src\ExtendedPdo.php on line 814

That line takes you to the setAttribute function. I made var_dump's for $attribute and $value, but I'm not seeing anything unexpected. Maybe I just don't know what to look for or maybe this is a quirk of the sqlsrv PDO extension itself?

Thanks for the help, I really appreciate it.

[harikt]

@MarcusFulbright good to know it worked for you. I don't know more on sqlsrv .

[MarcusFulbright]

After messing with ERRMODE on a native PDO object I can confirm that ERRMODE_EXCEPTION is just plain not supported by sqlsrv: 'PDOException' with message 'SQLSTATE[IMSSP]: This function is not implemented by this driver.'. However, setting a native PDO to warning does not produce the bizarre <<unknown error>> from my above comment. It'd be nice to figure out why ERRMODE_WARNING generates that warning with ExtendedPdo and not with the vanilla PDO.

[pmjones]

Good grief, that's just ... frustrating.

@MarcusFulbright your mission, should you choose to accept it, is to write a patch for ExtendedPdo that examines the incoming DSN string to see if it's 'sqlsrv' and then modify the attributes to use WARNING instead of EXCEPTION.

[pmjones]

(Additionally, you may wish to file a bug for that at php.net, after checking first to see if one already exists.)