I have a problem with mysqli_prepare function, it doesn't work properly
calling a stored procedure which contains prepare itself.
PHP example code:
<?
$db = mysqli_connect("localhost","user","password","real");
$stmt = mysqli_prepare($db, "call testproc_safe2_prep(?,?)");
mysqli_stmt_bind_param($stmt,'ss', $_GET['param1'],$_GET['param2']);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name);
while (mysqli_stmt_fetch($stmt)) {
echo $name;
echo " ";
}
mysqli_stmt_close($stmt);
?>
My procedure:
PROCEDURE `real`.testproc_safe2_prep(IN param1 VARCHAR(255), IN param2
VARCHAR(255))
BEGIN
SET @query = 'SELECT field1 FROM test_table WHERE 1=1';
IF param1 IS NOT NULL Then
SET @query = CONCAT(@query, ' AND criteria1 = ', param1);
END IF;
IF param2 IS NOT NULL Then
SET @query = CONCAT(@query, ' AND criteria2 = ', param2);
END IF;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
When i open php script i get smth like this:
http://i013.radikal.ru/1101/c3/362690f7380d.jpg
I have another procedure, that does all the same as the first one:
PROCEDURE `real`.testproc_safe2_case(IN param1 VARCHAR(255), IN param2
VARCHAR(255))
BEGIN
SELECT field1 FROM test_table
WHERE 1 = 1
AND CASE WHEN param1 IS NULL THEN 1
ELSE CASE WHEN criteria1 = param1 THEN 1
ELSE 0 END END = 1
AND CASE WHEN param2 IS NULL THEN 1
ELSE CASE WHEN criteria2 = param2 THEN 1
ELSE 0 END END = 1;
END
So, when i change this way:
$stmt = mysqli_prepare($db, "call testproc_safe2_case(?,?)");
everything works fine:
http://s52.radikal.ru/i138/1101/26/2e29daf0daa4.jpg
The procedures do the same thing, first using prepare and the second using
case. But why first doesn't work with mysqli_prepare?
Example of launching using console:
mysql> call testproc_safe2_prep(1,1);
+-----------+
| field1 |
+-----------+
| Kuznetsov |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call testproc_safe2_case(1,1);
+-----------+
| field1 |
+-----------+
| Kuznetsov |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_table;
+----+-----------+-----------+-----------+
| id | field1 | criteria1 | criteria2 |
+----+-----------+-----------+-----------+
| 1 | Ivanov | 0 | 0 |
| 2 | Petrov | 0 | 1 |
| 3 | Sidorov | 1 | 0 |
| 4 | Kuznetsov | 1 | 1 |
+----+-----------+-----------+-----------+
4 rows in set (0.00 sec)
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.00 sec)
How i can use mysqli_prepare to work properly with the first procedure?
Maybe it was fixed in new versions or it's not a bug?
