RE: sql saga continues

Doug,

Unless I am missing something, when you code an SQL cursor, you
typically need an "open cursor" statement before you fetch from it (as
well as a "close cursor" statement when you are done).

That being said, it is not necessary to code a cursor when you expect
only one result row (such as returning a simple count).  No fetch, open
or close statement is necessary, either.  Like so...

0046.00 C/EXEC SQL                      
0048.00 C+    SELECT COUNT(*)
0048.10 C+    INTO :TCOUNT  
0049.00 C+    FROM INVMARGIN           
0050.00 C+    WHERE ILNINVDATE >= :SDATE AND  
0051.00 C+          ILNINVDATE <= :EDATE      
0052.00 C/END-EXEC                      

Hope that helps

Brian Price
Sr. Programmer/Analyst
Jefferson Regional Medical Center
870.541.8770
priceb@xxxxxxxx
 

-----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.