MySQL Forums :: Stored Procedures :: Stored Procedures retrieving multiple result sets


Advanced Search

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
Stored Procedures retrieving multiple result sets 2961 Prasad Kawade 05/21/2016 11:17PM
Re: Stored Procedures retrieving multiple result sets 407 Peter Brawley 05/21/2016 11:52PM
Re: Stored Procedures retrieving multiple result sets 381 Prasad Kawade 05/22/2016 12:04AM
Re: Stored Procedures retrieving multiple result sets 413 Peter Brawley 05/22/2016 11:43AM


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.