Did you know that you can navigate the posts by swiping left and right?

Accessing HarrisData (IBM iSeries) In PHP

09 Nov 2008 . category: blog . Comments
#AS/400 #HarrisData #iSeries #ODBC #PDO #PHP

HarrisData I have recently been working on a project to integrate with a HarrisData ERP Application. The Application runs on an IBM iSeries (AS/400). I used PHP’s built in PDO object with the ODBC extension. Using PDO, I was able to execute SQL Queries on the server. IBM systems use IBM’s DB2 syntax. This entry will outline some of the basic steps, in PHP, to communicate with the system

I will be assuming that you already have the ODBC driver installed and setup. I will also be assuming that your ODBC connection is called “AS400”.

I created a class called HarrisData. The constructor creates a PDO Object and assigns it to a class property called $database.

$this->database = new PDO('odbc:AS400', 'USERNAME', 'password');

Method in the class can use this object to run queries on the database. For example, let’s say we want the first 10 rows of a table called “HDCUST”.

$query = 'SELECT * FROM DBNAME.HDCUST FETCH FIRST 10 ROWS ONLY';
$results = $this->database->query($query);

// Check to make sure the query did not fail.
if (!$results) {
    return false;
}

while ($row = $results->fetch()) {
    // Do stuff...
}

I had to modify the columns being pulled several times. To make this easier and make the code cleaner and easier to maintain, I created an array of columns to be selected before each select statement. Note that I have placed the column names in the array in my example, but I used constants for the actual code.

$select_array = array(
  'CMBLTO',
  'CMUDF3',
  'CMCMLU',
  'CMCUST',
  'CMSLTO',
);

$query = 'SELECT ' . implode(',', $select_array) . ' FROM DBNAME.HDCUST FETCH FIRST 10 ROWS ONLY';
$results = $this->database->query($query);

That’s all there is to it. I have not had a need to write to the database at this time. If the need should arise, I will be sure to write a new entry about some of the techniques I used.


Me

James Armes is a software engineer and open source enthusiast from central Pennsylvania.