Alpha

These pages are built from turborel-js’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Prepared Statements

A statement prepared once runs as often as wanted, with new values for its parameters each time. This chapter tells how a program prepares and executes a statement, reads a query's rows in parts through a cursor, and lets a statement go.

Preparing and executing

prepare(sql) prepares a statement whose dynamic parameters are written ?, and gives a Statement: parameters, the type each ? takes, and columns, a query's columns. execute(statement, values) runs it with a value for each parameter, and gives its outcome as query does. A statement stays valid across transactions, until deallocate lets it go.

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 byId = await conn.prepare('SELECT NAME FROM STAFF WHERE ID = ?');
console.log(byId.parameters.map((p) => p.type.kind), byId.columns.map((c) => c.name));
console.log((await conn.execute(byId, [1n])).rows);
console.log((await conn.execute(byId, [2n])).rows);
await conn.deallocate(byId);
await conn.terminate();
// [ 'exact' ] [ 'NAME' ]
// [ [ 'Ada' ] ]
// [ [ 'Grace' ] ]

Values too few or too many for the parameters fail with SQLSTATE 07001; a statement deallocated, with 26000.

A column's type is the type its values are written by, and one tag stands for several declared types: an INTEGER column's is an exact numeric type of precision 10 and scale 0. The server tells each column's declared type too, as a connection asks by default, and columnType(column) gives it, INTEGER, or the type the tag says where the server did not tell it. A value cut to its parameter's type, as a string longer than its column's, completes with the warning 01004.

A CALL's parameters are its inputs alone. Its columns are one for each ? of an OUT or INOUT parameter, in the procedure's parameter order, so an INOUT ? is both; execute gives their values once the procedure ran, as one row.

import { Trust, connectTo, tcp } from 'turborel';

const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
await conn.query(
  'CREATE PROCEDURE TWICE(IN A INTEGER, OUT C INTEGER) LANGUAGE SQL CONTAINS SQL BEGIN SET C = A * 2; END',
);
const call = await conn.prepare('CALL TWICE(?, ?)');
console.log(call.parameters.length, call.columns.map((c) => c.name));
console.log((await conn.execute(call, [21n])).rows);
await conn.query('ROLLBACK');
await conn.terminate();
// 1 [ 'C' ]
// [ [ 42n ] ]

Cursors

openCursor(statement, values, { rows }) opens a cursor of a prepared query and gives its first rows rows, all if 0, and whether it has more. fetch(statement, { rows }) gives the next batch, and the cursor closes once its last row is read; close(statement) closes it sooner. A cursor already open fails a second open with SQLSTATE 24000, as a fetch or close of one not open does.

import { Trust, connectTo, tcp } from 'turborel';

const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
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 statement has several cursors open at once, each numbered by the program, { cursor: n }, once the server granted cursors; 0 is the one opened unless told. describeCursor(statement, n) gives an open cursor's columns, and describe(statement) the statement's parameters and columns as they are now, once the server granted describe.

XML and UUID values

An XML value comes as an Xml: text is its serialization, and nodes() parses it for its elements, attributes, text, comments and processing instructions. A parameter of an XML type is made of its text, new Xml(text). A value the server cannot serialize, such as an attribute standing alone, fails the fetch with SQLSTATE 2200W.

A UUID comes as a Uuid: octets holds its 16 octets and String(u) gives its text. A parameter is made of its octets, new Uuid(octets), or of its text, Uuid.parse(text).

import { Trust, Uuid, Xml, connectTo, tcp } from 'turborel';

const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
await conn.query('CREATE TABLE ORDERS (ID UUID, BODY XML)');
const insert = await conn.prepare('INSERT INTO ORDERS VALUES (?, ?)');
const id = Uuid.parse('919108f7-52d1-4320-9bac-f847db4148a8');
await conn.execute(insert, [id, new Xml('<order no="18"><item>jam</item></order>')]);
const { rows } = await conn.query('SELECT ID, BODY FROM ORDERS');
const [uuid, body] = rows[0];
console.log(String(uuid), body.text);
const [order] = body.nodes();
console.log(order.name, order.attributes[0].value, order.children[0].children[0].text);
await conn.query('ROLLBACK');
await conn.terminate();
// 919108f7-52d1-4320-9bac-f847db4148a8 <order no="18"><item>jam</item></order>
// order 18 jam