PHP PDO with SqlServer Stored Procedure doesn'

Question

I've been given an stored procedure that is called like this:

SP_REPORT_HOME 'param1','param2',1

It executes a bunch of code i'm not aware of, although it return a bunch of data I need to use. It contains some fields with null values, while others are fully filled (note that the entire row is not null, only some fields) Well I'm using PDO and PHP on an Ubuntu 10.10 machine to get that information, my getReport method is:

public function getReport($empresa1, $empresa2, $num) {
    $ds = $this->connection->prepare('SP_REPORT_HOME ?,?,?');
    $ds->bindParam(1, $empresa1, PDO::PARAM_STR, 4);
    $ds->bindParam(2, $empresa2, PDO::PARAM_STR, 4);
    $ds->bindParam(3, $num, PDO::PARAM_INT, 4);
    $ds->execute(); 
    return $ds->fetchAll();
}

The $this->connection is just a PDO instance created like this:

$this->connection = new PDO(
        "dblib:host=IP:PORT;dbname=DBNAME", 
        "LOGIN", 
        "PASS"
    );

The method returns the array containing the data without the rows that contain null fields, anyone know why it doesn't show these rows? I really need them. I'm using PHP5.3 and SQLServer 2008

Answer 1

Check out http://php.net/manual/en/pdo.setattribute.php for the ATTR_ORACLE_NULLS setting. I'm guessing tweaking this setting will garner the output you desire.

Answer 2

Well, ended up there was an error on the procedure, although it is really strange.

The procedure, in one of its steps create a temporary table and inserts the data I want. In the other systems we use here (built in java) the procedure works just fine, no errors at all and all the data is there. The same happens for SQL Server Management Studio, no query errors.

Although in php the insert fails because it creates the table with not null fields (although it is not said to do so). I found this trying the connection and query using the mssql way. In the $connection->query() call it showed an insert error, saying I was trying to inset null in a not null field. Didn't stop the script though. Resulting in the result without the rows with null fields. The pdo version probably had the same problem but was suppressing the error message.

Well, we changed the procedure so the table creation indicates null fields, but still, we have no clue on why it worked in all the other systems and in php it used another default for these fields...