ZF Blog

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 
'&lt;tr&gt;';
  foreach (
$row as $col) {
    echo 
'&lt;td&gt;' $col '&lt;/td&gt;';
  }
  echo 
'&lt;/tr&gt;';
}

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!

Return to entries

blog comments powered by Disqus