From YiPs Wiki (i powered)

XMLService: XMLSERVICEPAS

See XMLSTOREDP Program Associated Space (PAS)?

Goto Main Page
Goto Documents

XMLSERVICE (XMLSTOREDP) stored procedures iPLUGxxx, iPLUGRxxx are automatically updated on SAVOBJ/RSTOBJ via IBM i PGM/SRVPGM object Program Associated Space (PAS), so you will NOT not need crtsql, crtsql2, etc., when using simple SAV/RST operations (PAS works on V5, V6, V7+).

<?php
// see connection.inc param details ...
require_once('connection.inc');
// call IBM i
if ($i5persistentconnect) $conn = db2_pconnect($database,$user,$password);
else $conn = db2_connect($database,$user,$password);
if (!$conn) die("Bad connect: $database,$user");

$drop = <<<ENDPROC
DROP PROCEDURE QGPL.EXPLAIN;
ENDPROC;
$ret = db2_exec($conn, $drop);

$create = <<<ENDPROC
CREATE PROCEDURE QGPL.EXPLAIN ( 
	IN P_PGMNAME VARCHAR(50)  ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC QGPL.EXPLAIN
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	BEGIN 
	-- DECLARE VARIABLES 
	DECLARE V_CMDSTMT CHAR    ( 100 ) ; 
	DECLARE V_ROW     CHAR    ( 133 ) ; 
	DECLARE V_CMDLEN  INTEGER ; 
        DECLARE LOOKING_FOR_CREATE INTEGER;
        DECLARE FOUND_ONE          INTEGER;
	DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 

	DECLARE C_PRTSQL CURSOR FOR SELECT C1 FROM SESSION.PRTT1 ;

        DECLARE GLOBAL TEMPORARY TABLE 
	  SESSION.PRTT1 ( c1 char(133) ) WITH REPLACE ON COMMIT PRESERVE ROWS ; 

        --
        -- Override the command output into the temporary table instead of the spool file
        --
        SET V_CMDSTMT = 'OVRDBF FILE(QSYSPRT) TOFILE(QTEMP/PRTT1) OVRSCOPE(*JOB)';
        SET V_CMDLEN  = LENGTH(V_CMDSTMT);
        CALL QSYS2.QCMDEXC( V_CMDSTMT , V_CMDLEN ) ; 

        --
        -- Call the PRTSQLINF command, for the given library/program *PGM|*SRVPGM input
        --
        SET V_CMDSTMT = 'PRTSQLINF ' || P_PGMNAME;
        SET V_CMDLEN  = LENGTH(V_CMDSTMT);
        CALL QSYS2.QCMDEXC( V_CMDSTMT , V_CMDLEN ) ; 

        --
        -- Remove rows which do not contain procedure or function entries
        --

        -- Loop, preserving rows, through the end of the statement
        -- when these statements are found in the output
        -- 'CREATE PROCEDURE%' AND 'CREATE FUNCTION%'


        OPEN C_PRTSQL;
	FETCH FROM C_PRTSQL INTO V_ROW ; 
        SET LOOKING_FOR_CREATE = 1;

	W1 : WHILE ( SQLSTATE = '00000' ) DO 

        IF LOOKING_FOR_CREATE = 1 THEN
        --
        -- We're looking for the start of a 'CREATE PROCEDURE' OR 'CREATE FUNCTION' statement
        -- If we find one, keep it.  Else, delete the row from the output table.
        --
        SET FOUND_ONE = LOCATE('CREATE PROCEDURE', V_ROW);
        IF FOUND_ONE = 0 THEN 
           SET FOUND_ONE = LOCATE('CREATE FUNCTION', V_ROW);
        END IF;

        IF FOUND_ONE = 0 THEN 
           DELETE FROM SESSION.PRTT1 WHERE CURRENT OF C_PRTSQL;
        ELSE        
           SET FOUND_ONE = LOCATE('EXTERNAL NAME', V_ROW);
           IF FOUND_ONE = 0 THEN 
              SET LOOKING_FOR_CREATE = 0;
           END IF;
        END IF;
        GOTO LOOP_IT;

        ELSE
        --
        -- We're looking for the end of a 'CREATE PROCEDURE' OR 'CREATE FUNCTION' statement
        --

        SET FOUND_ONE = LOCATE('EXTERNAL NAME', V_ROW);
        IF FOUND_ONE <> 0 THEN 
           SET LOOKING_FOR_CREATE = 1;
        END IF;

        END IF;

        LOOP_IT:
	FETCH FROM C_PRTSQL INTO V_ROW ; 
	END WHILE W1 ; 
        CLOSE C_PRTSQL;

        --
        -- Return the remaining rows to the caller
        --
        OPEN C_PRTSQL;

	END; 
ENDPROC;
$ret = db2_exec($conn, $create);

$stmt = db2_prepare($conn, "call QGPL.EXPLAIN(?)");
if (!$stmt) die("Bad prepare: ".db2_stmt_errormsg());


$PASs = array("XMLSERVICE/XMLSTOREDP *SRVPGM","ZENDSVR/XMLSTOREDP *SRVPGM");
foreach ($PASs as $PAS) {
echo "<br><br>\n\n****  $PAS ****\n";
$ret=db2_execute($stmt,array($PAS));
if (!$ret) die("Bad execute: ".db2_stmt_errormsg());
$i=0;
$line = "";
while ($row = db2_fetch_array($stmt)){
  $line .= trim($row[0]);
  if (strpos($line,"PARAMETER STYLE GENERAL")>0) {
    if (strpos($line,"RESULT SETS")>0) 
      $type = "result set";
    else 
      $type = "out param*";
    $i++;
    echo "<br>$i ($type) - $line\n";
    $line = "";
  }
}
}

?>

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE

Retrieved from http://youngiprofessionals.com/wiki/index.php/XMLService/XMLSERVICEPAS
Page last modified on October 21, 2013, at 03:10 PM EST