[Phpmyadmin-devel] Display multiple query results

[Chanaka Dharmarathna]

> >> 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_routines.lib.php#L1260
>

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 !
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20120719/f597aa0d/attachment.html>