RE: sql saga continues

use interactive sql to see what you get.  If get a zero, the problem is not in 
the program.  Is the data instead.
If you get a count greater than zero, run the program again and check you job 
log, sometime it will tell you the sql errors.  If joblog is not showing 
anything, check the sql return code of the sql datastructure in the program.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
Sent: Thursday, July 14, 2005 9:04 AM
To: RPG Group
Subject: sql saga continues


I spent the better portion of the night last night continuing to work with 
the embedded sql code.....of course its a learning curve and at times 
frustrating as hell but I am learning it slowly....


I have however run into a situation that after 4 hours I have not been able 
to figure out.  I have created a very simple sqlrpgle program that for right 
now is just to get the count of the records and write it to a variable.  
Sounds simple enough, however when running it in bebug mode it seems to 
execute but when I F11 the variable to see its value, its all zeroes.

Here is the pertinent code:

0029.03  ***********************************************************
0039.01  *       STANDALONE VARIABLES                               
0041.00  ***********************************************************
0041.03 DSDATE            S               D   DATFMT(*USA)          
0041.04 DEDATE            S               D   DATFMT(*USA)          
0041.05 DTCOUNT           S              6S 0                       
0041.06  ***********************************************************
0042.00  *       MAINLINE                                           
0043.00  ***********************************************************
0043.01 C     *USA          MOVE      '06/01/2005'  SDATE           
0043.02 C     *USA          MOVE      '06/30/2005'  EDATE           
0043.03 C                   EXSR      COUNTING                      
0043.29 C                   EVAL      *INLR = *ON                   
0043.30 C                   RETURN                                  
0043.31  ***********************************************************
0044.00  *       GET RECORD COUNT AND WRITE TOTAL TO TCOUNT VARIABLE
0045.00 C     COUNTING      BEGSR       
0046.00 C/EXEC SQL                      
0047.00 C+    DECLARE COUNTCURSOR CURSOR
0048.00 C+    FOR SELECT COUNT(*) FROM  
0049.00 C+    INVMARGIN WHERE           
0050.00 C+    ILNINVDATE >= :SDATE AND  
0051.00 C+    ILNINVDATE <= :EDATE      
0052.00 C/END-EXEC                      
0053.00 C/EXEC SQL  
0054.00 C+ FETCH COUNTCURSOR     
0055.00 C+ INTO :TCOUNT          
0056.00 C/END-EXEC                                   
0057.00 C                   ENDSR
0058.00  ************************

I did not see any problem with the cursor or the fetch statement, and since 
the variable tcount is declared it should work....I also noticed when I F11 
on the :tcount it says that it cannot find the variable...which I thought 
was strange but maybe its my brain having one of its moments.

Any helpful suggestions would be appreciated.