Executes the specified SELECT statement and returns a set of rows as a result. In MemSQL 6.5 or later, the ECHO SELECT command can be used inside of a stored procedure to output one or more result row sets from the procedure, which is useful in returning multiple result sets to a client application or for debugging purposes.

Syntax

ECHO select_statement

Arguments

• select_statement: A SELECT statement.

Example 1: Multiple Result Sets

Multiple result sets can be returned from a single stored procedure call using ECHO SELECT. For example, the following procedure returns rows from a table video and linked rows from a table play.

CREATE DATABASE db;
USE db;

CREATE TABLE video(vid INT, title VARCHAR(30));
CREATE TABLE play(user VARCHAR(30), vid INT, played_at DATETIME);
INSERT INTO video VALUES (1, "Hercules");
INSERT INTO video VALUES (2, "Incredibles");
INSERT INTO play VALUES("Jill", 1, "2018-07-15 20:00:00");
INSERT INTO play VALUES("Rick", 2, "2018-07-15 20:01:00");
INSERT INTO play VALUES("Jane", 1, "2018-07-15 20:02:00");
INSERT INTO play VALUES("Bob", 2, "2018-07-15 20:03:00");

DELIMITER //
CREATE OR REPLACE PROCEDURE multiResult (search_video_id INT) AS
BEGIN
  ECHO SELECT * FROM video WHERE vid = search_video_id;
  ECHO SELECT * FROM play WHERE vid = search_video_id;
END //
DELIMITER ;

CALL multiResult(1);
+------+----------+
| vid  | title    |
+------+----------+
|    1 | Hercules |
+------+----------+
1 row in set (0.38 sec)

+------+------+---------------------+
| user | vid  | played_at           |
+------+------+---------------------+
| Jane |    1 | 2018-07-15 20:02:00 |
| Jill |    1 | 2018-07-15 20:00:00 |
+------+------+---------------------+
2 rows in set (0.46 sec)

Example 2: Printing to Help Debug Stored Procedure

ECHO SELECT can also be used to help debug stored procedures. After the stored procedure has been debugged, you can remove or comment the ECHO SELECT statements. For example:

DELIMITER //
CREATE OR REPLACE PROCEDURE driver() AS
DECLARE a ARRAY(INT);
BEGIN
  a = create_array(3);
  a[0] = 2; a[1] = 4; a[2] = 6;
  CALL processArray(a);
END //

CREATE OR REPLACE PROCEDURE processArray(a array(INT)) AS
DECLARE a_val INT;
BEGIN
  -- show array contents
  FOR v IN a LOOP
    ECHO SELECT v AS value;
  END LOOP;

  -- process elements of array
  -- ...
END //
DELIMITER ;

Calling the driver produces output for each array element, as shown below.

CALL driver();
+-------+
| value |
+-------+
|     2 |
+-------+
1 row in set (0.11 sec)

+-------+
| value |
+-------+
|     4 |
+-------+
1 row in set (0.11 sec)

+-------+
| value |
+-------+
|     6 |
+-------+
1 row in set (0.11 sec)

Example 3: A Client Application That Consumes Multiple Result Sets

An external client application may consume multiple result sets produced by ECHO SELECT statements run in a stored procedure by using the MySQL Multi-Resultset protocol.

The example below shows how to create such an application in Python, but any language with interfaces to MemSQL can be used. Copy the text below and put it into a file called multiresult.py.

Before running the script, make sure the MemSQL Python library is installed (https://github.com/memsql/memsql-python).

from memsql.common import database

def get_connection(db='db', host='127.0.0.1', port=3306, user='root', password=''):
    """ Returns a new connection to the database. """
    return database.connect(
        host=host, port=port, user=user, password=password, database=db)

def consume_multi_result_set(conn, proc, args):
    """ Calls a multi-result set SP and loops through the results. """
    query = 'CALL %s(%s)' % (proc, args)
    result_set_count = 1
    for res in conn.query(query):
        print "Processing result set %s." % result_set_count
        for val in res.values:
            assert len(res.fieldnames) == len(val)
            print zip(res.fieldnames, val)
        result_set_count += 1
        print ""

def main():
    with get_connection(db="db") as conn:
        proc = 'multiResult'
        args = '1'
        consume_multi_result_set(conn, proc, args)

if __name__ == '__main__':
    main()

Now, run it like this from the command prompt:

$ python multiresult.py

The results are as follows:

Processing result set 1.
[('vid', '1'), ('title', 'Hercules')]

Processing result set 2.
[('user', 'Jill'), ('vid', '1'), ('played_at', '2018-07-15 20:00:00')]
[('user', 'Jane'), ('vid', '1'), ('played_at', '2018-07-15 20:02:00')]

Remarks

For completeness, and ease of testing, it is also possible to run ECHO SELECT ... from a client and it is equivalent to simply SELECT ....

A different version of the ECHO command can also be used to call a stored procedure that returns a QUERY type value, and retrieve the rows produced by executing the QUERY type value.

Was this Article Helpful?

Thanks For Your Feedback!