RE: sql saga continues

Eugen's correct, you declare the cursor but never open it.

If you were checking SQL return codes in either SQLCODE or SQLSTATE, you'd have 
gotten a message about the problem.

In addition, you certainly don't need a cursor in this application.  Simply do 
the following:

 /exec SQL
c+  select count(*) into :tcount
c+  from invmargin
c+  where ILNINVDATE between :sdate and :edate
c/end-exec


The SELECT INTO form can (should) be used anytime you know that the result set 
will have only one row.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
> Sent: Thursday, July 14, 2005 10: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.
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>