I am running a Spring Java Web Application which can communicate with a database. The type of database is selectable by the user and can be MySQL, Oracle or SQLServer.
I don't understand why the following code fails to drop the table when using SQLServer ONLY (it works for Oracle and MySQL):
public void dropTables(String... tableNames) {
Session session = localSessionFactory.getObject().openSession();
try {
dropTables(session, tableNames);
} finally {
session.close();
}
}
private void dropTables(Session session, String... tableNames) {
for(String currentTable : tableNames) {
SQLQuery createSQLQuery = session.createSQLQuery("drop table "+currentTable);
createSQLQuery.executeUpdate();
}
}
The table fails to drop. It is as if the Drop SQL Query didn't run. The table still exists and retains its contents. The return value from the call to executeUpdate is 0.
However if I modify the code by adding a Transaction around the drop statements, then the drop succeeds on SQLServer:
public void dropTables(String... tableNames) {
Session session = localSessionFactory.getObject().openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
dropTables(session, tableNames);
} finally {
tx.commit();
session.close();
}
}
private void dropTables(Session session, String... tableNames) {
for(String currentTable : tableNames) {
SQLQuery createSQLQuery = session.createSQLQuery("drop table "+currentTable);
createSQLQuery.executeUpdate();
}
}
Why do I have to put the Transaction around it? I thought that since DROP is a DDL statement the transaction is not necessary. Can anyone explain this for me?
Unlike Oracle and MySQL, SQL Server supports transactional DDL. That means you have to commit DDL statements - unless your connection is configured to use auto-commit.
Transactional DDL is a very nice feature because it allows for "all-or-nothing" migration scripts. A rollback will also rollback a drop table
!
Transactional DDL is nothing special to SQL Server. Postgres, DB2 and Firebird also support this (just to name a few).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments