SQLServerException: The statement did not return a result set when executing SQL

Question

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

and I get:

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

The line is:

st.executeQuery(updateQuery)

Any ideas?

Answer 1

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.

Note: The connection used in this example is java.sql.connection not the com.microsoft.sqlserver.jdbc.SqlServerConnection.

SQL 2000 code

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 code

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);
}

Answer 2

The row you inserted failed therefore there is no identity ? set a breakpoint query is generated in Java copy out the query string and run it in management studio to see what the result is. This might show you what you are doing wrong.

Answer 3

Any option to upgrade the driver? Then you can just use Statement#getGeneratedKeys(). Also see this article: http://msdn.microsoft.com/en-us/library/ms378445%28SQL.90%29.aspx

If that is not an option, then you need to fire the INSERT and SELECT separately after each other on the same connection.