title: Errors using Maven Mojo sql:execute
body: <p>TL;DR version: I want to be able to use the Maven Mojo SQL Plugin to create/drop any given table in my DB schema (or load data for those tables) at will via the <code>mvn</code> command-line.  How?</p>
  
  <hr>
  
  <p>I'm a long-time Java developer, but for the most part I've been living in an <code>ant</code>-based world.  I like the power and explicitness of <code>ant</code>, and the control that it gives me over everything.  However, in my new job, there's a push to use <code>maven</code>.  So I've decided to learn it using a project I'm working on at home.</p>
  
  <p>One of the things that I have set up on a different personal project is the ability to completely set up and tear down my Postgres database from <code>ant</code> on the command line.  I can slice and dice any table, sequence, and integration test data that I please, individually or in concert.  Sure, it means that I have about a gajillion <code>ant</code> targets, but it works very well.  I like this; it has served me quite well over the years.</p>
  
  <p>In researching how to accomplish this in Maven over the weekend, I found the <a href="http://mojo.codehaus.org/sql-maven-plugin/" rel="nofollow">Mojo SQL Maven Plugin</a>.  After looking at the <a href="http://mojo.codehaus.org/sql-maven-plugin/usage.html" rel="nofollow">usage page</a> (and I use that term loosely, as it's really just a single semi-example with no explanations) and the <a href="http://mojo.codehaus.org/sql-maven-plugin/examples/execute.html" rel="nofollow">example page</a>, I was able to come up with some changes to my <code>pom.xml</code> file.  I fixed some obvious typos in the example (postgressql), and referenced the <a href="http://jdbc.postgresql.org/documentation/83/connect.html" rel="nofollow">PostgreSQL JDBC page</a> to make sure I had the JDBC connection string correct.  I'll paste all of the <code>pom.xml</code> (modified to protect the guilty) below:</p>
  
  <pre><code>&lt;project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"&gt;
      &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
      &lt;groupId&gt;com.mycompany.myapp&lt;/groupId&gt;
      &lt;artifactId&gt;myapp&lt;/artifactId&gt;
      &lt;packaging&gt;jar&lt;/packaging&gt;
      &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
      &lt;name&gt;myapp&lt;/name&gt;
      &lt;url&gt;http://maven.apache.org&lt;/url&gt;
  
      &lt;repositories&gt;
          &lt;repository&gt;
              &lt;id&gt;JBoss&lt;/id&gt;
              &lt;url&gt;https://repository.jboss.org/nexus/content/groups/public/&lt;/url&gt;
          &lt;/repository&gt;
      &lt;/repositories&gt;
  
      &lt;dependencies&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;junit&lt;/groupId&gt;
              &lt;artifactId&gt;junit&lt;/artifactId&gt;
              &lt;version&gt;4.10&lt;/version&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.hibernate&lt;/groupId&gt;
              &lt;artifactId&gt;hibernate-core&lt;/artifactId&gt;
              &lt;version&gt;4.0.0.CR7&lt;/version&gt;
          &lt;/dependency&gt;
      &lt;/dependencies&gt;
  
      &lt;build&gt;
          &lt;plugins&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.codehaus.mojo&lt;/groupId&gt;
                  &lt;artifactId&gt;sql-maven-plugin&lt;/artifactId&gt;
                  &lt;version&gt;1.5&lt;/version&gt;
  
                  &lt;dependencies&gt;
                      &lt;dependency&gt;
                          &lt;groupId&gt;postgresql&lt;/groupId&gt;
                          &lt;artifactId&gt;postgresql&lt;/artifactId&gt;
                          &lt;version&gt;8.3-606.jdbc4&lt;/version&gt;
                      &lt;/dependency&gt;
                  &lt;/dependencies&gt;
  
                  &lt;configuration&gt;
                      &lt;driver&gt;org.postgresql.Driver&lt;/driver&gt;
                      &lt;url&gt;jdbc:postgresql://localhost:5432/myapp&lt;/url&gt;
                      &lt;settingsKey&gt;myapp&lt;/settingsKey&gt;
                      &lt;!--all executions are ignored if -Dmaven.test.skip=true--&gt;
                      &lt;skip&gt;${maven.test.skip}&lt;/skip&gt;
                  &lt;/configuration&gt;
  
                  &lt;executions&gt;
                      &lt;execution&gt;
                          &lt;id&gt;drop-db-before-test-if-any&lt;/id&gt;
                          &lt;phase&gt;process-test-resources&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;execute&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;!-- need another database to drop the targeted one --&gt;
                              &lt;url&gt;jdbc:postgresql://localhost:5432/template1&lt;/url&gt;
                              &lt;autocommit&gt;true&lt;/autocommit&gt;
                              &lt;sqlCommand&gt;drop database myapp&lt;/sqlCommand&gt;
                              &lt;!-- ignore error when database is not avaiable --&gt;
                              &lt;onError&gt;continue&lt;/onError&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
  
                      &lt;execution&gt;
                          &lt;id&gt;create-db&lt;/id&gt;
                          &lt;phase&gt;process-test-resources&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;execute&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;url&gt;jdbc:postgresql://localhost:5432/template1&lt;/url&gt;
                              &lt;!-- no transaction --&gt;
                              &lt;autocommit&gt;true&lt;/autocommit&gt;
                              &lt;sqlCommand&gt;create database myapp&lt;/sqlCommand&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
  
                      &lt;execution&gt;
                          &lt;id&gt;create-schema&lt;/id&gt;
                          &lt;phase&gt;process-test-resources&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;execute&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;autocommit&gt;true&lt;/autocommit&gt;
                              &lt;srcFiles&gt;
                                  &lt;srcFile&gt;src/main/sql/create_person.sql&lt;/srcFile&gt;
                              &lt;/srcFiles&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
  
                      &lt;execution&gt;
                          &lt;id&gt;create-data&lt;/id&gt;
                          &lt;phase&gt;process-test-resources&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;execute&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;orderFile&gt;ascending&lt;/orderFile&gt;
                              &lt;fileset&gt;
                                  &lt;basedir&gt;${basedir}&lt;/basedir&gt;
                                  &lt;includes&gt;
                                      &lt;include&gt;src/test/sql/person_data.sql&lt;/include&gt;
                                  &lt;/includes&gt;
                              &lt;/fileset&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
  
                      &lt;!-- drop db after test --&gt;
                      &lt;execution&gt;
                          &lt;id&gt;drop-db-after-test&lt;/id&gt;
                          &lt;phase&gt;test&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;execute&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;url&gt;jdbc:postgresql://localhost:5432/template1&lt;/url&gt;
                              &lt;autocommit&gt;true&lt;/autocommit&gt;
                              &lt;sqlCommand&gt;drop database myapp&lt;/sqlCommand&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
                  &lt;/executions&gt;
              &lt;/plugin&gt;
          &lt;/plugins&gt;
      &lt;/build&gt;
  &lt;/project&gt;
  </code></pre>
  
  <p>Now, since I haven't created the database, it doesn't show up in a <code>\l</code> on the PG command-line:</p>
  
  <pre><code>[mike@mike myapp]$ psql template1
  Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
  
  Type:  \copyright for distribution terms
         \h for help with SQL commands
         \? for help with psql commands
         \g or terminate with semicolon to execute query
         \q to quit
  
  template1=# \l
          List of databases
     Name    |  Owner   | Encoding 
  -----------+----------+----------
   postgres  | postgres | UTF8
   template0 | postgres | UTF8
   template1 | postgres | UTF8
  (3 rows)
  </code></pre>
  
  <p>Thus, when I run <code>mvn sql:execute</code>, I expect my database to get created...Or at least not to fail on the <code>drop-db-before-test-if-any</code> task since that is set to continue on error.  But of course:</p>
  
  <pre><code>[mike@mike myapp]$ mvn sql:execute
  [INFO] Scanning for projects...
  [INFO]                                                                         
  [INFO] ------------------------------------------------------------------------
  [INFO] Building myapp 1.0-SNAPSHOT
  [INFO] ------------------------------------------------------------------------
  [INFO] 
  [INFO] --- sql-maven-plugin:1.5:execute (default-cli) @ myapp ---
  [INFO] ------------------------------------------------------------------------
  [INFO] BUILD FAILURE
  [INFO] ------------------------------------------------------------------------
  [INFO] Total time: 1.667s
  [INFO] Finished at: Mon Dec 05 20:22:17 CST 2011
  [INFO] Final Memory: 3M/81M
  [INFO] ------------------------------------------------------------------------
  [ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -&gt; [Help 1]
  [ERROR] 
  [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
  [ERROR] Re-run Maven using the -X switch to enable full debug logging.
  [ERROR] 
  [ERROR] For more information about the errors and possible solutions, please read the following articles:
  [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException
  </code></pre>
  
  <p>The error page mentioned on the last line there isn't helpful; it just tells me that a plugin caused the error, not Maven itself.</p>
  
  <p>So let's run it with the <code>-X</code> switch.  I'll just post the interesting part of the error:</p>
  
  <pre><code>[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -&gt; [Help 1]
  org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist
      at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:217)
      at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
      at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
      at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:84)
      at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:59)
      at org.apache.maven.lifecycle.internal.LifecycleStarter.singleThreadedBuild(LifecycleStarter.java:183)
      at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:161)
      at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:319)
      at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:156)
      at org.apache.maven.cli.MavenCli.execute(MavenCli.java:537)
      at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:196)
      at org.apache.maven.cli.MavenCli.main(MavenCli.java:141)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:290)
      at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:230)
      at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
      at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
  Caused by: org.apache.maven.plugin.MojoExecutionException: FATAL: database "myapp" does not exist
      at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:618)
      at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:101)
      at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:209)
      ... 19 more
  Caused by: org.postgresql.util.PSQLException: FATAL: database "myapp" does not exist
      at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:444)
      at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:99)
      at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
      at org.postgresql.jdbc2.AbstractJdbc2Connection.&lt;init&gt;(AbstractJdbc2Connection.java:124)
      at org.postgresql.jdbc3.AbstractJdbc3Connection.&lt;init&gt;(AbstractJdbc3Connection.java:30)
      at org.postgresql.jdbc4.AbstractJdbc4Connection.&lt;init&gt;(AbstractJdbc4Connection.java:29)
      at org.postgresql.jdbc4.Jdbc4Connection.&lt;init&gt;(Jdbc4Connection.java:24)
      at org.postgresql.Driver.makeConnection(Driver.java:386)
      at org.postgresql.Driver.connect(Driver.java:260)
      at org.codehaus.mojo.sql.SqlExecMojo.getConnection(SqlExecMojo.java:899)
      at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:612)
      ... 21 more
  </code></pre>
  
  <p>But, but, but...<code>&lt;onError&gt;continue&lt;/onError&gt;</code>!</p>
  
  <p>So, to the questions:</p>
  
  <ol>
  <li>What am I doing wrong?  Is it my expectations, or my code?</li>
  <li>You'll notice that I have a <code>create-person.sql</code> file.  I know from the examples that I can have multiple files there, such as <code>create-address.sql</code>.  But in <code>ant</code>, I have the ability to create the <code>address</code> table separately from the <code>person</code> table, so long as I perform the ant tasks keeping in mind the order of referential integrity.  Is something like that possible with <code>maven</code>?  If so, how?</li>
  </ol>
  
  <p>Sorry for the verbosity, and thanks in advance for any assistance.</p>