db2_prepare() may issue a warning in addition to returning false. If you have an error handler set (e.g. in the Expressive pipeline) then the message created is "db2_prepare(): Statement Prepare Failed" which is not helpful.

Therefore, we create our own error handler and trap this ourselves so that we can throw an ErrorException with more information from db2_stmt_errormsg().

I've left the previous handling as I don't know PHP extension code well enough to be 100% sure that db2_prepare() always generates a warning when it returns false.

• [x] I was not able to find an open or closed issue matching what I'm seeing.
• [x] This is not a question. (Questions should be asked on slack (Signup for Slack here) or our forums.)

Sub queries with limit seems not to work like it is supposed to anymore in version 2.9.3 in combination with PDO. Likely the regression has been introduced in the following PR.

https://github.com/zendframework/zend-db/pull/300

Code to reproduce the issue

$queryBuilder = \Zend\Db\Sql\Sql();

$update = $queryBuilder->update('table1');
$update->set([ 'start_date' => 'now()']);

$select = $queryBuilder
    ->select('table1')
    ->columns(['id'])
    ->where([
        'start_date' => null,
        'completed_date' => null,
    ])
    ->limit(25);

$update->where(new In('id', $select));

$statement = $queryBuilder->prepareStatementForSqlObject($update);
$statement->execute();

Expected results

The query should work as expected like in version 2.9.2

Actual results

The following query and parameters are generated:

UPDATE "table1" 
SET "start_date" = :c_0 
WHERE "id" IN (
    SELECT "table1"."id" AS "id" 
    FROM "table1" 
    WHERE "start_date" IS NULL 
    AND "completed_date" IS NULL 
    LIMIT :limit
)

Parameters

array(2) {
    ["c_0"]=> string(5) "now()"
    ["subselect1limit"]=>  int(25)
}

An exception occurs:

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: :subselect1limit' in vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php:298

Stack trace:

#0 vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(298): PDOStatement->bindParam(':subselect1limi...', 25, 1)

#1 vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(232): Zend\Db\Adapter\Driver\Pdo\Statement->bindParametersFromContainer()

<snip>

This PR solve #35 and replaces #178. The $name in bindParam are replaced with the following, due to the PDO restriction [0-9a-zA-Z_]:

return ':' . md5($name);

For instance, a filed name test$ will be translated in :79f5d7a7e10367f279c2500dcd03b3de as bind param name. I used the md5() approach after some benchmark tests, see comments and commits below. I also provided unit test and integrational test.

To handle UTF columns (eg, store Russian characters in column) SQL server needs data type NVARCHAR.

Either need to add NVarchar class to Ddl\Column namespace, or add as an optional parameter column->setMultibyte(true) which appends N to type property.

This is to resolve issue #185. With the pdo_ibm driver when passed the platform & platform_options keys it stops processing anything after that. Might be an issue with other drivers but I'm not sure. Since the platform & platform_options key are specifically related to zend-db I don't see any reason we'd ever want them passed to \PDO. Also even if the base \PDO driver needed those keys for some reason they could be put in a driver_options array which is what I am personally already doing anyway.

Main architecture issue - current SqlObjects includes SqlBuilders. This is not good design, it's coupe two separate things together - Describing and Building Query, including:

• functionality of sql objects and their builders realized in the same class, which creates problems with modifiable classes, stability and support.
• multiple entry points to build sql strings
• not possible to use builders for Expression. As sample concat() function have different syntax for various DB
• not possible to build nested sql. As sample Insert::into('foo')->select(Select::from('bar')->limit(10)) not work for SqlServer

Solution:

• builders functionality moved to separate classes.
• added builders for Expressions
• one entry point Builder\Builder::getSqlString() and Builder\Builder::prepareStatement().

Additionally was changed:

• AbstractSqlBuilder::createSqlFromSpecificationAndParameters() - more readable and flexible specifications
• Expression parameters always return ExpressionParameter object
• objects, are used tables, always return TableSource or TableIdentifier
• optimized UnitTests, added tests for various platforms
• optimized builders for Select::limit() and Select::offset()
• move duplicates from MySql\Ddl\CreateTableBuilder and MySql\Ddl\AlterTableBuilder to Mysql\Ddl\Column\ColumnBuilder
• Sql\Insert - add multiple values
• Sql\Ddl\DropTable - add 'IF EXISTS'
• add Sql\Ddl\Sql oblect for DDL
• rename Sql\Join to Sql\Joins
• rename Sql\SqlInterface to Sql\SqlObjectInterface and Sql\PreparableSqlInterface to Sql\PreparableSqlObjectInterface

When I upgraded from an older version of Zend\Db I found that certain unit tests in the client project were failing. When a datasource provided to a ResultSet is an array, the ResultSet should work out of the box. It didn't, because current() did not account for a datasource which has entered the system as just an array, while the buffer is flagged -1, when the ResultSet was initialized with such a datasource. So that flag on the buffer can be used to early out current() in such a situation.

This problem was there in version 2.7. It was fixed in 2.8, but came back in 2.9.

• Database: 10.0.35-MariaDB
• PHP version: 7.2.7
• Zend Db version: 2.9.3

To reproduce, use a database with some empty table, and execute:

$myAdapter = new \Zend\Db\Adapter\Adapter([
    'host'      => 'mariadb',
    'driver'    => 'Mysqli',
    'username'  => 'devppl',
    'password'  => '******',
    'database'  => 'mydatabase',
    'charset'   => 'utf8',
    'options'   => ['buffer_results' => true]
]);

$tableGateway = new \Zend\Db\TableGateway\TableGateway('myEmptyTable', $myAdapter);
$row = $tableGateway->select('myField = 999')->current();
var_export($row);

Expected result would be NULL, I get false instead.

I noticed that when I set buffer_results to false, I don't have the bug.

Change Return Type of selectWith() method

Provide a narrative description of what you are trying to accomplish:

• [x] Are you fixing a bug?
  • [x] Detail how the bug is invoked currently.

phpstan raises an error saying that ResulSetInterface has no method current().

• [x] Detail the original, incorrect behavior.

The selectWith() method has defined the @returnType as ResultSetInterface. The method calls the method executeSelect(), which returns ResultSet. The return types should be the same. removes also the @throws flag as it doesn't throw an exception itself, but just the called methods.

• [ ] Detail the new, expected behavior.

• [ ] Base your feature on the master branch, and submit against that branch.

• [ ] Add a regression test that demonstrates the bug, and proves the fix.

• [ ] Add a CHANGELOG.md entry for the fix.

• [ ] Are you creating a new feature?

  • [ ] Why is the new feature needed? What purpose does it serve?
  • [ ] How will users use the new feature?
  • [ ] Base your feature on the develop branch, and submit against that branch.
  • [ ] Add only one feature per pull request; split multiple features over multiple pull requests
  • [ ] Add tests for the new feature.
  • [ ] Add documentation for the new feature.
  • [ ] Add a CHANGELOG.md entry for the new feature.

• [x] Is this related to quality assurance?

• [ ] Is this related to documentation?

Version: Zend-db version: 2.9.*

Issue: When binding named params into sql query, the sql trows error: "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined".

Sql:

$sql = "SELECT Version FROM `SystemInfo` WHERE `Name` = :infoName ORDER BY `SystemInfoID` DESC LIMIT 1";

PHP:

$params = [':infoName' => 'version' ];

$this->getAdapter() ->createStatement($sql) ->execute($params) ->next()

Explained:

This works on any of previous versions before 2.9.*, the fix was to rollback to version 2.8.2

Sorry, previous request was incorrect.

This one is correct. Add support for multi limit in nested queries; That fix need for nested queries inside field parameters;

In previous thread was mentioned that this is fix for that issue: #262

Fix bug for nested queries inside field parameters;

• [x] I was not able to find an open or closed issue matching what I'm seeing.
• [x] This is not a question. (Questions should be asked on slack (Signup for Slack here) or our forums.)

Description of what I am trying to accomplish:

• Given the recommendation here to fall back to manual sql using the adapter in order to accomplish FOR UPDATE statements in MariaDb / MySQL environments, one should still be able to inject an interface into their class objects.
• The \Zend\Db\Adapter\Adapter implements no such interface which defines a query method

Code to reproduce the issue

class FooBar
{
    /**
     * @var \Zend\Db\Adapter\AdapterInterface
     */
    private $adapter;

    public function  __construct(
        \Zend\Db\Adapter\AdapterInterface $adapter
    ) {
        $this->adapter = $adapter
    }

    public function getRecordById(int $id): \ArrayObject
    {
        /**
         * @var \Zend\Db\ResultSet\ResultSet $resultSet
         */
        $resultSet = $this->adapter->query('SELECT * FROM foo_bar WHERE id = :id FOR UPDATE', ['id' => $id]);
        return $resultSet->current();
    }
}

Expected results

• AdapterInterface should have query defined so that it is a known accessible method. This will:
  • Allow for DI replacement in applications that may require such things
  • Allow for mocking of the Interface in order to accomplish unit testing of the class

Actual results

Since (in my application) \Zend\Db\Adapter\Adapter is the concrete class that is injected at run time, the method still works. However this leads to:

• IDE shows an error as it is not a known method of the interface
• Inability to properly unit test the class since I cannot call ->query on a mocked instance of \Zend\Db\Adapter\AdapterInterface

• add 2 new data types in ParameterContainer class in order to manage differently BLOB from CLOB,
• for oci8 driver: add a new case statement in bindParametersFromContainer() method to save data binded as BLOB

• [X] I was not able to find an open or closed issue matching what I'm seeing.
• [X] This is not a question. (Questions should be asked on slack (Signup for Slack here) or our forums.)

Provide a narrative description of what you are trying to accomplish.

There appears to be a memory leak issue when connecting to a DB2 database via the DB2 Connect driver while using the Zend DB package.

OS: Ubuntu 18.04.1 LTS PHP Version: PHP 7.2.19-0ubuntu0.18.04.2 (cli) (built: Aug 12 2019 19:34:28) ( NTS ) DB2 Connect Version: 11.1

Explanation of Diagnosis: When I run a similar process with a simple PDO object, I do not get the same memory allocation issues. Here is the PDO code I was using to check:

$conn = new PDO('ibm:MYDEVENV', 'MYUSERNAME', 'MYPASSWORD');
$result = $conn->query('select ACCT_UNIT from MYLIBRARY.MYTABLE where MYVALUE = 113;');
if (!$result) {
	var_dump($conn->errorInfo());
}
while ($row = $result->fetch()) {
	var_dump($row);
}

The outcome is as expected:

array(2) {
  ["ACCT_UNIT"]=>
  string(15) "113            "
  [0]=>
  string(15) "113            "
}

When this database connection and query are run using Zend-Db package, a memory allocation error is thrown. See below:

Code to reproduce the issue

$configArray = array(
    'driver' => 'pdo_ibm',
    'dsn' => 'ibm:MYDEVENV',
    'platform' => 'IbmDb2',
    'platform_options' => [
        'quote_identifiers' => false,
    ],
    'driver_options' => [
        'isDefaultTableAdapter' => true,
        'i5_naming' => 'DB2_I5_NAMING_ON',
        'autocommit' => 'DB_AUTOCOMMIT_OFF',
        'i5_lib' => 'MYLIBRARY'
    ],
    'username' => 'MYUSERNAME',
    'password' => 'MYPASSWORD',
);
$adapter = new \Zend\Db\Adapter\Adapter($configArray);
$sql = 'select ACCT_UNIT from MYLIBRARY.MYTABLE where MYFIELD = :MYVALUE';
$parameters = ['MYVALUE' => 113];
try {
       $result = $adapter->query($sql, $parameters);

       if ($result instanceof ResultSet)
       {
            $arrayResults = $result->toArray();

            if (sizeof($arrayResults) === 1)
                return $arrayResults[0];
            else {
                $arrayResults[] = array('ACCT_UNIT'=>'0000');
                return $arrayResults[0];
             }
        }
} catch (\Exception $e) {
       $this->appLogger->crit($e);
       $errorMessage = $e->getMessage();
       throw new \Exception ($errorMessage);
}

Expected results

The query should return the value it was passed as a parameter inside an associative array: ["ACCT_UNIT" => "113"]

Actual results

I received the following error:

mmap() failed: [12] Cannot allocate memory
PHP Fatal error:  Out of memory (allocated 2097152) (tried to allocate 140346646331424 bytes) in /tmp/vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Connection.php on line 425

Some of Zend\Db\Adapter\Platform\AbstractPlatform inheritors (Mysql, Oracle, Postgresql, Sqlite, SqlServer) holding the reference of \PDO object (even if DriverInterface passed into constructor, because quote methods are reassigning resource reference of connection to local variable resource). This causing problems when you using Platform class and want to disconnect the \PDO instance with disconnect method of the ConnectionInterface.

We found that problem when noticed ~40k+ TCP connections on 3306 port stucked in CLOSE_WAIT state after disconnecting and exec another php script and exit original script.

Code to reproduce the issue

        $testConnection = function() {
            $mypid = getmypid();
            $lsof = shell_exec("lsof -i -P -n | grep 3306 | grep $mypid");
            echo ($lsof === null ? 'no connection' : 'connection established') . PHP_EOL;
        };

        // take your adapter with mysql driver inside
        $db = self::$infrastructure->db();

        $testConnection();

        $db->getDriver()->getConnection()->connect();

        $testConnection();

        $db->getDriver()->getConnection()->disconnect();

        $testConnection();

        $db->getDriver()->getConnection()->connect();

        $testConnection();

        $db->getPlatform()->quoteValue('test');
        $db->getDriver()->getConnection()->disconnect();

        $testConnection(); // are still connected? yes :(

Expected results

Connection is disconnected even when using quoteValue method of platform object

Actual results

Connection established

• [x] I was not able to find an open or closed issue matching what I'm seeing.
• [x] This is not a question. (Questions should be asked on slack (Signup for Slack here) or our forums.)

I am trying to create an expression that has zero as the parameter value. However, the parameters incorrectly show as empty.

Code to reproduce the issue

$expr = new \Zend\Db\Sql\Expression('?', '0');
print_r($expr->getParameters());

Expected results

It prints "0"

Actual results

It prints empty array

Proposed fix

https://github.com/zendframework/zend-db/blob/master/src/Sql/Expression.php#L59

In Zend\Db\Sql\Expression, update the constructor to explicitly check for '0' when setting parameters

if ($parameters || $parameters === '0') {
     $this->setParameters($parameters);
}