ZF-4553: DB2 Adapter with DB2/400 support -- Several issues when using to connect to i5/DB2 from Linux

Issue Type:
Bug
Created:
2008-10-10T19:46:40.000+0000
Last Updated:
2011-01-20T08:06:09.000+0000
Status:
Open
Fix version(s):
    Reporter:
    Gil Rutkowski (grutkowski)
    Assignee:
    Ralph Schindler (ralph)
    Tags:
    • Zend_Db_Table
    Related issues:
    Attachments:

    Description

    Just started testing the DB2 adapter with support for DB2/400 from the Zend Core release of ZFW on my Linux machine and I'm running into issues with SQL statements being constructed incorrectly. Haven't had a chance to see if these same problems exist when running from ZC on i5, but I'm assuming they will.

    An example:

    
class Personnel extends Zend_Db_Table_Abstract
{
    protected $_name = 'SCHEMA.TABLE';
}

    $personnel = new Personnel();
    $personnel->fetchAll();

    This makes it through the tableDescribe just fine, but errors on the actual fetchAll with the error:

    Zend_Db_Statement_Db2_Exception: [IBM][CLI Driver][AS] SQL5001N "*N" does not have the 
authority to change the database manager configuration file. SQLSTATE=42703

    The sql DB2/400 is choking on is:

    

The following version of this select WILL work:

    SELECT "SCHEMA"."TABLE".* FROM "SCHEMA"."TABLE" SELECT "TABLE".* FROM "SCHEMA"."TABLE" as TABLE```

    If needed, I may be able to make both i5 ZC and Linux -> DB/400 environments available to aid in the tracking of this and any other DB/400 issues.

    Comments

    Posted by old of Satoru Yoshida (yoshida@zend.co.jp) on 2009-05-10T20:48:23.000+0000

    I think this issue has big impact on setTable() method of Zend_Db_Table_Select class.

    Posted by Ralph Schindler (ralph) on 2009-06-24T06:36:38.000+0000

    There are a couple of unknowns here: a) is there something specific to the linux client that is not being discussed here? b) it seems to me that this code runs fine when run locally on i5 c) what does the connection settings look like?

    -ralph

    Posted by Alex Frenkel (sirshurf) on 2009-07-28T06:20:23.000+0000

    couple of addon's.

    The same error I had using LOCAL on i5.

    The only solution was to user an alias for a SCHEMA/TABLE (last example).

    Posted by Christian Dubé (christiand) on 2010-02-02T03:50:42.000+0000

    as of ZF 1.10, using on local i5, this issue still exist.

    [code] <?php class Model_DbTable_Test extends Zend_Db_Table_Abstract {

    function getValues()
{
    $string = "SELECT MYFILE.* from MYLIB.MYFILE";  
    $result = $this->_db->fetchAll($string);
    return $result;
}

    } [/code]

    Will produce the following: Message: Column qualifier or table MYFILE undefined. SQLCODE=-5001

    but [code] <?php class Model_DbTable_Test extends Zend_Db_Table_Abstract {

    function getValues()
{
    $string = "SELECT MYFILE.* from MYLIB.MYFILE as MYFILE";    
    $result = $this->_db->fetchAll($string);
    return $result;
}

    } [/code]

    Will work!

    This also make Zend_Auth_Db_Adapter fail

    Posted by Richard Noya (richardnoya) on 2010-02-02T04:44:47.000+0000

    I Agree, I have a selfmade fix for months now. But it should be fixed in Zend Framework.

    Posted by James Pankratz (jbgisser) on 2011-01-19T07:50:06.000+0000

    @Richard - can you share your fix with us?

    I noticed one fix that was shared in the duplicate bug 5535 http://zendframework.com/issues/browse/ZF-5535

    Posted by James Pankratz (jbgisser) on 2011-01-19T09:50:19.000+0000

    Hello All.

    We solved this problem by coding table and schema names in UPPERCASE.

    This sample code did not work -> got same problem as reported above:

    <?php class Application_Model_DbTable_Application extends Zend_Db_Table_Abstract { protected $_name = 'table_name'; protected $_schema = 'schema'; }

    But this did work:

    <?php class Application_Model_DbTable_Application extends Zend_Db_Table_Abstract { protected $_name = 'TABLE_NAME'; protected $_schema = 'SCHEMA'; }

    I may not know what I'm talking about as I have only been working with PHP for a month, and just finished ZF training - but hoping this helps.

    Posted by Richard Noya (richardnoya) on 2011-01-20T08:06:06.000+0000

    My fix which I implement in every version for several months now is the following:

    in Zend\Db\Adapter\Abstract.php (see commented lines "Fix for i5")

    protected function _quoteIdentifierAs($ident, $alias = null, $auto = false, $as = ' AS ')
{
    if ($ident instanceof Zend_Db_Expr) {
        $quoted = $ident->__toString();
    } elseif ($ident instanceof Zend_Db_Select) {
        $quoted = '(' . $ident->assemble() . ')';
    } else {
        if (is_string($ident)) {
            $ident = explode('.', $ident);
        }
        if (is_array($ident)) {
            $segments = array();
            foreach ($ident as $segment) {
                if ($segment instanceof Zend_Db_Expr) {
                    $segments[] = $segment->__toString();
                } else {
                    $segments[] = $this->_quoteIdentifier($segment, $auto);
                }
            }

    /*Fix for i5 if ($alias !== null && end($ident) == $alias) { $alias = null; } */ $quoted = implode('.', $segments); } else { $quoted = $this->_quoteIdentifier($ident, $auto); } } if ($alias !== null) { $quoted .= $as . $this->_quoteIdentifier($alias, $auto); } return $quoted; }

    So by never setting the alias to null, it works like a charm for my DB2 database AND MySql.

    regards Richard.