Strange problem calling SP from PHP using mysqli

[pandyboy]

I have encountered an unsual problem when using mysqli to call a stored procedure from php.

 

The procedure, which contains a CURSOR REPEAT is getting stuck in the middle. The loop will run one or two, maybe three times (it varies) and then apparently just stops.

 

This, however, only occurs when I call the procedure from PHP using mysqli. If I call the procedure in Navicat, it works perfectly every time.

 

The stored procedure code must be correct, as it works perfectly when run outside of PHP.

 

No errors are returned when I run it from PHP.

 

PHP code is simply as follows:

$link = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysqli_error($link),E_USER_ERROR); 
$av_query="CALL available_quotes(123456)";
$av=mysqli_query($link,$av_query) or die(mysqli_error($link));

 

Any help would be appreciated.

 

MySQL version 5.0.83

PHP version 5.2.9

[pandyboy]

I think I have actually solved the problem now, but not sure why!!

 

I used mysqli_multi_query instead of mysqli_query and it worked.

 

Can anyone explain why this might be so when there is only one query?

[mikosiko]

you first code doesn't show how you were managing the results of your SP. (most probably there is the cause)

 

when you solved with mysqli_multi_query are you using also mysqli_next_result?

[pandyboy]

you first code doesn't show how you were managing the results of your SP. (most probably there is the cause)

 

when you solved with mysqli_multi_query are you using also mysqli_next_result?

There are no results. The SP merely runs some calculations and inserts various records. Nothing is returned.

[mikosiko]

 

There are no results. The SP merely runs some calculations and inserts various records. Nothing is returned.

 

that is a classical mistake... SP ALWAYS return at least one recordset (Execution STATUS) and if you don't manage it correctly you could find "curious" errors.... the most common is the "Commands out of sync; you can't run this command now" , in some cases also some "Lost connection" error could happen.

[pandyboy]

I'm aware of the commands out of sync error, but that only happens if you run 2 SPs one after the other (or at least in my experience) and I can get over that with the usual way of freeing the result. In this case though I am running a single query which, even if it does return one recordset, should not as far as i can tell "trip itself up" - and if it did, surely it should be the same every time not random.

 

Also, I was not getting any errors returned.

[mikosiko]

...but that only happens if you run 2 SPs one after the other (or at least in my experience)

 

that is incorrect... trust me (if you can/want)  or run a couple test... p.e  in your PHP call your SP and after that try to execute other query (no SP)...

 

for further discussion maybe you should post your SP and the calling code... even when you have everything figure out and working the topic could be of help for somebody else.

 

glad to try to help

[pandyboy]

Apologies, yes you're right - after calling an SP then you need to free the result before running any other query.

But that is still different to what I am doing! I am running one isolated SP.

 

Not keen to post the exact SP code as it is quite complex and also I am bound by a non-disclosure agreement.