Data Base 101 I 5 Sql

(click to open)

Quick Page Table of Contents

Scanning…

Database 101 with i5 toolkit SQL model

You may want to read this module if you are a beginner with PHP i5 toolkit SQL.

This page is deprecated via new PHP toolkit (at least for now)

Key APIs:

  • i5_pconnect
  • i5_prepare / i5_execute

Bill of materials (click name to see source)

Introduction

Click image below to run demo.

101I5SQLBrowse.php

Click the image to run the demo.
In this case we replaced the RPG back-end model with a PHP i5 toolkit SQL model that does the essentially same job.

Why MVC?

We are using the same PHP view/control code as the original RPG back-end example with different model.

In this example we replaced the RPG back-end with a PHP i5 toolkit SQL model, therefore we have a new global include to handle connection to i5 toolkit.

<?php
define('APP_BASE_DIR', '');
include_once APP_BASE_DIR.'101Config.php';      /* Site:    site configuration         */
include_once APP_BASE_DIR.'101Connecti5.php';   /* Model:   i5 toolkit connect         */
include_once APP_BASE_DIR.'101I5SQLModel.php';  /* Model:   i5 toolkit SQL             */
include_once APP_BASE_DIR.'101HtmlView.php';    /* View:    plain old generated html   */
include_once APP_BASE_DIR.'101HtmlControl.php'; /* Control: main loop html forms       */
?>
101I5SQLBrowse.php

Click the image to see full source.

PHP Get Records (i5 toolkit SQL model)

PHP Get Records Connect (i5 toolkit model)

The first thing to do is to get a connection to the i5 toolkit daemon process (PGM-EASYCOM jobs). We are using the i5_(p)connect function or persistent connections, to avoid costly IBM i job startup and termination associated with i5_connect/i5/close.

In general connection is considered a common activity for a PHP application site, so we have a site common include.

function model_connect() 
{ global $MODEL;
  $db_options = array(I5_OPTIONS_JOBNAME=>"DVDSEARCH");
  $MODEL ['conn'] = i5_pconnect 
  ( $MODEL ['database'],
    $MODEL ['db_user'], 
    $MODEL ['db_password'],
    $db_options
  );
  if (! $MODEL ['conn']) 
  { model_error_i5("Connect fail");
    return False;
  }
  return model_chglibl();
101Connecti5.php

Click the image to see full source.

Every browser button click runs back through the i5_pconnect code, but the connection is cached for each Apache worker job, so that actual time is minimal. However, each button click in this example will not find it’s way back to the same worker job (see below).

How do I set the libl for my RPG program?

The library list drives many applications in RPG. However, in a Apache “stateless” environment like this sample, we can never count on the library list being set correctly. So, we have added chglibl code to the generic connection for i5 toolkit call.

function model_chglibl()
{ global $MODEL;
  $rc = i5_command
  ("chglibl",
    array("libl"=>$MODEL ['libl']),
	array()
  );
  if(!$rc)
  { model_error_i5("chglibl fail");
    return False;
  }
  return True;
} 
101Connecti5.php

Click the image to see full source.

Why do browser clicks of i5_pconnect route to different Apache jobs?

It should be noted that persistent i5 connections only means that each “stateless” Apache job will have a common connection to i5 toolkit to be used by ALL PHP i5 toolkit applications running, not a single application or “job”. Persistent connection does NOT mean that each browser click will return back to the same job, so do not attempt to commit transactions across multiple browser clicks.


Why is there many PGM-EASYCOM jobs with i5_pconnects?

If you supply a user id and password to the i5_pconnect, you will also spawn multiple PGM-EASYCOM server process for each different user profile specified depending on Apache user peak demand. Therefore it is good practice to limit the number of “active” web profiles.


In a different example we will be using a “private” connection that can be used for traditional “state full” RPG applications.

Why does ibm_db2 and i5_toolkit have different connects?

Simply put, ibm_db2 comes from IBM and i5_toolkit comes from another PHP vendor. The connection techniques are completely different technologies in the two products. The i5_toolkit uses a daemon approach that monitors a socket for in-coming connections (started by Zend Core menus and subsystem). The ibm_db2 extension uses either in-line calling and/or special memory shipping if between QSQ server jobs and Apache worker jobs.

Why use i5_prepare and i5_execute?

Here is the i5 toolkit SQL code that replaces our RPG back-end (101browseModelI5SQL.php). The i5 toolkit SQL MVC model function names are exactly the same as the i5 toolkit model function names (model_search), therefore when we replace the model code with i5 toolkit SQL, and the view and control code do not have to change (5250 code also can remain the same).

We are using the i5_prepare and i5_execute API combination to allow the DB2 engine to optimize and reuse SQL statements many times. On high volume web sites that have many DB2 calls these APIs can provide better performance.

<?php
function model_search($browsetype, $browse_title, $browse_actor, $browse_category, $limit_num, &$items) 
{ global $MODEL, $categories;
  if (!model_connect ()) 
  { False;
  }
  $items = array ( );
  $prepare = null;
  $sql = "";
  switch($browsetype)
  { case 'title':
      $sql .= "select * from products";
      $sql .= " where TITLE like '%$browse_title%'";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = i5_prepare($sql);
      break;
    case 'actor':
      $sql .= "select * from products";
      $sql .= " where ACTOR like '%$browse_actor%'";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = i5_prepare($sql);
      break;
    case 'category':
      // oops did not plan ahead for this
      for ($i=1;$i<=count($categories);$i++)
      { if ($categories[$i-1]==$browse_category)
        { break;
        }
      }
      $sql .= "select * from products";
      $sql .= " where CATEGORY = $i";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = i5_prepare($sql);
      break;
    default:
      break;
  }
  if (!$prepare)
  { model_error_i5("prepare $sql");
    return False;
  }
  // execute prepared statement
  $execute = i5_execute($prepare);
  if (!$execute)
  { model_error_i5("execute");
    return False;
  }
  // fetch the row data
  $i=0;
  while ($row = i5_fetch_assoc($prepare, I5_READ_NEXT))
  { array_push
      ( $items,
        array
        ( $row["PROD_ID"], 
          $row["TITLE"], 
          $row["ACTOR"], 
          $row["PRICE"]
        )
      );
      $i++;
  }
  if (!$i)
  { model_error_i5 ( "No DVDs found" );
    return False;
  }
  return True;
}
101I5SQLModel.php

Click the image to see full source.