Exploring the DB2 for i5/OS Extensions to the PHP Language
June 4, 2008 Erwin Earley
|
This is the fifth (and final) article in the PHP on i5/OS series. Previous articles in this series have looked at the structure of Zend Core in i5/OS, basic syntax of the PHP language, and the i5 Toolkit collection of APIs for accessing native i5/OS resources. This article will look at the extensions to the PHP language for accessing DB2 for i5/OS resources. The DB2 extensions can be broken down into a number of categories as follows:
This article will focus mainly on functions from the Server/Connection and Fetch categories. There are several differences between the i5 Toolkit APIs and the DB2 for i5/OS language extensions, including:
The difference in the location of the resources that can be accessed is summarized in the following diagram:
Connecting To a Database Resource Before looking at a specific example, let’s take a look at the different types of server connections that can be made with the db2_connect() function and the implication behind them. The db2_connect() function call expects three arguments–the server to connect to as well as the user profile and password to use for authentication. If no values (or NULL values) are provided for these parameters, then a connection is made to the local server:
db2_connect("","","");
When connecting without specifying a user profile and password, the database connection job is created using the same user as the Apache server profile, which in the case of a default Zend Core environment will be profile NOBODY (*USER). An authenticated connection can be made to the local server by providing the name “*LOCAL” for server and then a specific user profile and password as shown in this example:
db2_connect("*LOCAL","ERWIN","PASSWORD");
In this case the database connection is created under the user profile ERWIN (assuming the password provided is valid). A connection can be made to a remote database server by providing the network name of the server in the db2_connect() function call:
db2_connect("10.1.2.15","ERWIN","PASSWORD");
In this case a connection will be made to the database resource on 10.1.2.15 using the user profile ERWIN on that server with the password of PASSWORD. Exploring the DB2 for i5/OS Language Extensions Unlike other articles in this series, this article is going to take a single example and build upon it throughout the article. The example will create an employee information search and retrieval form. Let’s start by taking a look at the PHP code that will generate the employee search form.
<html>
<?php
define("PAGE_TITLE", "Employee Update");
define("PHP_FILE_NAME", "employee_update.php")
?>
<title><?= PAGE_TITLE . " - " . PHP_FILE_NAME; ?></title>
<body>
<?php
?>
</body>
</html>
Running the above block of code through a browser would generate a blank Web page with a title bar of “Employee Update – employee_update.php”. Now let’s add the code that will display the Employee Search form. The following block of code would be placed between the second <?php tab and the corresponding ?> tag.
Note: The code in the above graphic is available for download here. This block of code simply outputs in the HTML stream instructions for what is to be entered into the form along with the HTML to generate the form information. When executed, the PHP will cause the following HTML form to be generated:
The code we’ve developed so far should be saved as “employee_update.php”. Search Action Now that we have the form for the search request coded, we are ready to take a look at the code for the actual search action. The next section of code will establish the connection to the database server and it would be placed between the second <?php tag and the /*Display comment
Note: The code in the above graphic is available for download here.
Query The next section of code to look at will perform a query on the EMPLOYEE table and place the results onto the Employee Search Results form. The following code snippit would be placed between the</form> line and the second ?> PHP end tag:
Note: The code in the above graphic is available for download here. Let’s take a closer look at this code:
Note: Using the db2_exec() statement to insert PHP variable values into an SQL statement can be considered a security exposure. Rather then using the db2_exec() statement, you might want to consider calling the db2_prepare() statement to prepare an SQL statement with parameter markers for input values. Then the db2_execute() statement could be used rather then db2_exec() statement to pass in the input values. Now when you invoke the PHP file from a Web browser (after saving the changes), the search form will be displayed as shown earlier in this article. Entering data in the Last Name field and then selecting the Search button will cause the just coded search code to be executed and display a result page similar to the following:
The next part of the example would be to add the code to edit an Employee record. This code would be invoked when an employee number is selected from the search results window. The following block of code should be placed between the $action = $_POST[“action’] and if (!$action) lines:
Note: The code in the above graphic is available for download here.
Edit Record The next portion of code works with displays the values of a record and allows the user to edit those values. The following snippit of code would be placed into the PHP application between the bracket line and the second ?> PHP end tag.
Note: The code in the above graphic is available for download here.
It should be noted that the print ‘<form>’ line along with the print ‘<input> line will cause the program to place the “Update” action onto the URL line when the Update button is selected. Each of the field values are also placed into the URL string. This causes the application to “call” itself once again. The URL string values are retrieved using the $_GET function and then used in the SQL update call that will be discussed in the next part of the article. Now when you invoke the PHP program (after saving it) from a Web browser searching for a record and then select one of the returned records from the Employee Search Results form, it will display the Employee Edit Record form with the values from the selected record:
Record Update The next section of code is invoked when the user has selected the Update button from the Edit page. This code updates the DB2 for i5/OS table using the user modified fields. Once the record has been updated an HTML form is output that simply echoes out the SQL statement along with an indication of whether or not the update was successful. The form will include a Continue button that, as we will see from the code, resets the application and returns to the Search form. The first thing we need to do is determine the state of the application. The following lines of code should replace the $action = $_POST[“action”] line of code that can be found just prior to the $customerNumber= $_GET[“customerNumber”] statement:
$action = $_GET["action"];
/* if no value from $_GET, try $_POST */
if (!$action) {
$action = $_POST["action"];
}
Additionally, the section of code that resembles the following:
}
} else { /* Edit the selected customer number record */
Should be changed to resemble the following (i.e., remove the beginning close bracket on the else statement):
}
else { /* Edit the selected customer number record */
The following block of code should be placed into the employee_update.php file. This code is placed before the else { /* Edit the selected customer number record */ statement:
Note: The code in the above graphic is available for download here.
Note: In addition to inserting the above code snippit you will also need to add a line containing a closing bracket before the second ?> PHP end tag at the bottom of the application. Once all of these changes have been made you should save the file. Once the file has been saved, execute the program from your browser, search for a set of records, select a record, edit the values, and select the Update button. Upon a successful update a Web page similar to the following should be displayed:
Continue The final section of code that we need to take a look at is the code to process selection of the Continue button and reset the application back to the Employee Search form. The if (!$action) line of the following snippit of code:
/* Determine which form to display based upon the state of application */
if ($customerNumber == "") {
if (!$action) {
needs to be changed to test for the Continue action as follows:
if ((!$action) or ($action == "Continue")) {
The check for the Continue action will cause the Search form to be displayed when the Continue button is selected from the Update database form. That’s it. Save the file and re-execute the PHP application. Now going through the process of searching for records, selecting a returned record, editing the fields, selecting update, and then selecting Continue should return you back to the Search form. Conclusion In this article we have put together a complete PHP application that allows a user to search a database for records matching a specified selection criteria and update records from the returned set. I hope this article has provided you with some insight into the possibilities that PHP provides for developing PHP based applications that leverage DB2 for i5/OS resources. Thanks for reading this series of articles on PHP on i5/OS and taking the journey with me on exploring some of the features that PHP on i5/OS provides for developing and deploying Web-based applications that leverage both i5/OS native resources as well as DB2 for i5/OS resources. I hope that this series has been of interest to you and you will continue to explore the ways that PHP can be used in your shop. Erwin Earley is an advisory software engineer in the IBM lab located in Rochester, Minnesota, and heads up the Open Source Technologies Center of Competency for System i within the System i Technology Center. At that center, he provides education and enablement services for open source related technologies on System i including Linux, MySQL, and Zend’s PHP. Earley currently holds certifications from Red Hat as well as the Linux Professional Institute and is a candidate for certification with Zend’s PHP. RELATED STORIES Bringing i5/OS Resources to the Web Use PHP to Bring i5/OS Resources to the Web PHP: An Easy Yet Powerful Language Syntax PHP on i5/OS: A Whole New Stack
|
