How to crop a table using Spring JdbcTemplate?

Question

I am trying to trim a table using Spring:

jdbcTemplate.execute("TRUNCATE TABLE " + table); 

Receive error message:

Called: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [TRUNCATE TABLE RESULT_ACCOUNT]; java.sql.SQLException nested exception: Unexpected token: TRUNCATE in statement [TRUNCATE]

Any ideas?

Answer 1

The problem here was that you cannot do DDL (e.g. truncate) as part of an existing transaction. The reason is that DDL performs automatic committing that does not pursue transactional concepts (i.e. Rollback). So I set the NOT_SUPPORTED method, and I was good.

Answer 2

I found that SQLExceptions do not always indicate a problem. I would try to run the truncate query directly in the database and see if it works. (Most likely, you will get much better debugging information from the database itself.) This could be a violation of the foreign key constraint, the need to call a cascade call, etc.

Answer 3

Are you sure the database running this command supports the TRUNCATE TABLE command? Because it is an error message and the type of exception certainly sounds as if not.

You might want to take a look at the nested exception and the rest of your stacktrace to see exactly where this message comes from.

Answer 4

The root cause of the error is that your SQL statement is not valid for the database you are talking to. You can say that the exception is a SQLException (i.e. Not from Spring), so your RDBMS essentially says: "I don't know what TRUNCAT TABLE FOO means."

You will need to read the database system manual to find out how to trim the table. Although many major databases (in any case, the latest versions) seem to support TRUNCATE TABLE statements, it sounds as if you cannot.

Although it is less efficient, you can also try DELETE FROM FOO query, where FOO is the name of your table.

Answer 5

Here the database allows Truncate .. The JDBC template or Jdbc driver does not allow truncation