why wont my damn stored proc run

[fountjas]

Hi Guys,
 
Bit of a newbie to stored procs in MySQL, can someone tell me why my damn code won't work.
 
I am passing all the correct variables to this part of my script but for some reason it just does not call the stored proc, instead, I get the "'it did not run" as if there is something wrong with the SET or the call. The connection works without issue
 

$mysqli = new mysqli($Host, $Username, $Password, $Schema);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}else{
     echo 'connected<br />';
}
$mysqli->query("SET @p_operator_id  = '" . $mysqli->real_escape_string($m2mUserID) . "'");
$mysqli->query("SET @p_date_from   = '" . $mysqli->real_escape_string($date_from) . "'");
$mysqli->query("SET @p_date_to   = '" . $mysqli->real_escape_string($date_to) . "'");
$mysqli->query("SET @p_created_on   = '" . $mysqli->real_escape_string($created_on) . "'");
$mysqli->query("SET @p_company_subject_id   = '" . $mysqli->real_escape_string($company_subject_id) . "'");
$mysqli->query("SET @p_company_number   = '" . $mysqli->real_escape_string($company_number) . "'");
$mysqli->query("SET @p_driver_subject_id   = '" . $mysqli->real_escape_string($p_driver_subject_id) . "'");
$mysqli->query("SET @p_driver_number   = '" . $mysqli->real_escape_string($p_driver_number) . "'");
$mysqli->query("SET @p_history_comment   = '" . $mysqli->real_escape_string($p_history_comment) . "'");
 
if(!$mysqli->query("CALL usp_dispatch_bookkeeping_export_receipts(@p_operator_id, @p_date_from, @p_date_to, @p_created_on, @p_company_subject_id, @p_company_number, @p_driver_subject_id, @p_driver_number, @p_history_comment)")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}else{
     echo 'it did not run<br />';
}

 
Thanks in advance.

[Jacques1]

Um, what? PHP tells you that the call was successful by returning a truthy value. You're the one who prints that weird "did not run" message.

 

Besides that, your mysqli code is a hot mess. Before you jump to advanced features like stored procedures, you need to learn the basics like prepared statements (rather than escaping gymnastics) and sane error handling with exceptions (rather than fiddling with return values and dumping all messages on the screen). Or even better: Give up mysqli and switch to the much more programmer-friendly PDO.

 

Also, what's up with all those session variables? You only need variables for OUT or INOUT parameters, not for simply passing values to the procedure.

[fountjas]

Um, what? PHP tells you that the call was successful by returning a truthy value. You're the one who prints that weird "did not run" message.

 

Besides that, your mysqli code is a hot mess. Before you jump to advanced features like stored procedures, you need to learn the basics like prepared statements (rather than escaping gymnastics) and sane error handling with exceptions (rather than fiddling with return values and dumping all messages on the screen). Or even better: Give up mysqli and switch to the much more programmer-friendly PDO.

 

Also, what's up with all those session variables? You only need variables for OUT or INOUT parameters, not for simply passing values to the procedure.

Sorry I may not have been clear, I only put that "did not run" to confirm whether the CALL had executed or not, watching the server logs the call is NOT executed, as I mentioned new to stored procs and simply copied this from an example I found and replaced the in vars in the example with my own. will take a look at PDO thanks

[fountjas]

Thanks to Jacques1 I took a look at PDO and found a method that works so this is now resolved

[Jacques1]

A procedure call which reports success and then magically disappears is hard to believe. This sounds more like you're misinterpreting the information (wrong logfile or incomplete logging, complex procedure with unclear effects etc.)

 

Anyway, we'll probably never know.