Sqlsrv and affected_rows

[eluser]ColonelChlorine[/eluser]
When using sqlsrv drivers that come with CI 2.0.3, I run an update query that updates 7 records in my database (I check this via running the query in SQL Server Management Studio).

Code:

$this->db->query("UPDATE SomeTable SET Name = '1234' WHERE ID IN (1,2,3,4,5,6,7)");
$this->db->affected_rows();

Not only does it not return, but it crashes the app with no visible error (blank white screen). I tracked this down to /system/database/drivers/sqlsrv/sqlsrv_driver.php, line 274 which states:

Code:

return @sqlsrv_rows_affected($this->conn_id);

The @ sign apparently will cause an error but then suppress it. So I took @ out and got the following error: sqlsrv_rows_affected(): supplied resource is not a valid ss_sqlsrv_stmt resource. Bummer. I figured out the answer however with a little help from a random blog comment. The real error that lies hidden behind this one is: "This function only works with statements that are not scrollable"

The problem is that sqlsrv_rows_affected can't give you an answer if the statement is scrollable, which CI does by default. So perform the following code fixes to your sqlsrv_driver.php file and you'll be getting rows affected like nobody's business!

Change your _execute function as follows (it simply says if you're doing an INSERT or UPDATE ... don't make the result scrollable).

Code:

function _execute($sql) {
    $sql = $this->_prep_query($sql);
    if(stripos($sql,'UPDATE') !== FALSE || stripos($sql,'INSERT') !== FALSE) {
        return sqlsrv_query($this->conn_id, $sql, null, array());
    }    
    return sqlsrv_query($this->conn_id, $sql, null, array('Scrollable' => SQLSRV_CURSOR_STATIC, 'SendStreamParamsAtExec' => true));
}

and change affected_rows() to:

Code:

function affected_rows() {
    return sqlsrv_rows_affected($this->result_id);
}

I'm going to submit a pull-request on github I think. First time doing that, but I think this qualifies as a bug fix.


** Kudos to Noon, a commenter on this site for helping me along.