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.