Problem with mysql stored procedure call

Question

Problem with mysql stored procedure call

Navigation

#1 by (1 votes)

0

I have a problem calling several times in a row (within a while) to a procedure (with the CALL nameProcedure (parameter)) from php on my website.

This is the code of the procedure:

BEGIN
SELECT 
   @estado := estade,
   @subestado := subestado
FROM
   tabla1 
WHERE boletin = boletn; #(el "boletn" es el parametro que paso al procedimiento)

CASE @estado 
    WHEN 'estado1' THEN

        CASE @subestado
            WHEN '' THEN
                SET @tipo = 'Pendiente';

            ELSE
                SET @tipo = 'Sin identificar';
        END CASE;
END CASE;

UPDATE tabla1 SET tipo = @tipo WHERE boletin=boletn;

And then in php I have the following:

$instruccion0 = "SELECT boletin FROM tabla1 ORDER BY fechaCarga DESC LIMIT 4";
$resultado0 = mysql_query($instruccion0, $conexion);

while($fila0 = mysql_fetch_assoc($resultado0)){

    $boletin = $fila0['boletin'];

    $resultado1 = mysql_query("CALL establecerTipoBoletin('$boletin')", $conexion) or die ("Fallo en el CALL procedimiento. Error: " . mysql_error());
}

And this is where the problem comes from. Of course, what I need is that the 4 bulletins in the table (putting there are 4), the procedure is executed, that is, call each time a different bulletin, so that the while in php But when I do the action (that the php code is "executed" when I press a button) it shows me the following error message:

Failed in the CALL procedure. Error: Commands out of sync; you can not run this command now

In theory that error is due to the fact that the CALLS are being performed at the same time, but depending on what is being programmed, it enters the while, executes a call to a newsletter, re-enters the while, executes the call to the another newsletter ...

Note: If I do it with only 1 bulletin (changing the 1 by the 4 in "SELECT boletin FROM tabla1 ORDER BY fechaCarga DESC LIMIT 4"; ) if it does it correctly and it does not show me the error (only logically it only enters the once while).

Does anyone know why that error message?

EDITED:

I have verified that the same error message occurs if I execute the same procedure 2 times in a row, that is, if I do twice the CALL . If I do this:

$resultado1 = mysql_query("CALL establecerTipoBoletin('boletin1')", $conexion); $resultado1 = mysql_query("CALL establecerTipoBoletin('boletin2')", $conexion);

Skip the same error message.

Greetings.

mysql php stored-procedure

asked by M. Giner 23.05.2018 в 12:01

source

1 answer

Error updating Div automatically Web | Modal message only in desktop resolutions

score 1 · Accepted Answer

Translation of the answer to the question PHP Commands Out of Sync error

For those of you who do the right thing and use stored procedures with prepared statements.

For some reason, mysqli can not free resources when it uses an output variable as a parameter in the stored process. To solve this, simply return a set of records within the body of the procedure instead of storing the value in an output variable / parameter.

For example, instead of having SET outputVar = LAST_INSERT_ID (); you can have SELECT LAST_INSERT_ID (); Then in PHP I get the value returned like this:

$query= "CALL mysp_Insert_SomeData(?,?)"; 
$stmt = $mysqli->prepare($query); 
$stmt->bind_param("is", $input_param_1, $input_param_2); 
$stmt->execute() or trigger_error($mysqli->error); // trigger_error here is just for troubleshooting, remove when productionizing the code
$stmt->store_result();
$stmt->bind_result($output_value);
$stmt->fetch();
$stmt->free_result();
$stmt->close();
$mysqli->next_result();
echo $output_value;

Now you are ready to execute a second stored procedure without having the error "Commands not synchronized, you can not execute the command now". If you returned more than one value in the recordset, you can browse and search for them this way:

while ($stmt->fetch()) {
    echo $output_value;
}

If you are returning more than one set of records from the stored process (you have multiple selections), then be sure to go through all those record sets using

 $stmt->next_result();

Also in the official php doc there is already a solution to this problem: link