Result sets not working in RPG

[Chris Bratvold]

My RPG code...

         EXEC SQL
           Declare userList CURSOR FOR
             SELECT *
              FROM HFCRSLTS1 hfcData
              LEFT OUTER JOIN JOTSRSLTS1 JOTSData ON
                hfcData.USER_ID = JOTSData.P9USRID
              ORDER BY hfcData.LAST_NAME;

         EXEC SQL
           Open userList;

         EXEC SQL
           SET RESULT SETS WITH RETURN TO CALLER Cursor userList;

        *INLR = *on;
        return;

I've also tried CLIENT instead of CALLER.

My store procedure code...

Create Procedure PGMLIB.CF_PRODUCER_USR_ALL (OUT V_SQLERROR NUMERIC(10) )
    Language RPGLE 
    Parameter Style General 
    Reads SQL Data 
    Dynamic Result Sets 1 
    External name 'PGMLIB/CF_105U3'  
    Specific PGMLIB.CF_PRODUCER_USR_ALL;

If I run the SQL portion of the SQL code alone it does return records. How ever when I call it from the RPG program nothing is returned. What am I missing?

[tomliotta]

How ever when I call it from the RPG program nothing is returned.

When you "call" it? ...Call what?

Is that referring to calling the stored proc? Or does it refer to running the SQL itself inside the stored proc?

How do you know nothing is returned? That is, what does the code look like that calls the stored proc?

[tomholden]

can you check the SQLCOD & SQLSTATE after each SQL statement? sounds like there an issue happening somewhere prior to the SET RESULTS. just a WAG

[Chris Bratvold]

When you "call" it? ...Call what?

Is that referring to calling the stored proc? Or does it refer to running the SQL itself inside the stored proc?

How do you know nothing is returned? That is, what does the code look like that calls the stored proc?

When I say "it" I do mean the stored procedure or the RPG program directly.

The reason I know nothing is returned is because while debugging the program (RDI) I see this in the job log after the EXEC SQL SET RESULT SET command runs. SQLCODE is zero after all SQL statements.

Message ID . . . . . . :   SQL7995       Severity . . . . . . . :   00      
Message type . . . . . :   Completion                                        
Date sent  . . . . . . :   05/04/17      Time sent  . . . . . . :   14:02:18

Message . . . . :   0 result sets processed by the SET RESULT SETS statement.
Cause . . . . . :   0 result sets were processed by the SET RESULTS SETS    
  statement, including 0 array result sets with 0 rows.

Also, when I run the procedure from AQT the program returns "0" rows found.

Finally, the query should return 2 records. I've inserted test logic to perform a fetch next request and data is being returned.

[CRinger400]

I don't see anything obvious. What compile options did you use?

Ringer

[Chris Bratvold]

I don't see anything obvious. What compile options did you use?

Ringer

I did see that I was closing the cursor upon ending the module (change management compile defaults). I fixed that. It now closes when the activation group ends. However, it still doesn't work.

[tomliotta]

Numerous possibilities, but not enough code nor info about environments that succeed or fail shown. Code should include at least the PR and compile options. The stored proc shows OUT parm V_SQLERROR and code shows no reference to it. The CREATE PROCEDURE statement uses SQL naming, but it's not clear how it's called since no CALL is shown. Without more RPG, it's not clear if SQL or system naming is in effect. The potential for multiple tables of the same names in different schemas is an example possibility.

I'd start by making a CALL to the stored proc in STRSQL and then iNav's 'Run SQL Scripts'. By staying fully within IBM tools until reaching points of success, some inconsistencies might be easier to recognize.