Zend Framework 2 query to Oracle in incorrect format

Question

I guess that the way Zend use to build sql statement don't work with Oracle.

I'm using Oracle in my local Windows PC. My PHP works correct with Oracle already with oci8 extension, I configured following the link below:

http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html

I'm trying to connect ZF2 with Oracle. I'm using the tutorial code at Zend website:

http://zf2.readthedocs.org/en/latest/index.html#userguide

This code works perfectly with mySQL. I'm changing the database configuration to use Oracle instead.

Below is my config:

global.php

< ?php

return array( 'db' => array(

    'driver'         => 'Oci8',
    'host'         => 'localhost/orcl',
),

'service_manager' => array(
    'factories' => array(
        'Zend\Db\Adapter\Adapter'
                => 'Zend\Db\Adapter\AdapterServiceFactory',
    ),
),

);

?>

local.php (this work because I created a user 'test' with privileges on Oracle with, I tested this user with PHP and SQL script)

< ?php

return array(

'db' => array(
  'username' => 'test',
  'password' => 'test',
),

);

After configuring, I try to browse the url, the error appear say "table or view does not exist". This is Oracle error, it means Zend connected to Oracle but something wrong in SQL statement.

After some debug, I see the sql statement is:

SELECT "album".* FROM "album"

It' error because Oracle don't want to receive double quote.

I tried some hard-code in file /Zend/Db/Adapter/Driver/Oci8/Statement.php, function setSql line 112, change to:

public function setSql($sql)
    {
        $this->sql = $sql;
        $this->sql = "SELECT album.* FROM album";
        return $this;
    }

(remove double quote on query)

It's work!!!

I think there're some other configuration so make Zend work correctly.

Please help me! Thank you

Have you tried setting the table name to upper case? Seems to come up a few times from a quick google. — Aydin Hassan
Hi, I tried it. I think the problem is that Oracle don't receive double quotes, but Zend build the statement have these. — vietstone
@AydinHassan: Hi, you're right. Change all table names and field names to upper case will make the code work! Thank you — vietstone

Алексей Симаков Алексей Симаков · Accepted Answer · 2013-08-30T03:21:18

Same problem was here: ZF2 IBM

Solution: quote_identifiers == false:

   'db' => array(
    'driver' => $dbParams['driver'],
    'connection_string' => $dbParams['database'],
    'username' => $dbParams['username'],
    'password' => $dbParams['password'],
    'character_set' => $dbParams['character_set'],
    'platform_options' => array('quote_identifiers' => false)      
),

Zend Framework 2 query to Oracle in incorrect format

1 Answers