Passing parameters and receiving results

Query results

The result of executing a query Statement is a set of rows that are accessible via a java.sql.ResultSet object. The ResultSet object provides a set of "get" methods that allow access to the various columns of the current row. The ResultSet.next method can be used to move between the rows of the ResultSet.

// We're going to execute a SQL statement that will return a
// collection of rows, with column 1 as an int, column 2 as
// a String, and column 3 as an array of bytes.
java.sql.Statement stmt = conn.createStatement();

ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table1");

while (r.next()) 
{
 // print the values for the current row.
 int i = r.getInt("a");
 String s = r.getString("b");
 byte b[] = r.getBytes("c");
 System.out.println("ROW = " + i + " " + s + " " + b[0]);
}

There are two alternative ways of specifying columns. You can either use column indexes (for greater efficiency) or column names (for greater convenience). Thus for example there is both a getString method that takes a column index and a getString method that takes a column name.

Reviewer input convinced us that we had to support both column indexes and column names. Some reviewers were extremely emphatic that they require highly efficient database access and therefore preferred column indexes, other reviewers insisted that they wanted the convenience of using column names. (Note that certain SQL queries can return tables without column names or with multiple identical column names. In these cases, programmers should use column numbers.)

For maximum portability, columns within a row should be read in left-to-right order, and each column should only be read once. This reflects implementation limitations in some underlying database protocols.

Data conversions on query results

The ResultSet.getXXX methods will attempt to convert whatever SQL type was returned by the database to whatever Java type is returned by the getXXX method.

If you attempt an illegal conversion, or if a data conversion fails (for example if you did a getInt on a SQL VARCHAR value of "foo"), then a SQLException will be raised.

Null result values

To determine if a given result value is SQL "NULL" first read the column and then use the ResultSet.wasNull method to discover if the read returned a SQL "NULL".

When you read a SQL "NULL" using one of the ResultSet.getXXX methods, you will receive:

• A Java "null" value for those getXXX methods that return Java objects.
• A zero value for getByte, getShort, getInt, getLong, getFloat, and getDouble
• A false value for getBoolean.

Retrieving very large row values.

JDBC allows arbitrarily large LONGVARBINARY or LONGVARCHAR data to be retrieved using getBytes and getString, up to the limits imposed by the Statement.getMaxFieldSize value. However, application programmers may often find it convenient to retrieve very large data in smaller fixed size chunks.

To accommodate this, the ResultSet class can return java.io.Input streams from which data can be read in chunks. However each of these streams must be accessed immediately as they will be automatically closed on the next "get" call on the ResultSet. This behavior reflects underlying implementation constraints on large blob access.

Java streams return untyped bytes and can (for example) be used for both ASCII and Unicode. We define three separate methods for getting streams. GetBinaryStream returns a stream which simply provides the raw bytes from the database without any conversion. GetAsciiStream returns a stream which provides one byte ASCII characters. GetUnicodeStream returns a stream which provides 2 byte Unicode characters.

For example:

java.sql.Statement stmt = conn.createStatement();
ResultSet r = stmt.executeQuery("SELECT x FROM Table2");
// Now retrieve the column 1 results in 4 K chunks:
byte[] buff = new byte[4096];
while (r.next()) {
 java.io.InputStream fin = r.getAsciiStream("x");
 for (;;) {
  int size = fin.read(buff);
  if (size == -1) {
   break;
  }
  // Send the newly filled buffer to some ASCII output stream:
  output.write(buff, 0, size);
 }
}

Optional or multiple ResultSets

Normally we expect that SQL statements will be executed using either executeQuery (which returns a single ResultSet) or executeUpdate (which can be used for any kind of database modification statement and which returns a count of the rows updated).

However under some circumstances an application may not know whether a given statement will return a ResultSet until the statement has executed. In addition, some stored procedures may return several different ResultSets and/or update counts.

To accommodate these needs we provide a mechanism so that an application can execute a statement and then process an arbitrary collection of ResultSets and update counts. This mechanism is based on a fully general "execute" method, supported by three other methods, getResultSet, getUpdateCount, and getMoreResults. These methods allow an application to explore the statement results one at a time and to determine if a given result was a ResultSet or an update count.

    Passing IN parameters

To allow you to pass parameters to a SQL statement, the java.sql.PreparedStatement class provides a series of setXXX methods. These can be used before each statement execution to fill in parameter fields. Once a parameter value has been defined for a given statement, it can be used for multiple executions of that statement, until it is cleared by a call on PreparedStatement.clearParameters.

java.sql.PreparedStatement stmt = conn.prepareStatement("UPDATE table3 SET m = ? WHERE x = ?");

// We pass two parameters. One varies each time around the for loop,
// the other remains constant.
stmt.setString(1, "Hi");

for (int i = 0; i < 10; i++) 
{
 stmt.setInt(2, i);
 int rows = stmt.executeUpdate();
}

Data type conformance on IN parameters

The PreparedStatement.setXXX methods do not perform any general data type conversions. Instead the Java value is simply mapped to the corresponding SQL type (following the mapping specified in Table 3 on page 28) and that value is sent to the database.

It is the programmer's responsibility to make sure that the java type of each argument maps to a SQL type that is compatible with the SQL data type expected by the database. For maximum portability programmers, should use Java types that correspond to the exact SQL types expected by the database.

If programmers require data type conversions for IN parameters, they may use the PreparedStatement.setObject method which converts a Java Object to a specified SQL type before sending the value to the database.

Sending SQL NULLs as IN parameters

The PreparedStatement.setNull method allows you to send a SQL NULL value to the database as an IN parameter. Note, however, that specify the SQL type of the parameter.

In addition, for those setXXX methods that take Java objects as arguments, if a Java null value is passed to a setXXX method, then a SQL NULL will be sent to the database.

Sending very large parameters

JDBC itself defines no limits on the amount of data that may be sent with a setBytes or setString call. However, when dealing with large blobs, it may be convenient for application programmers to pass in very large data in smaller chunks.

To accommodate this, we allow programmers to supply Java IO streams as parameters. When the statement is executed the JDBC driver will make repeated calls on these IO streams to read their contents and transmit these as the actual parameter data.

Separate setXXX methods are provided for streams containing uninterpreted bytes, for streams containing ASCII characters, and for streams containing Unicode characters.

When setting a stream as an input parameter, the application programmer must specify the number of bytes to be read from the stream and sent to the database.

^{We dislike requiring that the data transfer size be specified in advance; however, this is necessary because some databases need to know the total transfer size in advance of any data being sent.}

An example of using a stream to send the contents of a file as an IN parameter:

java.io.File file = new java.io.File("/tmp/foo");
int fileLength = file.length();
java.io.InputStream fin = new java.io.FileInputStream(file);
java.sql.PreparedStatement stmt = conn.prepareStatement("UPDATE Table5 SET stuff = ? WHERE index = 4"); 
stmt.setBinaryStream(1, fin, fileLength);

// When the statement executes, the "fin" object will get called
// repeatedly to deliver up its data.
stmt.executeUpdate();

    Receiving OUT parameters

If you are executing a stored procedure call, then you should use the CallableStatement class. CallableStatement is a subtype of PreparedStatement.

To pass in any IN parameters you can use the setXXX methods defined in PreparedStatement as described in Section 7.2 above.

However, if your stored procedure returns OUT parameters, then for each OUT parameter you must use the CallableStatememt.registerOutParameter method to register the SQL type of the OUT parameter before you execute the statement. (See Appendix A.6.) Then after the statement has executed, use the corresponding CallableStatement.getXXX method to retrieve the parameter value.

java.sql.CallableStatement stmt = conn.prepareCall("{call getTestData(?, ?)}");
stmt.registerOutParameter(1,java.sql.Types.TINYINT);
stmt.registerOutParameter(2,java.sql.types.DECIMAL, 2);
stmt.executeUpdate();
byte x = stmt.getByte(1);
BigDecimal n = stmt.getBigDecimal(2,2);

Data type conformance on OUT parameters

The CallableStatement.getXXX methods do not perform any general data type conversions. Instead the registerOutParameter call must specify the SQL type that will be returned by the database and the programmer must then subsequently call the getXXX method whose Java type corresponds to that SQL type, as specified in Table 2 on page 27.

Retrieving NULL values as OUT parameters

As with ResultSets, in order to determine if a given OUT parameter value is SQL "NULL" you must first read the parameter and then use the CallableStatement.wasNull method to discover if the read returned a SQL "NULL".

When you read a SQL "NULL" value using one of the CallableStatement.getXXX methods, you will receive a value of null, zero, or false, following the same rules specified in section 7.1.2 for the ResultSet.getXXX methods.

Retrieving very large out parameters

We do not provide any mechanism for retrieving OUT parameters as streams.

Instead we recommend that programmers retrieve very large values through ResultSets.

Retrieve out parameters after results

If a stored procedure returns both results and out parameters, for maximum portability, the results should be retrieved prior to retrieving the out parameters.

    Data truncation

Under some circumstances data may be truncated when it is being read from or written to the database. How this is handled will depend on the circumstances, but in general data truncation on a database read will result in a warning, whereas data truncation on a database write will result in a SQLException.

Exceeding the Connection maxFieldSize limit

If an application uses Connection.setMaxFieldSize to impose a limit on the maximum size of a field, then attempts to read or write a field larger than this will result in the data being silently truncated to the maxFieldSize size, without any SQLException or SQLWarning.

Data truncation on reads

In general data truncation errors during data reads will be uncommon with JDBC as the API does not require the programmer to pass in fixed size buffers, but rather allocates appropriate data space as needed. However in some circumstances drivers may encounter internal implementation limits, so there is still a possibility for data truncation during reads.

If data truncation occurs during a read from a ResultSet then a DataTruncation object (a subtype of SQLWarning) will get added to the ResultSet's warning list and the method will return as much data as it was able to read. Similarly, if a data truncation occurs while an OUT parameter is being received from the database, then a DataTruncation object will get added to the CallableStatement's warning list and the method will return as much data as it was able to read.

Data truncation on writes

During writes to the database there is a possibility that the application may attempt to send more data than the driver or the database is prepared to accept. In this case the failing method should raise a DataTruncation exception as a SQLException.