#1754 Connection.rollback() does not remove schemas

[Martin Desruisseaux]

If schemas are created after a call to Connection.setAutoCommit(false), I would expect those schemas to be dropped after a call to Connection.rollback(). It does not seem to be the case according the following test:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Tmp {
    public static void main(String[] args) throws SQLException {
        try (Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:Test")) {
            verifySchemaPresence(c, "Before");
            c.setAutoCommit(false);
            try (Statement s = c.createStatement()) {
                s.execute("CREATE SCHEMA TEST");
            }
            verifySchemaPresence(c, "During");
            c.rollback();
            c.setAutoCommit(true);
            verifySchemaPresence(c, "After");
        }
    }

    private static void verifySchemaPresence(final Connection c, final String label) throws SQLException {
        int count = 0;
        try (ResultSet r = c.getMetaData().getSchemas()) {
            while (r.next()) {
                if ("TEST".equals(r.getString("TABLE_SCHEM"))) {
                    count++;
                }
            }
        }
        System.out.println(label + ": " + count);
    }
}

By contrast, the schema was removed on Apache Derby. The problem described in this issue is the reason for the DROP SCHEMA statement in https://sourceforge.net/p/hsqldb/bugs/1753, but that workaround has in turn a deadlock issue.

[Fred Toussi]

HSQLDB and some other databases do not support transactional DDL statements. When the DDL is about to be executed, the session commits the transaction, executes the DDL and autocommits.