List:General Discussion« Previous MessageNext Message »
From:Артем Лапковский Date:January 10 2011 5:46am
Subject:bug (or feature?) with mysqli_prepare
View as plain text  
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?

Thread
bug (or feature?) with mysqli_prepareАртем Лапковский10 Jan