HSQLDB cryptic exception message: "feature not supported"

Question

I have JDBC code which inserts into a database table by executing a PreparedStatement. When I run the code on an in-memory HSQLDB database (as part of a JUnit test) I get a SQLFeatureNotSupportedException with the only information being the message "feature not supported" and the vendor code -1500. What I'm doing is a basic insertion into a table -- I can't imagine that this is unsupported in the latest HSQLDB.

My code:

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 may be the problem or anything else I should investigate further? Thanks in advance for your help.

--James

Answer 1

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

You called the method PreparedStatement.executeUpdate(String sql), which is illegal according to the JDBC specification. It doesn't really make sense to pass the SQL statement again, because you have already passed it when you created the PreparedStatement object. Even thought you pass the same string, it's not legal to call this method. It's a bit strange that calling a method is not legal :-) but that's the way it is. All standard conforming JDBC drivers need to throw an exception in this case.

But I agree the error message is cryptic.

Answer 2

Some further info I found in 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.