SQLServerException:执行SQL时，该语句未返回结果集 [英] SQLServerException: The statement did not return a result set when executing SQL

问题描述

我正在使用sqljdbc4.jar(sqljdbc_2.0)版本.

I'm using using the sqljdbc4.jar (sqljdbc_2.0) version.

我正在执行插入+选择返回以获取如下身份:

I'm executing an insert + a select back to get the identity like this:

BEGIN 
INSERT INTO DateRangeOptions (Description,Code) 
VALUES ('dateRange.quickPick.option.all','ALL');  
SELECT SCOPE_IDENTITY()  
END

我得到:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

该行是:

st.executeQuery(updateQuery)

有什么想法吗?

推荐答案

从SQL 2000升级到SQL 2005，并切换到Microsoft SQL Server 2005 JDBC驱动程序1.2版.我在执行Insert并随后执行SELECT SCOPE_IDENTITY()时收到错误消息该语句未返回结果".我使用executeUpdate()和getGeneratedKeys而不是executeQuery解决了该问题.这是之前和之后的代码.

Upgraded from SQL 2000 to SQL 2005 and switched to Microsoft SQL Server 2005 JDBC Driver version 1.2. I got the error "The statement did not return a result" when executing an Insert followed by SELECT SCOPE_IDENTITY()".I solved the issue using executeUpdate() and getGeneratedKeys instead of executeQuery. Here is the before and after code.

注意:本示例中使用的连接是java.sql.connection，而不是com.microsoft.sqlserver.jdbc.SqlServerConnection.

SQL 2000代码

String  dbURL = "jdbc:sqlserver" + "://" + dbServer + ":" +
                 dbServerPort + ";SelectedMethod=cursor;databaseName="
                           + dbName + ";user=xxx;password=xxx";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
java.sql.Connection connection = DriverManager.getConnection(dbURL);
sql = "insert into Contact (name) values ('ABC'); SELECT SCOPE_IDENTITY()";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
     long id = rs.getLong(1);
     System.out.println("Id=" + id);
}

SQL 2005代码

String  dbURL = "jdbc:sqlserver" + "://" + dbServer + ":" +
                 dbServerPort + ";SelectedMethod=cursor;databaseName="
                           + dbName + ";user=xxx;password=xxx";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
java.sql.Connection connection = DriverManager.getConnection(dbURL);
sql = "insert into Contact (name) values ('ABC'); SELECT SCOPE_IDENTITY()";
PreparedStatement ps = connection.prepareStatement(sql);
ps.executeUpdate();  // do not use execute() here otherwise you may get the error
                     // The statement must be executed before 
                     // any results can be obtained on the next
                     // getGeneratedKeys statement.

ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
     long id = rs.getLong(1);
     System.out.println("Id=" + id);
}

这篇关于SQLServerException:执行SQL时，该语句未返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！