Nov 22

Walking through a DB2 PF data and allow update.

I was thinking about some of the tools I would like to see on the IBMi and written in PHP that allow you to carry out the same functionality as the PHPMyAdmin tool on MySQL. Not that I wanted to write the entire product but at times I would like the ability to just review and/or update the data in a particular file. Sometime ago we started to publish sample programs in PHP that show the very effective capabilities of running PHP against IBMi data an objects, one such project was being able to display file data and the file column names in a table see this post. So we decided to take that one step further and add the ability to output the data to a web interface and then allow you to update that data or move to the next record.

The final solution is very simple in terms of code required to generate the interface (we did not make it pretty just functional) but it is a good stepping stone to take it to the next stage. This first iteration simply retrieves the file data and displays the column heading along-side the actual data in the field. We have provided a button to move to the next entry but not added the move back or update buttons although these will be very simple to add. The process works by having a sign in screen (index.php) fronting the process, if a request is made that has no valid credentials a sign on screen is displayed. If Valid credentials are supplied it will automatically pass in a file name and start point to the called page which in-turn calls the function shown below.

/*
* function Dsp_Pfm()
* display the contents of a file
* @parms
* File
* File Library
* Starting record
* returns the number of errors.
*/

function Dsp_Pfm_2($conn,$file,$flib,$id) {

// query to get the data frm the file
$query = "SELECT rrn(a) as RRN, a.* FROM " .rtrim($flib) ."/" .rtrim($file) ." a WHERE RRN(a) > '" .$id ."' FETCH FIRST ROW ONLY";
$result = i5_query($query,$conn);
if(!$result){
echo("Failed to get the data<br>" .$query);
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
}
$rec = i5_fetch_assoc($result);
echo("<table>");
// the assoc array contains the field names so we can use those as the headers
$i = 0;
foreach($rec as $key => $value) {
echo("<tr><td>" .$key ."</td><td><input type=text name=data" .$i ." id=data" .$i ." value='" .$value ."' /></td></tr>");
$i++;
}
echo("</table>");
// button to get the next record
$id++;
echo("<input type=button value=NEXT OnClick=location='dsp_pfm.php?file=" .$file ."&flib=" .$flib ."&id=" .$id ."' />");
i5_free_query($result);
return;
}

That code will produce something similar to the following.

Sample output of edit panel

Output from the function

As I have said we have not added the ability to edit and update the data in the file, but that would be a fairly simple option to add.

I would encourage those developers out there who have not experimented with PHP yet to start! The power provided by the ability to call SQL and provide interfaces which are not limited by 10 character column names and 80 column displays has to be something you want to use.. Our HA4i High Availability product is using some of the technology we have demonstrated in the various Blog posts to it best advantage.

Happy PHP’ing..

Chris…