Zend Framework 2 - Oracle driver and limit() method

Question

Here's the situation: I'm using Zend Framework 2 to connect to an Oracle database. I've specified 'Pdo_Oci' as the driver for my Adapter. I'm using a Select object to build queries and execute them.

Everything went fine, until I tried to use the limit method on a Select object. Since I'm talking to Oracle, and Oracle has no LIMIT, I expected the framework to generate a sql string that makes sense to Oracle and emulates LIMIT (using ROWNUM, I guess).

However, the sql generated simply has the LIMIT keyword, which of course errors when executed since my database doesn't know what to do with it.

So, I see 2 possibilities:

1) My expectations are wrong and the limit() method is only a simple wrapper around LIMIT that can't be used with Oracle databases. Posts like this one seem to suggest that this is not the case, but I may be reading it wrong.

2) I'm doing something else wrong. Should I maybe be using another driver?

EDIT: In answer to ZF2 Student's question, but also in the hope of providing some more context for people to help me:

I'm not explicitly forming a connection string. I just have the necessary db config in local.php:

return array(
    'db' => array(
                'driver' => 'Pdo_Oci',
                'database' => 'my_database_host',
                'username' => 'my_user_name',
                'password' => 'my_password'

    ),  
);

In my global.php I set up an adapter factory:

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

);

And I retrieve the adapter in my controller like this:

$db = $this->getServiceLocator()->get('db');

So did You find a solution for this? What about sharing if so (by answering Your own question...)? — shadyyx

GertG GertG · Accepted Answer · 2013-05-08T09:13:12

I managed to resolve this, but only thanks to the 2.1 update to the Zend Framework. Apparently in 2.0 there wasn't an oci specific driver supplied. My 'pdo_oci' try was actually defaulting to the generic pdo driver.

With zf 2.1., I'm specifying my connection like this:

'db' => array(
            'driver' => 'oci8',
            'username' => 'myUser',
            'password' => 'myPW',
            'connection' => '(DESCRIPTION =
              (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = myPort))
              )
             (CONNECT_DATA =
              (SERVICE_NAME = myService)
             )
            )'
)

The 2.1 framework will then use the classes in the Zend\Db\Adapter\Driver\Oci8 namespace and they will correctly use the oci8 extension - which still needs to be enabled in your php.ini - and create the correct sql for the limit and offset methods.

I hope this helps someone else.

Zend Framework 2 - Oracle driver and limit() method

1 Answers