[Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures

[Dennis B. <den...@mc...>]

Hi,

When running the code below repeatedly I find that the
cursor created in 

 	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');

is never closed on the oracle server.  After enough iterations the
server will error with to many open cursors.

I am running sqlrelay 3.6.4, but the same results occur on 3.6.2.
PHP version is 4.4 . Oracle is 9.2g

Here is a couple of consecutive sample results from this code:

1.
there are 711 open cursors
Array
(
    [StudentSortOrder] => 0
    [ClassID] => 818
    [Class] => Best Wisshes
    [ClassDesc] => 
    [GradeLevel] => 4
    [ActiveStudents] => 1
)
there are 712 open cursors

2.
there are 712 open cursors
Array
(
    [StudentSortOrder] => 0
    [ClassID] => 818
    [Class] => Best Wisshes
    [ClassDesc] => 
    [GradeLevel] => 4
    [ActiveStudents] => 1
)
there are 713 open cursors



What might I be doing wrong?


*************************************************************************************
<pre>
<?php

$conn =
sqlrcon_alloc('localhost','','/tmp/emgames.socket','webuser','thisisit',0,1);
//print_r($conn);

//print_r($relay);

$countquery = "select count(*) from sys.gv_\$open_cursor";

$qcur = sqlrcur_alloc($conn);
sqlrcur_sendquery($qcur,$countquery);
sqlrcon_endSession($conn);

$rowarray=sqlrcur_getRow($qcur,0);

echo "there are {$rowarray[0]} open cursors\n";

$procedure = "BEGIN sa.s_classes_teacher(2559,:returncur); END;";

if($cur = sqlrcur_alloc($conn)){

	sqlrcur_prepareQuery($cur,$procedure);

	sqlrcur_defineOutputBindCursor($cur,'returncur');

	sqlrcur_executeQuery($cur);
  
	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');
  
	sqlrcur_fetchFromBindCursor($returncur);


	$numrows = sqlrcur_rowCount($returncur);
	$numcolumns = sqlrcur_colCount($returncur);
      
      
	// Get the column names and types
	for($i = 0; $i < $numcolumns; $i++){
		$columnnames[$i] = sqlrcur_getColumnName($returncur,$i);
		$columntypes[$i] = sqlrcur_getColumnType($returncur,$i);
	}

	// Get each row column by column and store each of them in the Results
array
	for($i = 0 ; $i < $numrows  ; $i++) {
		for($j = 0 ; $j < $numcolumns; $j++){
			$results[$i][$columnnames[$j]] = sqlrcur_getField($returncur,$i,$j);
		}
	}
  print_r($results[0]);    
	sqlrcon_endsession($conn);
	sqlrcur_clearbinds($returncur);
	sqlrcur_free($returncur);
	sqlrcur_clearbinds($cur);
	sqlrcur_free($cur);

 }
 else {
	 echo "couldn't open cursor";
 }

sqlrcur_sendquery($qcur,$countquery);
sqlrcon_endSession($conn);


$rowarray=sqlrcur_getRow($qcur,0);
 
echo "there are {$rowarray[0]} open cursors";


sqlrcur_free($qcur);
sqlrcon_free($conn);
?>
</pre>
*********************************************************************
-- 
Dennis Bolton
Manager, Web Engineering        
McGraw-Hill Learning Group Technology
425-806-3495