| Bug #14335 | Dynamic SQL in a stored procedure with Phpmyadmin | ||
|---|---|---|---|
| Submitted: | 26 Oct 2005 17:06 | Modified: | 27 Oct 2005 9:24 |
| Reporter: | Didier G. | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.15 | OS: | Linux (DEBIAN) |
| Assigned to: | CPU Architecture: | Any | |
[27 Oct 2005 6:12]
Valeriy Kravchuk
Thank you for a problem report. Looks like you just have to rebuild (relink) PHP with newer version of MySQL client (5.0.x). Please, try to do it and inform about the results.
[27 Oct 2005 7:50]
Didier G.
Thank you for your "fast" answer First - I have upgrade Mysql with the 5.0.15 version. I have uninstall PHP5 and install the latest version 5.0.5-3 from Debian UNSTABLE packages Same problem appears. #1312 - PROCEDURE xxx.yyyy can't return a result set in the given context The procedure style works fine in the Mysql client, mysql Query browser, navicat ....
[27 Oct 2005 8:34]
Valeriy Kravchuk
OK, I'll try to show you the real problem, not so "fast", but anyway.
[27 Oct 2005 9:24]
Valeriy Kravchuk
OK. Now my slow, step by step explanation comes. I was not able to repeat the problem you described when installing and using PHP with MySQL properly. These is what I did:
1. I had created tables and procedure as you described:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `mytables` (my_table_name VARCHAR(200));
Query OK, 0 rows affected (0,15 sec)
mysql> INSERT INTO `mytables` VALUES ('table1');
Query OK, 1 row affected (0,01 sec)
mysql> INSERT INTO `mytables` VALUES ('table2');
Query OK, 1 row affected (0,01 sec)
mysql> CREATE TABLE `table1` (val INT);
Query OK, 0 rows affected (0,04 sec)
mysql> INSERT INTO `table1` VALUES (1);
Query OK, 1 row affected (0,01 sec)
mysql> CREATE TABLE `table2` (val INT);
Query OK, 0 rows affected (0,01 sec)
mysql> INSERT INTO `table2` VALUES (1);
Query OK, 1 row affected (0,00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE `modify_my_tables`()
-> DETERMINISTIC
-> BEGIN
-> DECLARE compteur INT;
-> DECLARE nb_table INT;
-> DECLARE v_table CHAR(200);
-> DECLARE stmt_expr CHAR(200);
->
-> DECLARE curseur CURSOR FOR SELECT my_table_name FROM my_tables;
->
-> SELECT count(*) INTO nb_table FROM my_tables;
->
-> IF (nb_table>0) THEN
-> SET compteur = 0;
-> OPEN curseur;
->
-> WHILE (compteur<nb_table) DO
-> SET compteur = compteur+1;
-> FETCH curseur INTO v_table;
->
-> SET @stmt_expr = CONCAT('UPDATE ',v_table, ' SET val=val+1');
-> prepare stmt FROM @stmt_expr;
-> execute stmt;
-> deallocate prepare stmt;
-> END WHILE;
-> CLOSE curseur;
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0,06 sec)
mysql> select * from table1//
+------+
| val |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
mysql> delimiter ;
mysql> select * from table2;
+------+
| val |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
mysql> call modify_my_tables();
ERROR 1146 (42S02): Table 'test.my_tables' doesn't exist
This small problem (incorrect table name in your procedure) is easy to fix:
mysql> rename table mytables to my_tables;
Query OK, 0 rows affected (0,00 sec)
mysql> call modify_my_tables();
Query OK, 0 rows affected (0,00 sec)
mysql> select * from table2;
+------+
| val |
+------+
| 2 |
+------+
1 row in set (0,01 sec)
mysql> select * from table1;
+------+
| val |
+------+
| 2 |
+------+
1 row in set (0,00 sec)
So, yes, your procedure works as expected and you described.
2. I reread the PHP manual. (Note that I used php.net, and it simply redirected me to the appropriate mirror.) First this page (http://php.paco.net/manual/en/ref.mysql.php):
"This MySQL extension doesn't support full functionality of MySQL versions greater than 4.1.0. For that, use MySQLi."
Then the linked one (http://php.paco.net/manual/en/ref.mysqli.php):
"The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above."
That is how I had found a proper way to use MySQL SPs in PHP.
3. I had downloaded PHP 5.0.5 sources from their site (http://php.paco.net/get/php-5.0.5.tar.gz/from/a/mirror)
4. I had configured PHP with MYSQLi as described in the PHP manual (http://php.paco.net/manual/en/ref.mysqli.php):
"To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1."
So, I had performed the following commands:
cd /tmp/
gunzip < php-5.0.5.tar.gz | tar -xvf -
cd php-5.0.5
vi INSTALL
./configure --with-mysqli=/home/openxs/dbs/5.0/bin/mysql_config
make
su (to execute make install, installed binaried into /usr/local/bin)
cd ~/dbs/5.0/ (that is the localtion of my MySQL 5.0.16).
5. I had created a simplest PHP sript that calls your procedure:
<?php
$link = mysqli_connect("localhost", "root", "", "test");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CALL modify_my_tables()");
/* close connection */
mysqli_close($link);
?>
and put into 14335.php file.
6. I executed this file using properly built PHP 5.0.5 with MySQLi support:
[openxs@Fedora 5.0]$ /usr/local/bin/php 14335.php
Content-type: text/html
X-Powered-By: PHP/5.0.5
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from table1;
+------+
| val |
+------+
| 3 |
+------+
1 row in set (0,00 sec)
mysql> select * from table2;
+------+
| val |
+------+
| 3 |
+------+
1 row in set (0,00 sec)
So, it works as expected.
Summary:
This problem has nothing to do with MySQL server, client libraries, PHP and even PHP's MySQL interface. The real problem is something not done properly in your Debian packages (ask Debian people about that) or you being lazy enough to not read manuals or not compile FOSS from sources. Now, please, reread my "fast" answer once more.
I hope, this extended explanation will help you to solve your problem.
[27 Oct 2005 10:17]
Didier G.
Just to make a correction = FAST means QUICK also in french not SHORT... Sorry as English is not my mothertong Thank a lot for your quick answer. I haven't see Mysql functions don't work with Mysql version above 4.1 I will download php source, compile with mysqli, follow your saying and test. So, in all my codes, do i replace mysql_query by mysqli_query?
Description: I would update some fields of tables with a stored procedure. I've put names of these tables in a main table. I've create a cursor who read each table name of the main table. For each, I build a query who update a table and i want to execute it. So... The following script works with Mysql client but not in phpmyadmin or php script How to repeat: -- Creation of tables CREATE TABLE `mytables` (my_table_name VARCHAR(200)); INSERT INTO `mytables` VALUES ('table1'); INSERT INTO `mytables` VALUES ('table2'); CREATE TABLE `table1` (val INT); INSERT INTO `table1` VALUES (1); CREATE TABLE `table2` (val INT); INSERT INTO `table2` VALUES (1); -- Creation of Stored Procedure CREATE PROCEDURE `modify_my_tables`() DETERMINISTIC BEGIN DECLARE compteur INT; DECLARE nb_table INT; DECLARE v_table CHAR(200); DECLARE stmt_expr CHAR(200); DECLARE curseur CURSOR FOR SELECT my_table_name FROM my_tables; SELECT count(*) INTO nb_table FROM my_tables; IF (nb_table>0) THEN SET compteur = 0; OPEN curseur; WHILE (compteur<nb_table) DO SET compteur = compteur+1; FETCH curseur INTO v_table; SET @stmt_expr = CONCAT('UPDATE ',v_table, ' SET val=val+1'); prepare stmt FROM @stmt_expr; execute stmt; deallocate prepare stmt; END WHILE; CLOSE curseur; END IF; END CALL modify_my_tables();