In Oracle mode, OceanBase Connector/J supports the COM_STMT_PREPARE_EXECUTE protocol, and can issue all information contained in COM_STMT_PREPARE and COM_STMT_EXECUTE at a time, execute prepared statements, and process the corresponding results. This topic describes the COM_STMT_PREPARE_EXECUTE protocol and COM_STMT_PREPARE_EXECUTE RESPONSE.
COM_STMT_PREPARE_EXECUTE protocol
The packet of the COM_STMT_PREPARE_EXECUTE protocol contains the following content:
COM_STMT_PREPARE_EXECUTE
execute a prepared statement
direction: client -> server
response: COM_STMT_EXECUTE Response
payload:
1 [a1] COM_STMT_PREPARE_EXECUTE
4 statement-id
1 flags
4 Iteration count
n query
4 num-params
if num-params > 0:
n NULL-bitmap, length: (num-params+7)/8
1 new-params-bound-flag
if new-params-bound-flag == 1:
n type of each parameter, length: num-params * 2
n value of each parameter
4 execute_mode
4 num-close-stmt-count
if num-close-stmt-count > 0 :
4n num-close-stmt-count * close-stmt-id
4 check_sum
4 extend_flag
The following table describes the fields of the COM_STMT_PREPARE_EXECUTE protocol.
| Length | Field | Description |
|---|---|---|
| 1 | [0xa1] | The protocol number. |
| 4 | stmt-id | The ID of the prepared statement. 0 is sent for the first time. Each subsequent execution of the statement increments the value by 1, based on the last return value. |
| 1 | flags | The cursor scrollability. |
| 4 | iteration-count | batch parameters of the same type are supported. When the value is greater than 0, the server directly tags has-result-set. The default value is 1 for INSERT, UPDATE, and DELETE operations. When the value is greater than 1, BATCH operations are array_binding. For SELECT operations, the number of rows is returned. If cursorFetch is used, the value is the value of fetchSize. To return no result set, set the value to 0. |
| n | query | The SQL statement. |
| 4 | num-params | The number of parameters. |
| if num-params > 0 : | ||
| n | null-bitmap | The length is (num-params + 7)/8. Specifically, every eight parameters occupy one byte and the bit corresponding to the null value is 1. |
| 1 | new-params-bound-flag | Specifies whether to include the type in subsequent executions. If the value is set to 0, stmt-id must be specified. |
| if new-params-bound-flag == 1 : | ||
| n | type of each parameter | The length is num-params*2. |
| n | value of each parameter | The value of each execution parameter. |
| 4 | execute_mode | For more information, see OCIStmtExecute(). |
| 4 | num-close-stmt-count | The value is fixed to 0. |
| if num-close-stmt-count > 0 : | ||
| 4n | num-close-stmt-count * close-stmt-id | This field is not supported. |
| 4 | check_sum | The checksum. |
| 4 | extend_flag | The reserved extension flag. |
COM_STMT_PREPARE_EXECUTE RESPONSE
The following table describes the fields of COM_STMT_PREPARE_EXECUTE RESPONSE.
| Length | Field | Description |
|---|---|---|
| 1 | status | [0x00] OK |
| 4 | stmt-id | The ID of the prepared statement. |
| 2 | num-columns | The number of columns in the table. The value is 0 for a non-SELECT statement. |
| 2 | num-params | The number of SQL statement parameters. |
| 1 | reserved-1 | [00] filler |
| 2 | warning-count | The number of warnings. |
| 4 | extend_flag | The result set extension flag. |
| 1 | has-result-set | Specifies whether a result set is available.
|
| if num-params > 0 : | ||
| n*m | num-params * Protocol::ColumnDefinition | The parameter definition block. This field must be returned for the first EXECUTE operation on a prepared statement and can be omitted for subsequent requests. |
| EOF_Packet | The parameter definition block. This field must be returned for the first EXECUTE operation on a prepared statement and can be omitted for subsequent requests. | |
| if num- colums > 0 : | ||
| n*m | num-colums * Protocol::ColumnDefinition | The column definition block. This field must be returned for the first EXECUTE operation on a prepared statement and can be omitted for subsequent requests. The EOF_Packet and OK_Packet packets are compressed in the same protocol. |
| EOF_Packet [fe] | The column definition block. This field must be returned for the first EXECUTE operation on a prepared statement and can be omitted for subsequent requests. The EOF_Packet and OK_Packet packets are compressed in the same protocol. | |
| if has-result-set : | ||
| n*m | none or many ProtocolBinary::Resultset | The result set. |
| EOF_Packet [fe] | The EOF_Packet packet is separated from the OK_Packet packet, and is also contained in an empty result set. | |
| if execute-success : | ||
| OK_Packet [00] | To adapt to the return operations of INSERT, UPDATE, and DELETE, the result set must end with an OK_Packet packet if the operation is successful. |
|
| if execute-failed : | ||
| ERR_Packet [ff] | In OCI_EXACT_FETCH mode, the result set and error message are returned. | |
Client interaction process
The client interaction code of the COM_STMT_PREPARE_EXECUTE protocol is as follows:
Connection conn = setConnection("&useServerPrepStmts=true");
pstmt = conn.prepareStatement("insert into test_set_params values(?,?,?)"); // The COM_STMT_PREPARE request is not sent.
pstmt.setInt(1, 2);
pstmt.setString(2, "hijklmn");
pstmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
pstmt.executeUpdate(); // The COM_STMT_PREPARE_EXECUTE request is sent.