All pages
Powered by GitBook
1 of 10

3 - Binary Protocol (Prepared Statements)

The binary protocol defines a non-text-based (binary) format that is used instead of the text protocol, only for prepared statements

COM_STMT_CLOSE

Closes a previously prepared statement.

Direction

Client to server.

Implemented by

  • mysql_stmt_close()

  • mysql_stmt_prepare()

Fields

  • int<1> 0x19 COM_STMT_CLOSE header

  • int<4> Statement id

Example

05 00 00 00 19 04 00 00 00

Response

No response from server.

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_BULK_EXECUTE

Executes a bulk insert of a previously prepared statement.

A command that returns a resultset will return an error (Error packet).

Direction

Client to server.

Fields

  • int<1> 0xfa : COM_STMT_BULK_EXECUTE header

  • int<4> statement id

  • int<2> bulk flags

  • if (bulk_flag & SEND_TYPES_TO_SERVER)

    • for each parameter :

      • byte<1>: field type

      • byte<1>: parameter type flag

  • until end of packet

    • for each parameter (i.e param_count times)

      • byte<1>: parameter indicator

      • if indicator == NONE

        • byte : binary parameter value

Flags

bulk flags

SEND_UNIT_RESULTS

64

Return generated affected rows and auto-increment IDs as a resultset (only when server supports MARIADB_CLIENT_BULK_UNIT_RESULTS capability)

SEND_TYPES_TO_SERVER

128

Send types to server

parameter type flag:

128

unsigned

parameter indicator

0

NONE

Value follow

1

NULL

Value is null

2

DEFAULT

For INSERT/UPDATE, value is default

3

IGNORE

Value is default for insert, Is ignored for update

COM_STMT_BULK_EXECUTE response

The server can answer with 3 different responses:

  • 0xff: ERR_Packet if any errors occur.

  • 0x00: OK_packet when query execution works without resultset.

  • a resultset containing affected rows and auto-increment IDs when bulk flag SEND_UNIT_RESULTS is set

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_EXECUTE

Executes a previously prepared statement.

If specific data is large, it can be sent separately prior to this command (see COM_STMT_SEND_LONG_DATA).

If a statement is re-executed without changing the bind types, the types do not need to be sent to the server again.

Direction

Client to server.

Fields

  • int<1> 0x17 : COM_STMT_EXECUTE header

  • int<4> statement id

  • int<1> flags:

  • int<4> Iteration count (always 1)

  • if (param_count > 0)

    • byte<(param_count + 7)/8> null bitmap

    • byte<1>: send type to server (0 / 1)

    • if (send type to server)

      • for each parameter :

        • byte<1>: field type

        • byte<1>: parameter flag

    • for each parameter (i.e param_count times)

      • if parameter is not null

        • byte binary parameter value

Statement Id

Statement id is the identifier of the prepared statement (from COM_STMT_PREPARE answer)

Specific "-1" statement id value

Since MariaDB server version 10.2, value -1 (0xFFFFFFFF) can be used to indicate to use the last statement prepared on current connection if no COM_STMT_PREPARE has fail since.

This permit pipelining :

  • send COM_STMT_PREPARE + COM_STMT_EXECUTE with statement id -1

  • read COM_STMT_PREPARE + COM_STMT_EXECUTE response

In case COM_STMT_PREPARE returns an error, COM_STMT_EXECUTE will return an error that statement id -1 is unknown. This permits to avoid much of the network latency.

Flag

0

no cursor

1

read only

2

cursor for update

4

scrollable cursor

Cursors

If the flags of the COM_STMT_EXECUTE request a cursor to be opened, the returned result will only contain the column definitions and the EOF that terminates it and the resultset rows are fetched using separate COM_STMT_FETCH commands.

Whether a cursor is actually opened is indicated by the SERVER_STATUS_CURSOR_EXISTS bit in the first EOF packet in the response to the COM_STMT_EXECUTE. If it is not set in, no cursor is opened and a normal resultset is returned.

Parameter flag

parameter type flag byte:

128

unsigned

NULL-Bitmap

The NULL-Bitmap indicates if parameters are null (one bit per parameter). If the parameter is NULL, the bit is set in the bitmap and the parameter value is not sent.

The size in bytes of the NULL-bitmap can be calculated with: (parameter number + 7) / 8

Binary parameter encoding

The encoding of the COM_STMT_EXECUTE parameters are the same as the encoding of the binary resultsets.

COM_STMT_EXECUTE response

The server can answer with 3 different responses:

  • 0xff: ERR_Packet if any errors occur.

  • 0x00: OK_packet when query execution works without resultset.

  • one (or more) Resultset, when query execution return rows (in case of SELECT query for example).

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_FETCH

Fetch rows from a prepared statement.

A COM_STMT_EXECUTE with a non-zero cursor flag must have been successfully executed before any COM_STMT_FETCH commands can be executed.

Fields

  • int<1> 0x1C COM_STMT_FETCH header.

  • int<4> statement id.

  • int<4> number of rows to fetch.

Response

Returns one or more binary result set rows followed by an EOF packet.

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_PREPARE

Prepares a statement on the server.

Not all statements can be prepared. See PREPARE for a list of statements that can be prepared.

Implemented by

  • mariadb_stmt_execute_direct()

  • mysql_stmt_prepare()

Fields

  • int<1> 0x16 COM_STMT_PREPARE header

  • string SQL Statement

Example

1F 00 00 00 16 53 45 4C 45 43 54 20 2A 20 46 52  .....SELECT * FR
1F 4D 20 74 65 73 74 5F 62 69 6E 64 5F 72 65 73  OM test_bind_res
75 6C 74                                         ult

COM_STMT_PREPARE response

If anything goes wrong, the server will send an ERR_Packet. If the command succeeds, different packets will be received:

  • COM_STMT_PREPARE_OK

  • if number of parameters (count of '?' placeholders) > 0

    • for each parameter:

      • column definition packet

    • if !DEPRECATE_EOF eof_packet

  • if prepared statement returns result set and number of result set columns > 0

    • for each column:

      • column definition packet

    • if !DEPRECATE_EOF eof_packet

COM_STMT_PREPARE_OK

  • int<1> 0x00 COM_STMT_PREPARE_OK header

  • int<4> statement id

  • int<2> number of columns in the returned result set (or 0 if statement does not return result set)

  • int<2> number of prepared statement parameters ('?' placeholders)

  • string<1> -not used-

  • int<2> number of warnings

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_RESET

Resets a prepared statement on the client and server to state after preparing.

Fields

  • int<1> 0x1A COM_STMT_RESET header

  • int<4> Statement id

Response

ERR_Packet or OK_Packet

Example

05 00 00 00 1A 04 00 00 00

This page is licensed: CC BY-SA / Gnu FDL

COM_STMT_SEND_LONG_DATA

When data for a specific column is large, it can be sent separately to avoid the limitation of max_allowed_packet (see packet splitting).

Multiple COM_STMT_SEND_LONG_DATA commands with the same column id will append the data. COM_STMT_SEND_LONG_DATA will be sent before COM_STMT_EXECUTE.

Fields

  • int<1> 0x18 COM_STMT_SEND_LONG_DATA header

  • int<4> statement id

  • int<2> parameter number

  • byte data

Response

Server doesn't send response.

This page is licensed: CC BY-SA / Gnu FDL

Server Response Packets (Binary Protocol)

The following response packets will be sent from the server to client in binary protocol only. They may be mixed with packets from the text protocol, like OK_PACKET, PACKET_METADATA or ERR_PACKET.

PACKET_BINDATA

In contrast to the text protocol, the binary protocol transfers the data according to the format of the field types returned in PACKET_METADATA.

Field type
Representation

MYSQL_TYPE_BIT

str_LEC

MYSQL_TYPE_BLOB

str_LEC

MYSQL_TYPE_DATE MYSQL_TYPE_DATETIME MYSQL_TYPE_TIMESTAMP

int_11 (default)int_7 (no microseconds)int_4 (no time values)int_0 (no values)

MYSQL_TYPE_DECIMAL

str_LEC

MYSQL_TYPE_DOUBLE

int_8

MYSQL_TYPE_ENUM

str_LEC

MYSQL_TYPE_FLOAT

int_4

MYSQL_TYPE_GEOMETRY

str_LEC

MYSQL_TYPE_INT24

int_4

MYSQL_TYPE_JSON

str_LEC

MYSQL_TYPE_LONGLONG

int_8

MYSQL_TYPE_LONG_BLOB

str_LEC

MYSQL_TYPE_LONG

int_4

MYSQL_TYPE_MEDIUM_BLOB

str_LEC

MYSQL_TYPE_NEWDECIMAL

str_LEC

MYSQL_TYPE_NULL

stored in bitmap

MYSQL_TYPE_SET

str_LEC

MYSQL_TYPE_STRING

str_LEC

MYSQL_TYPE_SHORT

int_2

MYSQL_TYPE_TINY_BLOB

str_LEC

MYSQL_TYPE_TINY

int_1

MYSQL_TYPE_VARCHAR

str_LEC

MYSQL_TYPE_VAR_STRING

str_LEC

MYSQL_TYPE_YEAR

int_4

Fields

(column_count + 7)/8   null bitmap
while (!eof) {
  for (i=0; i < column_count; i++)
  {
    data (length depends on the data type)
  }
}

This page is licensed: CC BY-SA / Gnu FDL