Asked  8 Months ago    Answers:  5   Viewed   40 times

I have a stored procedure that has multiple result sets. How do I advance to the 2nd result set in mysqli to get those results?

Let's say it's a stored proc like:

create procedure multiples( param1 INT, param2 INT )
BEGIN

SELECT * FROM table1 WHERE id = param1;

SELECT * FROM table2 WHERE id = param2;

END $$

The PHP is something like this:

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');

mysqli_stmt_bind_param( $stmt, 'ii', $param1, $param2 );

mysqli_stmt_execute( $stmt );

mysqli_stmt_bind_result( $stmt, $id );

Then this is the part I can't get to work. I've tried using mysqli_next_result to move to the next result set, but can't get it to work. We did get it to work with mysqli_store_result and mysqli_fetch_assoc/array/row, but for some reason all the ints get returned as blank strings.

Any one else come across this and have a solution?

 Answers

62

I think you're missing something here (the following has not been tested):

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');
mysqli_stmt_bind_param($stmt, 'ii', $param1, $param2);
mysqli_stmt_execute($stmt);
// fetch the first result set
$result1 = mysqli_use_result($db);
// you have to read the result set here 
while ($row = $result1->fetch_assoc()) {
    printf("%dn", $row['id']);
}
// now we're at the end of our first result set.
mysqli_free_result($result1);

//move to next result set
mysqli_next_result($db);
$result2 = mysqli_use_result($db);
// you have to read the result set here 
while ($row = $result2->fetch_assoc()) {
    printf("%dn", $row['id']);
}
// now we're at the end of our second result set.
mysqli_free_result($result2);

// close statement
mysqli_stmt_close($stmt);

Using PDO your code would look like:

$stmt = $db->prepare('CALL multiples(:param1, :param2)');
$stmt->execute(array(':param1' => $param1, ':param2' => $param2));
// read first result set
while ($row = $stmt->fetch()) {
    printf("%dn", $row['id']);
}
$stmt->nextRowset();
// read second result set
while ($row = $stmt->fetch()) {
    printf("%dn", $row['id']);
}

But I have heard that the PDOStatement::nextRowset() is not implemented with the MySQL PDO driver making it impossible to retrieve multiple result sets:

  • PDO nextRowset not working on MySQL
  • pdo_mysql: stored procedure call returning single rowset blocks future queries
  • Can't use stored procedures from PDO on Windows

So, depending on your PHP version, you'd have to stick with your mysqli-solution. By the way: do you use the procedural style deliberately? Using object oriented style with mysqli would make your code look a little bit more appealing (my personal opinion).

Wednesday, March 31, 2021
 
TheTechnicalPaladin
answered 8 Months ago
57

The error message is absolutely right. You are sending 3 parameters to a stores procedure which takes only one.

What you've done is you have modified the stored proc which takes a single string such that it still expects a single string.

You should modify the definition of the stored procedure to take 3 parameters (that part is missing in your question)

Here is an example of a stored proc declaration with 3 parameters:

 CREATE PROCEDURE SP_ContSearch_TEST
    (IN sans1 CHAR(10),
     IN sans2 CHAR(10),
     IN sans3 CHAR(10)
     -- add as many other parameters here as you need
    )
 BEGIN
     -- your stored proc logic here.. can use sans1, sans2, and sans3
 END

You should also change your code to use parameterized queries instead of the way you're doing right now. See: http://php.net/manual/en/pdo.prepared-statements.php or http://php.net/manual/en/mysqli.prepare.php

Saturday, May 29, 2021
 
eek
answered 5 Months ago
eek
70

How to map a stored procedure in EF?

Since you are doing Database First Approach and you have an EDMX file, let EF generate the class of the stored procedure result for you. You may have many stored procedures and you want to avoid creating the classes manually: After all that is the whole point of using an ORM tool. Also some of your stored procedures may have parameters. Doing it the way below will handle all that for you. It is actually pretty simple.

To get EF to do this for you, follow the steps to below:

  1. Double click your EDMX file
  2. Choose Update Model from Database

You will see the dialog similar to below:

enter image description here

  1. Make sure you have checked the boxes as shown.

That will add the stored procedure and you will see it in your model browser as shown below:

enter image description here

  1. If you want to change the class name auto-generated by EF then do so. I strongly suggest you do this and give your class a meaningful names that follow .NET naming conventions. The convention I follow is remove any verbs from the stored procedure name and append the word result to the end. So you will end up with name as shown below:

enter image description here

  1. Press OK

Some Notes

This is much better than writing the classes manually in case your stored procedure name, or the parameters it needs, or the result it returns changes. This approach will work for user defined functions as well.

A Gotcha

There will be times when the stored procedure will not appear in the selection in the wizard dialog, that is because of this. Simply add this to the beginning of your stored procedure:

SET FMTONLY OFF -- REMEMBER to remove it once the wizard is done.
Monday, August 2, 2021
 
tsmbl
answered 3 Months ago
80

The DbContext has no native support for materialising multiple resultsets. However, it is reasonably straight forward to achieve by dropping down to the ObjectContext and using the Translate method to copy results from a DbDataReader into entities in your domain model.

Here's some example code. This assumes your ReferrerStatisticResult is just a container for the two lists called Set1 and Set2. Obviously adjust according to your actual domain model.

// Create container ready for the resultsets
var result = new RefererStatisticResult();

using (var myContext = new MyContext())
{
    // Create command from the context in order to execute
    // the `GetReferrer` proc
    var command = myContext.Database.Connection.CreateCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "[dbo].[GetReferrer]";
    // add in command parameters
    // (not shown)

    try
    {
        myContext.Connection.Open();
        var reader = command.ExecuteReader();

        // Drop down to the wrapped `ObjectContext` to get access to
        // the `Translate` method
        var objectContext = ((IObjectContextAdapter)myContext).ObjectContext;

        // Read Entity1 from the first resultset
        result.Set1 = objectContext.Translate<Entity1>(reader, "Set1", MergeOptions.AppendOnly);

        // Read Entity2 from the second resultset
        reader.NextResult();
        result.Set2 = objectContext.Translate<Entity2>(reader, "Set2", MergeOptions.AppendOnly);        
    }
    finally
    {
        myContext.Database.Connection.Close();
    }
}
Monday, August 2, 2021
 
Uours
answered 3 Months ago
68

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

$connect=&ConnectDB();
// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);
$stmt->execute();

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);
$stmt->execute();

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Remark:

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Thursday, August 5, 2021
 
Shibbir
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share