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 the prepared statement, 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 (bytes) | Field | Description |
|---|---|---|
| 1 | [0xa1] | The protocol number. |
| 4 | stmt-id | The ID of the prepared Statement. 0 is sent for the first time. Each time the statement is executed again, the value increments 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. To be specific, 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 parameter is not supported. |
| 4 | check_sum | The check sum. |
| 4 | extend_flag | Reserved extension flag. |
COM_STMT_PREPARE_EXECUTE RESPONSE
The following table describes the fields of COM_STMT_PREPARE_EXECUTE RESPONSE.
| Length (bytes) | Field | Description |
|---|---|---|
| 1 | status | [0x00] OK |
| 4 | stmt-id | The ID of the prepared Statement. |
| 2 | num-columns | The number of fields 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 alerts. |
| 4 | extend_flag | The result set extension flag. |
| 1 | has-result-set | Specifies whether a result set is available. * For INSERT, UPDATE, and DELETE operations, False is returned. * For SELECT operations, if interate-count is 0, False is returned. * For calls and anonymous blocks, if an OUT parameter is present, True is returned; otherwise, False is returned. |
| if num-params > 0 : | ||
| n*m | num-params * Protocol::ColumnDefinition | The parameter definition block. This parameter must be returned when the prepared statement is executed for the first time and can be omitted for subsequent executions of the statement. |
| EOF_Packet | The parameter definition block. This parameter must be returned when the prepared statement is executed for the first time and can be omitted for subsequent executions of the statement. | |
| if num- colums > 0 : | ||
| n*m | num-colums * Protocol::ColumnDefinition | The column definition block. This parameter must be returned when the statement is executed and can be omitted for subsequent executions of the statement. The EOF package and the OK package are compressed in the same protocol. |
| EOF_Packet [fe] | The column definition block. This parameter must be returned when the statement is executed and can be omitted for subsequent executions of the statement. The EOF package and the OK package 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 package is separated from the OK package, 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 package when 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 codes of the COM_STMT_PREPARE_EXECUTE protocol are 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.