Wire Protocol | Statements | Statement Information Request

Statement information request

Requests information on the statement prepared on the statement handle, including information on its input parameters and output columns or parameters, or information on the server-side statement handle itself.

Uses the [wireprotocol-requests-info] message with:

`p_operation`	`op_info_sql` — `70`
`p_info_object`	Statement handle
`p_info_items`	Values of constants in Firebird’s `inf_pub.h` starting with `isc_info_sql_`.

Free

Frees resources held by the statement.

Client

Int32 — p_operation

Operation code (op_free_statement — 67)

Int32 — p_sqlfree_statement

Statement handle

Int32 — p_sqlfree_option

`DSQL_close` — `1`	Closes the cursor opened after statement execute.
`DSQL_drop` — `2`	Releases the statement handle.
`DSQL_unprepare` — `4`	Firebird 2.5 or higher Close resources associated with statement handle, and unprepares the current statement text.The statement handle itself is retained. It is not necessary to unprepare before preparing a new statement text on the same handle.

The server treats these as flag values, so they can be combined with OR, but doing so makes little sense, as an unprepare also closes the cursor, and a drop effectively closes the cursor and unprepares the current statement text.

Server

[wireprotocol-responses-generic]

Deviations for protocol version 11

Request flushing can be deferred for ptype_batch_send or higher.For ptype_lazy_send, the response to op_free_statement is deferred;it requires another operation on the connection before the response is sent.

For DSQL_drop and DSQL_unprepare, we recommend flushing immediately so the server at least processes the request, which will prevent longer than necessary retention of metadata locks.

Prepare

Client

Int32 — p_operation

Operation code (op_prepare_statement — 68)

Int32 — p_sqlst_transaction

Transaction handle

Int32 — p_sqlst_statement

Statement handle

Int32 — p_sqlst_SQL_dialect

SQL dialect (1 or 3)

This should generally match the connection dialect.

String — p_sqlst_SQL_str

Statement to be prepared

Buffer — p_sqlst_items

Statement information items, including describe and describe bind

Example of requested information items

isc_info_sql_select
isc_info_sql_describe_vars
isc_info_sql_sqlda_seq
isc_info_sql_type
isc_info_sql_sub_type
isc_info_sql_length
isc_info_sql_scale
isc_info_sql_field
isc_info_sql_relation

Int32 — p_sqlst_buffer_length

Target buffer length for information response

See also the description of p_info_buffer_length in [wireprotocol-requests-info].

Server

[wireprotocol-responses-generic] — on success, p_resp_data holds the statement description (matching the requested information items)

For statements with a lot of columns and/or parameters, it may be necessary to handle truncation of the buffer by repeating the describe and/or describe bind information request using [wireprotocol-statements-information] and using isc_info_sql_sqlda_start to inform the server from which column or parameter to continue.

For an example, see Jaybird’s StatementInfoProcessor.handleTruncatedInfo(…).

Deviations for protocol version 11

The statement handle can no longer be allocated separately (or at least, its response is deferred).The initial [wireprotocol-statements-allocate] operation must be sent together with the first prepare operation.When allocating and preparing together, the value of the statement handle of the prepare message must be 0xFFFF (invalid object handle).The responses must be processed in order: first allocate response, then prepare response.

Once a statement handle has been allocated, it can be reused by sending a prepare message with its statement handle.

Describe

Requesting a description of output parameters (columns) of a query is done using the statement information request message

Example of requested information items

isc_info_sql_select
isc_info_sql_describe_vars
isc_info_sql_sqlda_seq
isc_info_sql_type
isc_info_sql_sub_type
isc_info_sql_length
isc_info_sql_scale
isc_info_sql_field
isc_info_sql_relation

The initial request can be done as part of [wireprotocol-statements-prepare].The information can be requested together with [wireprotocol-statements-describe-bind].

Describe bind (input parameters)

Describe of input parameters of a query is done using the statement information request message

Example of requested information items

isc_info_sql_select
isc_info_sql_describe_vars
isc_info_sql_sqlda_seq
isc_info_sql_type
isc_info_sql_sub_type
isc_info_sql_length
isc_info_sql_scale
isc_info_sql_field
isc_info_sql_relation

The initial request can be done as part of [wireprotocol-statements-prepare].The information can be requested together with [wireprotocol-statements-describe].

Execute

Client

Int32 — p_operation

Operation code

`op_execute` — `62`	DDL and DML statements
`op_execute2` — `76`	Executable stored procedures with return values, or singleton `RETURNING` (i.e. statements described as `isc_info_sql_stmt_exec_procedure`)

Int32 — p_sqldata_statement

Statement handle

Int32 — p_sqldata_transaction

Transaction handle

Buffer — p_sqldata_blr

Parameters in BLR format

If there are no parameters, send a zero-length buffer.

Int32 — p_sqldata_message_number

Unused, always use 0

Int32 — p_sqldata_messages

Number of messages — 1 if there are parameters, 0 if there are no parameters

Buffer — no name

Parameter values

If p_sqldata_messages is 0, this buffer must not be sent (not even as a zero-length buffer)

TODO: Might not even be a buffer, verify.

If using op_execute2 — 76 (the statement is a stored procedure and there are output parameters):

Buffer — p_sqldata_out_blr: Output parameters in BLR format
Int32 — p_sqldata_out_message_number: Output message number (0) ??

Additions in protocol 16

UInt32 — p_sqldata_timeout: Statement timeout value in milliseconds (0 — use connection-level statement timeout)

Additions in protocol 18

UInt32 — p_sqldata_cursor_flags

Cursor flags

CURSOR_TYPE_SCROLLABLE — 0x01

request scrollable cursor

Additions in protocol 19

UInt32-- p_sqldata_inline_blob_size: Maximum inline blob size

A value of 0 disables inline blobs.The server may use a lower limit than requested.In the Firebird 5.0.3 and Firebird 6 implementation at the time of writing, the server has a maximum of 65535 bytes.

TODO: Describe op_inline_blob somewhere

Server

For op_execute — 63:

[wireprotocol-responses-generic]

For op_execute2 — 76:

Success response: [wireprotocol-responses-sql] followed by [wireprotocol-responses-generic]

Failure response: only [wireprotocol-responses-generic]

Rows affected by query execution

Obtaining the rows affected by a query is done using the statement information request message

List of requested information items

isc_info_sql_records

Fetch

Client

Int32 — p_operation: Operation code (op_fetch — 65)
Int32 — p_sqldata_statement: Statement handle
Buffer — p_sqldata_blr: Output parameters in BLR format

Only needs to be sent on first fetch;subsequent fetches can send a zero-length buffer.
Int32 — p_sqldata_message_number: Message number (always 0)
Int32 — p_sqldata_messages: Message count/fetch size (e.g. 200)

The server may decide to return fewer rows than requested, even if the end-of-cursor wasn’t reached yet.

Server

Success response: one or more [wireprotocol-responses-fetch]

Failure response: [wireprotocol-responses-generic] — with an error in p_resp_status_vector

It is possible to receive [wireprotocol-responses-generic] with an error in the status vector after one or more fetch responses.

Set cursor name

Client

Int32 — p_operation: Operation code (op_set_cursor — 69)
Int32 — p_sqlcur_statement: Statement handle
String — p_sqlcur_cursor_name: Cursor name (null terminated!)
Int32 — p_sqlcur_type: Cursor type

Reserved for future use, always use 0.

Server

[wireprotocol-responses-generic]

Blobs

Create/Open

Client

Int32 — p_operation

Operation code

`op_create_blob` — `34`	Creates a new blob
`op_create_blob2` — `57`	Creates a new blob with a blob parameter buffer
`op_open_blob` — `35`	Opens an existing blob
`op_open_blob2` — `56`	Opens an existing blob with a blob parameter buffer

Buffer — p_blob_bpb

Blob parameter buffer

Only sent for op_create_blob2 — 57 and op_open_blob2 — 56.

Int32 — p_blob_transaction

Transaction handle

Int64 — p_blob_id

Blob ID

Server

[wireprotocol-responses-generic] — on success

+

p_resp_object is the blob handle
p_resp_blob_id is the blob id (for op_create_blob --35/ op_create_blob2 — 57)

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, other blob operations can be sent immediately after the open/create.They can use the invalid object handle (0xFFFF) instead of the — not yet received — blob handle.

Get segment

Client

Int32 — p_operation: Operation code (op_get_segment — 36)
Int32 — p_sgmt_blob: Blob handle
Int32 — p_sgmt_length: Segment length

Maximum length is 32767 for Firebird 2.5 and older, 65535 for Firebird 3.0 and higher.
Buffer — p_sgmt_segment: Always a zero-length buffer

Server

[wireprotocol-responses-generic] — on success, p_resp_data is the blob segment

The response buffer in p_resp_data contains zero or more segments.Each segment starts with 2-bytes for the length (little-endian), followed by that length of data.

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_get_segment can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).

Put segment

Client

Int32 — p_operation: Operation code (op_put_segment — 37)
Int32 — p_sgmt_blob: Blob handle
Int32 — p_sgmt_length: Length of segment data (effectively ignored; possibly only in recent Firebird versions)
Buffer — p_sgmt_segment: Blob segment

If the blob was created as a segmented blob, the maximum length is 32765 (Firebird 2.5 and older) or 65533 (Firebird 3.0 and higher).

For stream blobs, there is no length limitation other than the maximum buffer length (TODO: verify, might only be for recent versions).

Server

[wireprotocol-responses-generic]

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_put_segment can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).

Batch segments

Similar to [wireprotocol-blobs-putsegment], but allows to send multiple segments.

Client

Int32 — p_operation: Operation code (op_batch_segments — 44)
Int32 — p_sgmt_blob: Blob handle
Int32 — p_sgmt_length: Length of segment data (effectively ignored; possibly only in recent Firebird versions)
Buffer — p_sgmt_segment: Blob segments

The buffer can contain one or more segments, which are prefixed with 2 bytes of length (little-endian), followed by the data.The maximum length per segment is 32765 (Firebird 2.5 and older) or 65533 (Firebird 3.0 and higher).

Server

[wireprotocol-responses-generic]

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_batch_segment can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).

Seek

Seek is only supported for blobs that were created as a stream blob.Seek is not fully supported for blobs longer than 2 GiB (4 GiB?).

Client

Int32 — p_operation

Operation code (op_seek_blob — 61)

Int32 — p_seek_blob

Blob handle

Int32 — p_seek_mode

Seek mode

`blb_seek_from_head` — `0`	absolute seek from start of blob
`blb_seek_relative` — `1`	relative seek from current position
`blb_seek_from_tail` — `2`	absolute seek from end of blob

Int32 — p_seek_offset

Offset

Server

[wireprotocol-responses-generic] — on success, p_resp_object is the current position.

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_seek_blob can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).

Cancel

Cancels and invalidates the blob handle.If this was a newly created blob, the blob is disposed.

Client

Int32 — p_operation: Operation code (op_cancel_blob — 38)
Int32 — p_rlse_object: Blob handle

Server

[wireprotocol-responses-generic]

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_cancel_blob can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).Though doing this probably makes little sense for op_cancel_blob.

Close

Closes and invalidates the blob handle.

Client

Int32 — p_operation: Operation code (op_close_blob — 39)
Int32 — p_rlse_object: Blob handle

Server

[wireprotocol-responses-generic]

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, op_close_blob can be batched with [wireprotocol-blobs-create] (and other blob operations) by using the invalid object handle (0xFFFF).

Arrays

Get slice

Client

Int32 — p_operation: Operation code (op_get_slice — 58)
Int32 — p_slc_transaction: Transaction handle
Int64 — p_slc_id: Array handle
Int32 — p_slc_length: Slice length
Buffer — p_slc_sdl: Slice descriptor (SDL)
Buffer — p_slc_parameters: Slice parameters (always empty?, needs verification)
Buffer — p_slc_slice: Slice data (always empty)

Server

Success response: [wireprotocol-responses-slice]

Failure response: [wireprotocol-responses-generic]

Put slice

Client

Int32 — p_operation: Operation code (op_put_slice — 59)
Int32 — p_slc_transaction: transaction handle
Int64 — p_slc_id: Array handle
Int32 — p_slc_length: Slice length
Buffer — p_slc_sdl: Slice descriptor (SDL)
Buffer — p_slc_parameters: Slice parameters (always empty?, needs verification)
Buffer` — p_slc_slice: Slice data

Server

[wireprotocol-responses-generic] — on success, p_resp_blob_id is the array handle.

Batches

Statement batches were introduced in protocol 16 (Firebird 4.0).

Create

Client

Int32 — p_operation: Operation code (op_batch_create — 99)
Int32 — p_batch_statement: Statement handle
Buffer — p_batch_blr: BLR format of batch messages
UInt32 — p_batch_msglen: Message length
Buffer — p_batch_pb: Batch parameters buffer

If ptype_lazy or higher, flushing and response processing can be deferred.

Server

[wireprotocol-responses-generic]

Send messages

Client

Int32 — p_operation: Operation code (op_batch_msg — 100)
Int32 — p_batch_statement: Statement handle
UInt32 — p_batch_messages: Number of messages
Buffer — p_batch_data: Batched values (formatted message repeats 'Number of messages' times)

Server

[wireprotocol-responses-generic]

Execute batch

Client

Int32 — p_operation: Operation code (op_batch_exec — 101)
Int32 — p_batch_statement: Statement handle
Int32 — p_batch_transaction: Transaction handle

Server

Success response:

Int32 — p_operation: Operation code

If operation equals op_batch_cs — 103`:

Batch completion state

Int32 — p_batch_statement: Statement handle
UInt32 — p_batch_reccount: Total records count
UInt32 — p_batch_updates: Number of update counters (records updated per each message)
UInt32 — p_batch_vectors: Number of per-message error blocks (message number in batch and status vector of an error processing it)
UInt32 — p_batch_errors: Number of simplified per-message error blocks (message number in batch without status vector)
Byte[]: Update counters (records updated per each message), array of Int32, length is equal to p_batch_updates

Length is p_batch_updates * 4 bytes long.
Byte[]: Detailed info about errors in batch (for each error server sends number of message (Int32) and status vector in standard way (exactly like in op_response).Number of such pairs is equal to p_batch_vectors.

Length can only be determined by correctly parsing the <Int32><statusvector> pairs.
Byte[]: Simplified error blocks (for each error server sends number of message (Int32) w/o status vector).Used when too many errors took place.Number of elements is equal to p_batch_errors.

Length is p_batch_errors * 4 bytes.

Failure response: [wireprotocol-responses-generic]

Release batch

Client

Int32 — p_operation: Operation code (op_batch_rls — 102)
Int32 — p_batch_statement: Statement handle

Server

[wireprotocol-responses-generic]

Cancel batch

Client

Int32 — p_operation: Operation code (op_batch_cancel — 109)
Int32 — p_batch_statement: Statement handle

Server

[wireprotocol-responses-generic]

Sync batch

Introduced in protocol 17 (Firebird 4.0.1).

Used to force the server to acknowledge previously sent lazy intermediate operations (e.g. op_batch_msg, op_batch_regblob, op_batch_blob_stream and possibly others).

Client

Int32 — p_operation: Operation code (op_batch_sync — 110)

Server

[wireprotocol-responses-generic]

Set default blob parameters

Client

Int32 — p_operation: Operation code (op_batch_set_bpb — 106)
Int32 — p_batch_statement: Statement handle
Buffer — p_batch_blob_bpb: Default BLOB parameter buffer

Server

[wireprotocol-responses-generic]

Register existing blob

Client

Int32 — p_operation: Operation code (op_batch_regblob — 104)
Int32 — p_batch_statement: Statement handle
Int64 — p_batch_exist_id: Existing BLOB ID
Int64 — p_batch_blob_id: Batch temporary BLOB ID

Server

[wireprotocol-responses-generic]

Stream of BLOB data

Caution

This description needs further verification and possibly correction.For example, it seems to mix up Buffer and Byte[].We’re also not able to match some fields to the implementation.For example, the repeated "Record length" seems to be absent, or may actually refer to the p_batch_blob_data buffer length.

Client

Int32 — p_operation

Operation code (op_batch_blob_stream)

Int32 — p_batch_statement

Statement handle

Buffer[] — p_batch_blob_data

BLOB stream

This stream is a sequence of blob records.Each blob records contains:

UInt32: Record length

The following three fields are called BLOB header
Int64: Batch temporary BLOB ID
UInt32: BLOB size
UInt32: BLOB parameters buffer size
Buffer: BLOB parameters buffer
Buffer: BLOB data (length - BLOB size bytes) (what does this mean?)

BLOB headers and records in a stream need not match, i.e. one record may contain many BLOBs and BLOB may stretch from one record to next.

Server

[wireprotocol-responses-generic]

Batch information request

Uses the [wireprotocol-requests-info] message with:

`p_operation`	`op_info_batch` — `111`
`p_info_object`	Statement handle
`p_info_items`	Values of `INF_` constants of `IBatch` (in `IdlFbInterfaces.h`)

Services

Attach

Attach to a service.Use message [wireprotocol-connect-attach] with op_service_attach — 82.

Note on `p_atch_file`:

Current Firebird versions only support one service: service_mgr.Since Firebird 3.0, this can also be an empty string (empty buffer) with the same meaning.

Detach

Send [wireprotocol-connect-detach] with op_service_detach — 83, followed by [wireprotocol-connect-disconnect].

Start

Although the message looks similar to [wireprotocol-requests-info], it has different semantics.

Client

Int32 — p_operation: Operation code (op_service_start — 85)
Int32 — p_info_object: Unused, always use 0
Int32 — p_info_incarnation: Incarnation of object (0)

TODO: Usage and meaning?
Buffer — p_info_items: Service parameter buffer

Server

[wireprotocol-responses-generic]

Query service

Although the message looks similar to [wireprotocol-requests-info], it has different semantics.

Client

Int32 — p_operation: Operation code (op_service_info — 84)
Int32 — p_info_object: Unused, always use 0
Int32 — p_info_incarnation: Incarnation of object (0)

TODO: Usage and meaning?
Buffer — p_info_items: Service parameter buffer
Buffer — p_info_recv_items: Requested information items
Int32 — p_info_buffer_length: Requested information items buffer length

Server

[wireprotocol-responses-generic] — on success, p_resp_data contains the requested information.