ECHO SELECT

Syntax

SQL

ECHO select_statement

Arguments

select_statement: A SELECT statement.

Remarks

ECHO SELECT may be used inside or outside of a stored procedure. When used outside of a stored procedure, SELECT and ECHO SELECT are equivalent.

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. ECHO may also be used to call a stored procedure that returns a scalar data type.

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.

SQL

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:

SQL

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.

SQL

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 SingleStore can be used. Copy the text below and put it into a file called multiresult.py.

Before running the script, make sure the SingleStore Python library is installed.

Python

import singlestoredb as s2

def get_connection(db='db'):
    """ Returns a new connection to the database. """
    return s2.connect(
        host=HOST, port=PORT, user=USER, password=PASSWORD, database=db, results_type='dict')

def consume_multi_result_set(conn, proc, args):
    """ Calls a multi-result set SP and loops through the results. """
    result_set_count = 1
    has_results = True

    with conn.cursor() as cursor:
        cursor.callproc(proc, args)

        while has_results:
            print(f"Processing result set {result_set_count}.")
            for row in cursor:
                print(row)

            result_set_count += 1

            has_results = cursor.nextset()

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:

Shell

python multiresult.py

The results are as follows: