Understanding Data Type Conversions
To facilitate the conversion of Java programming language data types to SQL Server data types, the Microsoft SQL Server 2005 JDBC Driver provides data type conversions as required by the JDBC specification. For added flexibility, all types are convertible to and from Object, String, and byte[] data types.
Getter Method Conversions
Based on the SQL Server 2005 data types, the following chart contains the JDBC driver’s conversion map for the get<Type> methods of the SQLServerResultSet class, and the supported conversions for the get<Type> methods of the SQLServerCallableStatement class.
.gif)
There are three basic categories of conversions that are supported by the JDBC driver’s getter methods:
Non-Lossy (x): Conversions for cases where the getter type is the same or smaller than the underlying server type. For example, when calling getBigDecimal on an underlying server decimal column, no conversion is necessary.
Converted (y): Conversions from numeric server types to Java language types where the conversion is regular and follows Java language conversion rules. For these conversions, precision is always truncated—never rounded—and overflow is handled as modulo of the destination type, which is smaller. For example, calling getInt on an underlying decimal column that contains "1.9999" will return "1", or if the underlying decimal value is "3000000000" then the int value overflows to "-1294967296".
Data Dependent (z): Conversions from underlying character types to numeric types require that the character types contain values that can be converted into that type. No other conversions are performed. If the value is too large for the getter type, the value is not valid. For example, if getInt is called on a varchar(50) column that contains "53", the value is returned as an int; but if the underlying value is "xyz" or "3000000000", an error is thrown.
Setter Method Conversions
For the Java typed data passed to the update<Type> methods of the SQLServerResultSet class, and the setObject<Type> methods of the SQLServerPreparedStatement class, the following conversions apply.
.gif)
The setObject method with no specified target type will use the default mapping. The typed setters for parameters, the set<Type> methods of the SQLServerPreparedStatement class, and the set<Type> methods of the SQLServerCallableStatement class also use the default Java to JDBC type mapping, and will pass that type to the server. The server will try any conversions and return errors on failure.
In the case of the String data type, if the value exceeds the length of VARCHAR, it maps to LONGVARCHAR. The same is true for byte[]. Values longer than VARBINARY become LONGVARBINARY.
There are two basic categories of conversions that are supported by the JDBC driver’s setter methods:
Non-Lossy (x): Conversions for numeric cases where the setter type is the same or smaller than the underlying server type. For example, when calling setBigDecimal on an underlying server decimal column, no conversion is necessary. For numeric to character cases, the Java numeric data type is converted to a String. For example, calling setDouble with a value of "53" on a varchar(50) column will produce a character value "53" in that destination column.
Converted (y): Conversions from a Java numeric type to an underlying server numeric type that is smaller. This conversion is regular and follows SQL Server conversion conventions. Precision is always truncated—never rounded—and overflow throws an unsupported conversion error. For example, using updateDecimal with a value of "1.9999" on an underlying integer column will result in a "1" in the destination column; but if "3000000000" is passed, the driver will throw an error.
SQL Server performs the bulk of the set and update conversions and will pass errors back to the JDBC driver when there are problems. Client-side conversions are the exception and are performed only in the case of date, time, timestamp, Boolean, and String values.
In the case of updateObject and setObject, the methods look up the type of the passed object and make the call to the appropriately typed setter method.