ECHO SELECT
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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 SingleStore can be used.multiresult.
.
Before running the script, make sure the SingleStore Python library is installed.
import singlestoredb as s2def 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 = 1has_results = Truewith 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 += 1has_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:
python multiresult.py
The results are as follows:
Processing result set 1.{'vid': 1, 'title': 'Hercules'}Processing result set 2.{'user': 'Jane', 'vid': 1, 'played_at': datetime.datetime(2018, 7, 15, 20, 2)}{'user': 'Jill', 'vid': 1, 'played_at': datetime.datetime(2018, 7, 15, 20, 0)}Processing result set 3.
Last modified: November 27, 2024