Functions for SQL

Functions for SQL

The box module contains two functions related to SQL:

  • box.internal.sql_create_function – for making Lua functions callable from SQL statements. This, or an SQL statement with the same effect, will be part of the documentation regarding SQL Plus Lua.
  • box.execute – for making SQL statements callable from Lua functions.

Some SQL statements are illustrated in the SQL tutorial.

box.execute(sql-statement[, extra-parameters])

Execute the SQL statement contained in the sql-statement parameter.

  • sql-statement (string) – statement, which should conform to the rules for SQL grammar
  • extra-parameters (table) – optional list for placeholders in the statement

depends on statement

There are two ways to pass extra parameters for box.execute():

  • The first way is to concatenate strings. For example, this Lua script will insert 10 rows with different primary-key values into table t:

    for i=1,10,1 do
      box.execute("insert into t values (" .. i .. ")")
  • The second way is to put one or more placeholder “?” tokens inside the string, and pass a second argument, which must be a table containing values for each placeholder. For example these two requests are equivalent:

    box.execute([[INSERT INTO tt VALUES (1,'x');]]);
    x = {1,'x'}; box.execute([[INSERT INTO tt VALUES (?,?);]], x);

Since box.execute() is an invocation of a Lua function, it either causes an error message or returns a value.

For some statements the returned value will contain a field named rowcount. For example;

tarantool> box.execute([[INSERT INTO tt VALUES (8,8),(9,9);]])
tarantool> box.execute([[CREATE TABLE table1 (column1 INT PRIMARY key, column2 VARCHAR(10));]])
- rowcount: 1
tarantool> box.execute([[INSERT INTO table1 VALUES (55,'Hello SQL world!');]])
- rowcount: 1

For statements that cause generation of values for PRIMARY KEY AUTOINCREMENT columns, there will also be a field named “autoincrement_ids”.

For SELECT statements the returned value will contain a field named metadata (a table with column names and data types) and a field named “rows” (a table with the result set). For example:

tarantool> box.execute([[SELECT * FROM table1 WHERE column1 > 0;]])
- metadata:
  - name: COLUMN1
    type: integer
  - name: COLUMN2
    type: string
  - [55, 'Hello SQL world!']

The result structure contains Tarantool/NoSQL data type names in MsgPack format. For example, for a statement SELECT “x” FROM t WHERE “x”=5; where “x” is an integer column and there is one row, the raw data for the result set will look like this:

dd 00 00 00 01                  1-element array
82                              2-element map (for metadata + rows)
a8 6d 65 74 61 64 61 74 61      string = "metadata"
91                              1-element array (for column count)
82                              2-element map (for name + type)
a4 6e 61 6d 65                  string = "name"
a1 78                           string = "x"
a4 74 79 70 6                   string = "type"
a7 69 6e 74 65 67 65 72         string = "integer"
a4 72 6f 77 73                  string = "rows"
91                              1-element array (for row count)
91                              1-element array (for field count)
05                              contents

The order of components within a map is not guaranteed.

Alternative: if you are using the Tarantool server as a client, you can switch languages thus:

\set language sql
\set delimiter ;

Afterwards, you can enter any SQL statement directly without needing box.execute().

There is also an execute() function available via module, for example after conn = net_box.connect(url-string) one can say conn:execute(sql-statement]).