How to return dynamic results sets from a Db2 anonymous block via JDBC?

Question

I'm looking at the Db2 LUW feature "returning result sets from SQL", which seems to work in a similar fashion to what's possible in MySQL, SQL Server by running a simple SELECT from any procedural logic, or in Oracle by using DBMS_SQL.RETURN_RESULT. The following anonymous block seems to be valid:

BEGIN
  DECLARE i INTEGER DEFAULT 1;
  WHILE i < 10 DO
    BEGIN 
      DECLARE cur CURSOR WITH RETURN TO CLIENT FOR SELECT i FROM sysibm.dual;
      OPEN cur;
      SET i = i + 1;
    END;
  END WHILE;
END

Yet, it produces this warning, and no results are being fetched by e.g. DBeaver:

Procedure "BEGIN...END" returned "9" query result sets, which exceeds the defined limit "0".. SQLCODE=464, SQLSTATE=0100E, DRIVER=4.26.14

If this were a procedure, I'd have to declare:

DYNAMIC RESULT SETS n

But how can I declare this in an anonymous block?

Answer 1

You can't return result sets if not from a procedure

See docs

WITH RETURN Specifies that the result table of the cursor is intended to be used as a result set that will be returned from a procedure. WITH RETURN is relevant only if the DECLARE CURSOR statement is contained with the source code for a procedure. In other cases, the precompiler might accept the clause, but it has no effect.

I understand that it may just be an example, but procedural statements are not needed to return integers from 1 to 10, the following query also does :

with loop(k) as (
  values 1
  union all select k+1 from loop where k < 10
)
select * from loop order by k