MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Procedures retrieving multiple result sets
Posted by: Peter Brawley
Date: May 21, 2016 11:52PM

sprocs return multiple resultsets. Usually no problem calling them in the mysql commandline client program.

Calling them in a program you write requires that you use the multi-query facility of the API of the language you're using. The PHP for calling an sproc that might do multiple Selects looks like this ...

$mysqli = new mysqli(  "HOST", "USR", "PWD", "DBNAME" ); 
$ivalue=1; 
$res = $mysqli->multi_query( "CALL myproc($ivalue,@x);SELECT @x" ); 
if( $res ) { 
  $results = 0; 
  do { 
    if ($result = $mysqli->store_result()) { 
      printf( "<b>Result #%u</b>:<br/>", ++$results ); 
      while( $row = $result->fetch_row() ) { 
        // do something with the row 
      } 
      $result->close(); 
      if( $mysqli->more_results() ) echo "<br/>"; 
    } 
  } while( $mysqli->next_result() ); 
} 
$mysqli->close();

The logic is similar in other APIs (Java, C# &c).

> url's where I can go and search some useful content about mysql only

The "Documentation" link at the top of this page, and ...
http://www.artfulsoftware.com/queries.php
http://www.artfulsoftware.com/infotree/mysqltips.php
http://mysql.rjweb.org/



Edited 1 time(s). Last edit at 05/21/2016 11:55PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedures retrieving multiple result sets
4974
May 21, 2016 11:52PM


Sorry, you can't reply to this topic. It has been closed.

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.