FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

Exit or disconnect command (aux connection)

Note

As far as we’re aware, this is only sent on the aux connection.It is similar to the disconnect request from client to server for the main connection.

Instructs the client to close the aux connection.

Int32 — p_operation

Operation code (op_exit — 2 or op_disconnect — 6)

After receiving this message, the client should close the aux connection.It’s generally only sent just before the main connection is closed.

Common requests

A few requests in the protocol have a common message format, where the operation code differs, and — possibly — the set of allowed values of other fields.

We describe the request format here, and describe the allowed values in the section for a specific request.

Generic information request

Client
Int32 — p_operation

Operation code (value depends on the actual operation)

Int32 — p_info_object

Object handle (e.g. statement, transaction, etc.)

Int32 — p_info_incarnation

Incarnation of object (0)

TODO: Usage and meaning?

Buffer — p_info_items

Requested information items

A list of requested information items (each byte is an information item), so-called SingleTpb items.Some operations may have items that do have values (e.g. isc_info_sql_sqlda_start of [wireprotocol-statements-information]).Most values are specific to the operation.

The list should end with isc_info_end — 1[1].

Int32 — p_info_buffer_length

Length of buffer available for receiving response

In protocol 10, this is a signed Int16, encoded as Int32.
In protocol 11 and higher, this is an unsigned Int32.

For compatibility reasons, values greater than or equal to 4,294,901,760 (i.e. 0xFFFF_0000 or greater) are masked with 0xFFFF, so only the low 16 bits are used.

A too small value may lead to receiving a truncated buffer (last item is isc_info_truncated — 2 instead of isc_info_end — 1), which necessitates requesting information again with a larger size.Some operations may have additional mechanisms to handle truncation, like

The buffer in the response is sized to the actual length of the response (upto the declared available length), so specifying a larger than necessary size does not inflate the response on the wire.However, specifying an unnecessarily large size can lead to inefficiencies for the server.

Server

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

A truncated response is considered a success, and can only be determined by parsing p_resp_data.

Important

Information about how to parse the information buffer sent by the Firebird server can be found in the InterBase 6.0 documentation set

Establishing connection

This chapter describes how to connect to a database or service.Other operations on a database or service, or information specific to connecting to a database or service are documented in [wireprotocol-databases] and [wireprotocol-services].

Attaching to a database or service

In protocol 10 and 11, attachment to a database or service is done in two steps, first identification (connect) to the server, then attach to — or creation of — a database, or attach to a service.In protocol 13, this was changed to a more complex state machine to handle multiple authentication plugins, wire protocol encryption, and database encryption key callback.

In deviation of the normal description in this documentation, and previous versions of this documentation, we will first cover the individual messages, and then explain the order and logic of message exchange.

Identification (connect)

Requests connection to the server and specifies which protocol versions the client can use.

Int32 — p_operation

Operation code (op_connect — 1)

Int32 — p_cnct_operation

Unused, always use 0

Some implementations use op_attach — 19/op_service_attach — 82 for historic(?) reasons.

Int32 — p_cnct_cversion

Connect version:

CONNECT_VERSION2 — 2

user identification encoding is undefined (Firebird 1.0 — Firebird 2.5)
CONNECT_VERSION3 — 3

user identification is UTF-8 encoded (since Firebird 3.0 and higher, but backwards compatible as the version wasn’t checked before Firebird 3.0)
Int32 — p_cnct_client

Architecture type (e.g. arch_generic — 1).

String — p_cnct_file

Database path or alias

The encoding of this is undefined, which can lead to problems with non-ASCII paths if the server and client use a different encoding.

For a service connection, this value can be the service name (service_mgr or empty), or the “expected database” name (same value as isc_spb_expected_db).

Int32 — p_cnct_count

Count of protocol versions understood (e.g. 1).

Buffer — p_cnct_user_id

User identification

TODO: Needs further description

Important

The next block of data declares the protocol(s) that the client supports.It should be sent as many times as protocols are supported (and specified in p_cnct_count above).Values depend on the protocol.

If a client sends more than 10 (Firebird 5.0 and older) or 11 (Firebird 6.0) protocols, the surplus are ignored.
Int32 — p_cnct_version

Protocol version (e.g. PROTOCOL_VERSION10 — 10).

Protocol versions greater than `10` need to be OR'ed with `0x8000` for differentiation from newer InterBase protocol versions.
For example, `PROTOCOL_VERSION11` is `0x8000 | 11` or `32779` (`0x800B`)
Int32 — p_cnct_architecture

Architecture type (e.g. arch_generic — 1)

It is possible to use a different architecture value, but then connection is only possible with a server of the same architecture.In addition, it changes how responses and/or data needs to be parsed or encoded (the authors don’t know the exact details).In short, use arch_generic.

Int32 — p_cnct_min_type

Minimum type (e.g. ptype_batch_send — 3)

Connection type (ptype) values
ptype_page — 1

Page server protocol (never supported in Firebird)
ptype_rpc — 2

Simple remote procedure call (not supported since Firebird 3.0)
ptype_batch_send — 3

Batch sends, no asynchrony
ptype_out_of_band — 4

Batch sends w/ out of band notification (semantics not documented in this manual)
ptype_lazy_send — 5

Deferred packets delivery
Int32 — p_cnct_max_type

Maximum type (e.g. ptype_lazy_send — 5)

If the client wants to set up wire compression, this ptype-code must be OR’ed with pflag_compress (0x100).See also Known p_acpt_type flags below.

Int32 — p_cnct_weight

Preference weight (e.g. 2).Higher values have higher preference.For equal weights, the last supported occurrence will be selected.

Simple accept

Specifies the protocol selected by the server.This response is — as far as we know — not sent if the server accepts protocol 13 or higher;then the extended Accept with data or conditional accept is sent instead.

Int32 — p_operation

Operation code

If operation equals op_accept — 3:

Int32 — p_acpt_version

Protocol version accepted by server

Int32 — p_acpt_architecture

Architecture for protocol

Int32 — p_acpt_type

Accepted type and additional flags.Obtain the type by masking with 0xFF (p_acpt_type & 0xFF).

Known p_acpt_type flags
pflag_compress — 0x100

Turn on compression

From client to server, it signals a request to use wire compression.
From server to client, it is an acknowledgement, and wire compression must be enabled after reading this entire response, but before reading or writing any other messages.
pflag_win_sspi_nego — 0x200

Win_SSPI supports Negotiate security package

Only sent from server to client.

Failure response: [wireprotocol-responses-generic]

Accept with data or conditional accept

Introduced in protocol 13.

The op_accept_data — 94 and op_cond_accept — 98 responses start with the same fields as Simple accept, followed by additional fields for authentication and encryption purposes.

Int32 — p_operation

Operation code

If operation equals op_accept_data — 94 or op_cond_accept — 98:

Int32 — p_acpt_version

Protocol version number accepted by server

Int32 — p_acpt_architecture

Architecture for protocol

Int32 — p_acpt_type

Accepted type and additional flags.

See also p_acpt_type in op_accept message

Buffer — p_acpt_data

Authentication plugin data

String — p_acpt_plugin

Authentication plugin to continue with

Int32 — p_acpt_authenticated

Authentication complete in a single step (0 — false, 1 — true)

This will generally only be 1 if Legacy_Auth was tried first, though third-party authentication plugins might also authenticate in a single step.

Buffer — p_acpt_keys

“Keys” known by the server (used for configuring authentication and wire encryption)

Continue authentication

Introduced in protocol 13(?).

This message is used both by client and server to exchange authentication information.

Int32 — p_operation

Operation code (op_cont_auth — 92)

Buffer — p_data

Authentication data

String — p_name

Name of the current authentication plugin

String — p_list

On first authentication from client to server: list of (remaining) plugins known to the client, including the current plugin;
on subsequent authentication from client to server, or from server: empty

The list of plugin names can be separated by space, tab, comma or semicolon.

Buffer — p_keys

From client to server: empty;
from server to client: “keys” known by the server (used for configuring authentication and wire encryption)

Trusted authentication

Introduced in protocol 11.

Int32 — p_operation

Operation code (op_trusted_auth — 90)

Buffer — p_trau_data

Trusted authentication data

Database encryption key callback

Introduced in protocol 13.

Used to exchange information between the client and server parts of a database encryption plugin for the encryption key.The server sends this message, and the client responds with the same message type.Specifics of the message exchange depends on database encryption plugin.It is possible that multiple message of this type are exchanged.

In protocol 13, this message can only occur after authentication and — optionally — establishing wire protocol encryption.In protocol 15 and higher, it can also occur immediately after op_connect, if the database is its own security database and is encrypted.

If this message is received before op_accept/op_accept_data/op_cond_accept (so no protocol version has been confirmed yet), you need to assume protocol 15 behaviour for this message and the client response.

Int32 — p_operation

Operation code (op_crypt_key_callback — 97)

Buffer — p_cc_data

Crypt callback data

Additions in protocol 14
Int32 — p_cc_reply

Maximum expected reply size (16-bit signed integer encoded as 32-bit int)

Judging by the code in Firebird for protocol 14 and higher, this value may be negative, and should then be considered equivalent to 1.

From client to server, the reply size should be 0.

Enable wire encryption

Introduced in protocol 13.

Enables wire encryption by telling the server the selected plugin and key type.

Client
Int32 — p_operation

Operation code (op_crypt — 96)

String — p_pluginSelected wire encryption plugin

String — p_keySelected key type

After sending this message, the client must set up wire encryption both for sending and receiving data.Subsequent messages — including the server response to this message — must be sent or received with encryption enabled.

Server

[wireprotocol-responses-generic]

1. This is not required, at least not in recent Firebird versions. The server handles the end of the buffer without seeing isc_info_end as an implicit isc_info_end. However, we’re not sure if that was always the case, so for potential compatibility reasons, consider it “required”

Attachment to database or service

This message is used for:

Client
Int32 — p_operation

Operation code (op_attach — 19, op_create — 20, or op_service_attach — 82)

Int32 — p_atch_database

Unused, always use 0

String — p_atch_file

Database path or alias, or service name (e.g. service_mgr).

If isc_dpb_utf8_filename is present in the database parameter buffer below, the encoding is UTF-8, otherwise, the encoding is undefined.The isc_dpb_utf8_filename item is supported since Firebird 2.5.

Buffer — p_atch_dpb

Database or service parameter buffer

Server

In protocol 10 and 11:

[wireprotocol-responses-generic]

In protocol 13 and higher:

It’s complicated.

Connection rejection

Server response rejecting the connection.This is usually sent if op_connect only sent protocols the server can’t support.

Int32 — p_operationOperation code (op_reject — 4)

If this message is received, the client should report error isc_connect_reject (335544421) or equivalent.

Connection handshake

Protocol 10 — 12

For protocol 10 — 12, the connection handshake is pretty simple.

  1. Client → [wireprotocol-op-connect]

  2. Server

    • ← op_accept — 3 — Server accepts and reports selected protocol, continue with step 3

    • ← op_reject — 4 — Server can’t fulfill the requested protocol

      • Report error isc_connect_reject (335544421) or equivalent

      • Close connection

    • ← op_response — 9 — Error or other problem

      • If p_resp_status_vector has an error, report it, otherwise report error isc_login (335544472) or equivalent

      • Close connection

  3. Client → Attachment to database or service with op_attach, op_create or op_service_attach

  4. Server ← op_response — 9

    • If p_resp_status_vector has no error or only a warning, connection is successful and can be used for other operations

    • Otherwise, connection is unsuccessful

Protocol 13 and higher

For protocol 13 and higher, the handshake is more complex.

Note

This might not be the best way to document the connection handshake.We’re open to suggestions.

  1. Client → [wireprotocol-op-connect]

    The p_user_identification should include:

    • CNCT_plugin_name with the current authentication plugin

    • CNCT_plugin_list with the authentication plugins supported by the client (including the current plugin);this list is separated by space, comma or semicolon

    • CNCT_specific_data with authentication plugin data (NOTE: this tag has a special “multipart” encoding as the data is generally longer than the 255 bytes supported for a single tag value)

  2. Server

    • ← op_crypt_key_callback — 97 (read as protocol 15)

    • ← op_accept — 3 — Record selected protocol and type, continue with step 5 (attach)

    • ← op_accept_data — 94 or op_cond_accept — 98

      Record the selected protocol and type, and use that for sending and receiving subsequent messages.Enable wire compression if acknowledged by server.

      If p_acpt_authenticated == 1, mark authentication completed

      • If op_accept_data — 94, process the data, plugin and keys, and continue with step 5 (attach)

      • If op_cond_accept — 98, continue with step 3 (pre-attach-auth), item for op_cond_accept

    • ← op_reject — 4 — Server can’t accept any of the protocols or protocol options

      • report error isc_connect_reject (335544421) or equivalent

      • close connection

    • ← op_response — 9 — Error or other problem

      • If p_resp_status_vector has an error, report it, otherwise report error isc_login (335544472) or equivalent

      • close connection (end of this flow)

  3. Server — pre-attach auth

    If the requested authentication plugin name (p_acpt_plugin/p_name) is non-empty and different from the current authentication plugin name, switch to that authentication plugin.

    If the client cannot fulfill the server request for an authentication plugin or has no current authentication plugin, error isc_login (335544472) or equivalent should be reported, and the connection closed (end of this flow)

    If coming from step 2, treat this as if op_cond_accept was just received.

  4. Client — pre-attach auth → [wireprotocol-op-cont-auth] with:

    • p_data — authentication plugin data

    • p_name — current authentication plugin

    • p_list — list of remaining authentication plugins, including current plugin (separated by space, comma, or semicolon)

      This only needs to be sent once;for subsequent messages an empty buffer can be sent.

    Continue with step 3 (Server — pre-attach auth)

  5. Client — attach → Attachment to database or service with op_attach, op_create or op_service_attach

    If authentication was not yet complete at this point (as far as we know, only when op_accept — 3 or op_accept_data — 94 was received in the previous step), and protocol 13 or higher was selected, the database or service parameter buffer should include the following tags:

    If protocol 13 or higher is used, the “wide” parameter buffer variant (isc_dpb_version2/isc_spb_version3 or higher) must be used given the size of the client authentication data (…​_specific_auth_data).

    • isc_dpb_auth_plugin_list/isc_spb_auth_plugin_list — with remaining authentication plugins (separated by space, comma or semicolon)

    • isc_dpb_auth_plugin_name/isc_spb_auth_plugin_name — current authentication plugin

    • isc_dpb_specific_auth_data/isc_spb_specific_auth_data — client authentication data

    It should not include any of these tags (if protocol 13 or higher):

    • isc_dpb_password/isc_spb_password

    • isc_dpb_password_enc/isc_spb_password_enc

    • isc_dpb_trusted_auth/isc_spb_trusted_auth

  6. Server/client — post-attach auth

    This is the same as steps 3 and 4 (pre-attach auth), except op_cond_accept cannot occur, and its “Continue with step 5 (attach)” should be read as “Connection successful (end of flow)” (also noted there).

    If the pre-attach auth flow was previously entered, this will essentially be only an op_response — 9 with either an error or acceptance (connection success).

Detach

Detaches from the database (op_detach — 21) or service (op_service_detach — 83).After detach the connection is still open, to disconnect send Disconnect (op_disconnect).

Client
Int32 — p_operation

Operation code (op_detach — 21, or op_service_detach — 83)

Int32 — p_rlse_object

Unused, always use 0

Server

[wireprotocol-responses-generic]

Disconnect

Client

Int32 — p_operation

Operation code (op_disconnect)

Server

No response, remote socket close.

Closing the connection (socket) without sending an op_disconnect will result in “Connection reset by peer” (error 10054 (Windows) or 104 (Linux)) in firebird.log.

Databases

Attach database

Attach to an existing database.Use message [wireprotocol-connect-attach] with op_attach — 19.

Table 1. Example of parameters sent in the DPB
Parameter Description Value Optional

isc_dpb_version1

Version (must be first item!)

 

 

isc_dpb_dummy_packet_interval

Dummy packet interval

120

*

isc_dpb_sql_dialect

SQL dialect

3

 

isc_dpb_lc_ctype

Character set

UTF8

 

isc_dpb_sql_role_name

User role

RDB$ADMIN

*

isc_dpb_connect_timeout

Connection timeout

10

*

isc_dpb_user_name

User name

SYSDBA

 

isc_dpb_password

User password

masterkey

 

Create database

Create a database and connect to it.Create uses [wireprotocol-connect-attach] with p_operation op_create — 20.

There are a number of DPB items to configure the newly created database, including page size (isc_dpb_page_size) — which cannot be modified after creation.

The CREATE DATABASE statement

Although Firebird has a CREATE DATABASE statement, the documented syntax is not fully supported by Firebird server.Part of the syntax (e.g. database name, user, password, page size) are parsed by fbclient to execute the op_create (or equivalent for embedded).

After the database is successfully created, fbclient then uses execute immediate (op_execute_immediate) without transaction to execute a reduced CREATE DATABASE statement for additional configuration of the database.

Drop

Drops the currently attached database.

Client
Int32 — p_operation

Operation code (op_drop_database)

Int32 — p_rlse_object

Unused, always use 0

Server

[wireprotocol-responses-generic]

Detach and disconnect

Send [wireprotocol-connect-detach] with op_detach — 21, followed by [wireprotocol-connect-disconnect].

Database information request

Requests database or server information.

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

p_operation

op_info_database — 40
p_info_object

Unused, always use 0
p_info_items

Values of enum db_info_types in Firebird’s inf_pub.h.

Cancellation

Protocol 12 and higher.

Cancels a running operation on the server.

Note

Operation fb_cancel_abort — 4 should not be sent to the server, but instead the client should simply close the socket connection.

Client

Int32 — p_operation

Operation code (op_cancel)

Int32 — p_co_kind

Cancellation kind, one of:

fb_cancel_disable — 1

disable cancellation until fb_cancel_enable is sent

fb_cancel_enable — 2

enable cancellation if it was disabled previously

fb_cancel_raise — 3

cancel current operation

fb_cancel_abort — 4

See note above, this kind should not be sent to the server.

As cancellation is generally performed asynchronously to be effective, the client implementation must take special care how the operation is sent.

For example, if you use a lock for socket operations, this operation will need to ignore it (running the risk of interfering/corrupting another send operation), or you need to split your locks in a lock for writing, and a lock for reading, or have some other way of detecting that another thread is not currently sending data.

Server

No formal response, cancellation is signalled as a [wireprotocol-responses-generic] with a failure for the cancelled operation.

Transactions

Start transaction

Starts a transaction with the transaction options specified in the transaction parameter buffer.

Client
Int32 — p_operation

Operation code (op_transaction — 29)

Int32 — p_sttr_database

Unused, always use 0

Buffer — p_sttr_tpb

Transaction parameter buffer

Server

[wireprotocol-responses-generic] — on success, p_resp_object is the new transaction handle.

The SET TRANSACTION statement

Instead of using op_transaction to start a transaction, it is also possible to use the SET TRANSACTION statement.

This statement needs to be executed with execute immediate (op_execute_immediate) without transaction.On success, the p_resp_object holds the transaction handle.

Deviations for protocol version 11

Request flushing and response processing can be deferred.

If ptype_batch_send or higher is used, other transaction operations can be sent immediately after starting the transaction.They can use the invalid object handle (0xFFFF) instead of the — not yet received — transaction handle.This probably only makes sense for [wireprotocol-transactions-info].

Commit transaction

Commits an active or prepared transaction.

Client
Int32 — p_operation

Operation code (op_commit — 30)

Int32 — p_rlse_object

Transaction handle

Server

[wireprotocol-responses-generic]

Rollback transaction

Rolls back an active or prepared transaction.

Client
Int32 — p_operation

Operation code (op_rollback — 31)

Int32 — p_rlse_object

Transaction handle

Server

[wireprotocol-responses-generic]

Commit retaining

Commits an active or prepared transaction, retaining the transaction context.

Client
Int32 — p_operation

Operation code (op_commit_retaining — 50)

Int32 — p_rlse_object

Transaction handle

Server

[wireprotocol-responses-generic]

Rollback retaining

Rolls back an active or prepared transaction, retaining the transaction context.

Client
Int32 — p_operation

Operation code (op_rollback_retaining — 86)

Int32 — p_rlse_object

Transaction handle

Server

[wireprotocol-responses-generic]

Prepare

Performs the first stage of a two-phase commit.After prepare, a transaction is in-limbo until committed or rolled back.

Simple prepare

Client
Int32 — p_operation

Operation code (op_prepare — 32)

Int32 — p_rlse_object

Transaction handle

Server

[wireprotocol-responses-generic]

Prepare with message

Associates a message (byte data) with the prepared transaction.This information is stored in RDB$TRANSACTIONS and can be used for recovery purposes.

Client
Int32 — p_operation

Operation code (op_prepare2 — 51)

Int32 — p_prep_transaction

Transaction handle

Buffer — p_prep_data

Recovery information

Server

[wireprotocol-responses-generic]

Reconnect transaction

Reconnects a prepared (“in-limbo”) transaction for 2-phase commit or rollback.

This operation can be used for recovery operations if a connection was closed or killed after preparing a transaction, but not yet committing or rolling it back.

Client
Int32 — p_operation

Operation code (op_reconnect — 33)

Int32 — p_sttr_database

Unused, always use 0

Buffer — p_sttr_tpb

Transaction id to reconnect, encoded in little-endian.

For Firebird 2.5 and lower, always 4 bytes (Int32 little-endian).

For Firebird 3.0 and higher, transaction ids greater than 0x7FFF_FFFF (231 - 1) must be encoded in 8 bytes (Int64 little-endian), while smaller ids may be encoded in 4 bytes (Int32 little-endian).

This encoding is atypical, as it’s essentially a transaction parameter buffer without version or item tags.

Server

[wireprotocol-responses-generic] — on success, p_resp_object holds the transaction handle.

Deviations for protocol version 11

Request flushing and response processing can be deferred.

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

Transaction information request

Requests information on the transaction bound to the transaction handle.

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

p_operation

op_info_transaction — 42
p_info_object

Transaction handle
p_info_items

Values of constants in Firebird’s inf_pub.h starting with isc_info_tra_ or fbinfo_tra_.

Statements

Allocate

Allocates a statement handle on the server.

Client
Int32 — p_operation

Operation code (op_allocate_statement — 62)

Int32 — p_rlse_object

Unused, always use 0

Server

[wireprotocol-responses-generic] — on success, p_resp_object is the allocated statement handle.

Deviations for protocol version 11

In protocol 11 and higher with ptype_lazy_send, the response to op_allocate_statement is deferred;it requires another operation on the connection before the response is sent.

In general, this means the allocate operation should be sent together with a prepare operation using the invalid object handle (0xFFFF).

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

+

  1. p_resp_object is the blob handle

  2. 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.