JET Wire Protocol — Specification

1. Design Philosophy

The JET wire protocol adopts PostgreSQL's v3 message-oriented structure as its foundation: every message is a single type byte followed by a 4-byte big-endian length (including the length word itself), then the payload. This framing is simple, self-describing, and used identically by both sides — client to server and server to client.

Where PostgreSQL's protocol defines features the Jet/ACE engine cannot support (parse trees, portals, COPY, LISTEN/NOTIFY, logical replication), those message types are omitted or repurposed. Where MariaDB's protocol adds capabilities that are relevant to Jet (multi-statement batches, connection-level statistics, mid-session user switching, compression), those are incorporated as extension messages.

2. Message Framing

2.1 Message Type Space

PostgreSQL uses ASCII characters as message type identifiers. JET follows the same convention, with the letter J (0x4A) reserved as a prefix byte for JET-specific extension messages that have no PostgreSQL equivalent.

2.2 Multi-Packet Messages (MySQL Borrowing)

PostgreSQL assumes a single message fits in one TCP segment (practical limit ~1 GB with int32 length). MySQL's protocol handles >16 MB payloads by splitting across packets with a sequence number. JET adopts PostgreSQL's single-message model — the int32 length field allows up to 2 GB per message, more than sufficient for Jet/ACE result sets. No multi-packet splitting is needed.

3. Connection Lifecycle

3.1 SSL Negotiation

Identical to PostgreSQL. The client sends an 8-byte SSLRequest message (int32 length=8, int32 magic=80877103). The server responds with S to upgrade or N to refuse. On S, a TLS handshake begins immediately.

3.2 StartupMessage

Identical to PostgreSQL's v3 StartupMessage. Protocol version 3.0 (int32 = 196608). Key-value pairs follow: user (required), database (path to .accdb/.mdb file, required), dbpassword (optional — .accdb/.mdb file password, tried only if the database is password-protected), options (Jet-specific: --jet-exclusive=false, --jet-systemdb=path).

3.3 Authentication

JET supports three authentication methods, using PostgreSQL's AuthenticationRequest message format (int32 type code):

Jet/ACE's native user-level security (workgroup file, user/group permissions) maps to the wire protocol's user identity. The Workspace is created with that user name:

3.4 ParameterStatus

After authentication, the server sends ParameterStatus messages for server metadata. Standard PG keys plus JET-specific keys:

4. Simple Query Protocol

Identical to PostgreSQL's simple query flow: client sends Query, server executes it, sends back result sets and ReadyForQuery. A single Query message may contain multiple SQL statements separated by semicolons — the server processes each in sequence, sending a result descriptor + rows (or CommandComplete) for each, then a single ReadyForQuery at the end.

4.1 Query Execution Flow

4.2 SQL Dialect

The JET endpoint accepts Jet SQL (Access SQL). The worker does not translate between dialects. A PostgreSQL psql client connecting to this endpoint must send Jet SQL, not PostgreSQL SQL. See §8 for schema introspection helpers that bridge the gap.

4.3 Result Set Messages — DAO Mappings

RowDescription — Detailed Format

DataRow — Detailed Format

5. Extended Query Protocol

PostgreSQL's extended query protocol uses a three-phase model: Parse (prepare a statement), Bind (supply parameters), Execute (run it). DAO supports this through its QueryDef object, which is a saved or temporary parameterized query. JET maps the PG extended protocol onto QueryDef operations.

5.1 Statement Names and Portals

Statement name maps to a DAO QueryDef object. An unnamed statement ("") creates a temporary QueryDef scoped to the current parse cycle. Named statements persist in a per-connection statement cache — the DAO equivalent of PostgreSQL's prepared statement hash table.

Portal maps to an open DAO Recordset. The portal name identifies an in-progress cursor. Only a single portal is active at a time (matching DAO's limitation — a QueryDef can only have one open Recordset).

5.2 Parameter Binding

DAO QueryDefs support named parameters using the PARAMETERS declaration or positional ? placeholders. JET uses the MariaDB-style positional parameter model (matching ? placeholders) rather than PostgreSQL's $1 syntax, because Jet SQL uses ?:

Unlike PostgreSQL's extended query protocol, which sends parameter types in the Parse message, JET infers parameter types from the SQL (the Jet engine does this internally when the QueryDef is created). The client may still send type OIDs in the Parse message for forward compatibility, but the server ignores them.

5.3 Extended Query Messages — DAO Mappings

6. Type Mapping — DAO to PostgreSQL OIDs

The protocol uses PostgreSQL OIDs in RowDescription messages so that standard PG clients can interpret column types. The worker thread maps DAO DataTypeEnum values to the closest PG type OID.

7. JET Extension Messages (MariaDB-Inspired)

These messages use the J prefix byte followed by a subtype character. They have no PostgreSQL equivalent but add capabilities relevant to the Jet engine or to database administration.

7.1 JET_STATISTICS — Server Statistics

Inspired by: MariaDB's COM_STATISTICS. Returns server status information.

7.2 JET_CHANGE_USER — Mid-Session User Switch

Inspired by: MariaDB's COM_CHANGE_USER. Resets the connection state and re-authenticates as a different user, without closing the TCP connection.

7.3 JET_MULTI_STATEMENT — Multi-Statement Mode

Inspired by: MariaDB's COM_SET_OPTION with MYSQL_OPTION_MULTI_STATEMENTS_ON. Toggles whether a single Query message may contain multiple SQL statements separated by semicolons.

7.4 JET_BEGIN_TRANS, JET_COMMIT, JET_ROLLBACK — Explicit Transaction Control

While PostgreSQL and MariaDB accept BEGIN/COMMIT/ROLLBACK as SQL statements, Jet SQL has its own transaction syntax (BeginTrans/CommitTrans/Rollback in DAO, or BEGIN TRANSACTION in Jet SQL). JET provides these as protocol-level messages for completeness and to avoid dialect confusion.

7.5 JET_SET_DATABASE — Switch Database

Inspired by: PostgreSQL's no mid-session database switch (must reconnect) vs. MySQL's USE database. JET provides this as a protocol message since Jet/ACE can have multiple open databases per workspace.

7.6 JET_PING — Connection Keepalive

Inspired by: MariaDB's COM_PING. Checks if the connection is alive without executing a query.

7.7 JET_COMPRESS — Compression Toggle

Inspired by: MariaDB's compressed protocol (mysql_compress packet header). Toggles zlib compression on the connection.

8. Schema Introspection — INFORMATION_SCHEMA Bridge

PostgreSQL clients expect to discover schema by querying information_schema or pg_catalog. Jet/ACE has no equivalent system catalog. However, DAO provides collections that expose the same data. The worker thread intercepts specific SQL patterns and returns results from DAO collections rather than executing the SQL against Jet.

8.1 Intercepted Queries

8.2 QueryDefs as "Views"

DAO's QueryDefs collection contains saved SELECT queries, action queries, and parameter queries. These are exposed through information_schema.views and can be queried like tables:

9. Error Handling

JET uses PostgreSQL's ErrorResponse and NoticeResponse message formats. The field type codes are identical to PG's.

Jet Error → SQLSTATE Mapping

10. Message Type Registry — Complete List

11. Message Flow Examples

11.1 Simple SELECT

11.2 Extended Query with Parameters

12. Implementation Notes

12.1 Thread Model

• Listener thread: opens listen socket, poll()/select() for new connections, accept(), spawns worker thread per connection. Never touches DAO.
• Worker thread: calls CoInitializeEx(NULL, COINIT_APARTMENTTHREADED), creates Workspace + Database, runs the protocol message loop. One connection = one thread = one COM apartment = one Workspace.
• Cancel thread: listens on a separate socket for CancelRequest messages. Looks up the target backend by PID + secret key, signals the worker thread to abort the current operation.

12.2 Statement Cache

Named statements (from Parse with a non-empty statement name) are stored in a per-connection hash map: name → QueryDef object. The unnamed statement ("") is a single slot reused by each Parse. The cache is cleared on Sync with an error, on JET_CHANGE_USER, and on disconnect.

12.3 Transaction State Tracking

The ReadyForQuery transaction indicator is derived from DAO's transaction state:

• I (idle): no active transaction. workspace.BeginTrans() has not been called, or the last CommitTrans/Rollback closed the outermost level.
• T (in transaction): BeginTrans called, not yet committed or rolled back. PG clients will not auto-commit in this state.
• E (failed): a query within a transaction returned an error. The transaction is aborted; only Rollback is permitted until the error is cleared.

12.4 DAO Error Collection Handling

DAO's DBEngine.Errors collection may contain multiple errors for a single operation. The first error (Errors(0)) is the primary error and maps to the wire protocol's ErrorResponse. Subsequent errors are concatenated into the Detail field. Example:

12.5 Large Result Sets — Fetch-Size Borrowing from MariaDB

PostgreSQL's extended query protocol supports Execute with a max_rows parameter to limit rows per fetch. JET adopts this directly — the worker calls rs.MoveNext() up to max_rows times, then sends PortalSuspended. The client can re-Execute to fetch the next batch.

For the simple query protocol, JET sends all rows at once. For very large result sets, the worker should stream: send RowDescription, then send DataRow messages in batches of 100, flushing the socket buffer between batches. This prevents the worker from buffering the entire result set in memory.

13. Compatibility Summary