Get return data from a MySQL Stored Procedure

Certainly! To call a MySQL stored procedure from PHPRunner and retrieve the values, you can use either output parameters or a result set. Here’s how you can do it using both methods.

Using Output Parameters

If you want to use output parameters, you will need to modify your stored procedure to accept parameters for output. Here’s a basic example of how your stored procedure might look:

DELIMITER //
CREATE PROCEDURE MyStoredProcedure(
 IN param1 INT,
 IN param2 INT,
 IN param3 INT,
 OUT outputValue1 VARCHAR(50),
 OUT outputValue2 VARCHAR(50)
)
BEGIN
 -- Your logic here
 SET outputValue1 = 'Ok';
 SET outputValue2 = 'Record added';
END //
DELIMITER ;
Then in PHPRunner, you can call your stored procedure in the Before Processing event like this:
// Prepare the statement
$conn = db_connect(); // Assuming you have a function to connect to your database

// Prepare output variables
$outputValue1 = '';
$outputValue2 = '';

// Call the stored procedure
$stmt = $conn->prepare("CALL MyStoredProcedure(?, ?, ?, @output1, @output2)");
$stmt->bind_param("iii", $param1, $param2, $param3);
$stmt->execute();

// Retrieve output values
$result = $conn->query("SELECT @output1 AS output1, @output2 AS output2");
$row = $result->fetch_assoc();

$outputValue1 = $row['output1'];
$outputValue2 = $row['output2'];

// Now you can use $outputValue1 and $outputValue2
Using Result Set
If your stored procedure returns a result set, you could modify it to return the values like this:
DELIMITER //
CREATE PROCEDURE MyStoredProcedure(
 IN param1 INT,
 IN param2 INT,
 IN param3 INT
)
BEGIN
 SELECT 'Ok' AS value1, 'Record added' AS value2;
END //
DELIMITER ;
Then, in PHPRunner, you can retrieve the result set like this:
// Prepare the statement
$conn = db_connect(); // Assuming you have a function to connect to your database

// Call the stored procedure
$result = $conn->query("CALL MyStoredProcedure(1, 2, 3)");

if ($result) {
 $row = $result->fetch_assoc();
 $value1 = $row['value1'];
 $value2 = $row['value2'];

 // Now you can use $value1 and $value2
}
Conclusion
Choose the method that fits your needs best. If you want to keep the stored procedure simple and just return values, using a result set might be the easiest route. If you prefer to use output parameters, ensure your stored procedure is set up accordingly.
Make sure to handle any errors that might occur and close the database connection as needed. If you have any other questions or need further assistance, feel free to ask!

If your stored procedure returns a recordset, you can simply use Database API to execute a stored procedure call and return data:
https://xlinesoft.com/phprunner/docs/db_query.htm