I would like to see a native Firebird driver like we had in ZF1 that does not rely on PDO.

I have already created a Zend-Db Firebird driver that uses the native ibase_ functions. I can connect to the database, execute queries and return results.

However I need some help/advice on how to deal with Firebird's syntax for FIRST x SKIP y (equivalent to LIMIT x OFFSET y). This goes at the beginning of the SELECT clause, not at the end like MySQL. I think this would impact the Zend\Db\Sql\Select class. Which is a bigger impact that just adding some new driver classes.

I would also appreciate any advice on writing new tests, or using existing zend-db tests.

Pgsql connection class isn't configuring a charset parameter. I'll create a PR.

This refers to file https://github.com/zendframework/zend-db/blob/master/src/Sql/AbstractExpression.php.

On certain conditions when normalizeArgument($argument, $defaultType = self::TYPE_VALUE) is called passing an array with columns and values (for instance when the dbTable authenticator adapter builds the query) it calls $this->buildNormalizedArgument($key, $value); with the arguments misplaced giving an exception:
Argument type should be in array(identifier,value)

The call with misplaced arguments happens in (a 12 Nov 2014 commit):
https://github.com/zendframework/zend-db/blob/master/src/Sql/AbstractExpression.php#L57

For example, I have the following codes:

use Zend\Db\TableGateway\TableGateway;
use Zend\Stdlib\Hydrator\ObjectProperty;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Adapter\Adapter;

require 'vendor/autoload.php';

$adapter = new Adapter(array(
    'driver'   => 'pdo_mysql',
    'database' => 'db-album',
    'username' => 'root',
    'password' => ''
));


//using TableGateway instance
$sampleTable = new TableGateway('album', $adapter, null,new ResultSet());
//selecting...
$rowset = $sampleTable->select(array('artist' => 'Non-existent'));
$row = $rowset->current();

var_dump($row); // return false

While in code, it should be return array|\ArrayObject|null , see https://github.com/zendframework/zend-db/blob/master/src/ResultSet/ResultSet.php#L95

The NotBetween class is basically same as Between class, except its specification value is replaced with '%1$s NOT BETWEEN %2$s AND %3$s'. For example:

<?php
/**
 * Zend Framework (http://framework.zend.com/)
 *
 * @link      http://github.com/zendframework/zf2 for the canonical source repository
 * @copyright Copyright (c) 2005-2015 Zend Technologies USA Inc. (http://www.zend.com)
 * @license   http://framework.zend.com/license/new-bsd New BSD License
 */

namespace Zend\Db\Sql\Predicate;

class NotBetween extends Between
{
    protected $specification = '%1$s NOT BETWEEN %2$s AND %3$s';
}

Without NotBetween class, my code will look like this:

$between = new Between($identifier, "'$minValue'", "'$maxValue'");

if ('notBetween' === $predicate) {
     $between->setSpecification('%1$s NOT BETWEEN %2$s AND %3$s');
}

$predicate->addPredicate($between);

With NotBetween class, my code will look much more simple:

$predicate->$predicate($identifier, "'$minValue'", "'$maxValue'");

Thanks for reading and I hope you may consider this! =)

I'm dropping a constraint on mysql5.6, code:

$AlterTable = new Ddl\AlterTable($test_table_name);
$AlterTable->dropConstraint('text_UniqueKey');

echo $sql->buildSqlString($AlterTable,$adapter);

$adapter->query(
    $sql->buildSqlString($AlterTable,$adapter),
    $adapter::QUERY_MODE_EXECUTE
);

And this code output :

ALTER TABLE `test` DROP CONSTRAINT `text_UniqueKey`

Here is the problem, Mysql nerver support the Syntax "DROP CONSTRAINT", so it throw an error :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `text_UniqueKey`' at line 2

Note: $adapter config array is

'db' => array(
    'driver' => 'Pdo_Mysql',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => 'abc123',
    'database' => 'easypay',
)

Help ! What to do now ???

Check headers are correct

TLDR; Headers should use the #, ## etc to format different levels of headers, and not be underlines using ===== or ``-----`, or be psuedo header using bold

Check all headers on the documentation - headers should use the hash style of declaration rather then be underlined with equals or dashes. The more hashes, the more of a subheading. Eg:

• # is equal to <h1>
• ## is equal to <h2>
• ### is equal to <h3>
• #### is equal to <h4>
• ##### is equal to <h5>

Headings should be appropriate for their level in the documentation.

Psuedo headers using bold tags ** should be replaced with appropriate level of heading tag.

I have a situation where I want to pre-build the select query based on the logged in user (or potentially other criteria) and I’m thinking that the strategy pattern would be the best way to do that, specifically by refactoring TableGatewayAbstractFactory to implement the StrategyEnabledInterface.

Here's a sample use case; I have a fetchAll method which returns all documents in my application, and I want to be able to call just that one method, but transparently take in to account the logged in user, so that they only see the documents which they should.

Say I have a logged in user, and they're an admin user. I want that user to be able to see all records, with no filtering, as they should be able to edit everything. So the select object would be left as is, with nothing pre-added to it.

Later, a normal user logs in. In this case I want to ensure that the user sees only the records which they're responsible for, not ones belonging to any other user. So I'd want to add a join and a where (or other criteria), resulting in pre-filtering the results.

Given that the criteria can change at runtime, if they're applied at all, I'm thinking that a Strategy Pattern is the right approach, as it can keep the logic separate, testable, and not break SRP, as well as be applied if and when needed.

Am I missing existing functionality, or looking at this from the wrong perspective?

What about adding:

leftJoin(...)
rightJoin(...)
innerJoin(...)
outerJoin...()
leftOuterJoin(...)
rightOuterJoin(...)

methods to Sql\Select?

in my case i have a table with "my field" as field name it is returned as "[my] [field]" instead of "[my field]".

The problem is currently being triggered in Zend\Db\Adapter\PlatformAbstractPlatform::quoteIdentifierInFragment. On line 44 there is a preg_split function that splits the fieldname.

i tried to add my case in the tests but have no time to run them atm and/or fixed any issues that will turn up

Suppose we have a table named mytable with more than two rows in our database.
$driver is an instance of the Zend\Db\Adapter\Driver\Pdo\Pdo class.
Let's look at the following code:

$results = $driver->getConnection()->execute('SELECT * FROM mytable');
foreach ($results as $result) {
    // For some reason our loop ends up at the first level
    break;
}
// Next, in another place we do that (with the same $results object):
foreach ($results as $result) {
    echo $result['mycolumn'];    //Here we start from the second row! (though rewind() was executed)
}

The problem is with the Zend\Db\Adapter\Driver\Pdo\Result::rewind() method. Let's look at the actual implementation:

public function rewind()
{
    if ($this->statementMode == self::STATEMENT_MODE_FORWARD && $this->position > 0) {
        throw new Exception\RuntimeException(
            'This result is a forward only result set, calling rewind() after moving forward is not supported'
        );
    }
    $this->currentData = $this->resource->fetch($this->fetchMode);
    $this->currentComplete = true;
    $this->position = 0;
}

As we can see, the Exception is thrown when $this->position is greater than 0, but the Exception should be thrown when $this->position is greater than -1.
In addition to this we should correct the current() and next() methods a little bit too if we want to use them not only in foreach loops.

Sorry for my bad English.

Now, for connection to db through unix socket I need setup $dsn string manually as full string in config. It's not good, because I need to make some logic in configuration file.
I propose to add this lines to src/Adapter/Driver/Pdo/Connection.php after #239 row:

                    if (isset($unix_socket)) {
                        $dsn[] = "unix_socket={$unix_socket}";
                    }

Check the tables in a document

TLDR; All tables should be in the format of GHFM using | and - as horizontal and vertical separators respectively

Check all tables are in the correct format. Please don't use leading and trailing | - more information on github flavoured markdown tables can be found here.

getSqlString quotes the values for LIMIT and OFFSET, which results in int values quoted to strings, which in turn results in a syntax error for example in MySQL.
https://github.com/zendframework/zend-db/blob/master/src/Sql/Select.php#L739
https://github.com/zendframework/zend-db/blob/master/src/Sql/Select.php#L752

Very simple test

$sql = @(new \Zend\Db\Sql\Select)->from('tableName')->limit(10)->offset(0)->getSqlString(new \Zend\Db\Adapter\Platform\Mysql);

$sql === "SELECTtableName.* FROMtableNameLIMIT '10' OFFSET '0'" This is a Syntax error for MySQL and possibly others as well.

I tested this with the PDO driver
https://github.com/zendframework/zend-db/blob/master/src/Adapter/Platform/Mysql.php#L93 quotes the int values to strings.

Noteworhty

When preparing and executing the same Select as statement the correct sql "SELECTtableName.* FROMtableNameLIMIT 10 OFFSET 0" is generated and send to the MySQL server.

Possible untested solution

Since Select->limit(numeric $value) and Select->offset(numeric $value) only allow numeric values quoting might be omitted, but I cannot say this for sure as quotes might be needed for other platforms or a different use case might require it.
https://github.com/zendframework/zend-db/blob/master/src/Sql/Select.php#L360-L366
https://github.com/zendframework/zend-db/blob/master/src/Sql/Select.php#L378-L384

Another alternative is adding quoteLimit($value) and quoteOffset($value) to the platform interface and use them in processLimit and processOffset

Check code blocks are correct

TLDR; Check in all files that codeblocks are correct, in PSR-2 format and have PHP syntax highlighting applied.

Code blocks should be in the following format...

```php
 'ZEND-FRAMEWORK');

// No required options
$rendererOptions = array();
$renderer = Barcode::factory(
    'code39', 'image', $barcodeOptions, $rendererOptions
);

```

Note the three backticks then php in the opening fence, and the closing fence is just three backticks. It's common for the opening backticks to have no code type, or something like source.

Code should also have been automatically formatted into PSR-2 format, but sometimes these slip through the net.

Zend\Sql\Select contains constant declaration for both left and right outer join as follow:

const JOIN_OUTER_RIGHT = 'outer right';
const JOIN_OUTER_LEFT  = 'outer left';

Unfortunately, as described in the wikipedia article for instance, values should be respectively left outer and right outer instead of outer left and outer right.

Is there any reason why these methods return false and null respectively? To put it in context, I was attempting to use the SequenceFeature recently and when I tried to call lastSequenceId() discovered that I couldn't because of how the methods are implemented, or a lack thereof. I'm happy to submit a PR if required. But before doing so I'm keen to know if I've missed something.

Oci8 driver implementation doesn't allow to fetch cursors

In Zend\Db\Sql\Select class are two invalid constants JOIN_OUTER_RIGHT and JOIN_OUTER_LEFT. Their values are 'outer right' / 'outer left', but correct values are 'right outer' / 'left outer'.
I checked this bug in MySql and it produce syntax error. Same problem is probably in PostgreSql (see documentation)

Considering that a UNION should be able to replace an aliased table in a query, is there any reason the Select class prohibits doing this:

(new Select(['u' => new Combine([$select1, $select2, $select3])]);

Could this be changed?

Now I install my old project, with 1 dependency — zend-db. And catch this

/*************************************/
ERROR: Repository not found.

Please make sure you have the correct access rights
and the repository exists.
/*************************************/

When I run composer up, I install required version (2.3) from this repo without problem.
But what about support for legacy projects?

The origin sql

select max(convert(SUBSTRING_INDEX(order_uniqid,'-',-1), unsigned int)) as maxid from table

I want to write code like

$select->from($tablename)->column([maxid => new Expression('max(convert(SUBSTRING_INDEX(order_uniqid,'-',-1), unsigned int))')]; 

But Zend DB will never work.

Show errror:

Supplied expression must be a string.

the example order_uniqid may looks like

2343-234243-323434

After some time I realized this was why my results were one off from my actual count. The entire prepareStatement method seems to have been removed from the decorator which was performing the rather necessary task of setting the Scrollable option to SQLSRV_CURSOR_STATIC. While it is simple enough to just set this with the setPrepareOptions method in the Statement it is another matter when using a TableGateway. In this case the decorator really becomes the place to set this option. I do realize that a Sql object with a overriding decorator on the SqlPlatform could be passed to the TableGateway constructor but it all seems a bit much for something that should just be built in (and was) as the default.

Hello,

I can't get DB-Connected resources fetch single item from Oracle. I always get a "Item not found" message, but I've noticed that it only occurs when I call $resultSet->count(), because it returns null.

$resultSet = $this->table->select([ $this->identifierName => $id ]);

if (0 === $resultSet->count()) {
    throw new \Exception('Item not found', 404);
}

return $resultSet->current();

Is there any restriction to the Oci8 adapter?

I can get it to work if I don't check count() function, but to do so, I have to overwrite every DB-Connected resource class.

When a persistent connection fails to connect, other unrelated DB connections appear to fail when they are successfully connected - they should not falsely appear to fail.
Could be related to https://bugs.php.net/bug.php?id=69884 or https://bugs.php.net/bug.php?id=71803

PHP 5.6.14 (cli) (built: Dec 18 2015 14:17:39)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
with Xdebug v2.3.3, Copyright (c) 2002-2015, by Derick Rethans
with test_helpers v1.0.1-dev, Copyright (c) 2009-2013, by Johannes Schlueter, Scott MacVicar, Sebastian Bergmann

<?php
include 'vendor/autoload.php';

use Zend\Db\Adapter\Driver\Mysqli\Connection;

error_reporting(E_ERROR);              // Disable displaying warnings for this test.

$use_persistence = true;               // try this same test with false, and it will work without problems
$use_disconnect = true;                // this makes a difference
$use_same_variable_name_again = true;  // this seems to make a difference as well

$good_config = [
    'driver' => 'Mysqli',
    'database' => 'test',
    'username' => 'root',
    'password' => 'root',
    'host' => ($use_persistence ? 'p:' : '') . '127.0.0.1',
    'port' => '3306',
];

$good = new Connection($good_config);
$good->connect();

if ($use_disconnect) {
    $good->disconnect(); // no-op with persistence?
}

$bad_config = array_merge($good_config, ['port' => '3307']); // break the config intentionally

// Try to connect to the "bad" config (wrong port).

$bad = new Connection($bad_config);
try {
    $bad->connect(); // no exception thrown
} catch (\Exception $e) {
    // We expected this to fail with the bad config. But...
    var_dump($e->getMessage() == "Connection error");
}

// try to connect using the good config, shouldn't throw an exception (but due to a bug, it does)

if ($use_same_variable_name_again) {
    $good = new Connection($good_config);
    $good->connect();
} else {
    $good2 = new Connection($good_config);
    $good2->connect();
}

// Expected result: bool(true) with no exception thrown, in all cases

// Actual result:
//    persistence   disconnect    same variable         result
//    ------------- ------------- ---------------       ----------------
//    false         *             *                     no exception (correct behavior)
//    true          true          true                  exception (bug?)
//    true          false         true                  exception (bug?)
//    true          true          false                 exception (bug?)
//    true          false         false                 no exception (correct behavior)

Other variations of this test, including connecting to completely different database host IP addresses will exhibit the same "corrupted" error, which Zend Db uses to check that the connection was successful or not.

As a patch/workaround, I would recommend Zend Db not use the connection errno or error string to determine if the connection was successful or not, and instead rely on the return value of making a connection.

Hello!

I just cloned the repo and try a vagrant up. I received this message:

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'chef/ubuntu-14.04' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'chef/ubuntu-14.04'
    default: URL: https://atlas.hashicorp.com/chef/ubuntu-14.04
The box you're adding has a name different from the name you
requested. For boxes with metadata, you cannot override the name.
If you're adding a box using `vagrant box add`, don't specify
the `--name` parameter. If the box is being added via a Vagrantfile,
change the `config.vm.box` value to match the name below.

Requested name: chef/ubuntu-14.04
Actual name: bento/ubuntu-14.04

I'll create a PR.

Zend\Db\Sql is missing a way to create a REPLACE INTO table_name VALUES(row_data) statement.
Thought, I'd add this here 'cause apparently noone else was missing that feature.

Check docs for other problems

TLDR; Cast your eye over the documetation for any problems not covered in the other issues

Things slip through the net, so check the documentation for other problems that have been missed. Common other problems include

• Bullet lists (should be single * then space at the start of line)
• Inline code - should be marked by three backticks at start and finish
• bookdown.json file is correctly formated and has the right escaping
• Links between documentation using RST have been stripped
• Any other RST has been removed correctly
• Anything and everything not covered

If you end up fixing the same problem over and over, please ping Gary Hockin - we may be able to add bespoke issue for that problem, or fix in automated capacity

When there's a dash in the column name, inserts and updates fail.
See http://php.net/manual/en/pdo.prepared-statements.php#97162

The relevant code is:
https://github.com/zendframework/zend-db/blob/master/src/Adapter/Driver/Pdo/Pdo.php#L300

Is there a reason why this wouldn't work with positional placeholders instead?

I have a database table that makes use of a "type" column. I would like to be able to pass in a factory as my $objectPrototype so my result set can contain objects from distinct classes instead of single class that contains methods that switch on "type"

This could be as simple as creating a HydratingResultSet\PrototypeFactoryInterface and changing the HydratingResultSet::current() method to check instanceof before just cloning the $objectPrototype

I'm trying to use a function as a table in Zend\Db\Sql\Select and it appears to be quoting incorrectly. The entire function including parens is wrapped in quotes and I get an error saying the table doesnt exist.

<?php

require_once 'vendor/autoload.php';

$config = [
    'driver'   => 'pdo_pgsql',
    'host'     => 'postgresvm.dev',
    'username' => 'user',
    'password' => 'pass',
    'dbname'   => 'mydb',
    'port'     => 5432
];

$dbAdapter  = new \Zend\Db\Adapter\Adapter($config);
$sqlAdapter = new \Zend\Db\Sql\Sql($dbAdapter);

$select = new \Zend\Db\Sql\Select();
$select->from("my_schema.ufn_user_client(2, 3)");

$results =  $dbAdapter->query(
    $sqlAdapter->buildSqlString($select),
    \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE
)->toArray();

var_dump($results);

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "my_schema.ufn_user_client(2, 3)" does not exist. I've tried wrapping it in a Zend\Db\Sql\Expression but it requires the table name to be a string.

I think it would be useful and powerful to integrate events into zend-db, probably along similar lines to the way it's done in zend-cache.
A minimal set of useful events might be:
connect.[pre, post, exception]
disconnect.[pre, post, exception]
execute.[pre, post, exception]

We use Oracle database, and a couple of potential use cases I've thought of are:

1. run a query immediately after connecting to the database to initialize the connection (eg to set NLS settings)
2. disconnect from database after executing a query (if using a connection pool).

Thoughts?

I'm not sure if this is something which has already been covered, but recently I've been attempting to use the AlterTable and CreateTable classes with PostgreSQL and have been hitting an array of errors. These have been primarily caused by:

• Incorrect quoting of schema elements
• Incorrectly generated SQL

On investigating further, the support for PostgreSQL doesn't seem as robust as that for DB2, MySQL, Oracle or SQLServer. Am I missing something here? Or is it worth both implementing a set of Platform classes for PostgreSQL and spending time debugging and correcting the other issues which I've come across?

Would really appreciate feedback regarding this.

From Sqlite documentation :

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)

Using it, it's a pain to make it ' for each database connection ' !
So is it possible to activate it by default or adding an option for executing ' pragmas ' directive on connection ??

For the moment i've patched localy the file :
zend-db/src/Adapter/Driver/Pdo/Connection.php
From line 255 :

if (isset($charset) && $pdoDriver == 'pgsql') {
   $this->resource->exec('SET NAMES ' . $this->resource->quote($charset));
}
//---------------------------------------------------------------------------------------------------------
//   Sqlite : Enabling Foreign Key Support 
else if ($pdoDriver == 'sqlite' ) {
    $this->resource->exec('PRAGMA foreign_keys = on');
}

For queries like
SELECT * FROM t WHERE id IN (SELECT t1.id FROM t1 INNER JOIN t2 ON t1.ref=t2.ref WHERE t2.vintage=2016)
we need JOIN that return no column.
But if we send an empty array in 3rd parameter of Zend\Db\Sql\Join::join() method, in line 136, key 'column' is assigned by [Select::SQL_STAR] and the query send 2 columns, which causes an error :
Statement could not be executed (21000 - 1241 - Operand should contain 1 column(s))

Can you remove the conditional assignment ?

Since the Interface already has beginTransaction() and commit(), i'd suggest adding inTransaction() to the Interface. The property is set by the classes themselves in these both methods so I don't see why inTransaction() shouldn't be added to the interface.
ConnectionInterface.php

AbstractConnection.php
IbmDb2/Connection.php
Mysqli/Connection.php
Oci8/Connection.php
Pdo/Connection.php
Pgsql/Connection.php
Sqlsrv/Connection.php

After upgrade from 2.7.0 to 2.8.0 :

When joining tables in a Select object, the buildSqlString() method includes all columns from joined tables (SQL_STAR) although they should be discarded by the third param in the Select::join($name, $on, $columns = self::SQL_STAR, $type = self::JOIN_INNER) method.

This bug may lead to really poor performance after upgrade as joined tables are often used to filter results or even worse disclosing information in case the query is used to generate data (api). This probably may be marked as security bugfix as well.

This bug have been tested with php 5.6.20 / 7, Mysql, Oracle, Sql92 platforms.

Take a look at this example
(notice the $columns=[] param in the join() method and see also the columns()):

$select = new \Zend\Db\Sql\Select();
$select->from('my_table')
       ->join('joined_table', 'my_table.id = joined_table.id', $columns=[])
       ->columns([
          'my_table_column',
       ]);
$sql_string = $this->sql->buildSqlString($select, $this->getAdapterForPlatform('Sql92'));

On 2.7.0 and before the resulting query string is correctly only producing one column:

SELECT 
    "my_table"."my_table_column" AS "my_table_column", 
FROM "my_table" 
INNER JOIN "joined_table" ON "my_table"."id" = "joined_table"."id"

After update to 2.8.0, the resulting query string incorrectly add all columns from the joined table. Take a look on the third line (joined_table.*)

SELECT 
    "my_table"."my_table_column" AS "my_table_column", 
    "joined_table".*
FROM "my_table" 
INNER JOIN "joined_table" ON "my_table"."id" = "joined_table"."id"

I'm currently looking to provide a P/R and add some specs into the SqlFunctionnalTests. Feel free to help.

Trying to create:

SELECT
  uf.friend_id AS friend_id, 
  u.*
FROM users AS u
   LEFT JOIN user_friends AS uf ON uf.user_id = :user_id OR uf.friend_id = :user_id

When trying to use the same parameter twice like so:

$select = new Select(['u' => 'users']);
$select->join(
    ['uf' => 'user_friends'],
    new Expression(
        'uf.user_id = :user_id OR uf.friend_id = :user_id',
        ['user_id' => $userId]
    ),
    ['friend_id' => 'friend_id'],
    Select::JOIN_LEFT
);

$sql = new \Zend\Db\Sql\Sql($this->pivotTable->getAdapter());
$stmt = $sql->prepareStatementForSqlObject($select);

Expression will throw a Runtime Exception: The number of replacements in the expression does not match the number of parameters

The following workaround works just fine:

$select = new Select(['u' => 'users']);
$select->join(
    ['uf' => 'user_friends'],
    new Expression(
        'uf.user_id = ? OR uf.friend_id = ?',
        [$userId, $userId]
    ),
    ['friend_id' => 'friend_id'],
    Select::JOIN_LEFT
);

$sql = new \Zend\Db\Sql\Sql($this->pivotTable->getAdapter());
$stmt = $sql->prepareStatementForSqlObject($select);

AbstractResultSet::current() can buffer non-valid data.

Similar example as in #51:

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

require 'vendor/autoload.php';

$adapter = new Adapter(array(
    'driver'   => 'pdo_mysql',
    'database' => 'db-album',
    'username' => 'root',
    'password' => ''
));

//using TableGateway instance
$sampleTable = new TableGateway('album', $adapter, null,new ResultSet());
//selecting...
$rowset = $sampleTable->select(array('artist' => 'Non-existent'));
$rowset->buffer();
var_dump($rowset->current()); // null
var_dump($rowset->current()); // false

You should be able to use JOINs in UPDATE statements the same way you do in SELECT statements.
If i'm not mistaken you should be able to use Expressions for values to make column names work in statements, so it should be quite simple to implement that.

Just a small typo fix in Adapter\ParameterContainer::setFromArray(Array $array) method. Array should be in lowercase.

Check the document for bad blockquoutes

TLDR; Check blockquotes are formatted correctly using > and check headings in blockquotes are using ###

Blockquotes are donated by a single greater than character and then a space. Make sure all the blockquotes in every doc file are correctly formatted. Headings in blockquotes should use ### and not bold.

Any paragraph spacing in blockquotes should be marked using a single greater than, then a space.

In /src/Metadata/Source/OracleMetadata.php#L127 :table is a reserved word and therefore the query fails

Until version 2.7 is was possible to run the test suite (partially) without setting the DB connection parameters.

Since 2.8 this is not possible, if no servers running, the test suite fails.

You cannot create a result set from an unbuffered MySQLi query result. It fails during initialization. Take the example from documentation:

use Zend\Db\Adapter\Driver\ResultInterface;
use Zend\Db\ResultSet\ResultSet;

$adapter = new Zend\Db\Adapter\Adapter(array(
    'driver' => 'Mysqli',
    'database' => 'zend_db_example',
    'username' => 'developer',
    'password' => 'developer-password',
));

$stmt = $adapter->getDriver()->createStatement('SELECT * FROM users');
$stmt->prepare();
$result = $stmt->execute();

if ($result instanceof ResultInterface && $result->isQueryResult()) {
    $resultSet = new ResultSet;
    $resultSet->initialize($result);

    foreach ($resultSet as $row) {
        echo $row->my_column . PHP_EOL;
    }
}

// Fatal error:  Uncaught exception 'Zend\Db\Adapter\Exception\RuntimeException' with message 'Row count is not available in unbuffered result sets.' in vendor/zendframework/zend-db/src/Adapter/Driver/Mysqli/Result.php:316

It looks like the count property is initialized too early. It should be called only when ResultSetInterface::count() is called at the first time. In this case, the result set based on a unbuffered result still could be used as iterator.

I have a use case in which I'd like to (at various points before executing a query) add columns to my select. Contrived example:

$resultSet = $this->select(function($select) use($options) {

  if($options['includeSomething']) {
    $select->columns('something');
  }

  if($options['includeOtherThing']) {
    $select->columns('other_thing');
  }

});

I've found that's not possible, because:

• select.columns() replaces the query columns each time
• select.columns is protected, meaning I can't get at it to merge with before calling select.columns()

Would you be receptive to a PR that did one of these things:

• Add a getter / made select.columns public
• Add a merge param to select.columns()
• Add a select.addColumns() method

Sorry if this is in a strange place, or seems like a strange request! I am a new ZF2 user, and have had a look over the source and am reasonably sure this isn't possible currently (though I could be wrong!).

I know it should be possible to extend the Select class and implement these things myself. But I'm not quite sure how to get ZF2 to then use my extended class.

Thanks!

Hi,

When i use a view with a paginator the where->like with sql\expression doesn't work with the message :

Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%aa%') AS original_select' at line 1)

The code :
$select->where->like(new \Zend\Db\Sql\Predicate\Expression("concat(nom,' ',prenom)"), '%' . $value . "%");

Actually to solve this pb, i do the concatenation in my view.

Regards,

PYT

The change in #56, to check if the result is an array breaks BC. I'm extending some of the result classes to allow for simple value objects being returned, and this change break my code, unfortunately.

Also, as far as I can see, the result is still buffered, and can be returned on consecutive calls.

Maybe it would be better explicitly to check if $data === false?

SQLite does not appreciate parentheses in UNION:

(SELECT "4" AS "column1") UNION (SELECT "5" AS "column1")

$select = (new Combine())
    ->union(
        [
            (new Select())->columns(['column1' => 4]),
            (new Select())->columns(['column1' => 5])
        ]
    );

$sql  = (new Sql(new Adapter(new PdoDriver(new Connection(new PDO('sqlite::memory:'))))));
$stmt = $sql->prepareStatementForSqlObject($select);
$result = $stmt->execute();
var_dump(iterator_to_array($result));

Zend\Db\Adapter\Exception\RuntimeException: near "(": syntax error

or explicitly on the PHP PDO instance:

PDOException: SQLSTATE[HY000]: General error: 1 near "(": syntax error

StackOverflow question reference: http://stackoverflow.com/questions/4653124

The doc block states that an PredicateInterface[] will be returened. What is returned is a copy of the protected $predicates[] member. This array is populated by the orPredicateand andPredicate functions in such a way, that it is not an PredicateInterface[], but rather an array of [string, \Zend\Db\Sql\Predicate\Operator].