PHP MSSQL num_rows() not working with sqlsrv driver in Codeigniter

Question

I'm getting the following error on a simple query that's supposed to return a number of rows:

Type: Error

Message: Call to a member function num_rows() on boolean

Filename: /var/www/cfc-dev/docroot/project/system/database/DB_query_builder.php

Line Number: 1428

I'm using Codeigniter 3.1.9. I recently started using PHP7.2 and also sqlsrv driver to connect to a MSSQL database.

In some other post, someone mentioned that scrollable should be set to an option different than SQLSRV_CURSOR_FORWARD so I dump the value of $this->scrollable in sqlsrv_driver.php and found out that its value is buffered

/**
     * Execute the query
     *
     * @param   string  $sql    an SQL query
     * @return  resource
     */
    protected function _execute($sql)
    {
        echo $this->scrollable; die();
        return ($this->scrollable === FALSE OR $this->is_write_type($sql))
            ? sqlsrv_query($this->conn_id, $sql)
            : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => $this->scrollable));
    }

Not quite sure why it's failing. Any other queries that don't include num_rows() are fine so far. Thanks.

It would be helpful if you posted the query and the code which failed. From the error message it looks like the query failed. — Nick, Jul 31 '18 at 13:43

score 0 · Answer 1 · answered Aug 03 '18 at 05:56

All your SELECTqueries will work with your method, but I guess you're also excecuting an UPDATE or INSERT or DELETE or else with the same method, in those cases you should use instead $this->db->affected_rows()

You can see a reference in here. Also a bit more of code and the example of your query would be very nice.

AbdulAhmad Matin · Answer 2 · 2018-08-03T07:06:44.357

0

Test other method for count row. $query->count_all_results(); or $query->affected_rows();

edited Aug 03 '18 at 07:06

answered Aug 03 '18 at 06:53

AbdulAhmad Matin

1,047
1
18
27

score 0 · Answer 3 · answered Aug 03 '18 at 09:24

It is always difficult to answer without actual source code, but these explanations may help you.

The error, that you receive (I hope that we are talking about the same CodeIgniter version), is in public function count_all_results($table = '', $reset = TRUE), when if ($result->num_rows() === 0) is executed. The -> syntax is to access property/method on object. Using it on boolean does nothing but triggers an error. Variable $result holds the return value of $this->query() function. The query() function returns a database result object when “READ” type queries are run, which you can use to show your results. When “WRITE” type queries are run it simply returns TRUE or FALSE depending on success or failure. You can try to echo $result just before that $result->num_rows() call to see the actual value.

So, possible reasons for such an error may be the execution of INSERT/UPDATE/DELETE statement or wrong T-SQL statement. Try to replace count_all_results() with affected_rows() for INSERT/UPDATE/DELETE statements. Also you may check the T-SQL statement with SQL Management Studio.

About $scrollable property. You are right that sqlsrv_num_rows() requires a client-side (buffered), static, or keyset cursor, and will return FALSE if you use a forward cursor or a dynamic cursor. But this can't be a reason for your error, because $scrollable member of class CI_DB_sqlsrv_driver has default value FALSE or SQLSRV_CURSOR_CLIENT_BUFFERED, and num_rows() member of class CI_DB_sqlsrv_result doesn't seems to raise an error.

score 0 · Accepted Answer · answered Aug 03 '18 at 17:13

Found the issue! Apparently, sqlsrv driver does not seem to convert double-quoted strings into single-quote. So:

$tmp = 'SELECT * FROM tmpTable WHERE tmpName="test"';

when executing this query an error was being returned which was evaluated by Codeigniter as FALSE. I've got the same application in two different instances, one with PHP5.6 and the other with PHP7.2. With the PHP5.6 (different driver) instance I don't have this issue, only with PHP7.2 and sqlsrv driver. I assume the driver for 5.6 converts

$tmp = 'SELECT * FROM tmpTable WHERE tmpName="test"';

to

$tmp = "SELECT * FROM tmpTable WHERE tmpName='test'";

PHP MSSQL num_rows() not working with sqlsrv driver in Codeigniter

4 Answers4