Re: MySQL Store Procedure can't return multiple datasets

[Peter Brawley]

> 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.