ZF2 TableGateway query returning empty resultset, Adapter query works

Question

I've moved an application between servers.

From:

• Windows 2008 R2
• ISS 6.1
• PHP 5.4.11
• SQL Server 10
• ZF 2.4

To:

• Windows 10
• PHP built-in server
• PHP 7.2.5
• SQL Server 14
• ZF 2.4

Below the TableGateway selectWith() returns an empty resultset but when the same $select is queried via Adapter->query() it works as expected. It worked fine on the previous server. The TableGateway selectWith() is the original code and needs to work. Apart from the version numbers above, nothing else has changed.

    $select = $this->tableGateway->getSql()->select()->where(array(
        'col1 = ?' => $input
    ));

    // via selectWith
    $resultSet = $this->tableGateway->selectWith($select);

    // via query
    $selectString = $this->tableGateway->getSql()->buildSqlString($select);
    $adapter = $this->tableGateway->getAdapter();
    $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

    // works
    var_dump($results->current());
    // doesn't work
    var_dump($resultSet->current());

Update:

$select = $this->tableGateway->getSql()->select()->where(array(
   'col1 = \''.$input.'\''
));

The above code works. So the problem must be with the way TableGateway Select placeholder and replacements. I've tried all the variations in the docs and they should work but don't.

Further update:

Obviously one of these uses sqlsrv_query and the other uses sqlsrv_execute. I've been through the library and there must be a problem with the way it binds params to sqlsrv_prepare but I haven't got to the bottom of it yet. sqlsrv_prepare requires pass by reference. I suspect the bug is an incompability with this breaking change between 5.6 and 7.

Answer 1

The issue here is that ZF2 is not compatible with PHP > 5.6. In this instance the API for sqlsrv has been made stricter due to deprecation of "call-time pass-by-reference". The solution is to stick with PHP 5.6 or refactor a large amount of the code.