library/adodb/tests/testoci8cursor.php

   1 <?php
   2 /*
   3 V4.20 22 Feb 2004  (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
   4   Released under both BSD license and Lesser GPL library license.
   5   Whenever there is any discrepancy between the two licenses,
   6   the BSD license will take precedence.
   7   Set tabs to 4 for best viewing.
   8
   9   Latest version is available at http://php.weblogs.com/
  10 */
  11
  12 /*
  13         Test for Oracle Variable Cursors, which are treated as ADOdb recordsets.
  14
  15         We have 2 examples. The first shows us using the Parameter statement.
  16         The second shows us using the new ExecuteCursor($sql, $cursorName)
  17         function.
  18
  19 ------------------------------------------------------------------
  20 -- TEST PACKAGE YOU NEED TO INSTALL ON ORACLE - run from sql*plus
  21 ------------------------------------------------------------------
  22
  23
  24 -- TEST PACKAGE
  25 CREATE OR REPLACE PACKAGE adodb AS
  26 TYPE TabType IS REF CURSOR RETURN tab%ROWTYPE;
  27 PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames in varchar);
  28 PROCEDURE data_out(input IN varchar, output OUT varchar);
  29
  30 procedure myproc (p1 in number, p2 out number);
  31 END adodb;
  32 /
  33
  34 CREATE OR REPLACE PACKAGE BODY adodb AS
  35 PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames in varchar) IS
  36         BEGIN
  37                 OPEN tabcursor FOR SELECT * FROM tab where tname like tablenames;
  38         END open_tab;
  39
  40 PROCEDURE data_out(input IN varchar, output OUT varchar) IS
  41         BEGIN
  42                 output := 'Cinta Hati '||input;
  43         END;
  44
  45 procedure myproc (p1 in number, p2 out number) as
  46 begin
  47 p2 := p1;
  48 end;
  49 END adodb;
  50 /
  51
  52 ------------------------------------------------------------------
  53 -- END PACKAGE
  54 ------------------------------------------------------------------
  55
  56 */
  57
  58 include('../adodb.inc.php');
  59 include('../tohtml.inc.php');
  60
  61         error_reporting(E_ALL);
  62         $db = ADONewConnection('oci8');
  63         $db->PConnect('','scott','natsoft');
  64         $db->debug = 99;
  65
  66
  67 /*
  68 */
  69
  70         define('MYNUM',5);
  71
  72
  73
  74         $stmt = $db->Prepare("BEGIN adodb.open_tab(:RS,'A%'); END;");
  75         $db->InParameter($stmt, $cur, 'RS', -1, OCI_B_CURSOR);
  76         $rs = $db->Execute($stmt);
  77
  78         if ($rs && !$rs->EOF) {
  79                 print "Test 1 RowCount: ".$rs->RecordCount()."<p>";
  80         } else {
  81                 print "<b>Error in using Cursor Variables 1</b><p>";
  82         }
  83
  84
  85         print "<h4>Testing Stored Procedures for oci8</h4>";
  86
  87         $stid = $db->PrepareSP('BEGIN adodb.myproc('.MYNUM.', :myov); END;');
  88         $db->OutParameter($stid, $myov, 'myov');
  89         $db->Execute($stid);
  90         if ($myov != MYNUM) print "<p><b>Error with myproc</b></p>";
  91
  92
  93         $stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;",true);
  94         $a1 = 'Malaysia';
  95         //$a2 = ''; # a2 doesn't even need to be defined!
  96         $db->InParameter($stmt,$a1,'a1');
  97         $db->OutParameter($stmt,$a2,'a2');
  98         $rs = $db->Execute($stmt);
  99         if ($rs) {
 100                 if ($a2 !== 'Cinta Hati Malaysia') print "<b>Stored Procedure Error: a2 = $a2</b><p>";
 101                 else echo  "OK: a2=$a2<p>";
 102         } else {
 103                 print "<b>Error in using Stored Procedure IN/Out Variables</b><p>";
 104         }
 105
 106
 107         $tname = 'A%';
 108
 109         $stmt = $db->PrepareSP('select * from tab where tname like :tablename');
 110         $db->Parameter($stmt,$tname,'tablename');
 111         $rs = $db->Execute($stmt);
 112         rs2html($rs);
 113
 114
 115 ?>