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.

Statement, PreparedStatement and CallableStatement

NAME

Statement, PreparedStatement and CallableStatement: SQL run over a connection of the driver, its parameters, batches and timeouts

SYNOPSIS

Statement stmt = conn.createStatement();
PreparedStatement prep = conn.prepareStatement(sql);
CallableStatement call = conn.prepareCall("{call P(?, ?)}");

Each is JDBC 4.3's interface, of a result set that is forward-only, read-only and closed at commit (TYPE_FORWARD_ONLY, CONCUR_READ_ONLY, CLOSE_CURSORS_AT_COMMIT).

DESCRIPTION

Running. execute, executeQuery and executeUpdate run one statement, its JDBC escapes first translated (below): a query gives a result set, and another statement the rows it changed, as the server counted them, granted row-counts, else 0. executeQuery of a statement that gives no result set is refused, as is executeUpdate of a query, its result set then closed. Running a statement closes its last result set. Each statement gives one result, so getMoreResults is false. Under autocommit each statement is its own transaction.

Fetch size. Of a fetch size of 0, the default, a query's rows are read whole as it runs. Of a fetch size above 0 they are read by a cursor, that many at a time as next needs them: a Statement prepares its SQL to open it, and deallocates it as the cursor ends. setMaxRows gives at most that many rows, all if 0; setMaxFieldSize cuts each character or binary string read to at most that many octets, a character string at a character's end as UTF-8 writes it, none if 0.

Prepared statements. prepareStatement prepares the SQL at once, so a statement the server refuses fails there. Each ? is a parameter, its type the server's, and a value set is made a value of that type, as ResultSet and Metadata tells: so setString of an INTEGER parameter sends the number the text is, and setObject's target type, and setNull's, are not needed and not used. A parameter of no value fails the statement, nothing sent (07001). getMetaData gives the columns of a query, none of another statement, and getParameterMetaData the parameters, without running it. Closing it deallocates it.

Callable statements. prepareCall takes a CALL, or JDBC's {call …}. JDBC counts each ? of it, and the server its IN parameters alone: so each ? given a value is an IN parameter, and each registered by registerOutParameter an OUT parameter, one both an INOUT. The IN values are sent in the order of their ? marks, and once it is executed the OUT parameters are read by their own numbers, by getInt(2) and the others, from the row of OUT values the server sends in the order of theirs. A parameter set or registered by its name is refused.

Batches. addBatch keeps a statement's SQL, or a prepared statement's values, and executeBatch sends them all as one pipeline (Pipelines), giving each one's count of rows, SUCCESS_NO_INFO where the server counted none. Each runs whatever another fails. A batch of which one failed, or gave a result set, fails with a BatchUpdateException: its counts, EXECUTE_FAILED for each that did, its SQLSTATE and vendor code the first failure's, and that failure its cause. Under autocommit each is its own transaction.

Generated keys. Granted generated-values, getGeneratedKeys gives the identity values the last statement's INSERT generated, a row of each, its one column GENERATED their text, whatever columns were asked for by name or number.

Timeouts and cancelling. setQueryTimeout cancels a statement still running after so many seconds, 0 for none; cancel cancels the one running now, from another thread. Each sends the server Cancel over a connection of its own, as Connections says, and a statement cancelled fails (TB920). A connection over a stream the program gave cannot be cancelled. The timer runs on the driver's thread turborel-jdbc-timeouts.

Warnings. A statement's warnings are the conditions its completion carried, granted conditions, each an SQLWarning of its SQLSTATE and SQLCODE: as 01004, of a value cut to fit its column.

Escapes. Before it is sent, JDBC's escape syntax is translated: {d '…'}, {t '…'} and {ts '…'} as DATE, TIME and TIMESTAMP literals, {call …} as CALL, {fn …} and {oj …} as what they hold, and {escape '…'} as ESCAPE; braces in a literal, a delimited identifier or a comment are not escapes. setEscapeProcessing(false) sends a Statement's SQL as it is. A named cursor is refused.

ERRORS

SQLCODE SQLSTATE When
-910 TB910 A statement or its connection closed; executeQuery of no result set, executeUpdate of a query; a parameter of no place; SQL given to a prepared statement's execute; an escape not closed, or of a kind JDBC has not; a negative fetch size, maximum or timeout
-921 07001 A parameter of no value, nothing sent
-301 22018 A value that is no value of its parameter's type
-920 TB920 A statement cancelled, by cancel or its timeout
-900 0A000 A named cursor; a parameter set or registered by its name; a REF, ROWID or DATALINK parameter
the server's The statement's own failure; a batch's first, as a BatchUpdateException

EXAMPLES

A prepared statement run twice, then a batch:

try (PreparedStatement prep = conn.prepareStatement("SELECT NAME FROM STAFF WHERE SALARY > ? ORDER BY ID")) {
    for (String least : List.of("5000", "6000")) {
        prep.setBigDecimal(1, new BigDecimal(least));
        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                System.out.print(rs.getString(1) + " ");
            }
        }
        System.out.println();
    }
}
try (PreparedStatement insert = conn.prepareStatement("INSERT INTO STAFF (NAME, SALARY) VALUES (?, ?)")) {
    for (String name : List.of("Barbara", "John")) {
        insert.setString(1, name);
        insert.setInt(2, 5000);
        insert.addBatch();
    }
    System.out.println(Arrays.toString(insert.executeBatch()));
}
Ada Grace
Grace
[1, 1]

A procedure's OUT parameter:

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE PROCEDURE DOUBLED (IN N INTEGER, OUT M INTEGER) CONTAINS SQL BEGIN SET M = N * 2; END");
}
try (CallableStatement call = conn.prepareCall("{call DOUBLED(?, ?)}")) {
    call.setInt(1, 21);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    System.out.println(call.getInt(2));
}
42

STANDARD

[DESIGN] D2; JDBC 4.3, java.sql.Statement, java.sql.PreparedStatement and java.sql.CallableStatement; [CLIENT Prepared statements], [CLIENT Pipelines].

SEE ALSO

Connection, ResultSet and Metadata, Statements and Cursors