SQL-specific requests and responses | Tarantool
Документация на русском языке
поддерживается сообществом

SQL-specific requests and responses

Below are considered the IPROTO_EXECUTE and IPROTO_PREPARE requests, followed by a description of responses.

Name Code Description
IPROTO_EXECUTE 0x0b Execute an SQL statement (box.execute())
IPROTO_PREPARE 0x0d Prepare an SQL statement (box.prepare())

Code: 0x0b.

The body is a 3-item map:

IPROTO_EXECUTE, prepared statementSizeMP_UINTHeaderIPROTO_REQUEST_TYPEIPROTO_EXECUTEIPROTO_SYNCMP_UINTBodyIPROTO_STMT_IDMP_INTIPROTO_SQL_BINDMP_ARRAYIPROTO_OPTIONSMP_ARRAYIPROTO_EXECUTE, SQL stringSizeMP_UINTHeaderIPROTO_REQUEST_TYPEIPROTO_EXECUTEIPROTO_SYNCMP_UINTBodyIPROTO_SQL_TEXTMP_STRIPROTO_SQL_BINDMP_ARRAYIPROTO_OPTIONSMP_ARRAY
  • Use IPROTO_STMT_ID (0x43) and statement-id (MP_INT) if executing a prepared statement. Use IPROTO_SQL_TEXT (0x40) and statement-text (MP_STR) if executing an SQL string.
  • IPROTO_SQL_BIND (0x41) corresponds to the array of parameter values to match ? placeholders or :name placeholders.
  • IPROTO_OPTIONS (0x2b) corresponds to the array of options. It is usually empty.

Suppose we prepare a statement with two ? placeholders, and execute with two parameters, thus:

n = conn:prepare([[VALUES (?, ?);]])
conn:execute(n.stmt_id, {1,'a'})

Then the body will look like this:

BodyIPROTO_STMT_ID0xd7aa741bIPROTO_SQL_BIND[1, 'a']IPROTO_OPTIONS[]

The Understanding binary protocol tutorial shows actual byte codes of the IPROTO_EXECUTE message.

To call a prepared statement with named parameters from a connector pass the parameters within an array of maps. A client should wrap each element into a map, where the key holds a name of the parameter (with a colon) and the value holds an actual value. So, to bind foo and bar to 42 and 43, a client should send IPROTO_SQL_TEXT: <...>, IPROTO_SQL_BIND: [{"foo": 42}, {"bar": 43}].

If a statement has both named and non-named parameters, wrap only named ones into a map. The rest of the parameters are positional and will be substituted in order.

Let’s ask for full metadata and then select the two rows from a table named t1 that has columns named DD and Д:

conn.space._session_settings:update('sql_full_metadata', {{'=', 'value', true}})
conn:prepare([[SELECT dd, дд AS д FROM t1;]])

In the iproto request, there would be no IPROTO_DATA and there would be two additional items:

  • 34 00 = IPROTO_BIND_COUNT and MP_UINT = 0 (there are no parameters to bind).
  • 33 90 = IPROTO_BIND_METADATA and MP_ARRAY, size 0 (there are no parameters to bind).

Here is what the request body looks like:

BodyIPROTO_STMT_IDMP_UINTIPROTO_BIND_COUNTMP_INTIPROTO_BIND_METADATAMP_ARRAYIPROTO_METADATAIPROTO_FIELD_NAME'DD'IPROTO_FIELD_TYPE'integer'IPROTO_FIELD_IS_NULLABLEfalseIPROTO_FIELD_IS_AUTOINCREMENTtrueIPROTO_FIELD_SPANnilIPROTO_FIELD_NAME'Д'IPROTO_FIELD_TYPE'string'IPROTO_FIELD_COLL'unicode'IPROTO_FIELD_IS_NULLABLEtrueIPROTO_FIELD_SPAN'дд'

Code: 0x0d.

The body is a 1-item map:

IPROTO_PREPARESizeMP_UINTHeaderIPROTO_REQUEST_TYPEIPROTO_PREPAREIPROTO_SYNCMP_UINTBodyIPROTO_STMT_IDMP_INT

Thus the IPROTO_PREPARE map item is the same as the first item of the IPROTO_EXECUTE body.

After the header, for a response to an SQL statement, there will be a body that is slightly different from the body for non-SQL requests/responses.

If the SQL statement is SELECT or VALUES or PRAGMA, the response contains:

Response to SELECT, VALUES, or PRAGMASizeMP_UINTHeaderIPROTO_REQUEST_TYPEIPROTO_OKIPROTO_SYNCMP_UINTIPROTO_SCHEMA_VERSIONMP_UINTBodyIPROTO_METADATAMP_ARRAY of column mapsIPROTO_DATAMP_ARRAY of tuples

Let’s ask for full metadata and then select the two rows from a table named t1 that has columns named DD and Д:

conn.space._session_settings:update('sql_full_metadata', {{'=', 'value', true}})
conn:execute([[SELECT dd, дд AS д FROM t1;]])

The response body might look like this:

BodyIPROTO_METADATAIPROTO_FIELD_NAME'DD'IPROTO_FIELD_TYPE'integer'IPROTO_FIELD_IS_NULLABLEfalseIPROTO_FIELD_IS_AUTOINCREMENTtrueIPROTO_FIELD_SPANnilIPROTO_FIELD_NAME'Д'IPROTO_FIELD_TYPE'string'IPROTO_FIELD_COLL'unicode'IPROTO_FIELD_IS_NULLABLEtrueIPROTO_FIELD_SPAN'дд'IPROTO_DATAMP_ARRAY[1, 'a'], [2, 'b']

The tutorial Understanding the binary protocol shows actual byte codes of responses to the above SQL messages.

If the SQL request is not SELECT or VALUES or PRAGMA, then the response body contains only IPROTO_SQL_INFO (0x42). Usually IPROTO_SQL_INFO is a map with only one item – SQL_INFO_ROW_COUNT (0x00) – which is the number of changed rows.

Response to SQL requests other than SELECT, VALUES, or PRAGMASizeMP_UINTHeaderIPROTO_REQUEST_TYPEIPROTO_OKIPROTO_SYNCMP_UINTIPROTO_SCHEMA_VERSIONMP_UINTBodySQL_INFOSQL_INFO_ROW_COUNTMP_UINT(Optional)SQL_INFO_AUTO_INCREMENT_IDSMP_ARRAY

For example, if the request is INSERT INTO table-name VALUES (1), (2), (3), then the response body contains an IPROTO_SQL_INFO map with SQL_INFO_ROW_COUNT = 3.

The IPROTO_SQL_INFO map may contain a second item – SQL_INFO_AUTO_INCREMENT_IDS (0x01) – which is the new primary-key value (or values) for an INSERT in a table defined with PRIMARY KEY AUTOINCREMENT. In this case the MP_MAP will have two keys, and one of the two keys will be 0x01: SQL_INFO_AUTO_INCREMENT_IDS, which is an array of unsigned integers.

Нашли ответ на свой вопрос?
Обратная связь