Alpha

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

Running Queries

A program runs a statement over a connection and reads what it gave: a query's columns and rows, or another statement's completion. This chapter tells how, with the low-level client, as Connecting does: a connection's statements() runs its statements, as JDBC's statements do beneath (Statement). The examples' server is the one that chapter connects to, and its table is made so:

CREATE SCHEMA AUTHORIZATION ANN;
CREATE TABLE STAFF (ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR(20), SALARY DECIMAL(8,2));
INSERT INTO STAFF (NAME, SALARY) VALUES ('Ada', 5200.00), ('Grace', 6100.50);
COMMIT;

A query's rows

query runs one statement. Of a query it gives Outcome.Rows, its columns, each of a name, a data type and the type it was declared with, and its rows, each a list of values, one for each column. typeWritten gives a column's declared type as SQL writes it.

Outcome.Rows rows = (Outcome.Rows) conn.statements().query("SELECT ID, NAME, SALARY FROM STAFF ORDER BY ID");
for (Column column : rows.columns()) {
    System.out.println(column.name() + " " + column.typeWritten());
}
for (List<Value> row : rows.rows()) {
    System.out.println(row);
}
ID INTEGER
NAME CHARACTER VARYING(20)
SALARY DECIMAL(8,2)
[Exact[units=1, scale=0], Text[text=Ada], Exact[units=520000, scale=2]]
[Exact[units=2, scale=0], Text[text=Grace], Exact[units=610050, scale=2]]

A value is a Value: Exact, its units and scale, which toBigDecimal gives as one number; Approximate, a double; Text; Decfloat; Datetime and Interval; Truth; a binary string, a plug-in's value, a large object, JSON, XML and UUID values, rows, arrays and multisets; and Value.NULL of the null value (Values). A query that returns no row has no rows; its completion is SQLCODE 100, SQLSTATE 02000.

Another statement's completion

Of any other statement query gives Outcome.Done, its completion: its SQLCODE, 0 or 100, and its SQLSTATE. A statement that fails throws the driver's error, TurborelException, of its SQLCODE, SQLSTATE and message, the rule it cites apart, and where in the statement it failed; the connection goes on (Errors and Diagnostics).

Outcome.Done done = (Outcome.Done) conn.statements().query("UPDATE STAFF SET SALARY = SALARY * 1.1");
System.out.println(done.completed().sqlcode() + " " + done.completed().sqlstate()
        + ", rows " + conn.told().rowsAffected());
try {
    conn.statements().query("SELECT * FROM NOPE");
} catch (TurborelException e) {
    System.out.println(e.sqlcode() + " " + e.getSQLState() + ": " + e.getMessage());
    System.out.println("rule " + e.rule() + ", at character " + e.position().getAsLong());
}
0 00000, rows 2
-103 2A000: table ANN.NOPE is not defined
rule X3.135-1986 §5.4 SR5, at character 14

Rows as they come

query holds a query's rows whole. queryInto gives them to a Sink a batch at a time, as they come, its columns first, so no more than a batch is held; it gives how many rows went to the sink, or another statement's completion.

long[] largest = {0};
Sink sink = new Sink() {
    @Override
    public void columns(List<Column> columns) {
        System.out.println("columns " + columns.size());
    }

    @Override
    public void rows(List<List<Value>> rows) {
        largest[0] = Math.max(largest[0], rows.size());
    }
};
Streamed streamed = conn.statements().queryInto(
        "WITH RECURSIVE C(N) AS (VALUES CAST(1 AS INTEGER) UNION ALL SELECT N + 1 FROM C WHERE N < 100000) SELECT N FROM C", sink);
System.out.println(streamed + ", none held beyond a batch: " + (largest[0] < 100000));
columns 1
Rows[count=100000], none held beyond a batch: true

What a completion tells

A statement's completion tells more, each part as the server granted it (Connecting, what a connection asks for). The connection's told() gives the last statement's: rowsAffected, the rows an INSERT, UPDATE, DELETE or MERGE changed, or the rows a query returned once all were read; lastGenerated, the identity values an INSERT generated, in the order of its rows, as SQL writes them; commitPosition, the log position of the last commit, which a replica's session sets as REPLICATION.READ_AFTER; and serverTime, the server's time for the statement in microseconds, total, queued and waiting for locks.

conn.setAutocommit(true);
conn.statements().query("INSERT INTO STAFF (NAME, SALARY) VALUES ('Edsger', 4800.00), ('Barbara', 5900.00)");
System.out.println("rows " + conn.told().rowsAffected() + ", generated " + conn.told().lastGenerated());
System.out.println("committed at a log position: " + (conn.told().commitPosition() != null));
System.out.println("the server's time told: " + (conn.told().serverTime() != null));
conn.statements().query("DELETE FROM STAFF WHERE ID > 2");
rows 2, generated [3, 4]
committed at a log position: true
the server's time told: true

Several statements may also go as one request, each in order until one fails, by the connection's controls().queryMany (Connections).

The classes are told in full in the API Reference, Statements and Cursors.