Choosing an ODBC Zend PDO Database

Question

I am trying to adapt a Zend Skeleton application to use an ODBC connection. I can set up a PDO connection outside of Zend (same table, server, everything) as follows:

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 was 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 <databaseName>.<tableName> ends with double quotes when the request passes through Zend. I can’t explain why Zend is looking for my table under the username. However, I cannot get the PDO to recognize the table without a prefix, even though I tried to declare my database every time I initialized the PDO. I can think.

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

I apologize if I use the wrong language here. I get a little lost between Schema, Database, Library, File, Table, etc. when I go between SQL and iSeries.

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

Answer 1

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

Now that my library is correctly listed in my connection string, I don't need a table name prefix for my queries to work, although I never got Zend to accept the schema when building the TableGateway. I am going to mark this as an answer at the moment, but if anyone knows how to send the schema name to the new TableGateway, I would be happy to change that.

Answer 2

Not familiar with PDO since I use ibm_db2, but the database name can be found using the CL WRKRDBDIRE .

With * SQL, an unqualified table name ( SELECT * FROM TABLENAME ) implicitly qualifies with the user profile name. Therefore, if SARAHK makes a selection, it becomes SELECT * FROM SARAHK.TABLENAME . So the error you see indicates that IBM, I believe that you have an unqualified table name.

If you can configure the ODBC driver to use the * SYSTEM naming convention, it will use the list of libraries to search for unqualified table names.

I'm an old RPG programmer, so I'm more familiar with traditional names; here is the cheat sheet:

• Library → Scheme
• File → Table
• Member -> No SQL equivalent - use ALIAS

Answer 3

I just went through the process of converting a tutorial for connecting to IBM with ODBC driver, but I didn’t want to specify (aka hardcode) library in the connection string. Setting the "NAMING" parameter to "1" (* SYS, not * SQL) will use the * USRLIBL user connection / profile / JOBD (WRKJOBD INLLIBL (...)). If you use * SQL naming, it will use the user ID as a schema when searching for a table.

My DSN is as follows:

 'dsn' => "odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM= your host IP;HOSTNAME=your host;DATABASE=your dbname;NAMING=1" 

As long as the table I'm looking for is in * LIBL, I don't need to worry about the schema on the client.