From YiPs Wiki (i powered)

XMLService: XMLSERVICE 123 PHPODBC

(click to open)

Quick Page Table of Contents

Scanning…

XMLSERVICE PHP odbc

Goto Main Page
Goto Documents

PHP odbc was used for this XMLSERVICE test (included with Zend Server downloads 1/2 tier). This is not the Zend PHP Toolkit interface, instead this is what happens below that “nice” PHP interface wrapper.

Step 1) Install XMLSERVICE

Download:
1) XMLSERVICE main page has download "latest" and install instructions.

Step 2) Configure PHP odbc

Option 1-tier)
PHP 1-tier on IBM i you only need to start host servers (STRHOSTSVR).

STRHOSTSVR SERVER(*DATABASE)
-- your AS/400’s IP address connection (example LP0164D)

Option 2-tier)
2-tier from Linux/Windows to IBM i you need iAccess product/drivers from IBM to call IBM i (WRKRDBDIRE).

Linux instructions (similar for Windows): 

Q: How do i setup my odbc connection?
A: edit the /etc odbc files
>sudo gedit /etc/odbcinst.ini
[iSeries Access ODBC Driver]
Description	= iSeries Access for Linux ODBC Driver
Driver		= /usr/lib/libcwbodbc.so
Driver64	= 
Setup		= /usr/lib/libcwbodbc.so
Setup64		= 
UsageCount	= 
CPTimeout	= 
CPReuse		= 
Threading	= 2
>sudo gedit /etc/odbc.ini
[LP0164D]
Description	= iSeries os400
Driver		= iSeries Access ODBC Driver
System		= LP0164D.somewhere.com

Q: How do I test my odbc connection?
A: use odbc tool isql
>isql LP0164D MYID MYPASSWORD

Step 3 - Example - PGM complex data with DS

Example calling a typical RPG PGM with a few parameters, one of which is a data structure. In this case all the parameters are io=‘both’ (input/output), so the XML going in looks exactly like the XML coming out of XMLSERVICE, except the return XML data values will change to whatever the called function writes into the parameter variables.

XMLSERVICE stored procedures with result set available in various sizes:

$stmt = odbc_prepare($conn, "call $libxmlservice.iPLUGR32K(?,?,?)");
$clobIn = getxml();
$clobOut = "";
error_reporting(~E_ALL); 
$ret=odbc_execute($stmt,array($ipc,$ctl,$clobIn));
if (!$ret) die("Bad execute: ".odbc_errormsg());
error_reporting(E_ALL);
// may need option $ctl .= " *hack" and junk away routine
while(odbc_fetch_row($stmt)) {
  $row = odbc_result($stmt, 1);
  $clobOut .= $row;
}

DB2 Result set returned (connections not supporting in/out parameters):
... sizes: 4K, 32K, 65K, 512K, 1M, 5M, 10M up to 15M (see crtsql in download) ...
CREATE PROCEDURE XMLSERVICE.iPLUGR4K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CHAR(4096)) 
iPLUGR32K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CHAR(32000)) 
iPLUGR65K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(65K)) 
iPLUGR512K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(512K)) 
iPLUGR1M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(1M)) 
iPLUGR5M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(5M)) 
iPLUGR10M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(10M)) 
iPLUGR15M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(15M)) 

connection.inc - connection information

<?php
// database
$database    = "LP0164D"; 
$user        = "ADCU"; 
$password    = "NICEXXXX";
$internalKey = "/tmp/rangerusr";

// misc test settings
$i5persistentconnect = false;
$libxmlservice       = 'XMLSERVICE'; // ZENDSVR
$toolkitdir          = ".";          // zend ship also
$i5rest              = "http://lp0164d.rchland.ibm.com/cgi-bin/xmlcgi.pgm";
$i5restdatabase      = "*LOCAL";     // only *LOCAL tested

// call parms
$ipc        = $internalKey;
$ctl        = "*sbmjob";
$clobIn     = "";
$clobOut    = "";

function test_lib_replace($xml) {
  global $libxmlservice;
  $was = array("xyzlibxmlservicexyz");
  $now = array("$libxmlservice");
  $out = str_replace($was,$now,$xml);
  return $out;
}
?>

test_odbc_proc.php - test stored procedure call XMLSERVICE

<?php
require_once('connection.inc');
// call IBM i
if ($i5persistentconnect) $conn = odbc_pconnect($database,$user,$password);
else $conn = odbc_connect($database,$user,$password);
if (!$conn) die("Bad connect: $database,$user");
$stmt = odbc_prepare($conn, "call $libxmlservice.iPLUGR32K(?,?,?)");
if (!$stmt) die("Bad prepare: ".odbc_errormsg());
$clobIn = getxml();
$clobOut = "";
// bad behavior odbc extension ... 
// why IBM i result set warning???
error_reporting(~E_ALL); 
$ret=odbc_execute($stmt,array($ipc,$ctl,$clobIn));
if (!$ret) die("Bad execute: ".odbc_errormsg());
while(odbc_fetch_row($stmt)) {
  $row = odbc_result($stmt, 1);
  $clobOut .= $row;
}
// -----------------
// output processing
// -----------------
// dump raw XML (easy test debug)
var_dump($clobOut);

//     D  INCHARA        S              1a
//     D  INCHARB        S              1a
//     D  INDEC1         S              7p 4        
//     D  INDEC2         S             12p 2
//     D  INDS1          DS                  
//     D   DSCHARA                      1a
//     D   DSCHARB                      1a           
//     D   DSDEC1                       7p 4      
//     D   DSDEC2                      12p 2            
//      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//      * main(): Control flow
//      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//     C     *Entry        PLIST                   
//     C                   PARM                    INCHARA
//     C                   PARM                    INCHARB
//     C                   PARM                    INDEC1
//     C                   PARM                    INDEC2
//     C                   PARM                    INDS1
function getxml() {
$clob = <<<ENDPROC
<?xml version='1.0'?>
<script>
<pgm name='ZZCALL' lib='xyzlibxmlservicexyz'>
 <parm  io='both'>
   <data type='1A' var='INCHARA'>a</data>
 </parm>
 <parm  io='both'>
   <data type='1A' var='INCHARB'>b</data>
 </parm>
 <parm  io='both'>
   <data type='7p4' var='INDEC1'>11.1111</data>
 </parm>
 <parm  io='both'>
   <data type='12p2' var='INDEC2'>222.22</data>
 </parm>
 <parm  io='both'>
  <ds>
   <data type='1A' var='INDS1.DSCHARA'>x</data>
   <data type='1A' var='INDS1.DSCHARB'>y</data>
   <data type='7p4' var='INDS1.DSDEC1'>66.6666</data>
   <data type='12p2' var='INDS1.DSDEC2'>77777.77</data>
  </ds>
 </parm>
 <return>
  <data type='10i0'>0</data>
 </return>
</pgm>
</script>
ENDPROC;
return test_lib_replace($clob);
}

Step 4 - Example - Use PHP odbc to Kill XMLSERVICE job

The keywords used to route the kill order are ctl=“*immed” and ipc=“/tmp/rangerusr” (same ipc used above example).

test_odbc_kill.php - kill XMLSERVICE job

<?php
require_once('connection.inc');
// call IBM i
if ($i5persistentconnect) $conn = odbc_pconnect($database,$user,$password);
else $conn = odbc_connect($database,$user,$password);
if (!$conn) die("Bad connect: $database,$user");
$ctlKill="*immed";
$clobInKill = '<?xml version="1.0"?>';
$sql = "call $libxmlservice.iPLUGR4K('$ipc','$ctlKill','$clobInKill')";
$ret=odbc_exec($conn,$sql);
?>

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE

Retrieved from http://youngiprofessionals.com/wiki/index.php/XMLService/XMLSERVICE123PHPODBC
Page last modified on October 21, 2013, at 01:57 PM EST