In a JDBC ResultSet, what should happen when getLong() or getShort() is called on an int result column?

Question

Say that I have a JDBC ResultSet, and I call the getLong() or getshort() method.

For which of the following SQL types {SMALLINT, INT, BIGINT} should I get long, and for which types should I get an error?

In other words, if I have an INT and I want a SMALLINT (A short), would I get it, or would I get an error? Similarly, if I have an INT and want a BIGINT (a long), would I get it, or would I get an error?

The Javadocs (listed below) say nothing.

public long getLong(int columnIndex) throws SQLException

Retrieves the value of the designated column in the current row

of this ResultSet object as a long in the Java programming language.

Parameters:
    columnIndex - the first column is 1, the second is 2, ... 
Returns:
    the column value; if the value is SQL NULL, the value returned is 0 
Throws:
    SQLException - if a database access error occurs

Answer 1

From the Retrieving Values from Result Sets section of the Java tutorials:

JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR. The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE , TIME, or TIMESTAMP.

I'm interpreting that to mean that the data will be coerced. It should work just fine if it's an upcast, but I'd expect potential loss of precision (naturally) if, for example, you're reading a LONG value using getInt(). I'd expect an Exception to be thrown if you try to read text using getInt().

Answer 2

It'll cast it to a long and it should be fine.

You'll get an error if you're trying to get a long from a string containing "Bob", or some other field that can't be easily converted to a long.

Answer 3

The spec doesn't say anything about this behavior. This will totally depend on the implementation of the drivers.

With the MySQL Connector, you can almost get anything looking like a number as long as it's in valid numeric format and it's in the range of long. Null/False are also returned as 0.

Answer 4

This is implementation dependent. The spec says that the ResultSet implemenation may support such a conversion, and you can check by calling DataBaseMetaData.supportsConvert(int fromType, int toType) (Section 15.2.3.1 of the 4.0 implementer spec).

Best is to not rely on the behavior but rather check the ResultSetMetaData for the correct type.