php - what does sqlsrv_query return for ALTER INDEX all ON tbl REORGANIZE query and why does sqlsrv_num_rows return FALSE?

Question

I've created a pull request to add a DatabaseResultInterface::getNumRows() function to CodeIgniter 4. Some changes I've made have broken a unit test. Specifically, an SQLSRV table optimization test is barfing because this framework tries to fetch a result array from the SQLSRV response to this table optimization query:

ALTER INDEX all ON db_job REORGANIZE

It's also a bit tricky to trace all the code being called that leads to this, but here's an exception:

1) CodeIgniterDatabaseLiveDbUtilsTest::testUtilsOptimizeDatabase
CodeIgniterDatabaseExceptionsDatabaseException: Error retrieving row count

/home/runner/work/CodeIgniter4/CodeIgniter4/system/Database/SQLSRV/Result.php:198
/home/runner/work/CodeIgniter4/CodeIgniter4/system/Database/BaseResult.php:193
/home/runner/work/CodeIgniter4/CodeIgniter4/system/Database/BaseUtils.php:177
/home/runner/work/CodeIgniter4/CodeIgniter4/tests/system/Database/Live/DbUtilsTest.php:105

Dissecting that, we see that the DBUtilsTest line 105 calls the BaseUtils::optimizeTable function with a table name, "db_job", and that uses the SQLSRVUtils::optimizeTable var to construct the query I provided above. This sql is then fed to a db object that calls CodeIgniterDatabaseSQLSRVConnection::query() which, through inheritance and various function calls routes that SQL through BaseConnection::query() which feeds the sql to BaseConnection::simpleQuery which hands the sql to SQLSRVConnection::execute which finally feeds the sql to sqlsrv_query and returns the result of that function back up through the call stack. So I guess this brings me to my first question:

Question 1: What is the value of $stmt if this ALTER command a) succeeds or b) fails?:

$stmt = sqlsrv_query($connID, 'ALTER INDEX all ON db_job REORGANIZE');

According to the sqlsrv_query documentation, this function:

Returns a statement resource on success and false if an error occurred.

Whatever that value is gets returned back up the call stack to line 625 of BaseConnection::query where, if successful, it is stored as $this->resultID and fed as the second parameter to the constructor at line 676 which effectively returns new SQLSRVResult($this->connID, $this->resultID). To be clear, connID refers to the SQLSRV db connection and resultID refers to whatever value of $stmt was returned by the sqlsrv_query call above.

The resulting $query variable is an instance of systemDatabaseSQLSRVResult in this function:

    public function optimizeTable(string $tableName)
    {
        if ($this->optimizeTable === false)
        {
            if ($this->db->DBDebug)
            {
                throw new DatabaseException('Unsupported feature of the database platform you are using.');
            }
            return false;
        }

        $query = $this->db->query(sprintf($this->optimizeTable, $this->db->escapeIdentifiers($tableName)));
        if ($query !== false)
        {
            $query = $query->getResultArray();
            return current($query);
        }

        return false;
    }

An instance of SQLSRVResult will not be false so that code will attempt to call SQLSRVResult::getResultArray which through inheritance calls BaseResult::getResultArray. This seems wrong to try to getResultArray from a query that optimizes a table or any sort of ALTER query, however an MySQL server will return a series of records in response to an OPTIMIZE query. Also, the sqlsrv_query function we just ran is just returning some of sqlserver statement or resource as its result.

I guess this brings me to my second question: Question 2: How does one tell from the $stmt result of sqlsrv_query whether the ALTER statement above succeeded?

Curiously, none of these pecularities caused any problem before. The problem appears to have arisen because I've deprecated an unused BaseResult::numRows property that was never being set in the old getResultArray code and have replaced it and all references to it with a new getNumRows method. In my new BaseResult::getResultArray function we now check getNumRows instead of numRows. This works fine for MySQLi, SQLite3, and PostGreSQL, but barfs in SQLSRV because the aforementioned $stmt result of the sqlsrv_query ALTER statement gets fed to sqlsrv_num_rows and returns false, which signifies an error according to the docs. Here's the code for SQLSRVResult::getNumRows function:

    public function getNumRows() : int
    {
        // $this->resultID contains the sqlsrv_query result of our ALTER statement
        // and $retval comes up false
        $retval = sqlsrv_num_rows($this->resultID); 
        if ($retval === false)
        {
            throw new DatabaseException('Error retrieving row count');
        }
        return intval($retval);
    }

This brings me to: Question 3: would it ever make any sense to try and fetch results from or call sqlsrv_num_rows on the result of an ALTER INDEX query?

question from:https://stackoverflow.com/questions/65546701/what-does-sqlsrv-query-return-for-alter-index-all-on-tbl-reorganize-query-and-wh

Answer 1

Waitting for answers