Zend\Db in ZF 2.1
Zend\Db just got a little better with the release of Zend
Framework 2.1. All the cool things you could do on Mysql, SQLite,
Postgresql and SQL Server can now be done on DB2 and Oracle. In addition,
a number of additions were brought into the Zend\Db\Sql\Select object as
well.
Connecting to DB2 and Oracle
Connecting to DB2 whether on Windows, *nix, Mac, or the IBM i, is the same as any other driver - using the array notation:
<?php
use Zend\Db\Adapter\Adapter as DbAdapter;
// DB2 Connection
$adapter = new DbAdapter(array(
'driver' => 'IbmDb2',
'database' => '*LOCAL',
'username' => '',
'password' => '',
'driver_options' => array(
'i5_naming' => DB2_I5_NAMING_ON,
'i5_libl' => 'LIB1 LIB2 LIB3'
),
'platform_options' => array('quote_identifiers' => false)
);
// Oracle Connection
$adapter = new DbAdapter(array(
'driver' => 'Oci8',
'hostname' => 'localhost/XE',
'username' => 'developer',
'password' => 'developer'
));
Both Oracle and DB2 carry different conventional usage patterns and
workflows than their more modern successors in the relational database
space. As such, certain default behaviors can be turned off. For example,
by default, when queries are generated via any of the Zend\Db\Sql object
(SQL abstraction), all known identifiers are identifier quoted. That means
if a developer wrote: $select->from('foo'); then "foo" would be quoted
in the database platform specific way. For MySQL this means
back-ticks, like `foo`, and for most other database that means being
quoted with double quotes. In cases of Oracle and DB2 where there is a
pronounced history of not quoting identifiers, Zend\Db\Adapter can be
provided an option to turn this off - as you can see above in the
"platform_options".
Once an adapter is created, it can be used by any of the Zend\Db API. Here
are a few examples of what you can do:
<?php
// Zend\Db\TableGateway
use Zend\Db\TableGateway\TableGateway;
$table = new TableGateway('ARTIST'), $adapter);
$results = $table->select(array('ARTIST_ID > ?' => 5000));
// iterate results outputting each column
foreach ($results as $row) {
echo '<tr>';
foreach ($row as $col) {
echo '<td>' . $col . '</td>';
}
echo '</tr>';
}
A more complex query:
<?php
// complex query
$sql = new Sql($adapter);
$select = $sql->select()->from('ARTIST')
->columns(array()) // no columns from main table
->join('ALBUM', 'ARTIST.ARTIST_ID = ALBUM.ARTIST_ID', array('TITLE', 'RELEASE_DATE'))
->order(array('RELEASE_DATE', 'TITLE'))
->where->like('ARTIST.NAME', '%Brit%');
$statement = $sql->prepareStatementFromSqlObject($select);
foreach ($statement->execute() as $row) {
// var_dump($row);
}
Other Interesting Additions to Zend\Db\Sql
Join From SubSelect:
<?php
$subselect = new Select;
$subselect->from('bar')->where->like('y', '%Foo%');
$select = new Select;
$select->from('foo')->join(array('z' => $select39subselect), 'z.foo = bar.id');
/* produces SQL92 SQL (newlines added for readability):
SELECT "foo".*, "z".*
FROM "foo"
INNER JOIN (
SELECT "bar".* FROM "bar"
WHERE "y" LIKE '%Foo%'
) AS "z" ON "z"."foo" = "bar"."id"
*/
Expressions inside Order:
<?php
$select = new Select;
$select->order(new Expression('RAND()'));
Call to Action
Since our DB2 and Oracle drivers are new, we are sure they are not perfect yet and can be improved to better allow a more natural workflow for the database needs of a DB2 or Oracle developer. If you find anything that is a bug, or feature request, please take the time to fill out an issue on our github repository for ZF2:
Happy ZFing!
