Annotation of embedaddon/php/ext/oci8/tests/refcur_prefetch_2.phpt, revision 1.1
1.1 ! misho 1: --TEST--
! 2: Prefetch with REF cursor. Test No 2
! 3: --SKIPIF--
! 4: <?php if (!extension_loaded('oci8')) die("skip no oci8 extension");
! 5: if (!extension_loaded('oci8')) die("skip no oci8 extension");
! 6: require(dirname(__FILE__)."/connect.inc");
! 7: if (preg_match('/Release 1[012]\./', oci_server_version($c), $matches) !== 1) {
! 8: die("skip expected output only valid when using Oracle 10g or greater databases");
! 9: } else if (preg_match('/^(11\.2|12)\./', oci_client_version()) != 1) {
! 10: die("skip test expected to work only with Oracle 11gR2 or greater version of client");
! 11: }
! 12: ?>
! 13: --FILE--
! 14: <?php
! 15: require dirname(__FILE__)."/connect.inc";
! 16:
! 17: // Creates the necessary package and tables.
! 18: $stmtarray = array(
! 19: "DROP TABLE refcurtest",
! 20: "CREATE TABLE refcurtest (c1 NUMBER, c2 VARCHAR(20))",
! 21: "CREATE or REPLACE PACKAGE refcurpkg is
! 22: type refcursortype is ref cursor;
! 23: procedure open_ref_cur(cur1 out refcursortype);
! 24: procedure fetch_ref_cur(cur1 in refcursortype, c1 out number,c2 out varchar2);
! 25: end refcurpkg;",
! 26: "CREATE or REPLACE PACKAGE body refcurpkg is
! 27: procedure open_ref_cur(cur1 out refcursortype) is
! 28: begin
! 29: open cur1 for select * from refcurtest order by c1;
! 30: end open_ref_cur;
! 31: procedure fetch_ref_cur(cur1 in refcursortype, c1 out number,
! 32: c2 out varchar2) is
! 33: begin
! 34: fetch cur1 into c1,c2;
! 35: end fetch_ref_cur;
! 36: end refcurpkg;"
! 37: );
! 38:
! 39: oci8_test_sql_execute($c, $stmtarray);
! 40:
! 41: // Insert 500 rows into the table.
! 42: $insert_sql = "INSERT INTO refcurtest (c1, c2) VALUES (:c1,:c2)";
! 43: if (!($s = oci_parse($c, $insert_sql))) {
! 44: die("oci_parse(insert) failed!\n");
! 45: }
! 46:
! 47: for ($i = 0; $i <= 500; $i++) {
! 48: $val2 = 'test'.$i;
! 49: oci_bind_by_name($s,':c1',$i);
! 50: oci_bind_by_name($s,':c2',$val2);
! 51: if (!oci_execute($s)) {
! 52: die("oci_execute(insert) failed!\n");
! 53: }
! 54: }
! 55:
! 56: // Steps to Fetch from PHP . For every sub-test,the cursor is bound and then executed.
! 57:
! 58: $sql1 = "begin refcurpkg.open_ref_cur(:cur1); end;";
! 59: $s1 = oci_parse($c,$sql1);
! 60: $cur1 = oci_new_cursor($c);
! 61: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
! 62: die("oci_bind_by_name(sql1) failed!\n");
! 63: }
! 64:
! 65:
! 66: // Steps to Fetch from PL/SQL . For every sub-test,the cursor is bound and then executed.
! 67:
! 68: $sql2 = "begin refcurpkg.fetch_ref_cur(:curs1,:c1,:c2); end;";
! 69: $s2 = oci_parse($c,$sql2);
! 70: if (!oci_bind_by_name($s2, ":curs1", $cur1, -1, SQLT_RSET)) {
! 71: die("oci_bind_by_name(sql2) failed!\n");
! 72: }
! 73: if (!oci_bind_by_name($s2, ":c1", $c1, -1, SQLT_INT)) {
! 74: die("oci_bind_by_name(sql2) failed!\n");
! 75: }
! 76: if (!oci_bind_by_name($s2, ":c2", $c2, 20, SQLT_CHR)) {
! 77: die("oci_bind_by_name(sql2) failed!\n");
! 78: }
! 79:
! 80:
! 81: echo "------Test 1- Check Roundtrips with prefetch 0 and 5 -----------\n";
! 82: oci_execute($s1);
! 83: oci_execute($cur1);
! 84: $initial_rt = print_roundtrips($c);
! 85: oci_set_prefetch($cur1,0);
! 86: for ($i = 0;$i<5;$i++) {
! 87: var_dump(oci_fetch_row($cur1));
! 88: }
! 89:
! 90: $cnt = (print_roundtrips($c) - $initial_rt);
! 91: echo "Number of roundtrips made with prefetch count 0 for 5 rows is $cnt\n";
! 92:
! 93: $initial_rt = print_roundtrips($c);
! 94: oci_set_prefetch($cur1,5);
! 95: for ($i = 0;$i<5;$i++) {
! 96: var_dump(oci_fetch_row($cur1));
! 97: }
! 98:
! 99: $cnt = (print_roundtrips($c) - $initial_rt );
! 100: echo "Number of roundtrips made with prefetch count 5 for 5 rows is $cnt\n";
! 101:
! 102: echo "------Test 2 - Set Prefetch before PL/SQL fetch ----------\n";
! 103: // Fetch from PHP
! 104: $cur1 = oci_new_cursor($c);
! 105: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
! 106: die("oci_bind_by_name(sql1) failed!\n");
! 107: }
! 108:
! 109: echo "Fetch Row from PHP\n";
! 110: oci_execute($s1);
! 111: oci_execute($cur1);
! 112: var_dump(oci_fetch_row($cur1));
! 113: oci_set_prefetch($cur1,5);
! 114:
! 115: // Fetch from PL/SQL
! 116: if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
! 117: die("oci_bind_by_name(sql2) failed!\n");
! 118: }
! 119: oci_execute($s2);
! 120: echo "Fetch Row from PL/SQL\n";
! 121: var_dump($c1);
! 122: var_dump($c2);
! 123:
! 124: function print_roundtrips($c) {
! 125: $sql_stmt = "select value from v\$mystat a,v\$statname c where
! 126: a.statistic#=c.statistic# and c.name='SQL*Net roundtrips to/from client'";
! 127: $s = oci_parse($c,$sql_stmt);
! 128: oci_define_by_name($s,"VALUE",$value);
! 129: oci_execute($s);
! 130: oci_fetch($s);
! 131: return $value;
! 132: }
! 133:
! 134: // Clean up here
! 135:
! 136: $stmtarray = array(
! 137: "drop package refcurpkg",
! 138: "drop table refcurtest"
! 139: );
! 140:
! 141: oci8_test_sql_execute($c, $stmtarray);
! 142:
! 143: echo "Done\n";
! 144: ?>
! 145: --EXPECTF--
! 146: ------Test 1- Check Roundtrips with prefetch 0 and 5 -----------
! 147: array(2) {
! 148: [0]=>
! 149: string(1) "0"
! 150: [1]=>
! 151: string(5) "test0"
! 152: }
! 153: array(2) {
! 154: [0]=>
! 155: string(1) "1"
! 156: [1]=>
! 157: string(5) "test1"
! 158: }
! 159: array(2) {
! 160: [0]=>
! 161: string(1) "2"
! 162: [1]=>
! 163: string(5) "test2"
! 164: }
! 165: array(2) {
! 166: [0]=>
! 167: string(1) "3"
! 168: [1]=>
! 169: string(5) "test3"
! 170: }
! 171: array(2) {
! 172: [0]=>
! 173: string(1) "4"
! 174: [1]=>
! 175: string(5) "test4"
! 176: }
! 177: Number of roundtrips made with prefetch count 0 for 5 rows is 6
! 178: array(2) {
! 179: [0]=>
! 180: string(1) "5"
! 181: [1]=>
! 182: string(5) "test5"
! 183: }
! 184: array(2) {
! 185: [0]=>
! 186: string(1) "6"
! 187: [1]=>
! 188: string(5) "test6"
! 189: }
! 190: array(2) {
! 191: [0]=>
! 192: string(1) "7"
! 193: [1]=>
! 194: string(5) "test7"
! 195: }
! 196: array(2) {
! 197: [0]=>
! 198: string(1) "8"
! 199: [1]=>
! 200: string(5) "test8"
! 201: }
! 202: array(2) {
! 203: [0]=>
! 204: string(1) "9"
! 205: [1]=>
! 206: string(5) "test9"
! 207: }
! 208: Number of roundtrips made with prefetch count 5 for 5 rows is 2
! 209: ------Test 2 - Set Prefetch before PL/SQL fetch ----------
! 210: Fetch Row from PHP
! 211: array(2) {
! 212: [0]=>
! 213: string(1) "0"
! 214: [1]=>
! 215: string(5) "test0"
! 216: }
! 217: Fetch Row from PL/SQL
! 218: int(101)
! 219: string(%d) "test101"
! 220: Done
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to refcur_prefetch_2.phpt CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / oci8 / tests