Re: [Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures
Brought to you by:
mused
From: Firstworks/4access <dav...@fi...> - 2005-09-22 19:13:15
|
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> > ********************************************************************* |