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

Question

I am using the sqljdbc4.jar version (sqljdbc_2.0).

I do an insert + select back to get an identifier 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.

Line:

 st.executeQuery(updateQuery) 

Any ideas?

Answer 1

Upgrading from SQL 2000 to SQL 2005 and upgrading to JDBC for Microsoft SQL Server 2005 version 1.2. I got the error “The statement did not return the result” when performing the insert followed by SELECT SCOPE_IDENTITY (). ”I solved the problem using executeUpdate () and getGeneratedKeys instead of executeQuery. Here is the code before and after.

Note. The connection used in this example is java.sql.connection, not 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

Wrong line you inserted, so no identifier? to set a breakpoint query in Java, copy the query string and run it in the management studio to see what the result is. This may show you what you are doing wrong.

Answer 3

Any option for updating 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 this is not an option, you need to run INSERT and SELECT one after the other in the same connection.