ID: 47782
Updated by: j...@php.net
Reported By: phpbug at smithii dot com
-Status: Open
+Status: Bogus
Bug Type: MySQLi related
Operating System: Linux, Windows
PHP Version: 5.2.9, 5.3.0RC2
New Comment:
For calling stored procedures you have to use mysqli_multi_query.
Previous Comments:
------------------------------------------------------------------------
[2009-04-15 10:09:31] phpbug at smithii dot com
The following script produces errors on 5.2.9 and 5.3.0RC2, on both
Linux and Windows:
<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
if (!$mysqli) die(mysqli_connect_error());
$sqls[] = <<<EOT
CREATE PROCEDURE echo0(p VARCHAR(255))
BEGIN
SELECT p;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo1(p VARCHAR(255))
BEGIN
SET @sql = CONCAT('SELECT ', QUOTE(p));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo2(p VARCHAR(255))
BEGIN
PREPARE stmt FROM 'SELECT ?';
SET @p = p;
EXECUTE stmt USING @p;
DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo3(p VARCHAR(255))
BEGIN
PREPARE stmt FROM 'SELECT 1234';
EXECUTE stmt;
DROP PREPARE stmt;
END
EOT;
$inp = strval($argv[1]);
foreach ($sqls as $i => $sql) {
$mysqli->query("DROP PROCEDURE IF EXISTS echo$i");
$mysqli->query($sql) || die($mysqli->error);
$sql = "CALL echo$i(?)";
printf("Executing: %s with '%s'\n", $sql, $inp);
$s = $mysqli->prepare($sql);
if (!$s) die($mysqli->error);
printf("inp=%s (%s)\n", $inp, bin2hex($inp));
$s->bind_param('s', $inp) || die($mysqli->error);
$s->execute() || die($mysqli->error);
$s->bind_result($out) || die($mysqli->error);
while ($s->fetch()) {
printf("out=%s (%s)\n", $out, bin2hex($out));
}
$s->close();
}
Here's the script's output:
Executing: CALL echo0(?) with '1234'
inp=1234 (31323334)
out=1234 (31323334)
Executing: CALL echo1(?) with '1234'
inp=1234 (31323334)
out=34
(3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34
(3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
Executing: CALL echo3(?) with '1234'
inp=1234 (31323334)
out=3420978 (33343230393738)
------------------------------------------------------------------------
[2009-03-26 00:08:39] phpbug at smithii dot com
Description:
------------
Using MySQL 5.0.77, and calling a stored procedure with a PREPAREd
statement, execute()/bind_result()/fetch() return anomalous results.
Reproduce code:
---------------
Using MySQL 5.0.77, and calling any stored procedure with a PREPAREd
statement, such as:
DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
SET @sql = CONCAT('SELECT ',QUOTE(p));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END;
//
DELIMITER ;
via this script:
<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
$sql = 'CALL echo(?)';
$s = $mysqli->prepare($sql);
$i = $argv[1];
printf("i=%s\n", $i);
$s->bind_param('s', $i);
$s->execute();
$s->bind_result($o);
while ($s->fetch()) {
printf("o=%s (%s)\n", $o, bin2hex($o));
}
$s->close();
produces anomalous results at least 50% of the time. For example:
$ php echo.php abcd
i=abcd
o=cd ⦠(6364000004)
If I remove the PREPAREd statement:
DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
SELECT p;
END;
//
DELIMITER ;
everything works fine.
Replacing execute()/bind_result()/fetch(), with query()/fetch_assoc()
also fixes the issue.
Other details:
mysqli_get_client_info=5.0.51a
mysqli_get_client_version=50051
mysqli_get_server_info=5.0.77-community-nt
mysqli_get_server_version=50077
mysqli_get_host_info=localhost via TCP/IP
mysqli_get_proto_info=10
Expected result:
----------------
$ php echo.php abcd
i=abcd
o=abcd (63646566)
Actual result:
--------------
$ php echo.php abcd
i=abcd
o=cd ⦠(6364000004)
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=47782&edit=1
