COM_STMT_PREPARE_EXECUTE|V2.2.11|OceanBase Connector/J |docs|OceanBase Documentations

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.