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

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

• GhosT15

  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.

  
  

• JasonWHowell

  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.

  
  

• ARNOLDL

  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

  
  

• Daveyc

  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!

  
  

• Tech G-nome

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

• Mike.R

  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

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