You are viewing an older version of this section. View current production version.
ECHO
Executes a stored procedure and returns a set of rows as a result.
Syntax
ECHO { procedure_name( [argument_list] )
| database_name.procedure_name( [argument_list] ) };
Arguments
-
procedure_name
: The name of the stored procedure to execute. -
argument_list
: A list of optional arguments to pass as input parameters to the stored procedure.
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 a SELECT
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
.
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. If the results of a stored procedure is a scalar value, the result of executing that stored procedure with ECHO
will be a single row with the column name RESULT
.
DELIMITER //
CREATE PROCEDURE return_scalar() RETURNS INT AS
BEGIN
RETURN 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. Consider the following table and stored procedure:
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)) AS
DECLARE
q query(a INT, b VARCHAR(30)) =
SELECT a, b
FROM t
WHERE a >= x;
BEGIN
RETURN 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 |
+------+-------+
Consume Output of Echo in a Python Application
A stored procedure may optionally return a QUERY
value which evaluates to
a set of rows. You can call a stored procedure from an application program and consume the set of rows produced
by the query using the 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)) AS
DECLARE
r QUERY(id INT, value VARCHAR(30)) =
SELECT id, value FROM tbl
WHERE id >= x;
BEGIN
RETURN r;
END
//
DELIMITER ;
Python Application
Then, make sure the MemSQL Python library is installed (https://github.com/memsql/memsql-python).
Now run the following:
from memsql.common import database
# Specify connection information for MemSQL
HOST = "127.0.0.1"
PORT = 3306
USER = "root"
PASSWORD = ""
# Specify which database to work with.
DATABASE = "test"
def get_connection(db=DATABASE):
""" Returns a new connection to the database. """
return database.connect(
host=HOST, port=PORT, user=USER, password=PASSWORD, database=db)
def test_echo(x):
with get_connection(db="test") as conn:
query = 'echo p(%s)' % (x);
for r in conn.query(query):
print "id = %s, value = %s" % (r.id, r.value)
Now, test_echo
can be called from the Python command line to display
rows retrieved using ECHO
. For example:
test_echo(2);
The following output is produced:
>>> test_echo(2);
id = 2, value = green
id = 3, value = blue