Statements and Cursors
NAME
Connection.query, queryInto, queryMany, prepare, execute,
openCursor, fetch, close, describe, deallocate, Statement:
running statements, and what the server tells of each.
SYNOPSIS
type Outcome =
| { kind: 'rows'; columns: readonly Column[]; rows: readonly (readonly Value[])[] }
| { kind: 'done'; completion: Completion };
type Streamed = { kind: 'rows'; count: number } | { kind: 'done'; completion: Completion };
interface Sink {
columns(columns: readonly Column[]): void | Promise<void>;
rows(rows: readonly (readonly Value[])[]): void | Promise<void>;
}
interface Batch { rows: readonly (readonly Value[])[]; more: boolean }
interface CursorOptions { rows?: number; cursor?: number }
interface Ran { outcomes: readonly Outcome[]; failure?: TurborelError }
interface Column {
name?: string; type: DataType; notNull: boolean;
declared?: { name: string; written: string };
}
function columnType(column: Column): string;
function columnTypeName(column: Column): string;
function typeWritten(type: DataType): string;
class Statement {
readonly number: number;
readonly parameters: readonly Parameter[];
readonly columns: readonly Column[];
}
class Connection {
query(sql: string): Promise<Outcome>;
queryInto(sql: string, sink: Sink): Promise<Streamed>;
queryMany(sql: string): Promise<Ran>;
prepare(sql: string): Promise<Statement>;
execute(statement: Statement, values?: readonly Value[]): Promise<Outcome>;
openCursor(statement: Statement, values?: readonly Value[], options?: CursorOptions): Promise<Batch>;
fetch(statement: Statement, options?: CursorOptions): Promise<Batch>;
close(statement: Statement, cursor?: number): Promise<void>;
describe(statement: Statement): Promise<Statement>;
describeCursor(statement: Statement, cursor?: number): Promise<readonly Column[]>;
deallocate(statement: Statement): Promise<void>;
readonly autocommit: boolean; setAutocommit(on: boolean): void;
setQueryAttributes(attributes: readonly (readonly [string, string])[]): void;
readonly inTransaction: boolean; readonly warning: string | undefined;
readonly rowsAffected: bigint | undefined; readonly lastGenerated: readonly string[];
readonly serverTime: { total: bigint; queued: bigint; locks: bigint } | undefined;
readonly commitPosition: bigint | undefined;
readonly conditions: readonly TurborelError[]; takeConditions(): TurborelError[];
}
DESCRIPTION
A connection runs one statement at a time. query runs a statement and
gives a query's columns and all its rows, or another statement's
completion. queryInto gives a query's columns and rows to a sink a
batch at a time, awaiting what it returns, and gives the count of rows.
queryMany runs several statements in one request, once the server
granted multi-statement: each outcome in order, and the failure that
stopped the rest. Under autocommit each statement is its own
transaction.
prepare numbers a statement itself and prepares it: its parameters'
types, its inputs', and a query's columns, or a CALL's, one for each
OUT or INOUT parameter. execute runs it with a value for each
parameter: a query's rows, all of them; a CALL's output parameters'
values, as one row of its columns; or a completion. openCursor
opens a cursor of a query and takes its first rows rows, all if 0;
fetch takes the next; the cursor closes once its last row is read, or
by close. Under autocommit, the server granting fetch-ends, the
cursor's transaction ends as the cursor does. A cursor numbered other
than 0 needs cursors; describe and describeCursor need describe.
deallocate lets a statement go.
A Column, of a statement's columns or of a query's outcome, has its
name, its type and whether it is NOT NULL. A type's tag can stand for
several declared types, so an INTEGER column's type is an exact
numeric type of precision 10 and scale 0. Granted declared-types,
which a connection asks for by default, its declared holds the type
it was declared with: its name as the Information Schema's DATA_TYPE
gives it, and the type as SQL written. columnType gives the declared
type where the server told it, else the type its tag says, as
typeWritten writes it, so INTEGER is NUMERIC(10,0); columnTypeName
gives its name the same way.
Each statement's completion leaves rowsAffected, lastGenerated,
serverTime, commitPosition, warning and conditions, as far as
the server granted row-counts, generated-values, commit-position
and conditions. setQueryAttributes sets attributes each later Query
and Execute carries, once the server granted query-attributes. A
parameter's value cut to its type, as a string longer than its
column's, completes with the warning 01004, which warning gives.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 07001 | -921 | Values not one for each parameter |
| 24000 | -501 | A cursor opened again, fetched or closed when not open; a statement deallocated while its cursor is open |
| 26000 | -921 | A statement deallocated, or never prepared |
| 08006 | -919 | A request of a capability not granted, refused before it is sent; a reply out of turn, which loses the connection |
| 2200W | -301 | An XML value with no serialization, as the fetch that sends it fails |
| TB916 | -916 | A reply holding a value the driver cannot take: the statement fails, the rest of its replies read past first, and the connection goes on (Values) |
The statement's own failures are the server's, as it reports them.
EXAMPLES
import { Trust, connectTo, tcp } from 'turborel';
const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
await conn.query('CREATE TABLE STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(20))');
await conn.query("INSERT INTO STAFF VALUES (1, 'Ada'), (2, 'Grace')");
await conn.query('COMMIT');
const all = await conn.prepare('SELECT ID, NAME FROM STAFF ORDER BY ID');
let batch = await conn.openCursor(all, [], { rows: 1 });
console.log(batch);
while (batch.more) {
batch = await conn.fetch(all, { rows: 1 });
console.log(batch);
}
await conn.query('COMMIT');
await conn.terminate();
// { rows: [ [ 1n, 'Ada' ] ], more: true }
// { rows: [ [ 2n, 'Grace' ] ], more: false }
A column's declared type, beside the type its tag says:
import { Trust, columnType, connectTo, tcp, typeWritten } from 'turborel';
const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
const typed = await conn.prepare('VALUES (CAST(1 AS INTEGER), CAST(2.5 AS DECIMAL(9,2)))');
for (const c of typed.columns) console.log(columnType(c), typeWritten(c.type), c.declared?.name);
await conn.terminate();
// INTEGER NUMERIC(10,0) INTEGER
// DECIMAL(9,2) NUMERIC(9,2) DECIMAL