[903] | 1 | <?php
|
---|
| 2 | /*
|
---|
| 3 | V4.80 8 Mar 2006 (c) 2000-2007 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://adodb.sourceforge.net
|
---|
| 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 | $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS,'A%'); END;");
|
---|
| 74 |
|
---|
| 75 | if ($rs && !$rs->EOF) {
|
---|
| 76 | print "Test 1 RowCount: ".$rs->RecordCount()."<p>";
|
---|
| 77 | } else {
|
---|
| 78 | print "<b>Error in using Cursor Variables 1</b><p>";
|
---|
| 79 | }
|
---|
| 80 |
|
---|
| 81 | print "<h4>Testing Stored Procedures for oci8</h4>";
|
---|
| 82 |
|
---|
| 83 | $stid = $db->PrepareSP('BEGIN adodb.myproc('.MYNUM.', :myov); END;');
|
---|
| 84 | $db->OutParameter($stid, $myov, 'myov');
|
---|
| 85 | $db->Execute($stid);
|
---|
| 86 | if ($myov != MYNUM) print "<p><b>Error with myproc</b></p>";
|
---|
| 87 |
|
---|
| 88 |
|
---|
| 89 | $stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;",true);
|
---|
| 90 | $a1 = 'Malaysia';
|
---|
| 91 | //$a2 = ''; # a2 doesn't even need to be defined!
|
---|
| 92 | $db->InParameter($stmt,$a1,'a1');
|
---|
| 93 | $db->OutParameter($stmt,$a2,'a2');
|
---|
| 94 | $rs = $db->Execute($stmt);
|
---|
| 95 | if ($rs) {
|
---|
| 96 | if ($a2 !== 'Cinta Hati Malaysia') print "<b>Stored Procedure Error: a2 = $a2</b><p>";
|
---|
| 97 | else echo "OK: a2=$a2<p>";
|
---|
| 98 | } else {
|
---|
| 99 | print "<b>Error in using Stored Procedure IN/Out Variables</b><p>";
|
---|
| 100 | }
|
---|
| 101 |
|
---|
| 102 |
|
---|
| 103 | $tname = 'A%';
|
---|
| 104 |
|
---|
| 105 | $stmt = $db->PrepareSP('select * from tab where tname like :tablename');
|
---|
| 106 | $db->Parameter($stmt,$tname,'tablename');
|
---|
| 107 | $rs = $db->Execute($stmt);
|
---|
| 108 | rs2html($rs);
|
---|
| 109 |
|
---|
| 110 |
|
---|
| 111 | ?> |
---|