Embedded Derby is a bit tricky to use from within Apache Ant scripts. The reason is that the Embedded Derby driver must unlock the database before another process can safely access the same database. If the first process terminates before the second process starts, there is no problem: a stale database lock is simply ignored. But if you have an Ant script that first accesses the database via <sql> tasks, and then via some other task deploys an application in some other process (e.g. GlassFish) that accesses the same database, the deployment will fail because the Ant process is still active and hasn't relinquished the lock.

I encountered this problem a few months ago, and found a nice solution here. Of all the stuff in that article, I only used the "StopDatabaseTask.java" code (without the "ArrayList subtasks" which seems to be a left-over from the original organ donor). This solved all my problems and worked perfectly fine with the built-in GlassFish "asant" which I used for my install scripts. It stopped working however when I tried the same thing in GlassFish v3, which doesn't have "asant", so I tried Apache Ant version 1.7.1 instead. Suddenly the <taskdef> for shutting down the embedded database stopped working. After scratching my head a lot, I realized that the cause was that Apache Ant classloading has been changed into something that ordinary mortals can no longer understand. So what to do? One concern is that I want the solution to be backwards compatible with Apache Ant version 1.6.5 also, since that's the base version for GlassFish "asant".

Here is what I did. I found that extending the core <sql> task into a custom version for Embedded Derby could be done in a way that works in both Apache Ant 1.6.5 and 1.7.1. The <taskdef> code is shown below.

    import java.sql.SQLException;
    import org.apache.tools.ant.taskdefs.SQLExec;
    import org.apache.tools.ant.BuildException;

    public class SQLDerbyTask extends SQLExec {
        private boolean create;
        public boolean getCreate() { return create; }
        public void setCreate(boolean x) { create = x; }

        public void execute() throws BuildException {
            String url = getUrl();
            if (create) {
                setUrl(url+ ";create=true");
            }
            try {
                super.execute();
            } finally {
                setUrl(url+ ";shutdown=true");
                try {
                    getConnection();
                } catch (BuildException e) {
                    Throwable t = e.getException();
                    if (t instanceof SQLException) {
                        SQLException x = (SQLException) t;
                        if ("08006".equals(x.getSQLState()) && 45000 == x.getErrorCode()) {
                            System.err.println("Embedded database shut down");
                            return;
                        }
                    }
                    throw e;
                }
            }
        }
    }

This works exactly like the core <sql> task, except that it also shuts down the Derby database after executing the SQL statements. And as can be seen, I have added a "create" property because I needed it. Other Derby properties like "createFrom" and "restoreFrom" can be added in the same way.