sql-maven-plugin抛出H2存储过程定义的SQL语法错误

Question

我尝试使用sql-maven-plugin version1.5在h2数据库(v1.3.170)中安装存储过程。

令人不快的SQL语句如下所示：

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

这是从H2网站上的User-Defined Functions and Stored Procedures改编的。

我得到的maven错误是：

[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

我使用以下pom内容来配置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>

有没有办法解决“$$”的语法问题？

更新:我尝试在SQuirrel中运行查询，但得到了相同的错误。所以问题可能与sql-maven-plugin无关，而是与插件或SQuirrel使用jdbc驱动程序的方式有关？

Answer 1

sql-maven-plugin在";“处拆分SQL语句。最初的声明是

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

然而，数据库的异常消息只显示了第一部分，直到";"：

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

( $$之前的标记[*]是解析失败的位置，因为解析器看不到结束标记$$。)

“好”的解决方案是更改sql-maven-plugin以支持引用的数据，但我猜这并不容易。作为一种解决办法，您可以尝试将语句更改为：

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