dt70
I'm trying to migrate to the 2005 JDBC driver against SQL Server 2000, but I'm getting the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. This is similar problem to a past thread: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=413019&SiteID=1
The difference is that my stored procedure uses cursors. This same stored procedure works fine with the 2000 JDBC driver.
Does anybody have any ideas how to resolve this problem
Re: SQLServerException: The statement did not return a result set when sp uses cursors
David Olix - MSFT
Hi,
Could you post some additional detail, including:
Driver version you are using (v1.0, v1.1, v1.2 CTP), preferably output from DatabaseMetaData.getDriverVersion().
A snippet of code you are using to call the stored procedure and process its results
The stored procedure definition
The JDBC driver manages cursors through the ResultSet APIs, so you should not need to create and return cursors from your stored procedure. Are you trying to return a cursor from the stored procedure, or just use one in the stored procedure
Thank you,
--David Olix [MSFT]
Re: SQLServerException: The statement did not return a result set when sp uses cursors
dt70
Hi David,
Thanks for replying. The 2005 jdbc driver version is 1.2.2323.101
I'm using Java 6 u1 and sql server 2000 database.
My stored procedure uses a cursor but does not return one. Another test I did was to create a stored procedure that creates a temporary table, inserts a couple of rows and then queries the temporary table. The result set from the query is what I should be getting back in this case, but I get the same exception as when I use a cursor in the stored procedure.
Example client code:
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
System.out.println("Loading driver...");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc
qlserver://localhost;User=xxxx;Password=xxxx;DatabaseName=xxx");
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("Driver version: " + dmd.getDriverVersion());
System.out.println("Preparing call...");
stmt = conn.prepareCall("{call TestTempTable()}");
// call stored procedure
System.out.println("Calling stored procedure...");
stmt.executeQuery();
System.out.println("Returned from stored procedure...");
System.out.println("Processing result set...");
while (rs.next()) {
System.out.print(rs.getInt(1));
}
}
catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
catch (ClassNotFoundException ce) {
ce.printStackTrace();
}
finally {
if (rs != null) {
try { rs.close(); } catch (SQLException sqlEx) {}
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException sqlEx) {}
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException sqlEx) {}
conn = null;
}
}
Example stored procedure:
CREATE PROCEDURE TestTempTable AS
/* create temp table */
if object_id('tempdb..#ids') is not null
drop table #ids
create table #ids (
id int
)
insert into #ids (id) values (1)
insert into #ids (id) values (2)
select * from #ids
drop table #ids
GO
Stack trace:
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at sqlserver.Test2005JDBCDriver.run(Test2005JDBCDriver.java:48)
at sqlserver.Test2005JDBCDriver.main(Test2005JDBCDriver.java:22)
Hi David,
Thanks for replying. The 2005 jdbc driver version is 1.2.2323.101
I'm using Java 6 u1 and sql server 2000 database.
My stored procedure uses a cursor but does not return one. Another test I did was to create a stored procedure that creates a temporary table, inserts a couple of rows and then queries the temporary table. The result set from the query is what I should be getting back in this case, but I get the same exception as when I use a cursor in the stored procedure.
Example client code:
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
System.out.println("Loading driver...");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc
qlserver://localhost;User=xxxx;Password=xxxx;DatabaseName=xxx");DatabaseMetaData dmd = conn.getMetaData();
System.out.println("Driver version: " + dmd.getDriverVersion());
System.out.println("Preparing call...");
stmt = conn.prepareCall("{call TestTempTable()}");
// call stored procedure
System.out.println("Calling stored procedure...");
stmt.executeQuery();
System.out.println("Returned from stored procedure...");
System.out.println("Processing result set...");
while (rs.next()) {
System.out.print(rs.getInt(1));
}
}
catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
catch (ClassNotFoundException ce) {
ce.printStackTrace();
}
finally {
if (rs != null) {
try { rs.close(); } catch (SQLException sqlEx) {}
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException sqlEx) {}
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException sqlEx) {}
conn = null;
}
}
Example stored procedure:
CREATE PROCEDURE TestTempTable AS
/* create temp table */
if object_id('tempdb..#ids') is not null
drop table #ids
create table #ids (
id int
)
insert into #ids (id) values (1)
insert into #ids (id) values (2)
select * from #ids
drop table #ids
GO
Stack trace:
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at sqlserver.Test2005JDBCDriver.run(Test2005JDBCDriver.java:48)
at sqlserver.Test2005JDBCDriver.main(Test2005JDBCDriver.java:22)
Re: SQLServerException: The statement did not return a result set when sp uses cursors
dt70
After further testing, it appears that creating a temporary table in the stored procedure is causing the SQLException and not the use of cursors within the stored procedure.
After further testing, it appears that creating a temporary table in the stored procedure is causing the SQLException and not the use of cursors within the stored procedure.
Re: SQLServerException: The statement did not return a result set when sp uses cursors
dt70
The same SQLException is thrown when the stored procedure uses a Table variable.
Example stored procedure:
declare @TableVar table (
customer varchar(50) NOT NULL
)
insert into @TableVar (customer) values ('Snoopy')
insert into @TableVar (customer) values ('Charlie')
select * from @TableVar
The same SQLException is thrown when the stored procedure uses a Table variable.
Example stored procedure:
declare @TableVar table (
customer varchar(50) NOT NULL
)
insert into @TableVar (customer) values ('Snoopy')
insert into @TableVar (customer) values ('Charlie')
select * from @TableVar
Re: SQLServerException: The statement did not return a result set when sp uses cursors
David Olix - MSFT
Hi,
Thank you for your thorough investigation and for providing a detailed repro.
Looking at your stored procedure, though, there are three results that you need to process: one update count for each INSERT, and a result set for the SELECT. In order to process all of the results from a statement that produces multiple results, you need to use Statement.execute() (not executeQuery()) in conjunction with Statement.getMoreResults(), Statement.getResultSet() and Statement.getUpdateCount(). You should only use Statement.executeQuery() with statements that produce a single ResultSet as their only result.
Hope this helps,
--David Olix [MSFT]
Re: SQLServerException: The statement did not return a result set when sp uses cursors
dt70
Thanks David. It did help, but I do have a couple of questions. You said that I should get three results from test stored procedure, but I'm only getting two, one update count and one result set. Any ideas why
Why am I able to use only the Statement.executeQuery() with the 2000 JDBC driver and get the result set without using getMoreResults(), etc What's changed The driver or JDBC spec
Thanks.
Thanks David. It did help, but I do have a couple of questions. You said that I should get three results from test stored procedure, but I'm only getting two, one update count and one result set. Any ideas why
Why am I able to use only the Statement.executeQuery() with the 2000 JDBC driver and get the result set without using getMoreResults(), etc What's changed The driver or JDBC spec
Thanks.
Re: SQLServerException: The statement did not return a result set when sp uses cursors
David Olix - MSFT
Hi,
If you're using the v1.2 CTP, which it looks like you are from your earlier posts, then you're hitting a bug where PreparedStatement.execute only returns the last update count in a series unless you set the lastUpdateCount connection property to false (it's default value is true). This is targeted to be fixed before the driver is released later this fall, possibly as early as the next CTP.
The SQL Server 2000 driver and the SQL Server 2005 driver (which also works with SQL Server 2000) are different products with different code bases. So there are some significant differences in behavior.
The JDBC spec has been consistent all along as far as I know. But quoting from https://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/statement.html :
"The method executeQuery is designed for statements that produce a single result set, such as SELECT statements.
[...]
The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two."
Regards,
--David Olix [MSFT]
Re: SQLServerException: The statement did not return a result set when sp uses cursors
Chris98031
There is another more subtle cause for this, if you aren't intending to send back multiple resultsets but are still getting the exception.
When you insert into a temp table, the number of rows affected is reported back to the client unless you use SET NOCOUNT ON. This can cause the exception when you least expect it.
I would suggest running your procedure from the development tool to make sure you're not getting this sort of message.
Happy Coding,