MySQL Forums
Forum List  »  Stored Procedures

Re: MySQL Store Procedure can't return multiple datasets
Posted by: Peter Brawley
Date: September 14, 2020 10:19AM

> When you wrote "mysql client program" \I'm > assigning you mean the Python code I'm writing

No. I mean the mysql client program that comes with MySQL server.

I ran your DDL and sproc code on 8.0.21 in the mysql client program. Worked without error.

Ran it in theUsual (https://www.artfulsoftware.com/theusualReadMe.html) against 8.0.21 and it runs without error there too.

No need for me to try it in phpMyAdmin ... many versions have had this issue. Here's the code in theUsual for calling sprocs ... as you can see, it calls a browser() function for every scrollable $result. You need to implement such logic in Python.

function docall( $thispage, $qry, $debug=FALSE, &$errs ) {
  GLOBAL $conn;
  $res = mysqli_real_query( $conn, $qry );
  $e = mysqli_error( $conn );
  if( !empty( $e )) {
    $errs++;
    if( $debug ) echo "<b>Real_query error: $e</b> <br />";
  }
  do {
    $result = mysqli_store_result( $conn );
    $e = mysqli_error( $conn );
    if( !empty( $e )) {
      $errs++;
      if( $debug ) echo "<b>Store_result error: $e</b> <br />";
    }
    $cols = mysqli_field_count( $conn );
    if( is_bool( $result )) {
      $e = mysqli_error( $conn );
      echo ( empty($e) ? ( $cols ? "Query in stored procedure returned no data" : "OK" ) : $e ), "<br/>";
    }
    else {
      $flags = array( TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, "", FALSE );
      $rows = mysqli_num_rows( $result );
      $dummy = null;
      echo "Retrieved $rows row", ( $rows==1 ? "" : "s" ), "...<br/>";
      browser( $thispage, $dummy, $dummy, $qry, $result, $dummy, $dummy, $flags );
      mysqli_free_result( $result );
      $e = mysqli_error( $conn );
      if( !empty( $e )) {
        $errs++;
        if( $debug ) echo "<b>Free_result error: $e</b> <br />";
      }
    }
    if( mysqli_more_results( $conn ) ) echo "<br/>";
    // STRICT MODE NEEDS FREE CALL TO BE FIRST:
  } while( mysqli_more_results($conn) && mysqli_next_result($conn) ); 
}



Edited 1 time(s). Last edit at 09/14/2020 10:22AM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL Store Procedure can't return multiple datasets
74
September 14, 2020 10:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.