Issues

ZF-5085: Zend_Db_Adapter_Pdo_Oci and Oracle uppercase tablename trouble

Description

By default Oracle turn all table names and column names in uppercase in sys tables.

so in SQL when you reference table with unquoted names, Oracle turn them to uppercase.

It's the contrary in Mysql...

so when you use Zend_Db_Table and specify a table name (protected $_name='foo') it work's fine with Pdo_Mysql but it dosn't work with Pdo_Oci because table 'foo' is in fact table 'FOO' in Oracle sys tables.

So there is a difference of behavior... and this is bad ;p

turning the table name to uppercase in Zend_Db_Adapter_Pdo_Oci::describeTable() could fix it but...

In Oracle you can define case sensitive table names and column names....

so table name should be turn uppercase only if option Zend_Db::CASE_FOLDING is set to Zend_Db::CASE_LOWER or Zend_Db::CASE_UPPER wich could mean user don't use CASE_NATURAL in table names or column names and expect default behavior.

same for schema...

here is the modification :


   public function describeTable($tableName, $schemaName = null)
    {
        
        switch ($this->_caseFolding) {
            case Zend_Db::CASE_LOWER: case Zend_Db::CASE_UPPER:
                $tableName=strtoupper($tableName);
                if($schemaName) $schemaName=strtoupper($schemaName);
            break;
        }
...

Comments

Do you use parameter autoQuoteIdentifier with Oracle? See http://framework.zend.com/manual/en/… for more informations

yes,

I always set it to false

let's try with true...

Zend_Db::AUTO_QUOTE_IDENTIFIERS false or true, the $tableName passed to Zend_Db_Adapter_Pdo_Oci::describeTable() is always the value set in Zend_Db_Table


class Table_Foo extends Zend_Db_Table
{
    protected $_name='foo'; // <== this value is passed to Zend_Db_Adapter_Pdo_Oci::describeTable()
}

so with 'foo' Zend_Db_Adapter_Pdo_Oci::describeTable() just end up with

'Zend_Db_Table_Exception' with message 'A table must have a primary key, but none was found'

it's logic the query to retrieve columns is binded with lowercase 'foo'.


...
$bind[':TBNAME'] = $tableName;
..

but it work's great if you specify 'FOO'


class Table_Foo extends Zend_Db_Table
{
    protected $_name='FOO'; // <== this value is passed to Zend_Db_Adapter_Pdo_Oci::describeTable()
}

or use strtolower ;p

Resolved in SVN12980 and merged to 1.7-branch