在 SQL Server 2014 上运行复合查询不返回结果集 [英] Running composite query on SQL server 2014 does not return result set

问题描述

我们有以下代码:

    Connection conn = null;
    String dbURL = "jdbc:sqlserver://DBDerver details here";
    String user = "user name";
    String pass = "password@123";

    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        conn = DriverManager.getConnection(dbURL, user, pass);

        String sql = "update Table1" + "set DBID = DBID+1 where TABLENAME = '" + "Table2" + "';" + "select DBID from Table 1 where TABLENAME = '" + "Table 2" + "'";
                System.out.println("generateId(), SQL = " + sql);
                Statement stmt = conn.createStatement();

                ResultSet rs = stmt.executeQuery(sql);

                int id = -1;
                System.out.println("Result set :->"+rs);
                while(rs.next()) {
                    id = rs.getInt(1);
                }
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

这在 SQL Server 2005 上运行良好.最近我们升级到 SQL Server 2014.我还将 jar 更新为 SQLJDBC4.jar(因为我们使用 JDK6 作为运行时).但是在 SQL Server 2014 上运行它会导致以下异常.

This was running fine on SQL server 2005. Recently we upgraded to SQL server 2014. I have also updated the jar to SQLJDBC4.jar (as we are using JDK6 as runtime). But running this on SQL server 2014 leads to following exception.

异常:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:800)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at com.hcl.JDBCTest$QueryTogether.executeQueryHere(JDBCTest.java:63)
at com.hcl.JDBCTest.main(JDBCTest.java:34)

这可能是因为 2012 返回更新计数 -> 结果集，其中 2005 返回结果集 -> 更新计数，但这只是推测(这里的任何确认都会为我增加奖励).

This maybe because 2012 returns update count -> result set, where 2005 returned result set -> update count, but that is just speculation (any confirmation here will be added bonus for me).

我不想将 executeQuery 更改为 execute/executeUpdate.有没有其他方法可以解决这个异常?另外我不使用存储过程.

I don't want to change the executeQuery to execute/executeUpdate. Is there any other way of getting around this exception? Also i don't use stored procedures.

或者

是否有任何其他 SQL 驱动程序可供我使用并使复合查询在 sql server 2014 上工作

Is there any other SQL driver that i can use and make the composite query work on sql server 2014

推荐答案

我只是将驱动程序更改为 JTD，如下所示，它的工作非常出色

I just changed the driver to JTD as follows and it worked like a charm

连接 conn = null;

Connection conn = null;

    String dbURL = "jdbc:jtds:sqlserver://DataBase Name";
    String user = "username";
    String pass = "password";

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(dbURL, user, pass);
        JDBCTest jt = new JDBCTest();

        String sql = "update Table1 " + "set DBID = DBID+1 where TABLENAME = '" + "Table2'" +"\n"+ 
                "select DBID from Table1 where TABLENAME = '" + "Table2" + "'";
        System.out.println("generateId(), SQL = " + sql);
        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(sql);

        int id = -1;
        System.out.println("Result set :->"+rs);
        while(rs.next()) {
            id = rs.getInt(1);
            System.out.println("id is :-> "+ id);
        }
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

输出:

generateId(), SQL = update SEQUENCE_TABLE set DBID = DBID+1 where TABLENAME = 'TBGP_TABLE'
select DBID from SEQUENCE_TABLE where TABLENAME = 'TBGP_TABLE'
Result set :->net.sourceforge.jtds.jdbc.JtdsResultSet@d42d08
id is :-> 86532

这篇关于在 SQL Server 2014 上运行复合查询不返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！