"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.