1 of 10

3 - Binary Protocol (Prepared Statements)

Understand the binary protocol for prepared statements. This section details how prepared statements are exchanged efficiently between client and server, optimizing performance and security.

COM_STMT_BULK_EXECUTE

This command executes a bulk insert for a previously prepared statement, using a compact binary format for efficiency.

Executes a bulk insert of a previously prepared statement.

A command that returns a result set returns an error (Error packet).

Direction

Client to server.

Fields

0xfa : COM_STMT_BULK_EXECUTE header.
statement id.
.

Flags

Bulk Flags

Flag

Value

Details

Parameter Type Flag

Parameter Indicator

Response

The server can answer with 3 different responses:

0xff: if any errors occur.
0x00: when query execution works without result set.
A result set 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_CLOSE

This command deallocates a prepared statement on the server, freeing up associated resources.

Closes a previously prepared statement.

Direction

Client to server.

Implemented by

Fields

0x19 COM_STMT_CLOSE header.
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_EXECUTE

This command executes a prepared statement using parameter values provided in the binary protocol format.

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

0x17 : COM_STMT_EXECUTE header.
.
flags.

Statement ID

Statement ID is the identifier of the prepared statement (from ).

Specific "-1" statement id value

Value -1 (0xFFFFFFFF) can be used to indicate to use the last statement prepared on current connection if no COM_STMT_PREPARE has failed 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 returns an error that statement ID -1 is unknown. This permits to avoid much of the network latency.

Flag

Cursors

If the flags of the COM_STMT_EXECUTE request a cursor to be opened, the returned result only contains the column definitions and the EOF that terminates it, and the result set 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 result set is returned.

Parameter Flag

Parameter type flag byte:

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 .

Response

The server can answer with 3 different responses:

0xff: if any errors occur.
0x00: when query execution works without result set.
One (or more) , when query execution return rows (in case of SELECT query, for example).

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

COM_STMT_FETCH

This command fetches rows from an existing result set of a prepared statement that was executed with a cursor.

Fetch rows from a prepared statement.

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

Fields

0x1C COM_STMT_FETCH header.

COM_STMT_PREPARE

This command prepares an SQL statement on the server, returning a statement ID and metadata about parameters and columns.

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

Fields

0x16 COM_STMT_PREPARE header
SQL Statement

Example

Response

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

.
If number of parameters (count of '?' placeholders) > 0:
- For each parameter:

COM_STMT_PREPARE_OK

0x00 COM_STMT_PREPARE_OK header.
statement ID.
number of columns in the returned result set (or 0 if statement does not return result set).

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

COM_STMT_RESET

This command resets the data of a prepared statement on the server, clearing any buffers or previous parameter values.

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

Fields

int<1> 0x1A COM_STMT_RESET header.
Statement ID.

Response

or .

Example

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

COM_STMT_SEND_LONG_DATA

This command sends long data, such as BLOB or TEXT values, in chunks for a specific parameter of a prepared statement.

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 append the data. COM_STMT_SEND_LONG_DATA is sent before COM_STMT_EXECUTE.

Fields

0x18 COM_STMT_SEND_LONG_DATA header.
statement id.
parameter number.
data.

Response

Server doesn't send response.

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

Server Response Packets (Binary Protocol)

This section details the structure of response packets sent by the server when using the binary protocol, particularly for result sets.

PACKET_BINDATA

This packet type in the binary protocol transfers result set row data in a compact binary format, determined by the column types returned in metadata.

In contrast to the text protocol, the binary protocol transfers 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)

Fields

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

COM_STMT_EXECUTE

This command executes a prepared statement using parameter values provided in the binary protocol format.

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

0x17 : COM_STMT_EXECUTE header.
.
flags.

Statement ID

Statement ID is the identifier of the prepared statement (from ).

Specific "-1" statement id value

Value -1 (0xFFFFFFFF) can be used to indicate to use the last statement prepared on current connection if no COM_STMT_PREPARE has failed 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 returns an error that statement ID -1 is unknown. This permits to avoid much of the network latency.

Flag

Cursors

Parameter Flag

Parameter type flag byte:

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 .

Response

The server can answer with 3 different responses:

0xff: if any errors occur.
0x00: when query execution works without result set.
One (or more) , when query execution return rows (in case of SELECT query, for example).

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