Hi Michal,
I'm trying to implement titled case related to bug [0]. In current code, any SQL going to executed via SQL window, is processed and if there are more SQL queries, it detect only one query. So whole statement with any valid number of SQl queries need to be detected. Then by using mysql_multi_query() function all results related to multiple queries can be obtained. As I think displaying these results can be done with looping the result sets.
Additionally I saw mysql_multi_query manual [1] mentioned that separate queries are identified by semicolon. May be I need to replace the delimiter if it is not a semicolon.
Please put me into right track with your suggestions if my way is not correct.
[0] : http://sourceforge.net/tracker/index.php?func=detail&aid=3474853&gro... [1] : http://php.net/manual/en/mysqli.multi-query.php
Regards !
*Chanaka*
Hi
Dne Tue, 17 Jul 2012 10:38:32 +0530 Chanaka Dharmarathna pe.chanaka.ck@gmail.com napsal(a):
I'm trying to implement titled case related to bug [0]. In current code, any SQL going to executed via SQL window, is processed and if there are more SQL queries, it detect only one query. So whole statement with any valid number of SQl queries need to be detected. Then by using mysql_multi_query() function all results related to multiple queries can be obtained. As I think displaying these results can be done with looping the result sets.
Additionally I saw mysql_multi_query manual [1] mentioned that separate queries are identified by semicolon. May be I need to replace the delimiter if it is not a semicolon.
It is not possible to replace it in all cases - eg. when creating procedures. So it might be better to run mysqli_query and collect results manually?
I think Rouslan might have some ideas here as well as he is quite familiar with stored procedures and related stuff.
On 07/18/2012 02:16 PM, Michal Čihař wrote:
Hi
Dne Tue, 17 Jul 2012 10:38:32 +0530 Chanaka Dharmarathna pe.chanaka.ck@gmail.com napsal(a):
I'm trying to implement titled case related to bug [0]. In current code, any SQL going to executed via SQL window, is processed and if there are more SQL queries, it detect only one query. So whole statement with any valid number of SQl queries need to be detected. Then by using mysql_multi_query() function all results related to multiple queries can be obtained. As I think displaying these results can be done with looping the result sets.
Additionally I saw mysql_multi_query manual [1] mentioned that separate queries are identified by semicolon. May be I need to replace the delimiter if it is not a semicolon.
It is not possible to replace it in all cases - eg. when creating procedures. So it might be better to run mysqli_query and collect results manually?
I think Rouslan might have some ideas here as well as he is quite familiar with stored procedures and related stuff.
I'm not too sure what the question is, but I can explain the current state of the support for stored routines, which might be of help. First of all, the mysql extension does not support multiple result sets, so it's possible to execute stored routines that return one result set only. Drizzle has no support for stored routines, so you don't need to worry about that either. Now onto the more interesting stuff. The support for executing stored routines in pma is quite incomplete. The relevant code can be found in the file libraries/rte/rte_rtoutines.lib.php, around line 1260 [0]. At the time that I was writing that code, I was convinced that a stored routines could only ever return one row of data (there is even a comment in the code stating this). Later I discovered that not only this was not true, and there could be multiple rows returned, but that it is even possible to return multiple sets of rows. So, I 've actually only implemented enough of the multi query support so that the "COMMANDS OUT OF SYNC" error is not thrown by MySQL when executing the routines.
As a practical example, let say that you have the following routine in the sakila db:
CREATE PROCEDURE `test`() BEGIN SELECT 1; SELECT 2; SELECT 3; END
The code required to display all of it's results would be:
<?php // standalone file, somewhere in the pma root folder include 'libraries/common.inc.php'; PMA_DBI_select_db("sakila"); $query = "CALL test();"; $resource = mysqli_multi_query($GLOBALS['userlink'], $query); $i = 0; do { if ($result = mysqli_use_result($GLOBALS['userlink'])) { echo "result " . ++$i . "<br />"; while ($row = mysqli_fetch_row($result) ) { printf("%s<br />", $row[0]); } } } while(PMA_DBI_more_results() && PMA_DBI_next_result()); ?>
As can be seen in the above code, some of the function calls are missing in our database abstraction layer and instead native mysqli function calls are used. These missing functions need to be implemented first. Btw, the above code is not a straight fit for how to execute the routines in pma, as there are other queries that need to be run, etc, etc. However it should give a good idea of what's missing.
Bye, Rouslan
[0]: https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/rte/rte_routi...
I'm trying to implement titled case related to bug [0]. In current code, any SQL going to executed via SQL window, is processed
and
if there are more SQL queries, it detect only one query. So whole statement with any valid number of SQl queries need to be
detected.
Then by using mysql_multi_query() function all results related to
multiple
queries can be obtained. As I think displaying these results can be done with looping the result sets.
Additionally I saw mysql_multi_query manual [1] mentioned that separate queries are identified by semicolon. May be I need to replace the delimiter if it is not a semicolon.
It is not possible to replace it in all cases - eg. when creating procedures. So it might be better to run mysqli_query and collect results manually?
I think Rouslan might have some ideas here as well as he is quite familiar with stored procedures and related stuff.
I'm not too sure what the question is, but I can explain the current state of the support for stored routines, which might be of help. First of all, the mysql extension does not support multiple result sets, so it's possible to execute stored routines that return one result set only. Drizzle has no support for stored routines, so you don't need to worry about that either. Now onto the more interesting stuff. The support for executing stored routines in pma is quite incomplete. The relevant code can be found in the file libraries/rte/rte_rtoutines.lib.php, around line 1260 [0]. At the time that I was writing that code, I was convinced that a stored routines could only ever return one row of data (there is even a comment in the code stating this). Later I discovered that not only this was not true, and there could be multiple rows returned, but that it is even possible to return multiple sets of rows. So, I 've actually only implemented enough of the multi query support so that the "COMMANDS OUT OF SYNC" error is not thrown by MySQL when executing the routines.
As a practical example, let say that you have the following routine in the sakila db:
CREATE PROCEDURE `test`() BEGIN SELECT 1; SELECT 2; SELECT 3; END
The code required to display all of it's results would be:
<?php // standalone file, somewhere in the pma root folder include 'libraries/common.inc.php'; PMA_DBI_select_db("sakila"); $query = "CALL test();"; $resource = mysqli_multi_query($GLOBALS['userlink'], $query); $i = 0; do { if ($result = mysqli_use_result($GLOBALS['userlink'])) { echo "result " . ++$i . "<br />"; while ($row = mysqli_fetch_row($result) ) { printf("%s<br />", $row[0]); } } } while(PMA_DBI_more_results() && PMA_DBI_next_result()); ?>
As can be seen in the above code, some of the function calls are missing in our database abstraction layer and instead native mysqli function calls are used. These missing functions need to be implemented first. Btw, the above code is not a straight fit for how to execute the routines in pma, as there are other queries that need to be run, etc, etc. However it should give a good idea of what's missing.
Bye, Rouslan
[0]:
https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/rte/rte_routi...
Hi Rouslan,
Thanks for your very descriptive explanation. I am not good at store procedures and routines. I'm currently trying to display multiple results which executing trhought the SQL window.
I'll immediately move into this and I'll need your help in this case.
Regards !
Hi,
I'm trying to implement support for display multiple results which generates from routines. As I found the current problem when running a routine with multiple queries, return result as false. This is due to using mysql_query function. Routine with one query should work properly.
I only get the message of executed query (not get the expected table) when run a stored procedure with single query. This should due to my changes on previous commit. (I check with little older copy of PMA) But I cannot find out what is the problem with that.
I have discovered these things : PMA_DBI_try_query() function calls in PMA_importRunQuery() function in import.lib.php (line 119) file and sql.php file (line 552) But the results are different when call the same function with the same parameter. PMA_DBI_try_query() returns correct result (mysql_result object) in the first case and returns false in second case.
Its very helpful if anyone give me some guidance to fix this ?
Regards !
*Chanaka*
Hi Michal,
I did some improvements regarding, display results for stored procedures with multiple SQL queries. There some issues in the code. I get some notices, warnings and some errors in particular scenario. (execute multiple queries or stored procedure, then click brows for any table) But I commit the code so that you may can help me to fix those.
As well while executing edit/insert/delete query from SQL window, after successful execution the query window doesn't appear. As I found the the code for AJAX request related to show SQL window back (line 824 in sql.php) is breaking due to some reason. But I couldn't find out the reason. Any help on that will be highly appreciated.
In current state, the inline edit or navigate the tables cannot be done. I think its better to not to have them. Otherwise UI will more complected as well as the code. I'll improve the code to display executing query and the resulted table for each result set. Please let me know your feed back.
Rouslan, it is time to know your thoughts. Can you please let me know from where the file you mentioned is going to executed. PMA_RTN_handleExecute() function calls only in PMA_RTN_main() and PMA_RTN_main() function only calls in rte_main.inc.php file. But when I'm executing a stored procedure from SQL window these functions are not calling. Can you please explain these things ?
Regards !
*Chanaka*
On 07/22/2012 12:24 PM, Chanaka Dharmarathna wrote:
Hi Michal,
I did some improvements regarding, display results for stored procedures with multiple SQL queries. There some issues in the code. I get some notices, warnings and some errors in particular scenario. (execute multiple queries or stored procedure, then click brows for any table) But I commit the code so that you may can help me to fix those.
As well while executing edit/insert/delete query from SQL window, after successful execution the query window doesn't appear. As I found the the code for AJAX request related to show SQL window back (line 824 in sql.php) is breaking due to some reason. But I couldn't find out the reason. Any help on that will be highly appreciated.
In current state, the inline edit or navigate the tables cannot be done. I think its better to not to have them. Otherwise UI will more complected as well as the code. I'll improve the code to display executing query and the resulted table for each result set. Please let me know your feed back.
Rouslan, it is time to know your thoughts. Can you please let me know from where the file you mentioned is going to executed. PMA_RTN_handleExecute() function calls only in PMA_RTN_main() and PMA_RTN_main() function only calls in rte_main.inc.php file. But when I'm executing a stored procedure from SQL window these functions are not calling. Can you please explain these things ?
PMA_RTN_handleExecute is called when you select a database, then click on the "Routines" tab and then click on the "Execute" link from the routines list.
Bye, Rouslan
Hi Rouslan,
Rouslan, it is time to know your thoughts.
Can you please let me know from where the file you mentioned is going to executed. PMA_RTN_handleExecute() function calls only in PMA_RTN_main() and PMA_RTN_main() function only calls in rte_main.inc.php file. But when I'm executing a stored procedure from SQL window these functions are not calling. Can you please explain these things ?
PMA_RTN_handleExecute is called when you select a database, then click on the "Routines" tab and then click on the "Execute" link from the routines list.
Thanks for your quick reply. I have never used that before. :) I'll try to do some improvements in that file.
Regards !
*Chanaka*
Hi Rouslan,
Rouslan, it is time to know your thoughts.
Can you please let me know from where the file you mentioned is going to executed. PMA_RTN_handleExecute() function calls only in PMA_RTN_main() and PMA_RTN_main() function only calls in rte_main.inc.php file. But when I'm executing a stored procedure from SQL window these functions are not calling. Can you please explain these things ?
PMA_RTN_handleExecute is called when you select a database, then click on the "Routines" tab and then click on the "Execute" link from the routines list.
Thanks for your quick reply. I have never used that before. :) I'll try to do some improvements in that file.
I did some improvements to that file [0], and seems routines working properly. I would be highly appreciated if you can just check the changes I did and let me know id there are points that may possible to break the behaviour, since you have idea on existed code as well as the behaviour. And what do you think about the display message ? (currently it shows affected rows, but for multiple tables ?)
[0] : https://github.com/Chanaka/phpmyadmin/commit/0659000a4e773ef8ae2f46f800bfdbc...
Regards !
*Chanaka*
On 07/22/2012 05:59 PM, Chanaka Dharmarathna wrote:
Hi Rouslan,
> Rouslan, it is time to know your thoughts. > Can you please let me know from where the file you mentioned is going to > executed. > PMA_RTN_handleExecute() function calls only in PMA_RTN_main() and > PMA_RTN_main() function only calls in rte_main.inc.php file. > But when I'm executing a stored procedure from SQL window these > functions are not calling. > Can you please explain these things ? PMA_RTN_handleExecute is called when you select a database, then click on the "Routines" tab and then click on the "Execute" link from the routines list. Thanks for your quick reply. I have never used that before. :) I'll try to do some improvements in that file.I did some improvements to that file [0], and seems routines working properly. I would be highly appreciated if you can just check the changes I did and let me know id there are points that may possible to break the behaviour, since you have idea on existed code as well as the behaviour. And what do you think about the display message ? (currently it shows affected rows, but for multiple tables ?)
[0] : https://github.com/Chanaka/phpmyadmin/commit/0659000a4e773ef8ae2f46f800bfdbc...
Regards !
/Chanaka/
I've tried the code from your branch and executing routines seems to work perfectly now, well done :)
As far as the display message goes, the number of affected rows by the last statement of the routine is as much information as we can get about the execution of a routine as far as I know.
Bye, Rouslan
Hi Rouslan,
> Rouslan, it is time to know your thoughts. > Can you please let me know from where the file you mentioned is going to > executed. > PMA_RTN_handleExecute() function calls only in PMA_RTN_main()and
> PMA_RTN_main() function only calls in rte_main.inc.php file. > But when I'm executing a stored procedure from SQL windowthese
> functions are not calling. > Can you please explain these things ? PMA_RTN_handleExecute is called when you select a database, then click on the "Routines" tab and then click on the "Execute" link fromthe
routines list. Thanks for your quick reply. I have never used that before. :) I'll try to do some improvements in that file.I did some improvements to that file [0], and seems routines working properly. I would be highly appreciated if you can just check the changes I did and let me know id there are points that may possible to break the behaviour, since you have idea on existed code as well as the behaviour. And what do you think about the display message ? (currently it shows affected rows, but for multiple tables ?)
[0] :
https://github.com/Chanaka/phpmyadmin/commit/0659000a4e773ef8ae2f46f800bfdbc...
Regards !
/Chanaka/
I've tried the code from your branch and executing routines seems to work perfectly now, well done :)
Thanks for your time to looking at that.
As far as the display message goes, the number of affected rows by the last statement of the routine is as much information as we can get about the execution of a routine as far as I know.
Yes, message should be give good information for the user. I'll look at that.
Regards !
*Chanaka *
Hi Michal,
I'm trying to implement titled case related to bug [0]. In current code, any SQL going to executed via SQL window, is processed
and
if there are more SQL queries, it detect only one query. So whole statement with any valid number of SQl queries need to be
detected.
Then by using mysql_multi_query() function all results related to
multiple
queries can be obtained. As I think displaying these results can be done with looping the result sets.
Additionally I saw mysql_multi_query manual [1] mentioned that separate queries are identified by semicolon. May be I need to replace the delimiter if it is not a semicolon.
It is not possible to replace it in all cases - eg. when creating procedures. So it might be better to run mysqli_query and collect results manually?
I'm not familiar with stored procedures and routines. So that I'm not clear what is the reason for that. But I'll look into that.
I have already try some modifications on display multiple query results executed throught the SQL window. Though it is not completed, now I can display several tables. But I have some problems to continue. There are many parameters are initialized in sql.php file which need to diplay relavant table. (which needs for setProperties() function) Though I need to get the use of same code snippets, since there is procedural code either I have to repeat the wheel or any otherway. So, what should I do for this ?
I'm intending to look on stored procedures and routines after implementing this first.
Regards !
-
Chanaka Dharmarathna -
Michal Čihař -
Rouslan Placella