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}

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

• 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

Parameter Type Flag

Parameter Indicator

Response

The server can answer with 3 different responses:

• 0xff: ERR_Packet if any errors occur.

• 0x00: OK_packet 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}

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 (for instance, 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

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 binary result sets.

Response

The server can answer with 3 different responses:

• 0xff: ERR_Packet if any errors occur.

• 0x00: OK_packet when query execution works without result set.

• One (or more) result set, 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.

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}

Prepares a statement on the server.

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

Response

If anything goes wrong, the server will send an ERR_Packet. If the command succeeds, different packets are 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}

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}

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

• 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}

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

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}