box.execute()
-
box.
execute
(sql-statement[, extra-parameters])¶ Execute the SQL statement contained in the sql-statement parameter.
Parameters: - sql-statement (
string
) – statement, which should conform to the rules for SQL grammar - extra-parameters (
table
) – optional table for placeholders in the statement
Return: depends on statement
There are two ways to pass extra parameters for
box.execute()
:- The first way, which is the preferred way, is to put placeholders in the
string, and pass a second argument, an extra-parameters table. A
placeholder is either a question mark “?”, or a colon “:” followed by a
name. An extra parameter is any Lua expression.
If placeholders are question marks, then they will be replaced by
extra-parameter values in corresponding positions, that is, the first ?
will be replaced by the first extra parameter, the second ? will be
replaced by the second extra parameter, and so on. If placeholders are
:names, then they will be replaced by extra-parameter values with
corresponding names. For example this request which contains literal
values 1 and ‘x’:
box.execute([[INSERT INTO tt VALUES (1, 'x');]]);
is the same as this request which contains two question-mark placeholders (?
and?
) and a two-element extra-parameters table:
x = {1,'x'}
box.execute([[INSERT INTO tt VALUES (?, ?);]], x);
and is the same as this request which contains two :name placeholders (:a
and:b
) and a two-element extra-parameters table with elements named “a” and “b”:
box.execute([[INSERT INTO tt VALUES (:a, :b);]], {{[':a']=1},{[':b']='x'}})
- The second 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 .. ")")
end
When creating SQL statements based on user input, application developers should beware of SQL injection.
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([[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 or PRAGMA statements, the returned value will be a result set, containing a field named “metadata” (a table with column names and Tarantool/NoSQL type names) and a field named “rows” (a table with the contents of each row).
For example, for a statement
SELECT "x" FROM t WHERE "x"=5;
where"x"
is an INTEGER column and there is one row, a display on the Tarantool client will look like this:tarantool> box.execute([[SELECT "x" FROM t WHERE "x"=5;]]) --- - metadata: - name: x type: integer rows: - [5] ...
For a look at raw format of SELECT results, see Binary protocol – responses for SQL.
The order of components within a map is not guaranteed.
If
sql_full_metadata
in the _session_settings system table is TRUE, then result set metadata may include these things in addition toname
andtype
:collation
(present only if COLLATE clause is specified for a STRING) = “Collation”.is_nullable
(present only if the select list specified a base table column and nothing else) = false if column was defined as NOT NULL, otherwise true. If this is not present, that implies that nullability is unknown.is_autoincrement
(present only if the select list specified a base table column and nothing else) = true if column was defined as PRIMARY KEY AUTOINCREMENT, otherwise false.span
(always present) = the original expression in a select list, which will often be the same asname
if the select list specifies a column name and nothing else, but otherwise will differ, for example afterSELECT x+55 AS x FROM t;
thename
is X and thespan
is x+55. Ifspan
andname
are the same then the content is MP_NIL.
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 net.box, for example afterconn = net_box.connect(url-string)
one can sayconn:execute(sql-statement])
.- sql-statement (