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.

ResultSet and Metadata

NAME

ResultSet, ResultSetMetaData, ParameterMetaData, Blob, Clob, SQLXML and Array: a statement's rows, how each value is read and sent, and the types of its columns and parameters

SYNOPSIS

ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData columns = rs.getMetaData();
ParameterMetaData parameters = prep.getParameterMetaData();

Each is JDBC 4.3's interface.

DESCRIPTION

Moving. A result set is forward-only and read-only: next moves to each row in turn, and isBeforeFirst, isFirst and isAfterLast say where it is. Any other move is refused, as is isLast, which a result set read by a cursor cannot know, and updating a row. A column is read by its number, from 1, or by its label, without regard to case. Closing the result set, its statement, or its connection closes it; a cursor's close is sent as the result set closes.

Types. A column's JDBC type is its declared type's, where the server told it, granted declared-types (RULES.md D14): SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION and FLOAT each its own, an expression's as the server declares it. Where no declared type is told, of a server that does not grant it, the type is its values' tag's: an exact number DECIMAL, of its precision and scale, an approximate one REAL to 24 binary digits and DOUBLE past them. A parameter's type is always its tag's.

SQL type java.sql.Types getObject gives
CHARACTER, CHARACTER VARYING CHAR, VARCHAR String
SMALLINT, INTEGER declared SMALLINT, INTEGER Integer
BIGINT declared BIGINT Long
DECIMAL, NUMERIC declared DECIMAL, NUMERIC BigDecimal
an exact number, or DECFLOAT DECIMAL BigDecimal
REAL REAL Float
DOUBLE PRECISION, FLOAT declared DOUBLE, FLOAT Double
BOOLEAN BOOLEAN Boolean
DATE, TIME, TIMESTAMP DATE, TIME, TIMESTAMP java.sql.Date, Time, Timestamp
TIME and TIMESTAMP WITH TIME ZONE TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE OffsetTime, OffsetDateTime
INTERVAL YEAR TO MONTH, DAY TO SECOND OTHER Period, Duration
BINARY, BINARY VARYING BINARY, VARBINARY byte[]
CLOB, BLOB CLOB, BLOB Clob, Blob
XML SQLXML SQLXML
an array ARRAY Array
UUID OTHER UUID
a reference REF byte[]
a structured type STRUCT String, as the value is written
JSON, a multiset, a row, a plug-in's type OTHER String, as the value is written

Reading. getObject(i, Class) gives the value as String, BigDecimal, BigInteger, Long, Integer, Short, Byte, Double, Float, Boolean, UUID, byte[], LocalDate, LocalTime, LocalDateTime, OffsetDateTime, OffsetTime, ZonedDateTime or Instant, or any class getObject gives. The getters read what they can: getString any value, as it is written; getInt and the other numbers a number, or text that is one, in their range; getBoolean a truth value, a number, true unless zero, or text, TRUE, T, YES, Y or 1 and their opposites; getBytes octets, a large object, a reference, a UUID, or text, as UTF-8; getDate, getTime and getTimestamp a datetime, or text that is one. A timestamp without a time zone is read in the calendar given, else the JVM's zone; one with a time zone is its instant. A null is null, or 0 or false, and wasNull says so.

Large objects. A large object comes whole in its row, or as a locator the server holds until the transaction ends, read a part at a time as the program asks; either is a Clob or a Blob, and free releases it. One held whole can be changed as JDBC's methods change it; one by locator is read alone, its changes refused. A Clob, Blob, stream or reader set as a parameter is sent as the parameter's type takes it.

SQLXML. An SQLXML from createSQLXML is written once, by setString, setCharacterStream, setBinaryStream or setResult(StreamResult.class), then set as a parameter. One read is read by getString, getCharacterStream, getBinaryStream or getSource(StreamSource.class). Other sources and results are refused.

Arrays. createArrayOf makes an array of the elements given, its base type named as given, sent as its parameter's element type takes it. An array read gives its elements by getArray, whole or from an index for a count, each as getObject reads its element type, the declared one where told; and by getResultSet, a row of INDEX and VALUE for each. Its base type is its element type's.

Metadata. ResultSetMetaData gives each column's name, which is also its label, "" of an unnamed one; its type, its type's name, the declared name where told, its precision, scale and display size, and the class getObject gives; whether it takes nulls, as the server says; whether a number is signed, and a string compared with its case. The protocol tells no column's table, so its table, schema and catalog are "". ParameterMetaData gives each parameter's type, of its tag, and its precision, scale and class; each is an IN parameter, whether it takes nulls unknown.

ERRORS

SQLCODE SQLSTATE When
-910 TB910 A result set closed, or on no row; a column of no place, or of no label; a move other than next; an object freed, or an SQLXML written twice or read before it is written
-301 22018 A value that a getter cannot read as asked
-301 22003 A number out of the getter's range
-900 0A000 isLast; updating a row; a REF, ROWID, URL or DATALINK; changing a large object read by locator; an SQLXML of another source or result; the result set of an array the program made
the server's A large object's read

EXAMPLES

The types of a query's columns:

try (ResultSet rs = stmt.executeQuery("SELECT ID, NAME, SALARY, ID * 1.5 AS HALF_MORE FROM STAFF ORDER BY ID")) {
    ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        System.out.println(md.getColumnName(i) + " " + md.getColumnTypeName(i) + " " + md.getColumnClassName(i));
    }
    rs.next();
    System.out.println(rs.getObject(1).getClass().getSimpleName() + " " + rs.getObject(4));
}
ID INTEGER java.lang.Integer
NAME CHARACTER VARYING java.lang.String
SALARY DECIMAL java.math.BigDecimal
HALF_MORE NUMERIC java.math.BigDecimal
Integer 1.5

STANDARD

[DESIGN] D2, D14; JDBC 4.3, java.sql.ResultSet, java.sql.ResultSetMetaData, java.sql.ParameterMetaData; [PROTOCOL Declared types].

SEE ALSO

Statement, Values, Large Objects, Appendix A