My program loads a simple CSV into a table. The data in the file is expected to match the table’s layout.
The (simplified) code is:
String sql = "select * from " + _tableName +
" where 1 < 0";
PreparedStatement stmt = _CONN.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
rs.close();
stmt.close();
_columnCount = rsmd.getColumnCount();
sql = "insert into " + _tableName + " values (" +
"?,".repeat(_columnCount - 1) + "?)";
stmt = _CONN.prepareStatement(sql);
...
int line_no = 0;
readingInput:
for (String line : Files.readAllLines(input,
StandardCharsets.UTF_8)) {
line_no++;
String[] sa = line.split(_inputSeperator, -1);
int column = 0;
for (String st : sa) try {
column++; /* JDBC numbers columns from 1... */
stmt.setObject(column, st,
rsmd.getColumnType(column));
} catch(SQLException e) {
logger.error("{}:{} SQL Exception parsing field {} of <<{}>>",
_fileName, line_no, column, line);
continue readingInput;
}
stmt.addBatch();
}
...
stmt.executeBatch();
This, actually, works for valid inputs, but one of my tests tries a bogus line — attempting to insert a string into a numeric column.
Such invalid input does generate an error, but only at the stmt.executeBatch() line at the end, where I’m not prepared for it: java.sql.BatchUpdateException: Error converting data type nvarchar to int.
Why wouldn’t the error trigger, when an invalid value is passed to the Statement.setObject() instead — where my code is prepared to report it, skip the bogus input-line and keep going?
All of the PreparedStatment.setFoo() methods are declared as throwing the SQLException — why wouldn’t setObject() actually throw one?
Perhaps, this is done to save time — and there is some option for an immediate validation of values passed to the PreparedStatement, even if it takes longer?
Update: I changed the inner loop to force the conversion into column’s Java-class thus:
int column = 0;
for (String st : sa) try {
column++; /* JDBC numbers columns from 1... */
int columnType = rsmd.getColumnType(column);
if (st.isEmpty()) {
stmt.setNull(column, columnType);
continue;
}
Object value;
try {
value = Class.forName(rsmd.
getColumnClassName(column)).
getConstructor(String.class).newInstance(st);
} catch(InvocationTargetException e) {
/* We want the underlying cause here... */
throw(e.getCause());
}
stmt.setObject(column, value, columnType);
} catch(java.lang.Throwable e) {
logger.error("{}:{} Exception parsing field {} ({}) of <<{}>>: {}",
_fileName, line_no, column,
rsmd.getColumnName(column), line,
e);
continue readingInput;
}
This, actually, works as I want it to — doing, what I expected the underlying setObject to do for me automatically…
The behaviour is driver-specific. Some JDBC drivers will validate the conversion themselves (usually because they need to apply the conversion client-side because the server expects specific datatypes), and then you would get the exception on calling setXXX. Other JDBC drivers will simply send a typed value to the server using the SQL-equivalent datatype of the type you used on set, and on execute, the server will perform the required conversion to the actual column datatype, and that will either succeed or fail if the requested conversion is not possible or fails some other requirement.
The JDBC specification allows for both, because of the great variance between features and behaviours in database server. There is no way for you to require the conversion is applied at a specific point.
Besides, even for drivers that perform or check basic conversion client-side, execute could still fail for the value you set, for example because of primary key, foreign key, CHECK, NOT NULL, or UNIQUE constraints, triggers, etc.
If you want to avoid errors, you could try to inspect the ParameterMetaData from PreparedStatement.getParameterMetaData(), and perform the necessary validation or conversions yourself, but again, that that does not mean a value couldn’t still produce a failure on execute.
5