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.

Prepared Statements

A statement run many times, with other values each time, is prepared once: the server reads it, and says what type each of its dynamic parameters, written ?, takes, and what columns a query's rows have. This chapter tells how, with the low-level client and the table of Running Queries.

Prepare and execute

prepare gives a Statement, its parameters' types in the order of their ? marks and its columns. execute runs it with a value for each parameter, as many times as wanted, across transactions; deallocate lets it go.

Statements statements = conn.statements();
Statement raise = statements.prepare("UPDATE STAFF SET SALARY = SALARY + ? WHERE ID = ?");
System.out.println(raise.parameters());
for (long id = 1; id <= 2; id++) {
    statements.execute(raise, List.of(new Value.Exact(BigInteger.valueOf(10000), 2),
            new Value.Exact(BigInteger.valueOf(id), 0)));
}
Statement pay = statements.prepare("SELECT SALARY FROM STAFF WHERE ID = ?");
Outcome.Rows rows = (Outcome.Rows) statements.execute(pay, List.of(new Value.Exact(BigInteger.ONE, 0)));
System.out.println(rows.rows());
statements.deallocate(raise);
statements.deallocate(pay);
statements.query("ROLLBACK");
[Parameter[varying=false, type=ExactType[precision=8, scale=2]], Parameter[varying=false, type=ExactType[precision=10, scale=0]]]
[[Exact[units=530000, scale=2]]]

Values not one for each parameter fail (SQLSTATE 07001); a statement deallocated, or forgotten by a reset, is none (26000).

A cursor, read in parts

openCursor executes a query and takes at most as many of its rows as asked, all if 0; the Batch says whether more follow. fetch takes the next, and the last batch closes the cursor; closeCursor closes it early. A cursor already open, or one not open fetched or closed, fails (SQLSTATE 24000); while a cursor is open its statement is neither replaced nor deallocated.

Statement names = statements.prepare("SELECT NAME FROM STAFF ORDER BY ID");
Batch batch = statements.openCursor(names, List.of(), 1);
System.out.println(batch);
while (batch.more()) {
    batch = statements.fetch(names, 1);
    System.out.println(batch);
}
Batch[rows=[[Text[text=Ada]]], more=true]
Batch[rows=[[Text[text=Grace]]], more=false]

A cursor is read within its transaction, which it keeps while it is open (Transactions).

A CALL's output parameters

A CALL's parameters are its inputs alone. Its columns are one for each ? of an OUT or INOUT parameter, in the procedure's order, named as each, an INOUT one both a parameter and a column. Executed, it gives a row of their values.

statements.query("CREATE PROCEDURE PAYROLL (IN MOST DECIMAL(8,2), OUT HEADS INTEGER) READS SQL DATA "
        + "SELECT COUNT(*) INTO HEADS FROM STAFF WHERE SALARY <= MOST");
Statement call = statements.prepare("CALL PAYROLL(?, ?)");
System.out.println(call.parameters().size() + " parameter, columns "
        + call.columns().stream().map(Column::name).toList());
Outcome.Rows out = (Outcome.Rows) statements.execute(call,
        List.of(new Value.Exact(BigInteger.valueOf(600000), 2)));
System.out.println(out.rows());
1 parameter, columns [HEADS]
[[Exact[units=1, scale=0]]]

Several cursors, and describe

Granted cursors, a statement has several cursors open at once, each by a number of the program's, a Statements.Cursor; 0 is the one a statement has without it. Granted describe, describe gives an open cursor's columns, or a statement's parameters and columns as they are now.

Statement names = statements.prepare("SELECT NAME FROM STAFF ORDER BY ID");
Statements.Cursor first = new Statements.Cursor(names, 1);
Statements.Cursor second = new Statements.Cursor(names, 2);
System.out.println(statements.openCursor(first, List.of(), 1).rows());
System.out.println(statements.openCursor(second, List.of(), 1).rows());
System.out.println(statements.describe(first));
statements.closeCursor(first);
statements.closeCursor(second);
[[Text[text=Ada]]]
[[Text[text=Ada]]]
[Column[name=NAME, type=CharacterType[varying=true, length=20, units=CHARACTERS], notNull=false]]

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