Data Base 101 I 5 Sql

I5.DataBase101I5Sql History

Hide minor edits - Show changes to output

Changed line 40 from:
define('APP_BASE_DIR', '/www/zendcore/htdocs/qiwikiCode/');
to:
define('APP_BASE_DIR', '');
Added lines 7-8:
'''This page is deprecated via new PHP toolkit (at least for now)'''
Changed line 11 from:
Bill of materials
to:
Bill of materials (click name to see source)
Changed lines 18-20 from:
** No RPG (replaced by i5 toolkit SQL)

to:
* No RPG (replaced by i5 toolkit SQL)

Added lines 11-20:
Bill of materials
* %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101I5SQLBrowse.php | 101I5SQLBrowse.php]] - Main: main program (web contact)
** %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Config.php | 101Config.php]] - Site: site configuration
** %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101HtmlView.php | 101HtmlView.php]] - View: plain old generated html
** %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101HtmlControl.php | 101HtmlControl.php]] - Control: main loop html forms
** %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Connecti5.php | 101Connecti5.php]] - Model: i5 toolkit connect
** %newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101I5SQLModel.php | 101I5SQLModel.php]] - Model: i5 toolkit SQL
** No RPG (replaced by i5 toolkit SQL)

Deleted lines 18-25:
!!! 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.

Added lines 129-137:
!!! 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.

Changed lines 50-59 from:
!!! 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.

%lfloat text-align
=center width=500 height=300%{$Imagedir}stateless.png%%
to:
!!! 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();
@]
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Connecti5.php | {$Imagedir}foil.png"101Connecti5.php"]]%%
Click the image to see full source.
Changed lines 79-86 from:

!!! 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.

%lfloat text-align=center width=500 height=300%{$Imagedir}i5pconnect
.png%%
to:
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;
}
@]
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Connecti5.php | {$Imagedir}foil.png"101Connecti5.php"]]%%
Click the image to see full source.
Added lines 109-131:

!!! 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.

%lfloat text-align=center width=500 height=300%{$Imagedir}stateless.png%%
[[<<]]


!!! 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.

%lfloat text-align=center width=500 height=300%{$Imagedir}i5pconnect.png%%
[[<<]]

Changed line 79 from:
Please note the i5 toolkit SQL MVC model function names are exactly the same as the
to:
The i5 toolkit SQL MVC model function names are exactly the same as the
Changed lines 5-6 from:
You may want to read this module if you are a beginner with PHP i5 toolkit SQL ...
to:
You may want to read this module if you are a beginner with PHP i5 toolkit SQL.

Key APIs:
* i5_pconnect
* i5_prepare / i5_execute

Changed lines 13-15 from:
%lfloat text-align=center width=300 height=200 newwin%[[{$Exampleurl}101browse.php?example=browse_i5_sql | {$Imagedir}db101_php_search.png"browse"]]%% PHP 101 with i5 toolkit SQL (no RPG):
to:
%lfloat text-align=center width=300 height=200 newwin%[[{$Exampleurl}101I5SQLBrowse.php | {$Imagedir}db101_php_search.png"101I5SQLBrowse.php"]]%%
Click the image to run the demo.
[[<<]]
Changed lines 18-19 from:
[[<<]]
to:
Deleted lines 27-28:
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Include.php | {$Imagedir}foil.png"101Include.php"]]%% MVC control
[[<<]]
Changed lines 29-32 from:
example with different $_GET parameter (?example=browse_i5_sql).  We can use our
handy demo
PHP technique of "dynamic" includes
to switch out MVC components with
runtime parameter ?example=browse_
i5_sql.
to:
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.
Changed lines 35-39 from:
  case "browse_i5_sql":
    include_once './101Control.php';            /* same control as browse_i5_to_RPG */
   include_once './101browseViewSimple.php';    /* same view as browse_i5_to_RPG    */
   include_once './101browseModeli5SQL.php'  /* i5 sql model                    */
   break;
to:
<?php
define('APP_BASE_DIR', '/www/zendcore/htdocs/qiwikiCode/');
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      */
?>
Changed lines 44-45 from:

to:
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101I5SQLBrowse.php | {$Imagedir}foil.png"101I5SQLBrowse.php"]]%%
Click the image to see full source.
[[<<]]

Changed lines 49-53 from:
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101browseModelI5SQL.php | {$Imagedir}foil.png"101browseModelIbmDb2.php"]]%% 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 (101Modeli5.php).
[[<<]]

to:
Added lines 59-62:
%lfloat text-align=center width=500 height=300%{$Imagedir}stateless.png%%
[[<<]]

Changed lines 67-72 from:
of "active" web profiles. In a different example
to:
of "active" web profiles.

%lfloat text-align=center width=500 height=300%{$Imagedir}i5pconnect.png%%
[[<<]]

In a different example
Changed lines 86-87 from:
that have many DB2 calls to complete these APIs can provide a performance
boast over i5_query.
to:
that have many DB2 calls these APIs can provide better performance.
Deleted line 88:
include_once './101Modeli5.php';
Changed line 92 from:
  { die(model_error());
to:
  { False;
Deleted line 94:
  // i5_prepare once (use many i5_execute)
Changed line 99 from:
     $sql .= "select * from {$MODEL['products']}";
to:
     $sql .= "select * from products";
Changed line 105 from:
     $sql .= "select * from {$MODEL['products']}";
to:
     $sql .= "select * from products";
Changed line 117 from:
     $sql .= "select * from {$MODEL['products']}";
to:
     $sql .= "select * from products";
Added line 136:
  $i=0;
Added line 147:
     $i++;
Added lines 149-152:
  if (!$i)
  { model_error_i5 ( "No DVDs found" );
    return False;
  }
Added lines 156-158:
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101I5SQLModel.php | {$Imagedir}foil.png"101I5SQLModel.php"]]%%
Click the image to see full source.
[[<<]]
Changed lines 5-6 from:
You may want to read this module if you are a beginner with PHP i5 toolkit ...
to:
You may want to read this module if you are a beginner with PHP i5 toolkit SQL ...
Changed line 26 from:
example with different $_GET parameter (?example=browse_ibm_db2).  We can use our
to:
example with different $_GET parameter (?example=browse_i5_sql).  We can use our
Changed line 67 from:
with ibm_db2, and the view and control code do not have to change
to:
with i5 toolkit SQL, and the view and control code do not have to change
Deleted lines 44-51:
!!! Why use db2_pconnect (over db2_connect)?
We are using ibm_db2 persistent
connections with API db2_pconnect() (not db2_connect()), to improve
performance of the overall web-site under heavy user web demand.
Use of persistent connections is absolutely required if your
site services multiple requests per second. If you forget and use db2_connect(),
you can set php.ini setting to force all db2_connect to db2_pconnect.

Added lines 13-146:

!!! 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 MVC?
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101Include.php | {$Imagedir}foil.png"101Include.php"]]%% MVC control
[[<<]]
We are using the same PHP view/control code as the original RPG back-end
example with different $_GET parameter (?example=browse_ibm_db2).  We can use our
handy demo PHP technique of "dynamic" includes
to switch out MVC components with
runtime parameter ?example=browse_i5_sql.
[@
  case "browse_i5_sql":
    include_once './101Control.php';            /* same control as browse_i5_to_RPG */
    include_once './101browseViewSimple.php';    /* same view as browse_i5_to_RPG    */
    include_once './101browseModeli5SQL.php';    /* i5 sql model                    */
    break;
@]


!! PHP Get Records (i5 toolkit SQL model)
%lfloat text-align=center width=30 height=30 newwin%[[{$Exampleurl}zzfoil.php?afile={$Exampledir}101browseModelI5SQL.php | {$Imagedir}foil.png"101browseModelIbmDb2.php"]]%% 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 (101Modeli5.php).
[[<<]]

!!! Why use db2_pconnect (over db2_connect)?
We are using ibm_db2 persistent
connections with API db2_pconnect() (not db2_connect()), to improve
performance of the overall web-site under heavy user web demand.
Use of persistent connections is absolutely required if your
site services multiple requests per second. If you forget and use db2_connect(),
you can set php.ini setting to force all db2_connect to db2_pconnect.

!!! 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 use i5_prepare and i5_execute?
Here is the i5 toolkit SQL code that replaces our RPG back-end (101browseModelI5SQL.php).
Please note 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 ibm_db2, 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 to complete these APIs can provide a performance
boast over i5_query.
[@
<?php
include_once './101Modeli5.php';
function model_search($browsetype, $browse_title, $browse_actor, $browse_category, $limit_num, &$items)
{ global $MODEL, $categories;
  if (!model_connect ())
  { die(model_error());
  }
  $items = array ( );
  // i5_prepare once (use many i5_execute)
  $prepare = null;
  $sql = "";
  switch($browsetype)
  { case 'title':
      $sql .= "select * from {$MODEL['products']}";
      $sql .= " where TITLE like '%$browse_title%'";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = i5_prepare($sql);
      break;
    case 'actor':
      $sql .= "select * from {$MODEL['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 {$MODEL['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
  while ($row = i5_fetch_assoc($prepare, I5_READ_NEXT))
  { array_push
      ( $items,
        array
        ( $row["PROD_ID"],
          $row["TITLE"],
          $row["ACTOR"],
          $row["PRICE"]
        )
      );
  }
  return True;
}
@]
Added lines 1-12:
(:quicktoc:)
>>round frame bgcolor=#ffffdd<<
!!! Database 101 with i5 toolkit SQL model
>><<
You may want to read this module if you are a beginner with PHP i5 toolkit ...

!! Introduction
Click image below to run demo.
%lfloat text-align=center width=300 height=200 newwin%[[{$Exampleurl}101browse.php?example=browse_i5_sql | {$Imagedir}db101_php_search.png"browse"]]%% PHP 101 with i5 toolkit SQL (no RPG):
In this case we replaced the RPG back-end model with a PHP i5 toolkit SQL model that does the
essentially same job.
[[<<]]