DataTruncation and silent truncation

Report set() and update() methods

There is a subtle difference between JDBC drivers. Some drivers such as the native and IBM Toolbox for Java JDBC drivers catch and report data truncation issues at the time of the parameter setting. This is done either on the PreparedStatement set method or the ResultSet update method. Other drivers report the problem at the time of processing the statement and is accomplished by the execute, executeQuery, or updateRow methods.

Reporting the problem at the time that you provide incorrect data, instead of at the time that processing cannot continue any further, offers these advantages:

• The failure can be addressed in your application when you have a problem instead of addressing the problem at processing time.
• By checking when setting the parameters, the JDBC driver can ensure that the values that are handed to the database at statement processing time are valid. This allows the database to optimize its work and processing can be completed faster.

ResultSet.update() methods throwing DataTruncation exceptions

In some past releases, ResultSet.update() methods posted warnings when truncation conditions existed. This case occurs when the data value is going to be inserted into the database. The specification dictates that JDBC drivers throw exceptions in these cases. As a result, the JDBC driver works in this manner.

There are no significant difference between handling a ResultSet update function that receives a data truncation error and handling a prepared statement parameter set for an update or insert statement that receives an error. In both cases, the problem is identical; you provided data that does not fit where you wanted it.

NUMERIC and DECIMAL truncate to the right side of a decimal point silently. This is how both JDBC for UDB NT works and how interactive SQL on the IBM i platform works.

Note: No value is rounded when a data truncation occurs. Any fractional portion of a parameter that does not fit in a NUMERIC or DECIMAL column is simply lost without warning.

The following are examples, assuming that the value in the values clause is actually a parameter being set on a prepared statement:

create table cujosql.test (col1 numeric(4,2))
a) insert into cujosql.test values(22.22)  // works - inserts 22.22
b) insert into cujosql.test values(22.223) // works - inserts 22.22
c) insert into cujosql.test values(22.227) // works - inserts 22.22
d) insert into cujosql.test values(322.22) // fails - Conversion error on assignment to column COL1.

The specification states that data truncation on a value to be written to the database throws an exception. If data truncation is not performed on the value being written to the database, a warning is generated. This means that the point at which a data truncation situation is identified, you must also be aware of the statement type that the data truncation is processing. Given this as a requirement, the following lists the behavior of several SQL statement types:

• In a SELECT statement, query parameters never damage database content. Therefore, data truncation situations are always handled by posting warnings.
• In VALUES INTO and SET statements, the input values are only used to generate output values. As a result, warnings are issued.
• In a CALL statement, the JDBC driver cannot determine what a stored procedure does with a parameter. Exceptions are always thrown when a stored procedure parameter truncates.
• All other statement types throw exceptions rather than post warnings.

Data truncation property for Connection and DataSource

There has been a data truncation property available for many releases. The default for that property is true, meaning that data truncation issues are checked and warnings are posted or exceptions are thrown. The property is provided for convenience and performance in cases where you are not concerned that a value does not fit into the database column. You want the driver to put as much of the value as it can into the column.

Data truncation property only affects character and binary-based data types

A couple releases ago, the data truncation property determined whether data truncation exceptions could be thrown. The data truncation property was put in place to have JDBC applications not worry about a value getting truncated when the truncation was not important to them. There are few cases where you would want either the value 00 or 10 stored in the database when applications attempted to insert 100 into a DECIMAL(2,0). Therefore, the JDBC driver's data truncation property was changed to only honor situations where the parameter is for character-based types such as CHAR, VARCHAR, CHAR FOR BIT DATA, and VARCHAR FOR BIT DATA.

Data truncation property is only applied to parameters

The data truncation property is a setting of the JDBC driver and not of the database. As a result, it has no effect on statement literals. For example, the following statements that are processed to insert a value into a CHAR(8) column in the database still fail with the data truncation flag set to false (assume that connection is a java.sql.Connection object allocated elsewhere).

Statement stmt = connection.createStatement();
Stmt.executeUpdate("create table cujosql.test (col1 char(8))");
Stmt.executeUpdate("insert into cujosql.test values('dettinger')");  
// Fails as the value does not fit into database column.

Native JDBC driver throws exceptions for insignificant data truncation

The native JDBC driver does not look at the data that you provide for parameters. Doing so only slows down processing. However, there can be situations where it does not matter to you that a value truncates, but you have not set the data truncation connection property to false.

For example, 'dettinger ', a char(10) that is passed, throws an exception even though everything important about the value fits. This does happen to be how JDBC for UDB NT works; however, it is not the behavior you would get if you passed the value as a literal in an SQL statement. In this case, the database engine would throw out the additional spaces quietly.

The problems with the JDBC driver not throwing an exception are the following:

• Performance overhead is extensive on every set method, whether needed or not. For the majority of cases where there would be no benefit, there is considerable performance overhead on a function as common as setString().
• Your workaround is trivial, for example, calling the trim function on the string value passed in.
• There are issues with the database column to take into account. A space in CCSID 37 is not at all a space in CCSID 65535, or 13488.

Silent truncation

The setMaxFieldSize statement method allows a maximum field size to be specified for any column. If data truncates because its size has exceeded the maximum field size value, no warning or exception is reported. This method, like the data truncation property previously mentioned, only affects character-based types such as CHAR, VARCHAR, CHAR FOR BIT DATA, and VARCHAR FOR BIT DATA.