#22 Can't Use Stored Procedure

open
Mark Hessling
None
5
2008-02-27
2008-02-27
TheDavidFactor
No

when I have a stored procedure that returns a record set I can't execute it with Rexx/SQL.

the sql code is: "call test()"

SQLEXECUTE doesn't return a recordset.
SQLOPEN returns an error because it's not a select statement
SQLCOMMAND returns a server error with the server saying "the procedure test() can't return a record set in the current context"

Discussion

  • Mark Hessling
    Mark Hessling
    2008-02-27

    • assigned_to: nobody --> rexx
     
  • Mark Hessling
    Mark Hessling
    2008-02-27

    Logged In: YES
    user_id=86185
    Originator: NO

    The MySQL driver for Rexx/SQL was written well before MySQL had stored procedures and the ability to execute multiple statements. It does not use the APIs available for handling multiple statements. The page at:
    http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
    explains how MySQL handles multiple statements (and result sets from a stored procedure).

    Given the example on that page there currently isn't the framework in place in Rexx/SQL to handle this. If the stored procedure returns multiple "rows affected" results, how is that to be provided to the caller of sqlcommand()?

    Similarly if the stored procedure contains:

    'select * from table1;' (where table1 is (col1 char, col2 char, col3 char) and has 2 rows )
    and
    'select * from table2;' (where table2 is (id integer, name varchar) and has 3 rows )

    should 'call sqlcommand( q1, 'call test() )' return:

    q1.col1.1 = a; q1.col2.1 = a; q1.col3.1 = a;
    q1.col1.2 = b; q1.col2.2 = b; q1.col3.2 = b;
    q1.id.3 = 12; q1.name.3 = 'mark'
    q1.id.4 = 13; q1.name.4 = 'fred'
    q1.id.5 = 14; q1.name.5 = 'joe'
    and all 0 stems set to 5?

    Then there is the problem with eventually adding prepared statements in MySQL to Rexx/SQL. Their use is limited to single statements (see same link above), so anything generic I might do now to support multiple statements will be incompatible with prepared statements.

    Rexx/SQL was designed (a long time ago) as a simple, database-portable, interface, not as an interface that handle every conceivable construct in different databases.

    I'll give some thought to how this can be implemented. Any suggestions welcome.

     
  • TheDavidFactor
    TheDavidFactor
    2008-02-28

    Logged In: YES
    user_id=1011368
    Originator: YES

    Thanks for addressing this issue. I'm a little out of my area of expertise here so if I ask dumb questions or make dumb comments please forgive me.

    I'm actually using the Rexx/SQL for ODBC interface, not the native MySQL interface. I don't know if that makes a difference or not, but it seems like it could to me. I've been doing some Google research on this after reading your response and it seems that there is an ODBC standard way of calling stored procedures which MySQL recently started supporting in their ODBC driver.

    Does this make it any simpler or change anything? I will continue to research.

     
  • TheDavidFactor
    TheDavidFactor
    2008-02-28

    Logged In: YES
    user_id=1011368
    Originator: YES

    I have continued doing research on this by trying other packages to see how they handle the situation. If I use a packet sniffer I can see that the server is returning two record sets and MySQL's graphical Query Browser displays two record sets. However two other packages I tried (SQLyog & ODBC Query Tool) both only display the first record set returned. It seems that at least for a first implementation there is some precedent for ignoring subsequent record sets when more then a single record set is returned. What are your thoughts?