In Oracle mode, OceanBase Connector/J supports the COM_STMT_PREPARE_EXECUTE protocol. This protocol allows you to send all the information contained in both the COM_STMT_PREPARE and COM_STMT_EXECUTE protocols in one go. It enables the execution of prepared statements and the handling of the corresponding results. This topic provides detailed information about the COM_STMT_PREPARE_EXECUTE protocol and the COM_STMT_PREPARE_EXECUTE response.
COM_STMT_PREPARE_EXECUTE protocol
The message content of the COM_STMT_PREPARE_EXECUTE protocol is as follows:
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 |
Name |
Description |
|---|---|---|
| 1 | [0xa1] | Protocol number |
| 4 | stmt-id | The ID of the prepared statement. The value is 0 for the first execution. If the stmt is executed repeatedly, the value is filled based on the value returned in the previous execution. |
| 1 | flags | Cursor scrolling |
| 4 | iteration-count | The value of the batch parameter of the same type. If the value is greater than 0, the server directly marks the has-result-set parameter. For INSERT, UPDATE, and DELETE operations, the default value is 1. For BATCH operations, the value is array_bounding when it is greater than 1. For SELECT operations, the value is the number of rows returned. If cursorFetch is used, the value is fetchSize. If no result set is returned, the value is set 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. Each 8 parameters occupy 1 byte. The bit corresponding to a null value is 1. |
| 1 | new-params-bound-flag | Indicates whether the subsequent execution continues to include types. If the value is 0, the stmt-id cannot be empty. |
| if new-params-bound-flag == 1 : | ||
| n | type of each parameter | The length is num-params*2. |
| n | value of each parameter | The values of all execution parameters. |
| 4 | execute_mode | For more information, see OCIStmtExecute(). |
| 4 | num-close-stmt-count | The value is always 0. |
| if num-close-stmt-count > 0 : | ||
| 4n | num-close-stmt-count * close-stmt-id | This parameter is not supported. |
| 4 | check_sum | The sum check. |
| 4 | extend_flag | A flag used for future extensions. |
COM_STMT_PREPARE_EXECUTE response
The following table describes the fields of the COM_STMT_PREPARE_EXECUTE response.
| Length | Name | Description |
| 1 | status | [0x00] OK |
| 4 | stmt-id | The ID of the prepared statement. |
| 2 | num-columns | The number of columns in the table. This field is set to 0 for statements other than SELECT. |
| 2 | num-params | The number of parameters in the SQL statement. |
| 1 | reserved-1 | [00] filler |
| 2 | warning-count | The number of warnings. |
| 4 | extend_flag | The result set extension flag. |
| 1 | has-result-set | Indicates whether a result set exists.
|
| if num-params > 0 : | ||
| n*m | num-params * Protocol::ColumnDefinition | The parameter definition block (Parameter Definition Block). This block must be returned for the first time, and can be omitted in subsequent responses. |
| EOF_Packet | The parameter definition block (Parameter Definition Block). This block must be returned for the first time, and can be omitted in subsequent responses. | |
| if num- colums > 0 : | ||
| n*m | num-colums * Protocol::ColumnDefinition | The column definition block (Column Definition Block). This block must be returned for the first time, and can be omitted in subsequent responses. The EOF packet and OK packet are compressed into the same protocol. |
| EOF_Packet [fe] | The column definition block (Column Definition Block). This block must be returned for the first time, and can be omitted in subsequent responses. The EOF packet and OK packet are compressed into the same protocol. | |
| if has-result-set : | ||
| n*m | none or many ProtocolBinary::Resultset | The result set. |
| EOF_Packet [fe] | The EOF packet and the subsequent OK packet are separated. The EOF packet is also included in an empty result set. | |
| if execute-success : | ||
| OK_Packet [00] | For compatibility with the return operations of INSERT, UPDATE, and DELETE, an OK packet is always returned at the end of a successful operation. |
|
| if execute-failed : | ||
| ERR_Packet [ff] | In OCI_EXACT_FETCH mode, the result set and error message are returned. | |
Client interaction process
The following code shows the client interaction process of the COM_STMT_PREPARE_EXECUTE protocol:
<<<<<<< llm_translation_of_commit_9fcedefd_260203102818
Connection conn = setConnection("&useOraclePrepareExecute=true");
pstmt = conn.prepareStatement("insert into test_set_params values(?,?,?)"); // No COM_STMT_PREPARE request is sent.
=======
Connection conn = setConnection("&useOraclePrepareExecute=true");
pstmt = conn.prepareStatement("insert into test_set_params values(?,?,?)"); // The COM_STMT_PREPARE request is not sent.
>>>>>>> V2.4.16
pstmt.setInt(1, 2);
pstmt.setString(2, "hijklmn");
pstmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
pstmt.executeUpdate(); // Sends a COM_STMT_PREPARE_EXECUTE request.