ECHO

Syntax

Positional Notation is the standard way for passing arguments to functions.  All the arguments are specified in order. Arguments may be omitted from right to left as long as they have specified defaults.

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. Note: named arguments cannot come before positional arguments.

ECHO { procedure_name([arg [, ...] [, arg=>val [,...] ]])
       | database_name.procedure_name([arg [, ...] [, arg=>val [,...] ]])

Arguments

• procedure_name: The name of the stored procedure to run.

• 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.

• Refer to the Permission Matrix for the required permissions.

• 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.

• Overloaded stored procedures are supported. Refer to Overloaded Functions and Stored Procedures for more information.

• Using Named Notation with overloaded stored procedures is not supported.

Example - Run a Stored Procedure

You cannot use ECHO to run 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 |
+------+-------+

Named notation and mixed notation can be used for the arguments of ECHO, similar to how named notation is used in CALL. Below shows the use of named notation with the stored procedure, p, created above. See CALL for more details on using named and mixed notation.

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

import singlestoredb as s2

# Specify connection information for SingleStore Helios
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 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_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

Example - Run an Overloaded Stored Procedure

The following example creates and runs an overloaded stored procedure named return_type that accepts an INT or BIGINT as an input parameter.

DELIMITER //

CREATE PROCEDURE return_type(input INT) RETURNS TEXT AS
  BEGIN
    RETURN "Integer";
  END //

CREATE PROCEDURE return_type(input BIGINT) RETURNS TEXT AS
  BEGIN
    RETURN "Big Integer";
  END //

DELIMITER ;

Run the two variants.

ECHO return_type(1:>INT);

+---------+
| RESULT  |
+---------+
| Integer |
+---------+

ECHO output_type(1:>BIGINT);

+-------------+
| RESULT      |
+-------------+
| Big Integer |
+-------------+