Include all SQL statements in batch fail exception [SPR-10677]

[spring-projects-issues]

Lefebvre opened SPR-10677 and commented

When use jdbcTemplate.batchUpdate(String[] scripts) if the update breaks an UncategorizedSQLException is thrown.

The UncategorizedSQLException.getSql() does not contains the sql that generate the error but the first scripts[] element.

---

Affects: 3.2.3

Attachments:

• wsTest.zip (5.12 MB)

Referenced from: commits 6a3a361, 2abec6f

[spring-projects-issues]

Phil Webb commented

What database server are you using? More specifically does it support batched updates?

It appears that the SQL in the exception should be provided via the currSql member of batchUpdate. If possible could you add try debugging the doInStatement method to see where the exception is raised and what the value of currSql is at the time.

A repro project would also be very useful to help us replicate the problem.

[spring-projects-issues]

Lefebvre commented

Please find a tests projets.

[spring-projects-issues]

Lefebvre commented

Do you reprocuce the problem with my sample ?

Sorry for my enlish :-)

[spring-projects-issues]

Phil Webb commented

Reproduced the problem, thanks for the test project.

The problem isn't that SQL in the exception contains the first element, rather that it contains the last. The reason for this is that your database supports batched updates so all the SQL is submitted as a single request. By the time the exception has been raised all the statements have been looped over and so the last one ends up in the message.

This is obviously a bit misleading but I don't think we have a safe way to tell precisely which SQL statement failed. Probably the best that we can do is ensure that the exception contains all the SQL statements concatenated together.

If you know that you will always be talking to HSQLDB it is possible to access the actual SQL that failed in the following (non portable) way:

if(dataAccessException.getCause() instanceof JdbcSQLException) {
	sql = ((JdbcSQLException) dataAccessException.getCause()).getSQL();
}

[spring-projects-issues]

Lefebvre commented

Yes you can know failed statements doing that :

List<String> sqlsToExecuteInBatch=new ArrayList<>();		
		try(Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");Statement statement=conn.createStatement()){
			
			sqlsToExecuteInBatch.add(String.format("BAD SQL FIRST"));
			sqlsToExecuteInBatch.add("CREATE TABLE TEST (id INTEGER)");
			for(int k=0;k<100;k++) {
				sqlsToExecuteInBatch.add(String.format("INSERT INTO TEST VALUES(%s)", k));
			}
			sqlsToExecuteInBatch.add(sqlsToExecuteInBatch.size()/2,String.format("BAD SQL MIDDLE"));
			sqlsToExecuteInBatch.add(sqlsToExecuteInBatch.size()/2,String.format("BAD SQL END"));
			
			for(String sql:sqlsToExecuteInBatch) {
				statement.addBatch(sql);
			}
			
			statement.executeBatch();
			
		} catch(BatchUpdateException batchUpdateException) {
			
			int[] updateCounts=batchUpdateException.getUpdateCounts();			
			List<String> failedStatements=new ArrayList<>();
			for(int k=0;k<updateCounts.length;k++) {
				if(updateCounts[k] == Statement.EXECUTE_FAILED) {
					failedStatements.add(sqlsToExecuteInBatch.get(k));
				}
				
			}
			
			for(String failedStatement:failedStatements) {
				System.err.println(String.format("sqlStatementFailed [%s].",failedStatement));
			}
		}

Display :

sqlStatementFailed [BAD SQL FIRST].
sqlStatementFailed [BAD SQL END].
sqlStatementFailed [BAD SQL MIDDLE].

[spring-projects-issues]

Phil Webb commented

Thanks, I was not aware of that.

[spring-projects-issues]

Lefebvre commented

:-)

[spring-projects-issues]

Lefebvre commented

For other databases like oracle, the updateCounts stop after the first statement breaks