IBM i Development with PHP: Communicating With Power Servers Using PHP and PDO

Power Servers are powerful machines that allow you to run enterprise level business functions and processes. They host the RPG langugage and are home to applications needed for the business to run. Many of these programs are run internally by employees for various different purposes, but what if we have data or processes that we want to expose to others through the web? Well the Power Server family can do that too. This guide will walk you through utilizing SQL stored procedures, called by a PHP script to read and write data using an RPGLE program, and deliver information back to the page. This will take the form of a deposit-withdrawal program that simulates depositing and withdrawing money at a bank. All the code for this project can be found at my GitHub page.

This guide will assume you have set up your environment to do PHP on a Power Server. If you have not set up your environment, please follow my guide linked here. It will also assume some knowledge of RPG, PHP, HTML, SQL, and Javascript.

In this blog post, you will learn how to:

Write a web page in HTML, PHP, and leverage Javascript for validation and dynamic loading of values on the page.

Write Stored Procedures that can be called from the web page which will query the database, call RPG programs, and return data to the page that called the Stored Procedure.

Write an RPG Program that will accept parameters through a procedure interface, accept sent parameters from an SQL Stored Procedure, process those values using an SQL table, and return a value to the Stored Procedure and back to the calling page.

1. Create An SQL Table to Save Transactions.

The first thing we need to do is create a Table that will hold records of our transactions. For this demonstration, we will be keeping things bare bones to explain the concepts in a focused manner. Because of this, we will create a table with only 3 fields. A transaction type field, an amount field, and a transaction time field.

The transaction type field will be a single character field that determines whether a transaction is a deposit ‘D’ or withdrawal ‘W’.

The amount field will be a decimal with 15 precision and 5 scale. This will be to hold the amount withdrawn or deposited. You may be asking “Why 15 precision and 5 scale?”. Well, when it comes to calls and the IBM i, any time a decimal value is passed to a call, it is required that that decimal be passed as a 15:5.

“Decimal constants are passed in packed form and with a length of LEN(15 5), where the value is 15 digits long, of which 5 digits are decimal positions. Thus, if a parameter of 12345 is passed, the receiving program must declare the decimal field with a length of LEN(15 5); the parameter is received as 12345.00000.” -IBM.com

It is because of this, that I have been declaring my passed and received numeric parameters and 15:5.

The transaction time field will serve as the key for our table. Because we are tracking the transaction time down to the microsecond, we can assure that this will always be a unique value. If we were to make this a multiple user application, which we may do in the future, we will need to add a UserId field to ensure uniqueness.

Now with some of the explaining out of the way, let’s go ahead and create our table.

Start ACS and open SQL Scripts.

SQL Scripts is included with ACS(Access Client Solutions), it allows for writing and executing SQL statements.

CREATE TABLE YourLibrary.BALANCETAB ( 
	TRANSTYPE CHAR(1) CCSID 273 NOT NULL , 
	AMOUNT DECIMAL(15, 5) NOT NULL , 
	TRANSTIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
	CONSTRAINT YourLibrary.Q_YourLibrary_BALANCETAB_TRANSTIME_00001 PRIMARY KEY( TRANSTIME ) )   
	  
	RCDFMT BAL        ;

This statement creates a table with three fields that were described before. You will have to input your own library where “YourLibrary” is indicated. It consists of 3 fields that are not allowed to be null, as that would create problems for us if we did.

We have created the TransTime field to have a default value, so an insert using SQL will automatically populate the TransTime field with the current date and time. Because we are using RPG, we cannot pass a null TransTime value on write, so we will have to generate that in our program that you will see later.

We have also created a record format name that differs from the table name as indicated by ‘RCDFMT BAL;’. This is because RPG requires a different record format name than the name of the table to write to the table.

2. Write the Page to Submit the Request and Receive the Information.

To begin this process, open VS Code. Create a new file and name it ‘Transact.php’ and save it in your PHP root folder, for example C:\php-8.4.1. By saving in this location, you can ensure that your PHP operates as a PHP file when you start your localhost. From here, we will create our form using HTML.

<body>
    <h1>Transaction Processor</h1>

    <h2>Bank Transaction Form</h2>
    <form id="transactionForm" method="POST" onsubmit="return validateForm()">
        <label for="transactionType">Transaction Type:</label>
        <select id="transactionType" name="transactionType">
            <option value="deposit">Deposit</option>
            <option value="withdrawal">Withdrawal</option>
        </select>
        <br><br>

        <label for="amount">Amount:</label>
        <input type="text" id="amount" name="amount" required>
        <br><br>

        <input type="submit" value="Submit">

    <br><br>
    <label for="balance">Current Balance:</label>
    <input type="text" id="balance" name="balance" readonly>
    <br><br>
    </form>

Let’s break down the individual elements of the form.

1. Title and Header:

<h1>Transaction Processor</h1>
<h2>Bank Transaction Form</h2>

These elements simply provide titles for the form, making it clear to the user that this is a transaction processor and a bank transaction form.

2. Form Tag:

<form id="transactionForm" method="POST" onsubmit="return validateForm()">

id="transactionForm": Assigns a unique identifier to the form.

method="POST": Specifies that the form data will be sent using the POST method when submitted.

onsubmit="return validateForm()": Attaches a JavaScript function validateForm() to the form’s submit event. This function is expected to validate the form’s inputs before submission. If it returns false, the form will not be submitted. The function will be added a little later in this tutorial.

3. Transaction Type Dropdown:

<label for="transactionType">Transaction Type:</label>
<select id="transactionType" name="transactionType">
    <option value="deposit">Deposit</option>
    <option value="withdrawal">Withdrawal</option>
</select>

<label for="transactionType">: Associates the label with the transactionType dropdown.

<select id="transactionType" name="transactionType">: Creates a dropdown menu for selecting the transaction type, with two options: “Deposit” and “Withdrawal”.

4. Amount Input Field:

<label for="amount">Amount:</label>
<input type="text" id="amount" name="amount" required>

<label for="amount">: Associates the label with the amount input field.

<input type="text" id="amount" name="amount" required>: Creates a text input field for the user to enter the transaction amount. The required attribute ensures that this field must be filled out before the form can be submitted.

5. Submit Button:

<input type="submit" value="Submit">

Creates a button that, when clicked, submits the form.

6. Current Balance Read-only Field:

<label for="balance">Current Balance:</label>
<input type="text" id="balance" name="balance" readonly>

<label for="balance">: Associates the label with the balance input field.

<input type="text" id="balance" name="balance" readonly>: Creates a text input field that is read-only, meaning the user cannot modify its content directly. It is likely intended to display the current balance.

After entering the code, go ahead and save the file in your PHP root folder.

Let’s test to make sure that the form looks good. Open Command Prompt if you are using Windows, or the equivalent for your given operating system.

Navigate to your PHP root directory by typing CD C:\php-8.4.1 in the command prompt.

Next, type php -S localhost:8000 to start your localhost to start your local server.

Now that you have started your localhost, enter http://localhost:8000/transact.php into your address bar in your web browser.

You should see a form like the one below:

If you do, congratulations, you have just created a form through which you will be able to interact with the DB2 database on the Power Server!

Next, let’s add some validation so things go well when the form data is submitted. We will accomplish this using Javascript.

The code below will go above your opening HTML body tag.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Transaction Processor</title>
    <script>
        function validateForm() {
            var amount = document.getElementById("amount").value;
            var regex = /^\d{1,15}(\.\d{1,2})?$/;

            if (!regex.test(amount)) {
                alert("Please enter a valid amount with up to 15 digits before the decimal and up to 2 digits after the decimal.");
                return false;
            }
            return true;
        }

        function populateBalance(balance) {
            document.getElementById("balance").value = "$" + balance;
        }
    </script>
</head>

Let’s break down the individual elements of the script

1. Document Type Declaration

<!DOCTYPE html>

This line specifies that the document is an HTML5 document.

2. HTML Tag with Language Attribute

<html lang="en">

This starts the HTML document and sets the language attribute to English (lang="en").

3. Head Section

<head>

This section contains metadata and links to resources such as stylesheets and scripts.

4. Character Encoding

<meta charset="UTF-8">

Specifies the character encoding for the document, ensuring it can display a wide range of characters correctly.

5. Viewport Meta Tag

<meta name="viewport" content="width=device-width, initial-scale=1.0">

This meta tag ensures the page is responsive, meaning it will adjust its layout to fit the screen width of the device being used.

6. Title

<title>Transaction Processor</title>

Sets the title of the web page, which appears in the browser tab.

7. Embedded JavaScript

<script>
    function validateForm() {
        var amount = document.getElementById("amount").value;
        var regex = /^\d{1,15}(\.\d{1,2})?$/;

        if (!regex.test(amount)) {
            alert("Please enter a valid amount with up to 15 digits before the decimal and up to 2 digits after the decimal.");
            return false;
        }
        return true;
    }

    function populateBalance(balance) {
        document.getElementById("balance").value = "$" + balance;
    }
</script>

This code contains two Javascript functions:

A. validateForm Function

function validateForm() {
    var amount = document.getElementById("amount").value;
    var regex = /^\d{1,15}(\.\d{1,2})?$/;

    if (!regex.test(amount)) {
        alert("Please enter a valid amount with up to 15 digits before the decimal and up to 2 digits after the decimal.");
        return false;
    }
    return true;
}

Retrieves the value of the amount input field. Uses a regular expression (regex) to check if the amount is valid:
^\d{1,15}(\.\d{1,2})?$:
- ^\d{1,15}: Up to 15 digits before the decimal point.
- (\.\d{1,2})?$: Optional decimal point followed by up to 2 digits.
If the value does not match the pattern, an alert message is displayed, and the function returns false to prevent form submission. If the value is valid, the function returns true.

B. populateBalance Function

function populateBalance(balance) {
    document.getElementById("balance").value = "$" + balance;
}

This function takes a balance value as an argument.
Sets the value of the balance input field to the given balance prefixed with a dollar sign ($).

Great! We now have a form and form validation. Go ahead and try it out! If you enter a value of 999.999 in the amount text box, you should get a message letting you know that it as invalid amount. If you enter a valid amount, the amount will simply disappear, but that’s ok, we haven’t added any place to submit the information to. We will do that shortly, but first, lets implement a stored procedure to retrieve the current balance and display it on the page, so a user knows what their balance is before they attempt to withdraw or deposit any funds.

    <?php
    $dsn = 'odbc:PUB400.com';
    $user = 'YourUserName';
    $password = 'YourPassword';

    try {
        $pdo = new PDO($dsn, $user, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
        $sql = "CALL YourLibrary.GETBALANCE(?)";

        $stmt = $pdo->prepare($sql) or die("Error in Prepare: ");
        
        $balance = '';
        $message = '';
        
        $stmt->bindParam(1, $balance, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
    
        try {
            $stmt->execute();
        } catch (PDOException $e) {
            echo 'Error: '.$e->getMessage();
        }
    
        $formattedBalance = number_format($balance, 2);

        // Display message and balance after submit button
        echo "<div>";
        echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";
        if ($message) {
            echo "<p style='color: red;'>" . htmlspecialchars($message) . "</p>";
        }
        echo "</div>";
    
        // Pass the formatted balance to JavaScript
        echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";
    
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
?>

1. Database Connection Setup

<?php
$dsn = 'odbc:PUB400.com';
$user = 'YourUserName';
$password = 'YourPassword';

The PHP opening tag <?php is used to start a block of PHP code within an HTML or other file.

$dsn: Data Source Name, specifies the ODBC driver and the data source.

$user and $password: Database credentials.

2. Establishing the PDO Connection

try {
    $pdo = new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

A PDO instance is created using the DSN, username, and password.
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION): Configures PDO to throw exceptions for database errors.

3. Preparing the SQL Statement

$sql = "CALL YourLibrary.GETBALANCE(?)";

$stmt = $pdo->prepare($sql) or die("Error in Prepare: ");

$sql: The SQL query to call the stored procedure GETBALANCE in the library YourLibrary.
The ? is a placeholder for a parameter.$stmt = $pdo->prepare($sql): Prepares the SQL statement for execution. If it fails, it dies with “Error in Prepare:”.

4. Binding Parameters:

    $balance = '';
    $message = '';

    $stmt->bindParam(1, $balance, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT);

$balance and $message: Variables to hold the output values from the stored procedure.
$stmt->bindParam(1, $balance, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT): Binds the first parameter (index 1) to the $balance variable, indicating it can be both input and output.

5. Executing the Statement

    try {
        $stmt->execute();
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }

The statement is executed within a try-catch block.
If execution fails, an error message is displayed.

6. Formatting and Displaying the Balance

    $formattedBalance = number_format($balance, 2);

    // Display message and balance after submit button
    echo "<div>";
    echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";
    if ($message) {
        echo "<p style='color: red;'>" . htmlspecialchars($message) . "</p>";
    }
    echo "</div>";

$formattedBalance = number_format($balance, 2): Formats the balance with two decimal places.Output:
A <div> is used to contain the output.
JavaScript is used to call the populateBalance function to update the balance on the page.
If there is a message, it is displayed in a red-colored paragraph.

7. Passing the Balance to JavaScript

// Pass the formatted balance to JavaScript
echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";

This ensures the populateBalance JavaScript function is called to update the balance field on the web page.

8. Error Handling for the PDO Connection

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

If the PDO connection or any of the operations within the try block fail, an error message is displayed.

Now, we have the script set up to call an SQL stored procedure and format and display the output into the current balance text box. Now we just need to create the SQL stored procedure to be called on page load.

3. Write the SQL Stored Procedure to Retrieve the Current Balance

We will be going back to SQL Scripts in ACS to create an SQL stored procedure that retrieves the current balance from the balance table.

CREATE PROCEDURE YourLibrary.GETBALANCE ( 
	OUT BALANCE DECIMAL(15, 5) ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC YourLibrary.GETBALANCE 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	BEGIN 
  
  
SELECT 
( COALESCE ( ( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = 'D' ) , 0 ) - 
COALESCE ( ( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = 'W' ) , 0 ) ) 
INTO BALANCE 
FROM SYSIBM . SYSDUMMY1 ; 
  
END  ;

1. Create Procedure

CREATE PROCEDURE YourLibrary.GETBALANCE (

This statement defines a new stored procedure named GETBALANCE in the YourLibrary schema.

2. OUT BALANCE DECIMAL(15, 5)

    OUT BALANCE DECIMAL(15, 5)
)

The procedure has one output parameter BALANCE of type DECIMAL with a precision of 15 digits and 5 decimal places.

3. DYNAMIC RESULT SETS 1

DYNAMIC RESULT SETS 1

Indicates that the procedure can return one dynamic result set.

4. Procedure Attributes

LANGUAGE SQL
SPECIFIC YourLibrary.GETBALANCE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT

LANGUAGE SQL: The procedure is written in SQL.
SPECIFIC YourLibrary.GETBALANCE: Specifies a unique name for the procedure, useful for identifying it in the system catalog.
NOT DETERMINISTIC: Indicates that the procedure can return different results when called with the same input parameters, meaning it is not purely deterministic.
MODIFIES SQL DATA: The procedure can modify SQL data.
CALLED ON NULL INPUT: Specifies that the procedure should be called even if some of the input parameters are null.

5. Execution Environment Options

SET OPTION  ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX

SET OPTION: Specifies various options for the procedure execution.
*ALWBLK = ALLREAD: Allows blocking for all read operations.
*ALWCPYDTA = OPTIMIZE: Optimizes data copying.
*COMMIT = NONE: No commit is required; the procedure runs without transaction control.
DECRESULT = (31, 31, 00): Decimal result options.
*DYNDFTCOL = NO: Dynamic default collection is not used.
*DYNUSRPRF = USER: Dynamic user profile.
*SRTSEQ = HEX: Hexadecimal sort sequence.

6. Begin

BEGIN

Marks the beginning of the procedural code block.

7. Body

SELECT 
( COALESCE ( ( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = 'D' ) , 0 ) - 
COALESCE ( ( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = 'W' ) , 0 ) ) 
INTO BALANCE 
FROM SYSIBM . SYSDUMMY1 ;

SELECT: Performs a SQL query to calculate the balance.
COALESCE: A function that returns the first non-null value.
( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = ‘D’ ): Subquery to sum all deposit amounts.
( SELECT SUM ( AMOUNT ) FROM YourLibrary.BALANCETAB WHERE TRANSTYPE = ‘W’ ): Subquery to sum all withdrawal amounts.
The result of the deposits sum minus the withdrawals sum is stored in the BALANCE variable.
FROM SYSIBM.SYSDUMMY1: A special table used to select a constant or evaluate an expression in DB2.

8. END

END ;

Marks the end of the procedural code block.

Run the SQL statement to create the stored procedure. You should see a success message appear in the messages portion of SQL Scripts.

Now, let’s add a little data to our BalanceTab table. This way we can test our stored procedure to ensure it is working. Luckily, the IBM i Developer Corner Credit Union deposits $5 when you create an account with them. Let’s run the Insert statement to accomplish this.

Insert Into YourLibrary.BalanceTab (Amount,TransType)
Values (5, 'D');

If the statement worked properly, you should see a message that says:

“Statement ran successfully (250 ms)
1 rows were affected by the statement”

Let’s see what kind of data we have in our table.

Select * From YourLibrary.BalanceTab;

If everything went right, you will see data in your table, with the TransTime field automatically populated with a Timestamp of the current date and time:

This is due to our default constraint that we added to the table earlier, nice!

Let’s test our stored procedure to see what kind of data we are getting, and to see if it’s accurate:

CALL YourLibrary.GETBALANCE (
  BALANCE => ?
);

Five dollars is what we are looking for, excellent!

If you still have your localhost going, lets try to see if we are calling our stored procedure to return our balance. If you need to restart your localhost, here is a refresher:

Navigate to your PHP root directory by typing CD C:\php-8.4.1 in the command prompt.

Next, type php -S localhost:8000 to start your localhost to start your local server.

Now enter http://localhost:8000/transact.php in your browsers address bar.

If you see something like the image below, congratulations, you have just called a stored procedure from PHP that hit the database and returned a value to your web page!

We did say we wanted this PHP script to call an RPG program though, didn’t we? We sure did, and we are going to accomplish this through the magic of Stored Procedures!

Unlike the last Stored Procedure, we are going to have the logic for interacting with the DataBase happen in the RPG program, but first, let’s write the Stored Procedure that will call it.

4. Write the Stored Procedure To Add Transactions to the BalanceTab Table.

Open up SQL Scripts if it is not already open.

CREATE PROCEDURE YourLibrary.PROCESSTRANSACTION ( 
	IN AMOUNT DECIMAL(15, 5) , 
	IN TTYPE CHAR(1) , 
	OUT BALANCE DECIMAL(15, 5) , 
	OUT MESSAGE CHAR(35) ) 
	LANGUAGE RPGLE 
	SPECIFIC YourLibrary.PROCESSTRANSACTION 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	EXTERNAL NAME 'YourLibrary/TRANSACT' 
	PARAMETER STYLE GENERAL ; 
  
GRANT ALTER , EXECUTE   
ON SPECIFIC PROCEDURE YourLibrary.PROCESSTRANSACTION 
TO YourUserName WITH GRANT OPTION ;

1. Procedure Name

CREATE PROCEDURE YourLibrary.PROCESSTRANSACTION (

The procedure is created within the library YourLibrary and is named PROCESSTRANSACTION.

2. Parameters

	IN AMOUNT DECIMAL(15, 5) , 
	IN TTYPE CHAR(1) , 
	OUT BALANCE DECIMAL(15, 5) , 
	OUT MESSAGE CHAR(35) )

IN AMOUNT DECIMAL(15, 5): The AMOUNT parameter is an input of type DECIMAL with a precision of 15 digits and 5 decimal places. This represents the amount involved in the transaction (deposit, withdrawal, etc.).
IN TTYPE CHAR(1): The TTYPE parameter is a 1-character input. This could represent the type of transaction (e.g., ‘D’ for deposit, ‘W’ for withdrawal).
OUT BALANCE DECIMAL(15, 5): The BALANCE parameter is an output parameter of type DECIMAL that will hold the resulting balance after the transaction is processed.
OUT MESSAGE CHAR(35): The MESSAGE parameter is an output parameter of type CHAR(35) that will store a message, indicating the success or failure of the transaction.

3. Attributes

	LANGUAGE RPGLE 
	SPECIFIC YourLibrary.PROCESSTRANSACTION 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	EXTERNAL NAME 'YourLibrary/TRANSACT' 
	PARAMETER STYLE GENERAL ;

LANGUAGE RPGLE: The procedure is implemented using RPGLE (RPG IV), a programming language used on the IBM i (AS/400) platform.
SPECIFIC YourLibrary.PROCESSTRANSACTION: A specific name is given to the procedure to identify it uniquely in the system.
NOT DETERMINISTIC: The procedure does not always return the same result for the same input parameters. This means that the result can vary depending on external factors (e.g., data changes).
MODIFIES SQL DATA: The procedure modifies the data in the database.
CALLED ON NULL INPUT: The procedure will be called even if some of the input parameters are NULL.
EXTERNAL NAME ‘YourLibrary/TRANSACT’: This indicates that the procedure is externally defined in the TRANSACT program located in the YourLibrary library. The TRANSACT program is written in RPGLE and implements the actual logic of the transaction.
PARAMETER STYLE GENERAL: This indicates that the procedure follows a general parameter style, meaning that the parameters are passed by value or reference depending on the database platform.

4. Grant

GRANT ALTER , EXECUTE   
ON SPECIFIC PROCEDURE YourLibrary.PROCESSTRANSACTION 
TO YourUserName WITH GRANT OPTION ;

This line grants the specified user (YourUserName) permission to ALTER and EXECUTE the stored procedure.
WITH GRANT OPTION: This means that YourUserName can also grant these permissions to other users.

Go ahead and run the statement, if done correctly, you should get a message saying “Statement ran successfully”.

So now, we have created a page with a form that will present the user with their balance. Now we need to set up the page to submit data to a stored procedure that passes that data to an RPG program for processing.

5. Add Logic to Call Stored Procedure and Pass Form Data to it.

Open up VS Code again and go to your Transact.php file. Add the following code below the following code that exists lines, and be sure to make the closing tag comes directly after the newly pasted code. Feel free to visit my GitHub page to ensure that your code matches mine.

Code that exists:

    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }

Code To Add:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
        if (!preg_match('/^\d{1,15}(\.\d{1,2})?$/', $_POST["amount"])) {
            echo "<p style='color: red;'>Invalid amount format. Please enter a valid amount.</p>";
        } else {
            // Get the amount from the form
            $amount = floatval($_POST["amount"]);
            $Ttype = $_POST["transactionType"];

            // Call the stored procedure to deposit, withdraw and return balance
            try { 
                $pdo = new PDO($dsn, $user, $password);
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $sql = "CALL HELPERHAL1.ProcessTransaction(?, ?, ?, ?)";

                $stmt = $pdo->prepare($sql) or die("Error in Prepare: ");

                if ($Ttype === "withdrawal") {
                    $type = 'W';
                } elseif ($Ttype === "deposit") {
                    $type = 'D';
                } else {
                    $type = '';
                }
                
                $stmt->bindParam(1, $amount, PDO::PARAM_STR);
                $stmt->bindParam(2, $type, PDO::PARAM_STR);
                $stmt->bindParam(3, $balance, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
                $stmt->bindParam(4, $message, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);

                try {
                    $stmt->execute();
                } catch (PDOException $e) {
                    echo 'Error: '.$e->getMessage();
                }

                // Format the balance to 2 decimal places
                $formattedBalance = number_format($balance, 2);

                // Display message and balance after submit button
                echo "<div>";
                echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";
                if ($message) {
                    echo "<p style='color: red;'>" . htmlspecialchars($message) . "</p>";
                }
                echo "</div>";

            } catch (PDOException $e) {
                echo "Error: " . $e->getMessage();
            }
        }
    }

    $pdo = null;
    ?>
</body>
</html>

1. Check the request method

if ($_SERVER["REQUEST_METHOD"] == "POST") {

Checks if the form was submitted using the POST method.

2. Validate the amount:

if (!preg_match('/^\d{1,15}(\.\d{1,2})?$/', $_POST["amount"])) {
            echo "<p style='color: red;'>Invalid amount format. Please enter a valid amount.</p>";

Checks if the amount field from the form matches a specific format:
Up to 15 digits before the decimal.
Up to 2 digits after the decimal.
If the format doesn’t match, an error message is displayed.

3. If the amount is valid

$amount = floatval($_POST["amount"]);
$Ttype = $_POST["transactionType"];

The amount is converted to a float using floatval($_POST["amount"]).
The transactionType ($Ttype) is retrieved from the form, which indicates whether it is a “deposit” or “withdrawal”.

4. Database connection and stored procedure call string creation

try { 
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "CALL YourLibrary.ProcessTransaction(?, ?, ?, ?)";

A PDO connection is created to interact with the database using the provided $dsn, $user, and $password.
The ProcessTransaction stored procedure string is saved in the $sql variable.

5. Prepare the SQL statement

 $stmt = $pdo->prepare($sql) or die("Error in Prepare: ");

if ($Ttype === "withdrawal") {
    $type = 'W';
} elseif ($Ttype === "deposit") {
    $type = 'D';
} else {
    $type = '';
}
                
$stmt->bindParam(1, $amount, PDO::PARAM_STR);
$stmt->bindParam(2, $type, PDO::PARAM_STR);
$stmt->bindParam(3, $balance, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(4, $message, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);

The SQL query calls the stored procedure ProcessTransaction, passing the parameters.
$stmt->bindParam() binds the input and output parameters to the SQL statement.

6. Execute the statement

try {
      $stmt->execute();
    } catch (PDOException $e) {
    echo 'Error: '.$e->getMessage();
    }

The statement is executed with $stmt->execute(). If an error occurs, it is caught and displayed.

7. Format and display the balance


$formattedBalance = number_format($balance, 2);

echo "<div>";
echo "<script>populateBalance('" . htmlspecialchars($formattedBalance) . "');</script>";

if ($message) {
   echo "<p style='color: red;'>" . htmlspecialchars($message) . "</p>";
                }
echo "</div>";

The balance is formatted to two decimal places using number_format($balance, 2).
A JavaScript function (populateBalance) is called to update the balance on the webpage.
If there’s a message (e.g., an error), it is displayed in red.

8. Error handling

            } catch (PDOException $e) {
                echo "Error: " . $e->getMessage();
            }
        }
    }

Both the database connection and statement execution are wrapped in try-catch blocks to handle any potential PDO exceptions and display error messages.

9. Close the PDO connection

    $pdo = null;
    ?>
</body>
</html>

Finally, $pdo = null; is used to close the database connection.

So now we have created a page that can call a Stored Procedure and pass parameters to it. We have created a callable Stored Procedure that can call an RPG program. Now, we just need to write the RPG program to process the passed in paramaters. Let’s do that now.

6. Write RPG Program to Process Parameters

Now, with the parameters we have, we need to Read and Write to the BalanceTab Table using RPG. Create a new member in YourLibrary called Transact.RPGLE, and enter the following code.

       ctl-opt option(*nodebugio:*srcstmt) ;
      **Free

       Dcl-F BalanceTab Disk Usage(*Input:*Output);

       Dcl-S Withdrawal Packed(15:2) Inz(*Zeros);
       Dcl-S Deposit    Packed(15:2) Inz(*Zeros);

       DCL-PI *n;
         Amt            Packed(15:5);
         TType          Char(1);
         Balance        Packed(15:5);
         Message        Char(35);
       END-PI;

       Dcl-S TransType Char(1);

       Read(e) BalanceTab;

       Dow Not %Eof(BalanceTab);
         If TRANSTYPE = 'W';
           Withdrawal += Amount;
         Else;
           Deposit += Amount;
         EndIf;
         Read BalanceTab;
       EndDo;

       Balance = Deposit - Withdrawal;

       TransType = TType;
       TransTime = %Timestamp(*Sys);
       Amount = Amt;

       If TType = 'W';

         If Amt > Balance;
    Message = 'Withdrawal Amount Exceeds Balance';
           *INLR = *On;
           Return;
         Else;
           Write Bal;
         EndIf;
       Else;
         Write Bal;
       EndIf;

       Clear Amount;
       Clear Withdrawal;
       Clear Deposit;

       SetLL *Start BalanceTab;

       Read(e) BalanceTab;

       Dow Not %Eof(BalanceTab);
         If TRANSTYPE = 'W';
           Withdrawal += Amount;
         Else;
           Deposit += Amount;
         EndIf;
         Read BalanceTab;
       EndDo;

       Balance = Deposit - Withdrawal;

       *inlr = *on;
       return;

1. Control Options and Free-form Directive

ctl-opt option(*nodebugio:*srcstmt);
**Free

Specifies control options. *nodebugio disables debugging for I/O operations, and *srcstmt indicates that source statement numbers should be used.
**Free indicates that the code is written in free-form RPG.

2. File Declaration

Dcl-F BalanceTab Disk Usage(*Input:*Output);

Declares a file named BalanceTab with input and output capabilities.

3. Variable Declarations

Dcl-S Withdrawal Packed(15:2) Inz(*Zeros);
Dcl-S Deposit    Packed(15:2) Inz(*Zeros);
Dcl-S TransType Char(1);

Withdrawal and Deposit are packed decimal variables initialized to zero.
TransType is a character variable used to store the transaction type.

4. Procedure Interface Declaration

DCL-PI *n;
  Amt            Packed(15:5);
  TType          Char(1);
  Balance        Packed(15:5);
  Message        Char(35);
END-PI;

This defines the parameters for the procedure interface. Amt is the amount, TType is the transaction type, Balance is the balance, and Message is a message string.

5. Reading and Processing the Balance Table

Read(e) BalanceTab;

Dow Not %Eof(BalanceTab);
  If TRANSTYPE = 'W';
    Withdrawal += Amount;
  Else;
    Deposit += Amount;
  EndIf;
  Read BalanceTab;
EndDo;

Balance = Deposit - Withdrawal;

The program reads the balance table and processes each record in a loop until the end of the file (%Eof).
For each record, it checks if the transaction type is withdrawal ('W'). If true, it adds the amount to Withdrawal; otherwise, it adds the amount to Deposit.
The balance is then calculated as the difference between Deposit and Withdrawal.

6. Transaction Handling and Balance Update

TransType = TType;
TransTime = %Timestamp(*Sys);
Amount = Amt;

If TType = 'W';

  If Amt > Balance;
    Message = 'Withdrawal Amount Exceeds Balance';
    *INLR = *On;
    Return;
  Else;
    Write Bal;
  EndIf;
Else;
  Write Bal;
EndIf;

Clear Amount;
Clear Withdrawal;
Clear Deposit;

The transaction type and timestamp are assigned.
For withdrawals, it checks if the amount exceeds the balance. If true, it sets an error message and ends the program. Otherwise, it writes the balance.
For other transaction types (deposits), it directly writes the balance.The Amount, Withdrawal, and Deposit variables are cleared.

7. Reprocessing the Balance Table to Recalculate Balances

SetLL *Start BalanceTab;

Read(e) BalanceTab;

Dow Not %Eof(BalanceTab);
  If TRANSTYPE = 'W';
    Withdrawal += Amount;
  Else;
    Deposit += Amount;
  EndIf;
  Read BalanceTab;
EndDo;

Balance = Deposit - Withdrawal;

*inlr = *on;
return;

The balance table is reset to the start (SetLL *Start).
It reads and processes the table again in a loop to recalculate balances based on transaction types.
The final balance is updated, and the program ends with *inlr = *on.

So now we have the RPG Program written to process the data, let’s compile! The easiest way to do this is to navigate to YourLibrary and type a 14 next to Transact.RPGLE to compile. Otherwise, follow the methods for compiling in your given IDE be it VS Code or RDi.

Recap

You have now written a web page that calls a stored procedure on load and presents the user with a form along with their current balance. You have written 2 stored procedures, 1 retrieves data with SQL and another takes in 2 parameters updates the database and returns a new current balance value, or an error message. Finally, you have written an RPG program that will take parameters passed in by a stored procedure to Read and Write to a SQL table, then return the current balance to the stored procedure, which returns the balance to the web page. The only thing left to do is to execute the script and try depositing and withdrawing!

With your localhost open, navigate to your page http://localhost:8000/transact.php and try it out! See that when you try to withdraw more than your current balance, you are given a warning message, and that when you deposit or withdraw, your balance updates accordingly. Finally, see that the form validation prevents you from submitting invalid data.

You should now have a general idea on how to connect RPG to the web. Feel free to adapt and change whatever you want. This demonstration was mostly to get someone comfortable with exposing RPG to the web with the smallest amount of requirements possible.

The i Developer's Corner