SQLRPGLE trouble with a cursor in a cursor - nested loop

[Freddythunder]

Hello again,

I am having a strange issue with a nested loop. I have used nested loops in this kind of situation in php/mysql more times than I remember so I'm not sure why this wouldn't work:

/free
exec SQL close c1;
exec SQL call storedproc1();
exec SQL associate result set locator (:rsltRecs) with procedure storedproc1;
exec SQL fetch next from c1 into :openRec;
dow sqlstt='00000'
    custnum = openRec.cust#;
    exec SQL close c2;
    exec SQL call storedproc2(:custnum);
    exec SQL associate result set locater (:mgrsRecs) with procedure storedproc2;
    exec SQL fetch next from c2 into :mgrRec;
    dow sqlstt='00000';
        otherstuff = mgrRec.otherstuff;
        exec SQL fetch next from c2 into :mgrRec;
    enddo;
    exec SQL fetch next from c1 into :openRec;
enddo;
/end-free 

When debugging, everything works until we fetch next from c2. Then the sqlstt = 22002 which has the meaning of "A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a host variable, because no indicator variable is specified."

I've been searching all over the interwebs for a situation like this and there's no easy way to get the info from the second stored procedure into the first.

Thanks

[GLS400]

Re: SQLRPGLE trouble with a cursor in a cursor - nested loop

Hi freddythunder:

Just a guess here.....In your storedproc2 you are doing a join.
The join is returning null values where there is a missing record.
If you correct that data you should be ok

Best of Luck
GLS

[tomliotta]

Re: SQLRPGLE trouble with a cursor in a cursor - nested loop

Is there a question? Are you asking about "sqlstt = 22002"? Or is it about how to "get the info from the second stored procedure into the first"?

[Freddythunder]

Re: SQLRPGLE trouble with a cursor in a cursor - nested loop

I'm sorry, my question was pretty much just how to do a loop in a loop - or a nested loop. GLS was correct. There was a join that no records were coming back to. Thanks!

[B.Hauser]

Re: SQLRPGLE trouble with a cursor in a cursor - nested loop

Then the sqlstt = 22002 which has the meaning of "A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a host variable, because no indicator variable is specified."

Your query returns NULL values that must be trapped. In this way you need to add NULL indicators defined as 5I 0 (for each column returned by the cursor) that must be specified after the Output Parameter.

[COLOR="#FF0000"][B]D MyIndDS         DS
D   Ind01                             5I 0
D   Ind02                             5I 0
...
D   IndN                               5I 0[/B][/COLOR]
/free
   exec SQL close c1;
   exec SQL call storedproc1();
   exec SQL associate result set locator (:rsltRecs) with procedure storedproc1;
   exec SQL fetch next from c1 into :openRec;
   dow sqlstt='00000'
       custnum = openRec.cust#;
       exec SQL close c2;
       exec SQL call storedproc2(:custnum);
       exec SQL associate result set locater (:mgrsRecs) with procedure storedproc2;
       exec SQL fetch next from c2 into :mgrRec[COLOR="#FF0000"][B] :MyIndDS[/B][/COLOR];
       dow sqlstt='00000';
        otherstuff = mgrRec.otherstuff;
        exec SQL fetch next from c2 into :mgrRec[B][COLOR="#FF0000"] :MyIndDS[/COLOR][/B];
      enddo;
      exec SQL fetch next from c1 into :openRec;
   enddo;
/end-free

And ... SQLCODE or SQLSTATE/SQLSTT should be checked after EACH SQL statement and potential errors should be handled explicitly!

Birgitta

[Freddythunder]

Re: SQLRPGLE trouble with a cursor in a cursor - nested loop

Hello, I never got back to this post, but the program is working now. It was indeed null values in a subquery in the stored procedure.

[gwilburn]

Frustrating... Found this post after having the same issue. One of the joins in my SQL select was returning a null value. So I tried to add an indicator DS as above... I'm retrieving 8 fields, so i created a DS to match.

       dcl-ds myIndDS;
         ind01                int(5);
         ind02                int(5);
         ind03                int(5);
         ind04                int(5);
         ind05                int(5);
         ind06                int(5);
         ind07                int(5);
         ind08                int(5);
       end-ds;

       Exec Sql
         Declare itcsr Cursor For
         Select ittrn#, itprt#, ittrnd, itwhs#, iktrnl,
                itqty#, itqoha, iaupcc
         From ICDETLIT
         Left Outer Join ICTRNSIK on ikcom#=itcom# and iktrns=ittrns
         Join ICPRTMIA on iacom#=itcom# and iaprt#=itprt#
         Where itcom#=:pcom# and ittrnd>=:fdate and ittrnd<=:tdate and
               ittscc='T' and itseq#<>0 and itqooa=' '
         and ittrn#='86900025'
         For Read Only;

       Exec Sql
         Open itcsr;

       Dou SqlStt = sqlNoRow;

         Exec Sql
          Fetch Next From itcsr
          Into :Trans :myIndDS;
         if SqlStt <> sqlOK;
           leave;
         endif;

This would not compile SQL0312
Same compiler error with a qualified ds.

After messing around, I changed the DS to an array

       dcl-s sqlInd          int(5) dim(8);

And it compiled (and worked).

For someone who doesn't know a bunch about embedded SQL, the SQL pre-compiler sure seems "very picky" and "not so verbose"...

[Rocky]

And it compiled (and worked).

For someone who doesn't know a bunch about embedded SQL, the SQL pre-compiler sure seems "very picky" and "not so verbose"...

I agree... and in my narrow minded bigoted opinion... seeing as IBM is pushing using SQL so much, including embedded SQL... I would like IBM to make embedded SQL as part of the ILE set rather than a pre-compiler. That would allow the pre-compiler to know EVERYTHING that the compiler does and vice-versa. At the very list, it would be nice to have the pre-compiler to have DBGVIEW to match the options of the compiler that it's calling.