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.

[eluser]JoJo17[/eluser]
This thread may be a couple of years old but it's just helped me solve a problem I was having with Ion Auth running on MSSQL so thank you And that was with me running Codeigniter 2.1.4 so I'm guessing the bug never got fixed.

Looks like you're owed another beer as I've been going round in circles trying to solve my problem

[eluser]stefenw[/eluser]
thank you very much colonel

:lol: :cheese: