php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #43492 problems with closing statements when selecting cursors
Submitted: 2007-12-04 07:47 UTC Modified: 2008-02-26 00:27 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: michael dot virnstein at brodos dot de Assigned:
Status: Not a bug Package: OCI8 related
PHP Version: 5.2.5 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: michael dot virnstein at brodos dot de
New email:
PHP Version: OS:

 

 [2007-12-04 07:47 UTC] michael dot virnstein at brodos dot de
Description:
------------
As requested in Bug #42496 (http://bugs.php.net/bug.php?id=42496), here's a separate bug report for the cursor issue:

If you use a query with more than one cursor as result column, the statement won't get closed correctly. You have to call oci_free_statement() for as many times as there are result-cursors to get the statement closed correctly.

I tried the fix for LOBs as reported in Bug #42496 for SQLT_RSET, but that didn't work really well. There are two problems:

1. If you use the reproduce code below with the fix (removing "zend_list_addref(outcol->statement->id);" from
SQLT_RSET in php_oci_define_callback function() [oci8_statement.c]), you'll get an "ORA-01001: invalid cursor" when you try to fetch from the returned
cursor. It seems that the statement ($stmt) is closed when leaving the function.

2. If you use the reproduce code below without the fix above, you'll get an "ORA-01000: maximum open cursors exceeded" when you hit you're "open_cursors" db-setting. The statement ($stmt) never gets closed and leaves a dangling cursor.

I don't know if that can be implemented easily, but the best thing would
be, that a statement gets closed if it is already out of scope and
therefore not accessible from the php-code anymore and all nested
cursors got closed already.



Reproduce code:
---------------
<?php
 
$conn = ocilogon('user', 'pass', 'db');
 
function fetch($conn, $id)
{
    $result = null;
    $stmt = ociparse($conn, 'select cursor(select * from dual) c from
dual');
    ociexecute($stmt, OCI_DEFAULT);
    ocifetchinto($stmt, $result, OCI_ASSOC);
    ociexecute($result['C'], OCI_DEFAULT);
    return $result['C'];
}

for ($id = 1; $id <= 300; $id++) {
    $cur = fetch($conn, $id);
    ocifetchinto($cur, $row, OCI_ASSOC);
    ocifreestatement($cur);    
}
?>

Expected result:
----------------
Neither an "ORA-01001: invalid cursor" nor an "ORA-01000: maximum open cursors exceeded"

Actual result:
--------------
Depends on the fix. Either "ORA-01001: invalid cursor" or "ORA-01000: maximum open cursors exceeded"

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-12-04 09:09 UTC] michael dot virnstein at brodos dot de
edit summary
 [2007-12-11 20:15 UTC] sixd@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

My colleague looked at this and sent the update below.  Note the two added lines to the testcase allow the script to complete successfully.

/*

While fetching the data from the ref cursor, the parent statement
needs to be around.  It makes sense that the refcount of the statement
is to be incremented.  In the test case provided, the parent statement
goes out of scope before fetching the data from the ref cursor.  Also
when the parent statement goes out of scope, it is not released which
is causing the leak.

*/

function fetch($c, $i) {
    global $s;   // ADDED
    $s = ociparse($c, 'select cursor(select * from bug43492_tab) c from bug43492_tab');
    ociexecute($s, OCI_DEFAULT);
    ocifetchinto($s, $result, OCI_ASSOC);
    ociexecute($result['C'], OCI_DEFAULT);
    return $result['C'];
}

for($i = 0; $i < 300; $i++) {
    $cur = fetch($c, $i);
    for($j = 0; $j < 10; $j++) {
        ocifetchinto($cur, $row, OCI_NUM);
        echo "row=$row[0] ";
    }
    echo "\n";
    ocifreestatement($cur);
    ocifreestatement($s);   // ADDED
}


 [2008-02-21 19:33 UTC] sixd@php.net
Also see http://bugs.php.net/bug.php?id=44206
 [2008-02-26 00:27 UTC] sixd@php.net
With the fix for Bug #44206, the original testcase succeeds.  However it is good practice to explicitly close cursors when they are no longer needed.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Mon Oct 14 05:01:26 2019 UTC