[Sqlrelay-discussion] Possible bug with sqlrelay-0.40 with oracle output bind cursors

[<ben...@fr...>]

Hi all,

I've just installed SQL Relay 0.40/Oracle on a Debian machine and found
a possible bug.
It is not reproducible with SQL Relay 0.39.4. (i use sqlrelay 0.39.4 /
rudiment 0.31 and oracle 9i in production without problem)

It concerns output bind cursors : it seems to be not freed correctly.

My tests :
I spawn 5 sqlr-connection.
I execute (php code) several times the same oracle package (with output
cursor). At the 6th execution, there is no data return until I reload
sqlrelay connexions. SELECT operations and Functions still working in this
state.
It seems that the output cursor is not freed correctly. (with 10
connections, the 11st request failed).


Based on the following discussion :
http://sourceforge.net/mailarchive/message.php?msg_id=47AC89D1.6030107%40portavita.nl
I commented out the 1658th ligne of oracle8connection.C. (with the
ligne, sqlr-connexions crashed (segfault maybe), when it's commented
out, they don't segfault anymore, but don't work better.)




Here some php and SQL to reproduce the bug :

create or replace PACKAGE               testrelay_proc AS
  TYPE ref_cursor IS REF CURSOR;
  PROCEDURE testrelay (
         PI$SITE_ID IN NUMBER
       , C_CURSOR       OUT ref_cursor
       , PI$ERROR       OUT integer);

  PROCEDURE testrelay2 (
         PI$SITE_ID IN NUMBER
       , PI$ERROR       OUT integer);
END testrelay_proc;

create or replace PACKAGE BODY               testrelay_proc AS
  PROCEDURE testrelay(
         PI$SITE_ID IN NUMBER
       , C_CURSOR       OUT ref_cursor
       , PI$ERROR       OUT integer) AS
  BEGIN
  PI$ERROR := -20999;
  OPEN C_CURSOR FOR
    SELECT NAME FROM xxxxxxxxx WHERE ID=PI$SITE_ID;
    PI$ERROR := 0;
    EXCEPTION
      WHEN OTHERS THEN
          IF C_CURSOR%ISOPEN THEN
              CLOSE C_CURSOR;
          END IF;
          PI$ERROR := sqlcode;
  END testrelay;

  PROCEDURE testrelay2 (
         PI$SITE_ID IN NUMBER
       , PI$ERROR       OUT integer) AS
  BEGIN
  PI$ERROR := -20999;
    PI$ERROR := 2;
  END testrelay2;
END testrelay_proc;



create or replace FUNCTION testrelay4 (
  PI$SITE_ID IN NUMBER
  ) RETURN number  as

  LN$NAME number;
  BEGIN
  SELECT 155 into LN$NAME FROM xxxxxxxxx ID=PI$SITE_ID AND rownum < 2;
  return LN$NAME;
end testrelay4;


<?php
    $con=sqlrcon_alloc("IP_sqlrelay",PORT,"","USER","PASS",1,3);
    $cur=sqlrcur_alloc($con);

    echo "testrelay1 <br>";
    $query1 = 'select system.testrelay4(10) from dual';
    sqlrcur_sendQuery($cur, $query1);

    echo sqlrcur_getField($cur,0,0); // returns 1 row 1 col.
    //Always return 155 (work fine in every case evry time)

    echo "<br>testrelay2 <br>";
    $query3 = 'BEGIN system.testrelay_proc.testrelay2(:SITE_ID,
:ERROR_BIND);END;';
    sqlrcur_prepareQuery($cur,$query3);
    sqlrcur_inputBind($cur,'SITE_ID', 10);
    sqlrcur_defineOutputBindInteger($cur,'ERROR_BIND');
    $resultquery = sqlrcur_executeQuery($cur);
    $bindcurError = sqlrcur_getOutputBindInteger($cur,'ERROR_BIND');
    var_dump($bindcurError);
    // Always return int 2 (work fine in every case)

    echo "testrelay3 - The Real Problem";
    $query = 'BEGIN system.testrelay_proc.testrelay(:SITE_ID,
:CURSOR_BIND, :ERROR_BIND);END;';
    sqlrcur_prepareQuery($cur,$query);
    sqlrcur_inputBind($cur,'SITE_ID', 10);

sqlrcur_defineOutputBindCursor($cur,'CURSOR_BIND');

sqlrcur_defineOutputBindInteger($cur,'ERROR_BIND');
    $resultquery = sqlrcur_executeQuery($cur);
    $bindcur =
sqlrcur_getOutputBindCursor($cur,'CURSOR_BIND');
    $bindcurError =
sqlrcur_getOutputBindInteger($cur,'ERROR_BIND');

    //Fetching from output cursor
    sqlrcur_fetchFromBindCursor($bindcur);
    $iNbFirstResult = sqlrcur_rowCount($bindcur);
    $return['NbResult'] =
$iNbFirstResult;


    for ($i=0; $i<sqlrcur_rowCount($bindcur); $i++) {
       for ($j=0; $j<sqlrcur_colCount($bindcur); $j++)
{
           $return[$i][sqlrcur_getColumnName($bindcur,$j)] =
sqlrcur_getField($bindcur,$i,$j);

       }

}

    var_dump($return);
    // work after sqlrelay restart for number of available connections -
but failed after : return array  'NbResult' => int 0

    // Clean cursors, connexions ...
    sqlrcon_endSession($con);
    sqlrcur_free($bindcur);
    sqlrcur_free($cur);
    sqlrcon_free($con);
?>


When changing connexion pointing to a sqlrelay 0.39.4, it's always working.

Someone else got the same bug ?
I didnt find any solutions.

Best regards.
Benoit Cattié