HSQLDB critical message exception message: "function not supported"

Question

I have JDBC code that is inserted into a database table by doing PreparedStatement. When I run the code in the HSQLDB database in memory (as part of the JUnit test), I get an SQLFeatureNotSupportedException exception, the only information is the message "function not supported" and the vendor code is -1500. What I am doing is a basic insertion into a table - I cannot imagine that this is not supported in the latest HSQLDB.

My code is:

public Observations saveOrUpdate(final Observations observations) { try { if (connection == null) { connection = getJdbcTemplate().getDataSource().getConnection(); } // create the prepared statement String sql = "INSERT INTO " + Observations.TABLE_NAME + " (OBS_YEAR, WINTER, SPRING, SUMMER, FALL, ANNUAL, DATA_TYPE, CREATED_DATE, UPDATED_DATE, " + Observations.ID_COLUMN_NAME + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, observations.getYear()); preparedStatement.setBigDecimal(2, observations.getJan()); preparedStatement.setBigDecimal(3, observations.getFeb()); preparedStatement.setBigDecimal(4, observations.getMar()); preparedStatement.setBigDecimal(5, observations.getApr()); preparedStatement.setBigDecimal(6, observations.getMay()); preparedStatement.setString(7, observations.getDataType().toString()); preparedStatement.setTimestamp(8, new Timestamp(observations.getCreatedDate().getTime())); preparedStatement.setTimestamp(9, new Timestamp(observations.getUpdatedDate().getTime())); preparedStatement.setLong(10, observations.getId()); preparedStatement.executeUpdate(sql); return observations; } catch (SQLException ex) { throw new RuntimeException(ex); } } 

Can anyone suggest what could be the problem or something else that I should investigate further? Thanks in advance for your help.

- james

Answer 1

You need to call preparedStatement.executeUpdate() (without sql parameter).

You called the PreparedStatement.executeUpdate(String sql) method, which is illegal according to the JDBC specification. It makes no sense to pass the SQL statement again because you already passed it when you created the PreparedStatement object. I even thought that you are passing the same line, it’s illogical to call this method. It's a little strange that calling a method is not legal :-), but as it is. All standard compatible JDBC drivers should throw an exception in this case.

But I agree that the error message is cryptic.

Answer 2

Additional information I found at http://hsqldb.org/doc/changelog_1_7_2.txt :

 The execute(String sql), executeUpdate(String sql) and executeQuery(String sql) commands are no-longer supported for PreparedStatements according to JDBC specs. Use an ordinary Statement for calling these methods. 

Answer 3

System problems with HSQLDB often arise due to inconsistencies in the server or driver version (any inconsistency will not work at all in my experience).

Basically, I suspect this is not your problem. Since you said that db is “in memory”, I assume that the server and driver are the same .jar file. But in case my guess is wrong, I thought I would throw it away.