1

Don't know if someone can help but for some reason when using call stored procedure that has a select before a dynamic SQL syntax error PHP doesn't report an error. If calling the stored procedure in workbench the error shows.

Basic setup MySQL:

create table testtable (testcolumn varchar(10), PRIMARY KEY (testcolumn));

DELIMITER ///
CREATE PROCEDURE teststoredproc ()
BEGIN

SELECT 'Select text';

SET @sql := 'SELECT * FROM testtable WHERE IFNULL(DATE(testcolumn), ''1900/01/01'' = DATE(''1900/01/01'')';
PREPARE stmtteststoredproc FROM @sql;
EXECUTE stmtteststoredproc;
DEALLOCATE PREPARE stmtteststoredproc;

END
DELIMITER ;

Basic setup php

<?php
$DB_NAME = '';
$DB_HOST = '';
$DB_USER = '';
$DB_PASS = '';

$mysqli = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$result = mysqli_query($mysqli, "CALL teststoredproc();") or die("Query fail: " . mysqli_error());

while ($row = mysqli_fetch_array($result)){   
    echo $row[0];
}
?>

Now instead of the die executing because there is a syntax error in the dymanic SQL, the while will run and echo 'Select text'.

The question is how do I correctly handle mysql stored procedure calls in php so that I will get the errors no matter the amount of selects, etc before the error happens?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user180442
  • 31
  • 2
  • To answer my own question just use mysql_multi_query for everything seeing as there is such a difference http://www.php.net/manual/en/mysqli.multi-query.php. The example on php.net lacks a bit of error handling, just search for stuff on handling errors with multi queries and should be sorted.. – user180442 Jun 06 '14 at 12:14

0 Answers0