A database access class or function might be in order.

Editor's Note: This article is an excerpt from the MC Press book The IBM i Programmer's Guide to PHP. 

Author's Note: You must have the Zend Core or Zend Platform installed on your System i in order for these scripts to work correctly.

To encourage native System i developers to make the switch to PHP, the Zend Core provides some data access methods that closely resemble native file access on the System i. If you're used to using SETLL, READ, CHAIN, and UPDATE in RPG, you may find these data access methods more intuitive than other alternatives. We applaud this effort to encourage RPG developers to use PHP, but there are some things you should think about before starting down this path. 

If you use the IBM_DB2 access methods (covered in chapter 8), you can run your scripts unchanged on any system that has PHP and DB2 installed. Linux, Windows, or IBM i will all run these scripts. Code written using the record-level access methods, however, is tied to the System i. If your applications will be used only on a System i, you don't have anything to worry about and can use either method. If you want platform-independent code, however, stick with the IBM_DB2 methods.

Sequential File Access

Let's start by reading a file, beginning with the first record in the file. This is a simple exercise and one that all of us who were writing RPG when using the program cycle was still acceptable have certainly done before. The script in Figure 1 shows how you do it in PHP.

<?php

if (!$i5Rsc = i5_connect( "LEGATO3", "jolen", "abc123"))

   die (i5_errormsg());

else

   echo "Connected successfully. <br>";

if (!$i5File = i5_open("qsys2/syscolumns", I5_OPEN_READ, $i5Rsc)) {

   echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

   $arr = error_get_last();

   die("php error: ".var_dump($arr));

}

else

   echo "Created file access resource. <br>";

$rcdCount = 0;

if ($arr_data = i5_fetch_assoc( $i5File, I5_READ_FIRST )) {

   do {

        $rcdCount++;

        echo "<table border=1><tr><th>Field Name</th><th>Value</th></tr> ";

        foreach ( $arr_data as $name=>$value ) {

               echo "<tr><td>".$name.":</td><td>".$value."</td></tr> ";

        }

        echo "<br><br>";

   } while ( ($arr_data = i5_fetch_assoc( $i5File, I5_READ_NEXT )) &&

                    ( $rcdCount < 5) );

}

i5_close($i5Rsc);

?>

Figure 1: Sequential System i file access in PHP

As we break down this code, you'll see many of the same steps we performed when we used the IBM_DB2 access methods. First, we create the connection. Then, we open the file using the i5_open function and create a file resource ($i5File). The i5_open function accepts three parameters, and we use all of them here. The first parameter is the qualified file name in "normal" IBM i notation. Second is the access type, which in this case is read-only (I5_OPEN_READ). Table 1 lists the predefined constants for access types. The third parameter specifies the connection resource we created in the first step.

Table 1: Access type constants

Note that the only true access types are I5_OPEN_READ and I5_OPEN_READWRITE. The other constants represent file-locking choices that can be combined with the access types.  For example, this next bit of code sets the read-only access to read-only access with an exclusive lock on the file.

if (!$i5File = i5_open("qsys2/syscolumns",

                       I5_OPEN_READ | I5_OPEN_EXCL, i5Rsc)) {

  echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

  $arr = error_get_last();

  die("php error: ".var_dump($arr));

}

Once we've successfully created the file resource, we can immediately try to read from it. Table 2 lists the options available for read type.

Table 2: Read type constants

If the file is keyed, the I5_READ_FIRST and I5_READ_LAST constants cause either the first or the last record in the file to be read using the key sequence; if the file is not keyed, the read will use the physical record sequence. After specifying I5_READ_SEEK, you can use I5_READ_NEXT to continue reading records in key sequence from the "seek" record pointer. But we're getting ahead of ourselves.

When retrieving the resulting data, we have the same data type options as we did when using the db2_* functions. Table 3 lists the functions available for retrieving a row. After retrieving the file data, the sample script simply displays the field name and value in an HTML table.

Table 3: Row retrieval functions

Indexed File Access

So, you can now read a file from beginning to end if you want. Most of the time, though, you're going to want to access specific records, so the next example changes things up a bit. The script shown in Figure 2 uses the i5_seek function to set the record pointer in a manner similar to RPG's SETLL opcode. The QADBIFLD file referenced in this code is a system file that holds the names and attributes of all the database fields on the system. It is keyed on library name, file name, and other fields. For this example, we use just the library and file names. In the part of the sample script that loads the $keys array, you'll need to change testlib and testfile to match an actual library and file on your system.

<?php

if (!$i5Rsc = i5_connect( "LEGATO3", "jolen", "abc123"))

     die (i5_errormsg());

else

     echo "Connected successfully. <br>";

if (!$i5File = i5_open("qsys/QADBIFLD", I5_OPEN_READ)) {

     echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

     $arr = error_get_last();

     die("php error: ".var_dump($arr));

}

else

     echo "Created file access resource. <br>";

$rcdCount = 0;

$keys = array( "TESTLIB", "TESTFILE" );

if (!i5_seek( $i5File, ">=", $keys )) {

     echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

     $arr = error_get_last();

     die("php error: ".var_dump($arr));

}

if ($arr_data = i5_fetch_assoc( $i5File, I5_READ_SEEK )) {

     do {

          $rcdCount++;

          echo "<table border=1><tr>

                     <th>Field Name</th><th>Value</th></tr> ";

          foreach ( $arr_data as $name=>$value ) {

                  echo "<tr><td>".$name.":</td><td>".$value."</td></tr> ";

          }

          echo "<br><br>";

  } while ( ($arr_data = i5_fetch_assoc( $i5File, I5_READ_NEXT )) &&

            ( $rcdCount < 5) );

}

i5_close($i5Rsc);

?>

Figure 2: Indexed System i file access in PHP

Your output should list the first five fields in the file you specify. Try experimenting with the i5_seek function, using it the same way you would SETLL.

Accessing a File by Record Number

Zend Core for i5/OS provides a third file access method: the function i5_data_seek, which accesses a file by record number. We don't usually use this method, but, in the interest of being thorough, we include it here. To test this technique, one change is required in the preceding sample script. Change the line that reads...

if (!i5_seek( $i5File, ">=", $keys )) {

...to...

if (!i5_data_seek( $i5File, 1 )) {

Once you've made this change, run the script again. Unless, by some fluke, the library and file you selected are the first physical records in the file (which is highly unlikely), you should obtain different results. You can execute the DSPPFM (Display Physical File Member) command to verify your results. Look closely, and you may notice that the first returned row is in fact the first physical record, but the following rows are not the second through fifth rows. This is because while the first READ returns the row pointed to by the i5_data_seek function, subsequent READs are still performed in key sequence. If you want to continue READing in sequential order from the first physical row, make the following two changes to your updated script.

First, replace these lines...

$rcdCount = 0;

$keys = array( "TESTLIB", "TESTFILE" );

if (!i5_data_seek( $i5File, 1 )) {

     echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

     $arr = error_get_last();

     die("php error: ".var_dump($arr));

}

...with...

$rcdCount = 1;

$keys = array( "TESTLIB", "TESTFILE" );

if (!i5_data_seek( $i5File, $rcdCount )) {

     echo "errmsg: ".i5_errormsg($i5Rsc)."errno: ".i5_error($i5Rsc)."<br>";

     $arr = error_get_last();

     die("php error: ".var_dump($arr));

}

Then replace...

  } while ( ($arr_data = i5_fetch_assoc( $i5File, I5_READ_NEXT )) &&

            ( $rcdCount < 5) );

...with...

       if (!i5_data_seek( $i5File, $rcdCount )) {

            echo "errmsg: ".i5_errormsg($i5Rsc).

                 "errno: ".i5_error($i5Rsc)."<br>";

            $arr = error_get_last();

            die("php error: ".var_dump($arr));

       }

  } while ( ($arr_data = i5_fetch_assoc( $i5File, I5_READ_SEEK )) &&

            ( $rcdCount <= 5) );

This revision should give you the first five rows in the file.

Adding and Updating Records

Being able to access your files is good, but sooner or later, you're going to want to update them. When you add and update rows using the Zend methods, things work in a little different sequence from what we RPG developers are used to. Let's go through an example of adding some rows to a file. After that, we'll update one of these rows.

Before we can add rows, we need to create a work file to use. Figure 3 shows the DDS for the test file BTNTEST.

A          R BTNR                      TEXT('BTNTEST FILE')

A            BTN           15A         COLHDG('BTN')

A            FNAME         50A         COLHDG('FIRST NAME')

A            LNAME         50A         COLHDG('LAST NAME')

A

A          K BTN

Figure 3: DDS source for file BTNTEST

To compile the DDS, execute the following command (replacing testlib with your own library name):

CRTPF FILE(testlib/BTNTEST)    +

      SRCFILE(testlib/QDDSSRC) +

      SRCMBR(BTNTEST)

The script shown in Figure 4 demonstrates the steps necessary to add a row and load it with data.

<?php

// function to add an empty row

function AddNewRow ( $i5File ) {

  if (!i5_addnew($i5File, I5_ADDNEW_CLEAR)) {

    echo "errmsg: ".i5_errormsg($i5Rsc)."errno:"

                  .i5_error($i5Rsc)."<br>";

    $arr = error_get_last();

    die("php error: ".var_dump($arr));

  }

}

// main script

// create connection

if (!$i5Rsc = i5_connect( "LEGATO3", "jolen", "abc123"))

  die (i5_errormsg());

else

  echo "Connected successfully. <br>";

// create file resource

if (!$i5File = i5_open("jeffo/btntest", I5_OPEN_READWRITE)) {

  echo "errmsg: ".i5_errormsg($i5Rsc)."errno:

       ".i5_error($i5Rsc)."<br>";

  $arr = error_get_last();

  die("php error: ".var_dump($arr));

}

else

  echo "Created file access resource. <br>";

// add new rows

AddNewRow( $i5File );

i5_setvalue($i5File, "BTN", '7145553799');

i5_setvalue($i5File, "FNAME", 'Jeff');

i5_setvalue($i5File, "LNAME", 'Olen');

i5_update( $i5File );

AddNewRow( $i5File );

i5_setvalue($i5File, "BTN", '7145556019');

i5_setvalue($i5File, "FNAME", 'Marlin');

i5_setvalue($i5File, "LNAME", 'Olen');

i5_update( $i5File );

AddNewRow( $i5File );

i5_setvalue($i5File, "BTN", '7145554145');

i5_setvalue($i5File, "FNAME", 'Brooke');

i5_setvalue($i5File, "LNAME", 'Morris');

i5_update( $i5File );

// close connection

i5_close($i5Rsc);

?>

Figure 4: Adding rows

Adding rows using the Zend methods differs from adding records in RPG in that you first must add an "empty" row, then load the data, and then update the row. In fact, because of how the add function works, you already have a bit of an introduction to the way you update records, too. The sample script uses three new functions: i5_addnew, i5_setvalue, and i5_update. As used here, these functions are fairly self-explanatory. However, the i5_setvalue function supports several different ways to update field data that are worth covering in detail.

The first technique is the one employed in the example: specifying each field name (or ordinal position) and value individually. When you use this approach, each field to be updated requires a separate i5_setvalue statement. Rather than the field name, we could have specified the ordinal position of each field in the file. The code snippet shown in Figure 5 demonstrates this alternative. This code works exactly the same as the code in the original script.

  .

  .

  .

// add new rows

AddNewRow( $i5File );

i5_setvalue($i5File, 0, '7145553799');

i5_setvalue($i5File, 1, 'Jeff');

i5_setvalue($i5File, 2, 'Olen');

i5_update( $i5File );

AddNewRow( $i5File );

i5_setvalue($i5File, 0, '7145556019');

i5_setvalue($i5File, 1, 'Marlin');

i5_setvalue($i5File, 2, 'Olen');

i5_update( $i5File );

AddNewRow( $i5File );

i5_setvalue($i5File, 0, '7145554145');

i5_setvalue($i5File, 1, 'Brooke');

i5_setvalue($i5File, 2, 'Morris');

i5_update( $i5File );

  .

  .

  .

Figure 5: Adding rows by specifying field ordinal positions

Adding rows this way is a bit laborious. Fortunately, the i5_setvalue function also lets you use an associative array of field names and values. To use this method, you would substitute the code shown in Figure 6 for the "add new rows" section of the preceding script.

  .

  .

  .

// add new rows

AddNewRow( $i5File );

$fldData = array( "BTN"=>"7145553799",

                  "FNAME"=>"Jeff",

                  "LNAME"=>"Olen"      );

i5_setvalue($i5File, $fldData);

i5_update( $i5File );

AddNewRow( $i5File );

$fldData=  array( "BTN"=>"7145556019",

                  "FNAME"=>"Marlin",

                  "LNAME"=>"Olen"      );

i5_setvalue($i5File, $fldData);

i5_update( $i5File );

AddNewRow( $i5File );

$fldData=  array( "BTN"=>"7145554145",

                  "FNAME"=>"Brooke",

                  "LNAME"=>"Morris"    );

i5_setvalue($i5File, $fldData);

i5_update( $i5File );

  .

  .

  .

Figure 6: Adding rows using an associative array

Again, the end result is exactly the same. Are you wondering whether a database access class or function might be in order? If so, good thinking.

Let's move on now to updating an existing record. We'll use the i5_update function again, combining it with some new i5_* functions. The script shown in Figure 7 changes the telephone number for one of our existing rows.

<?php

if (!$i5Rsc = i5_connect( "LEGATO3", "jolen", "abc123"))

  die (i5_errormsg());

else

  echo "Connected successfully. <br>";

if (!$i5File = i5_open("jeffo/btntest", I5_OPEN_READWRITE)) {

  echo "errmsg: ".i5_errormsg($i5Rsc)."errno:

                ".i5_error($i5Rsc)."<br>";

  $arr = error_get_last();

  die("php error: ".var_dump($arr));

}

else

  echo "Created file access resource. <br>";

$rcdCount = 0;

$keys = array( "7145556019" );

// retrieve record

if (!i5_seek( $i5File, ">=", $keys )) {

  echo "errmsg: ".i5_errormsg($i5Rsc)."errno:

                ".i5_error($i5Rsc)."<br>";

  $arr = error_get_last();

  die("php error: ".var_dump($arr));

}

if ($arr_data = i5_fetch_assoc( $i5File, I5_READ_SEEK )) {

  // lock record for edit

  if (!i5_edit($i5File, I5_EDIT_ONE)) {

    echo "errmsg: ".i5_errormsg($i5Rsc)."errno: "

                  .i5_error($i5Rsc)."<br>";

    $arr = error_get_last();

    die("php error: ".var_dump($arr));

  }

  // set new values

  i5_setvalue($i5File, "FNAME", "Scott");

  i5_setvalue($i5File, "LNAME", "Thomas");

  // write updates

  i5_update($i5File);

}

// close file

i5_close($i5Rsc);

?>

Figure 7: Updating an existing row

This script changes the name on the record whose BTN field has the value 7145556019.  In a production script, you obviously would want to add code to verify that you retrieved the correct record. Or you could change the second i5_seek parameter from >= to =. Just like a SETLL operation, the i5_seek function executes successfully even when it doesn't position to a record. However, the i5_fetch_* functions will fail, just like a READ after a SETLL that positions to the end of the file.