Scriptella - SQLServerException:生成了要更新的结果集

Question

我是第一次使用Scriptella，现在还在苦苦挣扎。

我似乎在SQL Server2012和MySQL上都遇到了同样的问题。使用这两种方法，我可以创建表和插入记录，但在使用SELECT语句时会出现以下错误。

使用驱动程序mssql-jdbc-8.4.0.jre8.jar (及更早版本)的SQL Server：

驱动程序异常: com.microsoft.sqlserver.jdbc.SQLServerException:生成了要更新的结果集。

MYSQL using driver mysql-connector-java-5.1.45.jar:驱动程序异常: java.sql.SQLException:无法通过executeUpdate()或executeLargeUpdate()发出SELECT。

我不知道这是我的脚本的问题，还是服务器配置的问题。我试图在sql server上设置nocount on和nocount off，但无济于事。

我尝试了Scriptella版本1.1和版本1.2，也遇到了同样的问题。

我被这个问题卡住了，在关注了stackoverflow.com多年之后，我终于在这里发布了我的第一个问题。我将非常感谢任何人的帮助。

请看下面我正在使用的脚本以及堆栈跟踪。谢谢。

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <properties>
        <include href="etl.properties"/> <!--Load from external properties file-->
    </properties>

    <!-- Connection declarations -->
    <connection driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>

    <!--ETL Queries and Scripts -->
    <script>
        <!-- CREATE TABLE WORKED!
          create table TestDB.dbo.test (
          id int
        );   -->

    <!-- INSERT WORKED!
      insert into TestDB.dbo.test (id) values (1);
    -->

    <!-- BELOW FAILS -->
        select * from TestDB.dbo.GLAccounts
        <script>
          insert into TestDB.dbo.test (id) values (?1);
        </script>
    </script>
</etl>

    etl-scriptsS   java -jar ~/Downloads/scriptella/scriptella-1.1/scriptella.jar my-first-etl.xml
    Aug 12, 2020 8:11:33 AM <WARNING> XML configuration warning in file:/home/user1/workspace/etl-scripts/my-first-etl.xml(28:14): The content of element type "script" must match "(include|dialect|onerror)".
    Aug 12, 2020 8:11:33 AM <INFO> Execution Progress.Initializing properties: 1%
    Aug 12, 2020 8:11:36 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
    WARNING: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum.
    Aug 12, 2020 8:11:36 AM <INFO> Execution Progress.Initialized connection JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}, Dialect{Microsoft SQL Server 11.00.2218}, properties {}: 5%
    Aug 12, 2020 8:11:36 AM <INFO> Execution Progress./etl/script[1] prepared: 10%
    Aug 12, 2020 8:11:36 AM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/home/user1/workspace/etl-scripts/my-first-etl.xml"
    Aug 12, 2020 8:11:36 AM <SEVERE> Script /home/user1/workspace/etl-scripts/my-first-etl.xml execution failed.
    Location: /etl/script[1]
    JDBC provider exception: Unable to execute statement
    Error statement: 
    select * from TestDB.dbo.GLAccounts
    Error codes: [null, 0]
    Driver exception: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
    etl-scriptsS   java -jar ~/Downloads/scriptella/scriptella-1.1/scriptella.jar my-first-etl.xml
    Aug 12, 2020 8:20:19 AM <WARNING> XML configuration warning in file:/home/user1/workspace/etl-scripts/my-first-etl.xml(28:14): The content of element type "script" must match "(include|dialect|onerror)".
    Aug 12, 2020 8:20:19 AM <INFO> Execution Progress.Initializing properties: 1%
    Aug 12, 2020 8:20:21 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
    WARNING: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum.
    Aug 12, 2020 8:20:22 AM <INFO> Execution Progress.Initialized connection JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}, Dialect{Microsoft SQL Server 11.00.2218}, properties {}: 5%
    Aug 12, 2020 8:20:22 AM <INFO> Execution Progress./etl/script[1] prepared: 10%
    Aug 12, 2020 8:20:22 AM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/home/user1/workspace/etl-scripts/my-first-etl.xml"
    Aug 12, 2020 8:20:22 AM <SEVERE> Script /home/user1/workspace/etl-scripts/my-first-etl.xml execution failed.
    Location: /etl/script[1]
    JDBC provider exception: Unable to execute statement
    Error statement: 
    select * from TestDB.dbo.GLAccounts
    Error codes: [null, 0]
    Driver exception: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
    etl-scriptsS    java -jar ~/Downloads/scriptella/scriptella-1.1/scriptella.jar -debug my-first-etl.xml
    Aug 12, 2020 9:30:52 AM <WARNING> XML configuration warning in file:/home/user1/workspace/etl-scripts/my-first-etl.xml(28:14): The content of element type "script" must match "(include|dialect|onerror)".
    Aug 12, 2020 9:30:52 AM <INFO> Execution Progress.Initializing properties: 1%
    Aug 12, 2020 9:30:53 AM <FINE> registerDriver: SQLServerDriver:1
    Aug 12, 2020 9:30:53 AM <FINE> Found driver class com.microsoft.sqlserver.jdbc.SQLServerDriver
    Aug 12, 2020 9:30:53 AM <FINE> DriverManager.getConnection("jdbc:sqlserver://XXX.XXX.XXX.XXX:12345")
    Aug 12, 2020 9:30:53 AM <FINE>     trying com.microsoft.sqlserver.jdbc.SQLServerDriver
    Aug 12, 2020 9:30:54 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
    WARNING: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum.
    Aug 12, 2020 9:30:54 AM <FINE> SQLWarning: reason(TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum.)
    Aug 12, 2020 9:30:54 AM <FINE> getConnection returning com.microsoft.sqlserver.jdbc.SQLServerDriver
    Aug 12, 2020 9:30:54 AM <FINE> jdbc:sqlserver://XXX.XXX.XXX.XXX:12345: Statement cache is enabled (cache size 64). Statement separator ';'. Autocommit: false.
    Aug 12, 2020 9:30:54 AM <INFO> Execution Progress.Initialized connection JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}, Dialect{Microsoft SQL Server 11.00.2218}, properties {}: 5%
    Aug 12, 2020 9:30:54 AM <INFO> Execution Progress./etl/script[1] prepared: 10%
    Aug 12, 2020 9:30:54 AM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/home/user1/workspace/etl-scripts/my-first-etl.xml"
    Aug 12, 2020 9:30:54 AM <FINE> Executing script /etl/script[1]
    Aug 12, 2020 9:30:55 AM <FINE> SQLState(null) vendor code(0)
    Aug 12, 2020 9:30:55 AM <FINE> Rolling back JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}
    Aug 12, 2020 9:30:55 AM <FINE> Closing JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}
    Aug 12, 2020 9:30:55 AM <SEVERE> Script /home/user1/workspace/etl-scripts/my-first-etl.xml execution failed.
    Location: /etl/script[1]
    JDBC provider exception: Unable to execute statement
    Error statement: 
    select * from TestDB.dbo.GLAccounts
    Error codes: [null, 0]
    Driver exception: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
    ---------------Debug Stack Trace-----------------
    scriptella.core.ExceptionInterceptor$ExecutionException: /etl/script[1] failed: Unable to execute statement
        at scriptella.core.ExceptionInterceptor.execute(ExceptionInterceptor.java:44)
        at scriptella.core.Session.execute(Session.java:103)
        at scriptella.execution.EtlExecutor.execute(EtlExecutor.java:227)
        at scriptella.execution.EtlExecutor.execute(EtlExecutor.java:183)
        at scriptella.tools.launcher.EtlLauncher.execute(EtlLauncher.java:276)
        at scriptella.tools.launcher.EtlLauncher.launch(EtlLauncher.java:193)
        at scriptella.tools.launcher.EtlLauncher.main(EtlLauncher.java:321)
    Caused by: scriptella.jdbc.JdbcException: Unable to execute statement. Error statement: 
    select * from TestDB.dbo.GLAccounts. Error codes: [null, 0]
        at scriptella.jdbc.SqlExecutor.statementParsed(SqlExecutor.java:140)
        at scriptella.jdbc.SqlParserBase.handleStatement(SqlParserBase.java:129)
        at scriptella.jdbc.SqlParserBase.parse(SqlParserBase.java:72)
        at scriptella.jdbc.SqlExecutor.execute(SqlExecutor.java:85)
        at scriptella.jdbc.SqlExecutor.execute(SqlExecutor.java:63)
        at scriptella.jdbc.JdbcConnection.executeScript(JdbcConnection.java:211)
        at scriptella.core.ScriptExecutor.execute(ScriptExecutor.java:49)
        at scriptella.core.ContentExecutor.execute(ContentExecutor.java:73)
        at scriptella.core.ElementInterceptor.executeNext(ElementInterceptor.java:56)
        at scriptella.core.StatisticInterceptor.execute(StatisticInterceptor.java:41)
        at scriptella.core.ElementInterceptor.executeNext(ElementInterceptor.java:56)
        at scriptella.core.ExceptionInterceptor.execute(ExceptionInterceptor.java:39)
        ... 6 more
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:886)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3200)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:710)
        at scriptella.jdbc.StatementWrapper$Simple.update(StatementWrapper.java:167)
        at scriptella.jdbc.SqlExecutor.statementParsed(SqlExecutor.java:130)
    ... 17 more

Answer 1

问题出在我的脚本上。下面更正的脚本运行良好...

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <description>Scriptella ETL File Template.</description>
    <properties>
        <include href="etl.properties"/> <!--Load from external properties file-->
    </properties>
    <!-- Connection declarations -->
    <connection driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>

    <!--ETL Queries and Scripts -->
    <query>
        select * from TestDB.dbo.GLAccounts;
        <script>
          insert into TestDB.dbo.test (id) values (?1);
        </script>
    </query>
</etl>