ECHO
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
Executes a stored procedure and returns a set of rows as a result.
Syntax
Positional Notation is the standard way for passing arguments to functions.
ECHO { procedure_name([arg[, ...]])| database_name.procedure_name([arg [, ...]]) };
Named Notation is when the argument name is specified using => to separate it from the argument expression.
ECHO { procedure_name([arg=>val [,...]])| database_name.procedure_name([arg=>val [,...]])
Mixed Notation is a combination of positional and named notation.
ECHO { procedure_name([arg [, ...] [, arg=>val [,...] ]])| database_name.procedure_name([arg [, ...] [, arg=>val [,...] ]])
Arguments
-
procedure_
: The name of the stored procedure to execute.name -
argument_
: A list of optional arguments to pass as input parameters to the stored procedure.list
Remarks
-
Stored procedures use a late binding approach when returning sets of rows.
That is, a query object with parameter bindings is returned by a stored procedure, not a materialized row set. To get the set of rows for the query returned by a stored procedure, you must run the procedure using ECHO
. -
You can consume the results of an
ECHO
statement from a client application program just like you consume the results of aSELECT
statement. -
The CALL command is similar to
ECHO
but does not produce a set of rows as a result.Any query value returned is ignored when using CALL
. -
Refer to the Permission Matrix for the required permission.
-
When using Named Notation and Mixed Notation the following are applicable:
-
Once a named argument is used, all arguments to the right must be named as well.
-
Named arguments cannot refer to other arguments.
-
Argument names are case-sensitive.
-
A different order of the same arguments may yield different plans.
-
Example
You cannot use ECHO
to execute a stored procedure that does not return a value, or a stored procedure that returns an ARRAY
or RECORD
value.ECHO
will be a single row with the column name RESULT
.
DELIMITER //CREATE PROCEDURE return_scalar() RETURNS INT ASBEGINRETURN 2;END //DELIMITER ;ECHO return_scalar();
+--------+
| RESULT |
+--------+
| 2 |
+--------+
1 row in set
The behavior is different when the stored procedure returns a query type variable.
CREATE TABLE t(a INT, b VARCHAR(30));INSERT t VALUES(1, "red"), (2, "green"), (3, "blue");DELIMITER //CREATE PROCEDURE p(x INT) RETURNS query(a INT, b VARCHAR(30)) ASDECLAREq query(a INT, b VARCHAR(30)) =SELECT a, bFROM tWHERE a >= x;BEGINRETURN q;END //DELIMITER ;
You can use the ECHO
command to call this procedure, evaluate the query it returns, and output the resulting rows, as follows:
ECHO p(2);
+------+-------+
| a | b |
+------+-------+
| 3 | blue |
| 2 | green |
+------+-------+
Named notation and mixed notation can be used for the arguments of ECHO
, similar to how named notation is used in CALL
.p
, created above.
ECHO p(x=>2);
+------+-------+
| a | b |
+------+-------+
| 3 | blue |
| 2 | green |
+------+-------+
You can use ECHO
command to obtain a scalar value from a stored procedure.
DELIMITER //CREATE or REPLACE PROCEDURE p() RETURNS INT ASBEGINRETURN 17;end //DELIMITER ;CALL p();ECHO p();
+---------+
| RESULT |
+---------+
| 17 |
+---------+
Consume Output of Echo in a Python Application
A stored procedure may optionally return a QUERY
value which evaluates to a set of rows.ECHO
statement to run the procedure.
Setup
As an example, first run the following SQL statements:
-- Setup for Python ECHO example.USE information_schema;DROP DATABASE IF EXISTS test;CREATE DATABASE test;USE test;CREATE TABLE IF NOT EXISTS tbl(id INT, value VARCHAR(30));INSERT INTO tbl VALUES(1, "red");INSERT INTO tbl VALUES(2, "green");INSERT INTO tbl VALUES(3, "blue");DELIMITER //CREATE OR REPLACE PROCEDURE p(x INT) RETURNS QUERY(id INT, value VARCHAR(30)) ASDECLAREr QUERY(id INT, value VARCHAR(30)) =SELECT id, value FROM tblWHERE id >= x;BEGINRETURN r;END//DELIMITER ;
Python Application
Then, make sure the SingleStore Python library is installed.
Now run the following:
import singlestoredb as s2# Specify connection information for SingleStore HeliosHOST = "127.0.0.1"PORT = 3306USER = "root"PASSWORD = ""# Specify which database to work with.DATABASE = "test"def get_connection(db=DATABASE):""" Returns a new connection to the database. """return s2.connect(host=HOST, port=PORT, user=USER, password=PASSWORD, database=db)def test_echo(x):with get_connection(db=DATABASE) as conn:query = 'echo p(%s)'with conn.cursor() as cur:cur.execute(query, [x])for r in cur:print("id = %s, value = %s" % (r[0], r[1]))
Now, test_
can be called from the Python command line to display rows retrieved using ECHO
.
test_echo(2);
The following output is produced:
>>> test_echo(2);
id = 2, value = green
id = 3, value = blue
Last modified: November 27, 2024