The binary protocol defines a non-text-based (binary) format that is used instead of the text protocol, only for prepared statements
Closes a previously prepared statement.
Client to server.
05 00 00 00 19 04 00 00 00
No response from server.
This page is licensed: CC BY-SA / Gnu FDL
Executes a bulk insert of a previously prepared statement.
A command that returns a resultset will return an error (Error packet).
Client to server.
int<1> 0xfa : COM_STMT_BULK_EXECUTE header
int<4> statement id
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
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
128
unsigned
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
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
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.
Client to server.
int<1> 0x17 : COM_STMT_EXECUTE header
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
Statement id is the identifier of the prepared statement (from COM_STMT_PREPARE answer)
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.
0
no cursor
1
read only
2
cursor for update
4
scrollable cursor
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 type flag byte:
128
unsigned
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
The encoding of the COM_STMT_EXECUTE parameters are the same as the encoding of the binary resultsets.
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
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.
Returns one or more binary result set rows followed by an EOF packet.
This page is licensed: CC BY-SA / Gnu FDL
Prepares a statement on the server.
Not all statements can be prepared. See PREPARE for a list of statements that can be prepared.
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
If anything goes wrong, the server will send an ERR_Packet. If the command succeeds, different packets will be received:
if number of parameters (count of '?' placeholders) > 0
for each parameter:
if !DEPRECATE_EOF eof_packet
if prepared statement returns result set and number of result set columns > 0
for each column:
if !DEPRECATE_EOF eof_packet
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
Resets a prepared statement on the client and server to state after preparing.
05 00 00 00 1A 04 00 00 00
This page is licensed: CC BY-SA / Gnu FDL
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.
Server doesn't send response.
This page is licensed: CC BY-SA / Gnu FDL
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.
In contrast to the text protocol, the binary protocol transfers the data according to the format of the field types returned in PACKET_METADATA.
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
(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