Issues

ZF-5535: Zend_Db_Table does not work with IBM DB/2

Description

I try to use Zend_Db_Table with DB/2.

My Table class looks like this:


class Mitarbeiter extends Zend_Db_Table_Abstract{
    protected $_name = 'mitarbeiter';
    protected $_schema = 'orga';
    protected $_primary = 'id';
    protected $_referenceMap = array(
        'Anrede' => array(
            'columns'=>'anrede',
            'refTableClass' => 'Anrede',
            'refColumns' => 'id')
        );
 }

When I run a simple select, i should get the following SQL-Statement:


"SELECT * FROM orga.mitarbeiter" but instead I get "SELECT mitarbeiter.* FROM orga.mitarbeiter"

This kind of SELECT-Statement is not allowed in DB/2.

It would work, if the name of user which is connected to the database is the same that the table schema and u omit the schema name. The result of that would be a statement like


"SELECT mitarbeiter.* FROM mitarbeiter"

I have to add one more Information:

The Statement:


"SELECT orga.mitarbeiter.* FROM orga.mitarbeiter"

would work too. It's just necessary that the "Column-Prefix" is the same, as the "Table-Part" of the From-Clause

Comments

I too ran into this problem with the Zend_Db_Table class for DB2. Just like the ticket's description said by appending the schema name to the tablename.columnname the problem resolve. So basically your sql statement will look something like this SELECT schema.table.column FROM schema.table; OR SELECT schema.table.* FROM schema.table;

After muckling around with the ZF i've decided to abstract a couple of classes to make this possible. 1. I've created a new directory structure within the ZF architecture library folder like below library ZendExt DB Table 2. Under the Table folder i added two new php files, Abstract.php and Select.php The code for

----------Abstract.php-----------

<?php class ZendExt_Db_Table_Abstract extends Zend_Db_Table_Abstract { //this class abstract the db_table from the Zend library //this allows us to append the schema name to the front of the column name. //Eg: SELECT SCHEMANAME.TABLENAME.COLUMNNAME FROM TABLENAME; public function select() { require_once 'ZendExt/Db/Table/Select.php'; return new ZendExt_Db_Table_Select($this); } } ?>

------------Select.php------------- <?php class ZendExt_Db_Table_Select extends Zend_Db_Table_Select { //Override function _renderColumns protected function _renderColumns($sql) { if (!count($this->_parts[self::COLUMNS])) { return null; } $columns = array(); foreach ($this->_parts[self::COLUMNS] as $columnEntry) { list($correlationName, $column, $alias) = $columnEntry; if ($column instanceof Zend_Db_Expr) { $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true); } else { if ($column == self::SQL_WILDCARD) { $column = new Zend_Db_Expr(self::SQL_WILDCARD); $alias = null; } if (empty($correlationName)) { $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true); } else { /* * @todo: * By: Huy Tran * Date: 20080206 * Description: This code here is for DB2 compatibility. This allow the appendtion of the schema to the front of the column name. * Eg. Select SELECT WEBSTUFF.ALIAS.ALNAME, WEBSTUFF.ALIAS.ALSID FROM WEBSTUFF.ALIAS */ $schema = $this->_parts[self::FROM][$correlationName]["schema"]; $columns[] = $this->_adapter->quoteColumnAs(array($schema.'.'.$correlationName, $column), $alias, true); } } } return $sql .= ' ' . implode(', ', $columns); } } ?>


  1. I then update the first 2 lines in my model from the DBTable folder to the following

require_once 'Zend/Db/Table/Abstract.php';

class Model_DbTable_Alias extends Zend_Db_Table_Abstract

TO

require_once 'ZendExt/Db/Table/Abstract.php';

class Model_DbTable_Alias extends ZendExt_Db_Table_Abstract

  1. And thats it. The whole purpose of this abstraction is to update the _renderColumns function within the Zend_Db_Table_Select to prepend the schema name to the column name.

Of course this is a temporary solution until Zend comes up with a fix. But so far this fix has allow me to fix the problem described in the ticket.

It seems to duplicate ZF-4553