title: Associate allocate cursor result set question
body: <p>I'm trying to run this code I found in this link: <a href="http://www.sqlines.com/db2-to-oracle/process_result_sets_in_procedures" rel="nofollow noreferrer">http://www.sqlines.com/db2-to-oracle/process_result_sets_in_procedures</a></p>
  
  <p>I'm pretty sure when I run my stored procedure, it returns the values I need, but when I put it to my allocate 'cur' and fetch the value, it returns nothing.</p>
  
  <pre><code>CALL MYSTOREDPROC();
  
  ASSOCIATE RESULT SET LOCATOR (result) WITH PROCEDURE MYSTOREDPROC;
  ALLOCATE cur CURSOR FOR RESULT SET result;
  FETCH cur INTO p_name;
  CLOSE cur; 
  </code></pre>
  
  <ol>
  <li>I tried to run it directly to the db2 and it runs successfully meaning it runs without error but I can't confirm if it really returns a result set, </li>
  <li><p>I tried to call it via jdbc, it returns this exception</p>
  
  <blockquote>
    <p>Exception in thread "main" com.ibm.db2.jcc.c.SqlException:
    [ibm][db2][jcc][10100][10910]
    java.sql.CallableStatement.executeQuery() was called but no result set
    was returned. Use java.sql.CallableStatement.executeUpdate() for
    non-query</p>
  </blockquote></li>
  </ol>
  
  <p>My question:</p>
  
  <p>How will I know if the 'result' is really put in 'cur'?</p>
  
  <p>Here is how I call my stored procedure:</p>
  
  <pre><code>String ret = "";
  statement = connection.prepareCall("{CALL SP_CALLER(?)}");
  statement.registerOutParameter(1, java.sql.Types.VARCHAR);
  resultSet = statement.executeQuery();   
  resultSet.next();
  ret = resultSet.getString(1);   
  resultSet.close();
  closeAllConnection();
  return ret;
  </code></pre>
  
  <p>based on the exception, it points out to db2, that it doesn't return any value. </p>
  
  <p>UPDATED</p>
  
  <p>Here's my stored procedure:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE ITRS.SP_CALLER()
  DYNAMIC RESULT SETS 1
  
  BEGIN
  
  DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE v_useraccountid SMALLINT;
  DECLARE v_roleid SMALLINT;
  DECLARE v_userid VARCHAR(20);
  DECLARE v_username VARCHAR(50);
  DECLARE v_lastlogindate TIMESTAMP;
  DECLARE v_deleted SMALLINT;
  
  DECLARE ret CURSOR WITH RETURN FOR
  SELECT * from ITRS.DUMBTABLE where username='JOHN';
  
  
  CALL ITRS.PR_USERACCOUNTGETALL();
  
  ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE ITRS.PR_USERACCOUNTGETALL;
  ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;
  
  ins_loop: 
  LOOP
  FETCH cur INTO v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted;
    IF SQLSTATE &lt;&gt; '00000' THEN LEAVE ins_loop; END IF;
    INSERT INTO ITRS.DUMBTABLE(USERACCOUNTID,ROLEID,USERID,USERNAME,LASTLOGINDATE,DELETED)
   VALUES(v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted);
  END LOOP ins_loop;
  
  CLOSE cur;
  open ret;
  END
  </code></pre>