How to Query IBM i Data with PHP and PDO_ODBC
Of the PHP extensions that can connect to IBM i Db2, we recommend PDO_ODBC whenever possible.
Although the classic extensions (ibm_db2, PDO_IBM) and older ODBC continue to work, we prefer PDO_ODBC for several reasons:
- PDO_ODBC supports many databases and PHP frameworks
- PDO’s fetchAll() can retrieve all rows at once, to fetch small recordsets quickly
- The PHP Toolkit can connect over PDO_ODBC
- Supports stored procedure output parameters better than other extensions
Example script using PDO_ODBC to query the sample QCUSTCDT table
We included try/catch blocks to demonstrate good error handling.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php // check prerequisites if (!extension_loaded('PDO')) { exit("PDO extension not loaded\n"); } if (!extension_loaded('PDO_ODBC')) { exit("PDO_ODBC extension not loaded\n"); } // connect try { // NAM=1 means connect using system naming mode (enable library lists) $conn = new PDO('odbc:DSN=*LOCAL;NAM=1;UID=myusr;PWD=mypass',null, null, array( PDO::ATTR_PERSISTENT => false, // if no persistence PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // show errors as exceptions )); } catch (PDOException $exception) { echo $exception->getMessage(); exit; } // (try connection) $sql = "select * from QCUSTCDT where city = ?"; try { $query = $conn->prepare($sql); } catch (PDOException $exception) { echo $exception->getMessage(); exit; } // (try prepare) $city = 'Dallas'; // city of your choice $query->bindParam(1, $city); $query->execute(); $rows = $query->fetchAll(); print_r($rows); ?> |
Note about special characters
If the User ID or Password contains a semicolon (;), wrap the value in curly braces:
|
1 2 |
$pass = 'abc;4#'; new PDO("odbc:DSN=*LOCAL;NAM=1;UID={".$user."};PWD={".$pass."}"); |
More about PDO_ODBC
- PDO manual page from php.net
- A general PDO tutorial page we like (even though it targets MySQL)
- ODBC Connection Strings for IBM i Db2
- Our ODBC Q&A
- ibm_db2 & ODBC Run Side-by-Side in Seiden CP+ PHP
Leave a Reply
Want to join the discussion?Feel free to contribute!