Search code examples
pdodb2odbczend-framework2ibm-midrange

Zend PDO ODBC Database selection

I am trying to adapt the Zend Skeleton App to use an ODBC connection. I am able to set up a PDO connection outside of Zend (same table, server, everything) like this:

new PDO('odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=<serverName>;HOSTNAME=<serverName>;DATABASE=<databaseName>;', 
            '<userName>', '<password>');
$r = $this->conn->query('SELECT * FROM <databaseName>.<tableName>');

But when I add this information to the global.php file:

'db' => array(
    'driver'         => 'Pdo',
    'dsn'            => 'odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=<serverName>;HOSTNAME=<serverName>;DATABASE=<databaseName>;',
),

And in local.php:

return array(
    'db' => array(
        'username' => '<userName>',
        'password' => '<password>',
    ),
);

I get an error that the table is not found:

SQLSTATE[42S02]: Base table or view not found: 0 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - <tableName> in <userName - yes you read that right> type *FILE not found.

I believe this is because my prefixed <databaseName>.<tableName> is being wrapped in double quotes when the query runs through Zend. I cannot explain why Zend is looking for my table under userName. However, I cannot get PDO to recognize the table without the prefix, even though I have tried declaring my database in the initialization of the PDO every way I can think of.

Is there a way for PDO to actually pick up the database name so I don't need the prefix? Or is there a way to tell Zend to use the prefix (not lumped in the quotes with the table name)?

Please pardon if I'm using the wrong language here - I get a little lost between Schema, Database, Library, File, Table, etc. when I'm going between SQL and iSeries.

I really appreciate any help you can offer, Zend is new to me.

Solution

  • I solved this by finally finding an addition to my DSN that properly specified the library/schema I wanted to work under. This site (http://www.sqlthing.com/HowardsODBCiSeriesFAQ.htm) gave me the parameter DBQ=<libraryName> which finally worked for me after trying DATABASE, DBNAME, and many others.

    Now that my library is properly specified in my connection string, I don't need to prefix the table name so my queries work even though I never got Zend to take a schema in the construction of a TableGateway. I'm going to mark this as an answer for now, but if anyone knows how to send a schema name to a new TableGateway I would be happy to change that.