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

[David M. <dav...@fi...>]

Wow, that's a strange bug!

Using your code I am able to reproduce it here.  If I turn debug on, I
see some strange behavior when fetching from the bind cursor, so I
should be able to track down what's happening.

I'll let you know what I find.  That sure is odd.

Dave
dav...@fi...

On Thu, 2007-08-30 at 11:30 +0200, Ingmar Brouns wrote:
> 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
> 
>      
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion