[Warn] the following error occurs when PHP executes the MySQL stored procedure: commands out of sync; you can't run this command now

Source: Internet
Author: User

Address: http://www.pczpg.com/a/2010/0507/7815.html

Call the MySQL stored procedure in the same thing in PHP and then execute another or multiple commands (or execute multiple stored procedures in the same thing) again ), if you use the query method of mysqli to obtain the result, an error is returned: commands out of sync; you can't run this command now SSS first.Code:

Stored Procedure:

Create procedure test1 ()
Begin
Drop table if exists tb1;
Create Table tb1
(
Val int not null
) Engine = InnoDB;
Insert into tb1 (VAL) values (1), (2), (3 );
Select * From tb1;
End

PHP code:

<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");

If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
$ Result = NULL;
$ Mysqli-> autocommit (false );
If (! ($ Result = $ mysqli-> query ("Call test1 ();")))
{
Echo mysqli_error ($ link );
$ Mysqli-> rollback ();
}
$ Mysqli-> commit ();

Print 'result1 :';

While ($ ROW = $ result-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result-> close ();
Mysqli_free_result ($ result );

Echo 'result2: <br/> ';
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
Mysqli_free_result ($ result2 );

Mysqli_close ($ link );
?>

After the above code is executed, the above error will occur. The message indicates that the MySQL database considers the command execution order as this error. The reason is that after the execution of the MySQL stored procedure, in addition to returning the actual result set, it also returns the transition state of the stored procedure execution.

The above Code only processes the first result set, and the second result set is not released.

When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the select call inside the stored procedure. 2 ndfor the call of the stored procedure itself

(2nd usually is only an OK or err packet ).

To solve this problem, we need to use the mysqli multi_query method to traverse all the result sets and release them. The Code is as follows:

<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");

If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
Echo 'result1: <br/> ';
$ Mysqli-> autocommit (false );
If ($ mysqli-> multi_query ("Call test1 ();"))
{
Do {
If ($ result = $ mysqli-> store_result ()){
While ($ ROW = $ result-> fetch_row ()){
Printf ("% s \ n", $ row [0]);
}
$ Result-> close ();
}
} While ($ mysqli-> next_result ());
}
$ Mysqli-> commit ();
Echo "<br/> ";
Echo "result2: <br/> ";
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ()){
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
$ Mysqli-> close ();
?>

Call the MySQL stored procedure in the same thing in PHP and then execute another or multiple commands (or execute multiple stored procedures in the same thing) again ), if you use the query method of mysqli to obtain the result, you will get an error: commands out of sync; you can't run this command now SSS first to provide the Code:

Stored Procedure:

Create procedure test1 ()
Begin
Drop table if exists tb1;
Create Table tb1
(
Val int not null
) Engine = InnoDB;
Insert into tb1 (VAL) values (1), (2), (3 );
Select * From tb1;
End

PHP code:

Code1
<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");

If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
$ Result = NULL;
$ Mysqli-> autocommit (false );
If (! ($ Result = $ mysqli-> query ("Call test1 ();")))
{
Echo mysqli_error ($ link );
$ Mysqli-> rollback ();
}
$ Mysqli-> commit ();

Print 'result1 :';

While ($ ROW = $ result-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result-> close ();
Mysqli_free_result ($ result );

Echo 'result2: <br/> ';
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
Mysqli_free_result ($ result2 );

Mysqli_close ($ link );
?>

After the above code is executed, the above error will occur. The message indicates that the MySQL database considers the command execution order as this error. The reason is that after the execution of the MySQL stored procedure, in addition to returning the actual result set, it also returns the transition state of the stored procedure execution.

The above Code only processes the first result set, and the second result set is not released.

When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the select call inside the stored procedure. 2 ndfor the call of the stored procedure itself

(2nd usually is only an OK or err packet ).

To solve this problem, we need to use the mysqli multi_query method to traverse all the result sets and release them. The Code is as follows:

<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");

If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
Echo 'result1: <br/> ';
$ Mysqli-> autocommit (false );
If ($ mysqli-> multi_query ("Call test1 ();"))
{
Do {
If ($ result = $ mysqli-> store_result ()){
While ($ ROW = $ result-> fetch_row ()){
Printf ("% s \ n", $ row [0]);
}
$ Result-> close ();
}
} While ($ mysqli-> next_result ());
}
$ Mysqli-> commit ();
Echo "<br/> ";
Echo "result2: <br/> ";
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ()){
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
$ Mysqli-> close ();
?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.