[Sqlrelay-discussion] Bug report, empty cursor/blob, with code to reproduce problem

[Ingmar B. <sw...@gm...>]

BUG report, empty cursor/blob, with code to reproduce problem

sqlrelay 0.39.2
Oracle 10g
php 4.3.11

I've encountered a bug that causes blobs or cursors to be empty
when they shouldn't be.  I've made an example which uses only oracle
functions so that it will be easy for you to verify/reproduce the bug.
I've encountered this bug several times, and in all these cases it happened
two steps. First an oracle procedure with multiple out parameters was
called.
And after that, a procedure was called which produces a reference cursor
or a blob. When the reference cursor of the blob is read out, it produces
the right results or nothing depending on the order in which the variables
are bound in the first procedure. By running the following code you will
see what I mean. The code does two things. First a query is executed
that does a call to dbms_output.get_line. The corresponding cursor is
freed afterwards. Then another query is performed which opens a cursor
for "SELECT * FROM DUAL". The rowcount for this cursor is echod,
this should always display one. However, by changing the order of the binds
in the first query, the cursor is suddenly empty. The same thing happens
when
the second query reads out a blob. I suspect that something goes wrong
with the freeing of resources, as in some more complex situations I believe
this problem has led to clients keeping connections while they should be
freed.

    //creates a new connection which is stored in the variable $conn
    //make_new_connection(.....);

    $sql = "BEGIN dbms_output.get_line(line => :line_out, status =>
:status_out);  END;";
    $cur = sqlrcur_alloc($conn);
    sqlrcur_prepareQuery($cur, $sql);

    /* In this situation, the cursor which references DUAL
     * in the next query will have a rowcount of 1, which is correct.
     * When the order of these two binds is switched, then the
     * the cursor referencing DUAL will have a rowcount of 0 !!!
     */
    sqlrcur_defineOutputBindInteger($cur,"status_out");
    sqlrcur_defineOutputBindString($cur,"line_out", 255);

    sqlrcur_executeQuery($cur);
    $line = sqlrcur_getOutputBindString( $cur, "line_out" );

    $status = sqlrcur_getOutputBindInteger( $cur, "status_out" );
    sqlrcur_free($cur);

    /*
     * This query declares a reference cursor which is opened
     * for SELECT * FROM DUAL, so it's opened for DUAL. The
     * reference cursor is bound to the out parameter curs.
     * Hence, echo sqlrcur_rowCount($cursor); should always display 1
     * as SELECT * FROM DUAL will give 1 row
     */
    $sql = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype; BEGIN OPEN
ref_cur FOR SELECT * FROM DUAL; :curs := ref_cur ; END;";
    $cur = sqlrcur_alloc($conn);
    sqlrcur_prepareQuery($cur,
$sql);
    sqlrcur_defineOutputBindCursor($cur,"curs");
    sqlrcur_executeQuery($cur);
    $cursor = sqlrcur_getOutputBindCursor( $cur, "curs" );
    sqlrcur_fetchFromBindCursor($cursor);

    echo sqlrcur_rowCount($cursor);

    sqlrcur_free($cursor);
    sqlrcur_free($cur);
    sqlrcon_free($conn);

I would very much like to hear whether the above code has helped
to verify what I've discribed.


Yours sincerely,

Ingmar Brouns