ECHO

ECHO { procedure_name( [argument_list] ) 
        | database_name.procedure_name( [argument_list] ) }; 

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 |
+------+-------+

You can use ECHO command to obtain a scalar value from a stored procedure.

DELIMITER //
CREATE or REPLACE PROCEDURE p() RETURNS INT AS
    BEGIN
    RETURN 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. 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 SingleStore Python library is installed.

Now run the following:

from memsql.common import database

# Specify connection information for SingleStoreDB
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