ECHO SELECT
On this page
Within a stored procedure, executes the specified SELECT
statement and returns a set of rows to the console or to the application that called the stored procedure.
Syntax
ECHO select_statement
Arguments
-
select_
: A SELECT statement.statement
Remarks
ECHO SELECT
may be used inside or 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
.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) ASBEGINECHO 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.ECHO SELECT
statements.
DELIMITER //CREATE OR REPLACE PROCEDURE driver() ASDECLARE a ARRAY(INT);BEGINa = create_array(3);a[0] = 2; a[1] = 4; a[2] = 6;CALL processArray(a);END //CREATE OR REPLACE PROCEDURE processArray(a array(INT)) ASDECLARE a_val INT;BEGIN-- show array contentsFOR v IN a LOOPECHO 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 SingleStoreDB can be used.multiresult.
.
Before running the script, make sure the SingleStore Python library is installed.
from memsql.common import databasedef 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 = 1for res in conn.query(query):print "Processing result set %s." % result_set_countfor val in res.values:assert len(res.fieldnames) == len(val)print zip(res.fieldnames, val)result_set_count += 1print ""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')]
Last modified: April 6, 2023