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

[Firstworks/4access <dav...@fi...>]

Looks like the server-side isn't cleaning up those cursors properly.
I'll take a look and see if I can figure out what's going on.

Dave

On Thu, 2005-09-22 at 11:01 -0700, Dennis Bolton wrote:
> 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>
> *********************************************************************