sql-maven-plugin throws SQL syntax error for H2 stored procedure definition

Question

I try to install a stored procedure in an h2 database (v. 1.3.170) using the sql-maven-plugin version 1.5.

The offending SQL statement looks like this:

CREATE ALIAS GET_DATA AS $$
 ResultSet getData(Connection conn, String id) {
  return null;
 }
$$;

This has been adapted from User-Defined Functions and Stored Procedures from the H2 website.

The maven error I get is this:

[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building dummy 0.5.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-clean-plugin:2.4.1:clean (default-clean) @ X4PAAsql ---
[INFO] Deleting D:\Data\Git\x4paa\SQL\target
[INFO]
[INFO] --- sql-maven-plugin:1.5:execute (createTables) @ X4PAAsql ---
[INFO] Executing file: C:\Users\THOMAS~1\AppData\Local\Temp\prepareDB.331774647sql
[INFO] Executing file: C:\Users\THOMAS~1\AppData\Local\Temp\storedProc.2069356353sql
[ERROR] Failed to execute:  CREATE ALIAS GET_DATA AS $$
 @CODE
 static ResultSet getData(Connection conn, String id) {
 return null
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.717s
[INFO] Finished at: Wed Aug 07 11:16:40 CEST 2013
[INFO] Final Memory: 4M/122M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (createTables) on project X4PAAsql: Syntax
 Fehler in SQL Befehl " CREATE ALIAS GET_DATA AS [*]$$
[ERROR] @CODE
[ERROR] static ResultSet getData(Connection conn, String id) {
[ERROR] return null"
[ERROR] Syntax error in SQL statement " CREATE ALIAS GET_DATA AS [*]$$
[ERROR] @CODE
[ERROR] static ResultSet getData(Connection conn, String id) {
[ERROR] return null" [42000-170]
[ERROR] -> [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

I use the following pom contents to configure the sql-maven-plugin.

<properties>
    <h2db.version>1.3.170</h2db.version>
    <sql-maven-plugin.version>1.5</sql-maven-plugin.version>        
</properties>
<build>
    <plugins>
        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>sql-maven-plugin</artifactId>
            <version>${sql-maven-plugin.version}</version>
            <dependencies>
                <dependency>
                      <groupId>com.h2database</groupId>
                      <artifactId>h2</artifactId>
                      <version>${h2db.version}</version>
                </dependency>
            </dependencies>
            <configuration>                         
                <driver>org.h2.Driver</driver>
                <url>jdbc:h2:SQL/target/H2DB/x4</url>
                <username>sa</username>
                <password>sa</password>
            </configuration>
            <executions>        
                <execution>
                    <id>createTables</id>
                    <phase>compile</phase>
                    <goals>
                        <goal>execute</goal>
                    </goals>
                    <configuration>
                        <forceMojoExecution>true</forceMojoExecution>
                        <srcFiles>
                            <srcFile>scripts/h2/prepareDB.sql</srcFile>
                            <srcFile>scripts/h2/storedProc.sql</srcFile>

                        </srcFiles>
                    </configuration>
                </execution>
            </executions>       
        </plugin>

Is there a way to work around the syntactical problem with the '$$'?

Update: I tried to run the query in SQuirrel and get the same error. So the problem is probably not related to the sql-maven-plugin but to the way the jdbc driver is used by the plugin or SQuirrel?

Answer 1

The sql-maven-plugin splits the SQL statement at the ";". The original statement was

CREATE ALIAS GET_DATA AS $$
ResultSet getData(Connection conn, String id) {
  return null;
}
$$;

however, the exception message of the database only shows the first part, until the ";":

CREATE ALIAS GET_DATA AS [*]$$
@CODE
static ResultSet getData(Connection conn, String id) {
return null

(The marker [*] just before the $$ is the position where the parsing fails, because the parser doesn't see the end token $$.)

The "good" solution would be to change the sql-maven-plugin to support quoted data, but I guess that will not be easy. As a workaround, you could try changing the statement to:

CREATE ALIAS GET_DATA AS $$
ResultSet getData(Connection conn, String id) {
return null; } $$;