jdbc sql error: statement did not return a result set

Question

I have two stored procedures as follows:

create stored procedure p1
as
    select * from table1 where datediff(day, table1.[date], getdate())

create stored procedure p2
as
   declare @t1 table(
     ref varchar(20)
   )
      insert into @t1 select * from table1 where ref = 'some ref'
      declare @t2 table(
      fname varchar(20),
      lname varchar(20),
      email varchar(1000)
  )
  declare @len int = (select count(ref) from @t1)
  while @len > 0
  begin
  declare @value varchar(20)  = (select top 1 ref from @t1)
  insert into @t2 select * from table2 where ref = @ref
  delete from @t1
  where ref = @value
  set @len = (select count(ref) from @t1)
  end
  select * from @t2

Java code

 ....
 String query = "Execute [p2]";

 try(CallableStatement cstmt = conn.prepareCall(query);
     ResultSet rs = cstmt.executeQuery()){
        ... some code
    }

The table variable @t1 hold select result from a table 'table1'

The variable @len hold the number of rows in @t1

Using @len > 0 as condition in while loop, I want to select records from another table 'table2' the table variable @t2 hold the select records from 'table2'

The delete statement removes value from @t1 @len set to new number of rows in @t1 the last statement return all the records store in @t2

The first procedure works fine, but the second procedure works only in SQL Server.

I get this an error message in my java application

statement did not return a resultset

I want this to return a result set with the select statement I have at the end of the query.

Please is there a way around this?

Solution 1

Your [p2] stored procedure needs to include SET NOCOUNT ON right at the beginning to suppress the "n rows affected" counts so JDBC doesn't get confused as to what it should put into the ResultSet:

CREATE PROCEDURE p2
AS

SET NOCOUNT ON;

declare @t1 table(
    ref varchar(20)
)

-- ... and so on

For more information on SET NOCOUNT see

SET NOCOUNT (Transact-SQL)

For more information on precisely what gets returned from a stored procedure call, see

How to get everything back from a stored procedure using JDBC

Solution 2

use method "execute" instead of "executeQuery".

Comments

[Jeff Fol]

For anyone who got the error: "org.pentaho.di.core.exception.KettleDatabaseException: The statement did not return a result set." from PDI this answer also resolves that problem when using JDBC(Native) driver.