I am attempting to port an 8-year-old PHP/MySQL web app to a more recent server stack using MariaDB instead of MySQL. I have found it impossible to run more than one stored procedure on the same connection due to a "packets out of order" error. Below is code that should work, but doesn't. Can someone point out where I may have gone astray here or what would be a successful alternative approach?
<?php
$host = "localhost";
$user = "mysqli_test";
$password = "";
$database = "mysqli_test";
function get_connection()
{
GLOBAL $host, $user, $password, $database;
$connection = new mysqli($host, $user, $password, $database);
if (! $connection || mysqli_connect_errno() )
printf("Connection failure: %s.\n", mysqli_connect_error());
return $connection;
}
// Minimum viable function: isolate necessary steps.
function get_person($connection, $first_name)
{
$query = "CALL Get_By_First_Name(?)";
if (($stmt = $connection->prepare($query))) // error here after first pass
{
$stmt->bind_param('s', $first_name);
if ($stmt->execute())
{
$stmt->store_result();
$stmt->bind_result($id, $fname, $lname, $pets);
while($stmt->fetch())
printf("= s s -.\n", $id, $fname, $lname, $pets);
$stmt->free_result();
while ($connection->next_result()) // my suspected culprit
{
$stmt->store_result();
while ($stmt->fetch())
;
$stmt->free_result();
}
}
$stmt->close();
}
}
if ($conn = get_connection())
{
get_person($conn, "Samuel"); // it works the first time
get_person($conn, "Zelda"); // this time it fails
}
?>
Running aPHP/mysqli code, lmost identical C code using the C API works fine, so I can isolate where I think the problem starts: with the next_result() function. In the C code, next_result() returns TRUE to indicate a new result was available after using the prepared statement to run the stored procedure. In the PHP/mysqli code, next_result() returns false, and, in fact, fails to produce a new result even if I ignore the false return value.
I created a github repository that includes more explanation and scripts that can run on your computer to replicate the error, if anyone is interested.
CodePudding user response：
The best practice is to avoid using stored procedures from PHP... That is not always possible; sometimes stored procedures are necessary and they might even be useful on rare occasions. But if you can, try to move the logic to the PHP application rather than storing it on a MySQL server. It's much less cumbersome this way.
If you want to know how to call stored procedures correctly, the best resource to reach for is the PHP manual. I have recently improved most of the examples in the manual so I know that the examples there reflect best practices and actually work. Read stored procedures using mysqli and mysqli::multi_query() documentation.
I would advise avoiding mysqli::multi_query(), despite stored procedures being probably the primary reason that function even exists. You have made the right choice to use prepared statements so you can bind the parameters and avoid SQL injection.
The main thing you have to remember is that CALL() statement produces an empty result. If the stored procedure also produces result set/sets then you need to iterate over them and fetch each one. The problem with stored procedures is that you can never be certain how many result sets will be produced. How can you handle the unknown?
Take a look at this example (it's your code but I made some changes and enabled error reporting):
function get_connection($host, $user, $password, $database): mysqli
{
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
return new mysqli($host, $user, $password, $database);
// you might want to set the correct charset (e.g. utf8mb4) here before returning
}
function get_person(mysqli $connection, $first_name): mysqli_result
{
$query = "CALL Get_By_First_Name(?)";
$stmt = $connection->prepare($query);
$stmt->bind_param('s', $first_name);
$stmt->execute();
/* We expect this SP to return the main result set,
which we want to return and then an empty result for CALL.
Get the result here and return immediately.
The prepared statement will be closed automatically once
we leave the scope and this will clean up the remaining result set.
*/
return $stmt->get_result();
}
$conn = get_connection($host, $user, $password, $database);
$res1 = get_person($conn, "Samuel");
$res2 = get_person($conn, "Zelda");
var_dump($res1->fetch_all(), $res2->fetch_all());
In the above code, I make an assumption that the SP I am calling will return only two result sets. I want only the first one and I don't care about the result of CALL(). The second result set is discarded when the statement is cleaned up. If that was not the case, I would need to call mysqli_stmt::next_result() until all result sets are fetched (not the mysqli::next_result()!). This is the easiest way to handle the cumbersome stored procedures.
Even if you converted the code to PDO, this would still be the simplest way to do it. With mysqli things can get very complicated if you go over the board though, so be careful not to overengineer the solution. If your stored procedures use cursors, note that there was a bug in PHP up to PHP 7.4.
CodePudding user response：
You should abtract the code that accesses the database. Here is a quick sample code (you may finish it) that queries any kind of stored procedure (multiple lines, multiple result sets).
Say you have a procedure like yours that looks like this (note that you in this case should use unique aliases for the columns):
create procedure Get_By_First_Name( inName varchar(20))
begin
-- First result set with just one row
select 1 as 'id', inName as 'First_name', 'Lastname' as Lastname, 'Cat' as 'pet';
-- Second result set with with multiple rows
select 1 as id, now()
union
select 2, date_add(now(), interval 2 day);
end
The code for the database access:
<?php
class SQL_Query {
private $host = "localhost";
private $user = "mysqli_test";
private $password = "";
private $database = "mysqli_test";
private $resultsets = [];
function get_connection()
{
$mysqli = @new mysqli($this->host, $this->user, $this->password, $this->database);
if (!$mysqli || $mysqli->connect_errno ) {
printf("Connection failure: %s.\n", $mysqli->connect_error);
return null;
}
return $mysqli;
}
function __construct($sql, $params)
{
$mysqli = $this->get_connection();
if ($mysqli) {
$sql = 'call '.$sql.'(';
for ($i=0; $i < sizeof($params); $i ) {
$sql .= ($i>0 ? ',' : '') . is_string($params[$i]) ? "'". $mysqli->real_escape_string($params[$i]) ."'" : $params[$i];
}
$sql .= ')';
$rs_num = 0;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
$rs_num ;
$fieldInfo = $result->fetch_fields();
while ($row = $result->fetch_row() ) {
$returned = [];
foreach ($row as $key => $value) {
$returned[$fieldInfo[$key]->name] = $value;
}
$this->resultsets[$rs_num][] = $returned;
}
$result->free();
}
} while ($mysqli->more_results() && $mysqli->next_result());
} else {
printf("(%d): %s<br>SQL: %s", $mysqli->errno, $mysqli->error, $sql);
}
$mysqli->close();
}
}
/*
Return the selected resultset as an array
*/
function get($rs=1)
{
return isset($this->resultsets[$rs]) ? $this->resultsets[$rs] : null;
}
/*
Return one field from selected resultset
*/
function get_one($field, $rs=1)
{
return isset($this->resultsets[$rs][0][$field]) ? $this->resultsets[$rs][0][$field] : null;
}
}
$q = new SQL_Query('Get_By_First_Name', ["Samuel'"]);
var_dump($q->get());
var_dump($q->get_one('First_name'));
var_dump($q->get(2));
?>