AS/400 Embedded SQL, RPGLE free format, compilation

715 pts.
Tags:
AS 400
PGM
RPGLE
SQL
1. My Problem:
I am not able to compile a RPGLE free format PGM with embedded SQL.
2. My simple basic code:
exec sql
select RAGNA
from RPFAGE ;
*inlr = *on ;
 (RPGAGE is the PF name and RAGNA is one of the fields in PF RPGAGE PF contains 100 records)
3, Member type is SQLRPGLE
4. Compile command:
CRTSQLRPGI OBJ(RES0101) SRCFILE(QRSUMSRC)
5. SQL pre-compiler error:
SQL0053 10 No SQL statements found.
6. RPG compilation error:
*RNF5347 30 1 An assignment operator is expected with the EVAL operation.
*RNF7030 30 48 000200 The name or indicator EXEC is not defined.
*RNF7030 30 48 000200 The name or indicator SQL is not defined.
I would appreciate help in getting the compilation thru and run the PGM.
Thanks!


Software/Hardware used:
AS400 OS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Program doesn’t know where to move the result Your Sql query should be as below.

Exec Sql 
select RAGNA into :wRagna
from RPFAGE ; 

wRagna should be declared as Work variable with same attributes as RAGNA.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • anandx
    Correction in point 2.
    (RPFAGE is the PF name and RAGNA is one of the fields in PF RPFAGE PF contains 100 records)
    715 pointsBadges:
    report
  • deepu9321
    If PF contains 100 records, you cannot just use the simple SQL to return the data into a variable.

    You should create a cursor and fetch the records in loop or should create Array (or multi occurance DS if multiple fields).

    EVAL doesn't seem to there in the code posted. Posting your full code helps.
    4,955 pointsBadges:
    report
  • anandx
    Thanks for your reply, wRagna:
    I have already tried 'into' clause and came across the same pre-compiler error, namely:
    SQL0053  10          No SQL statements found
    I have attached two codes, exact SQL commands in both, one in COBOL that compiles fine (and runs too)RPGLE, free:
    RPGLE which doesn't compile, to start with.
    I am aware 'end-exec' is NOT required in free format
    I've used crtsqlrpgi for rpg and crtsqlcbli for COBOL
    What am I missing here, I've no clue. Any idea where I'm going wrong?
    Thanks

    RPG:
    ctl-opt  option  ( *srcStmt:  *nodebugIo: *nounref ) ;
    ctl-opt  dftactgrp ( *no ) ;                          
                                                          
    dcl-s  host_RCOCD   char(01) ;                        
    dcl-s  host_RAGGR   char(01) ;                        
    dcl-s  host_RAGNA   char(07) ;                        
    dcl-s  host_RAGLL   zoned(3:0) ;                      
                                                          
    exec    sql                                              
    select  RAGNA, RAGLL                                  
    into    :host_RAGNA, :host_RAGLL                      
    from    RPFAGE                                        
    where   RCOCD = :host_RCOCD and :host_RAGGR = 'C' ;   
    
    *inlr = *on ;                                                                  
    --------------------------------------------------------
    COBOL
    
     working-storage section.   
                                
        exec SQL                
        include SQLCA           
        end-exec.               
    
    01  host_variables.                       
        05  host_RCOCD   pic x(01) value "R". 
        05  host_RAGGR   pic x(01) value "C". 
        05  host_RAGNA   pic x(07).           
        05  host_RAGLL   pic S9(03).          
                                              
    procedure  division.                      
    mainprocessing section.                   
    mainprocedure.
       exec  sql                                                
          select  RCOCD, RAGGR, RAGNA, RAGLL                 
          into    :host_RCOCD, :host_RAGGR, :host_RAGNA,     
                  :host_RAGLL                                
          from    RPFAGE                                     
          where   RCOCD = :host_RCOCD                        
    end-exec.                                                
    display  host_RCOCD, host_RAGGR, host_RAGNA, host_RAGLL. 
    stop run.                                                 
    ------------------------------------------------------------ and the other 
    715 pointsBadges:
    report
  • TheRealRaven
    In order to eliminate one possibility, what happens if you compile the same source as a non-SQL program?

    The SQL precompiler isn't always good at recognizing ILE RPG syntax or formatting problems that don't relate directly to the SQL. If the RPG compiler sees anything wrong (other than the SQL source lines), other errors should be reported. If not, we'll know more.

    Neither the RPG nor the COBOL will compile if you posted exact source. That makes it harder to be sure of anything from here. But the RPG compiler will tell us about how the source is formatted on your system.

    Compile with CRTBNDRPG, or as a *MODULE with CRTRPGMOD, or change the source type to RPGLE and use PDM to compile... It doesn't really matter as long as the SQL precompiler is removed from the operation for a test.
    24,415 pointsBadges:
    report
  • anandx
    deepu9321:
    1. PF contains 100 records, that's true. However, the RPG code that I have used picks up a single record. Pl see the where clause
    "where   RCOCD = :host_RCOCD and RAGGR = :host_RAGGR ;"
    2. Fetch in this scenario, I think, is not required as only a single row is returned
    3. I'm not sure I understand your point on EVAL. What I posted earlier is full RPG code, top to bottom, nothing else is missed out.

    I have re-posted the codes here.
    (A) COBOL code which returns a single row
    (B) interactive sql
    (C) COBOL code with fetch cursor
    (D) RPF free - FULL CODE
    (A) and (C) compile without error and produce expected result satisfactorily and so does (B)
    (D) doesn't even compile, it has the pre-comiple error:
    SQL0053  10  No SQL statements found

    No matter how correct is SQL statements in RPG, with or without fetch cursor, the compile fails at very first step of pre-compilation. It doesn't even recognize SQL by saying 'No SQL statements found'.
    I'm sure, I am omitting something in the code OUTSIDE of exec SQL block that makes the pgm fail in compilation

    If you have no objection in  sharing a simple RPGLE free format code, may be about 20-30 lines max, with appropriate compilation command ( I have used CRTSQLRPGI) that run successfully that I can simulate, I'll be much obliged
    Thanks


    715 pointsBadges:
    report
  • anandx
    (A) COBOL code which returns a single row:
    
    01  host_variables.                      
        05  host_RCOCD   pic x(01) value "R".
        05  host_RAGGR   pic x(01) value "C".
        05  host_RAGNA   pic x(07).          
        05  host_RAGLL   pic S9(03).         
    exec  sql                                               
          select  RCOCD, RAGGR, RAGNA, RAGLL                
          into    :host_RCOCD, :host_RAGGR, :host_RAGNA,    
                  :host_RAGLL                               
          from    RPFAGE                                    
          where   RCOCD = :host_RCOCD and                   
                  RAGGR = :host_RAGGR                       
    end-exec.                                               
                                                            
    display  host_RCOCD, host_RAGGR, host_RAGNA, host_RAGLL.
    
    COBOL displayed result: RCChild  002
    ---------------------------------------------------------
    (B) interactive sql:
    
    Query:
    
    select  RAGNA, RAGLL               
    from    RPFAGE                     
    where   RCOCD = 'R' and RAGGR = 'C'
    (SELECT statement run complete.)
    
    Query result:
    ....+....1....
    RAGNA    RAGLL
    Child       2
    -----------------------------------------------------------    
    (C) COBOL with fetch cursor:
    
    exec sql                                               
         declare  cursor1 cursor for                       
         select   RCOCD, RTRCD, RCHCL, RAGGR, RPRPC        
         from     RPFPBASE                                 
         where    RTRCD  <  :wsTRCD   and  RCOCD = :wsCOCD 
    end-exec.                                              
    
    exec sql                                               
         open cursor1                                      
    end-exec.                                              
    
    exec sql                                               
         whenever not found                                
         go to close-cursor1                               
    end-exec.                                              
    
    perform until sqlCode = 100                             
    
    exec sql                                                
         fetch  cursor1                                     
         into   :wsCOCD, :wsTRCD, :wsCHCL, :wsAGGR, :wsPRPC 
    end-exec                                                
    
    display  wsTRCD, wsCHCL, " ", wsAGGR, " ", wsPRPC       
                                    
    end-perform.                                            
    
    close-cursor1.           
        exec sql             
              close cursor1  
        end-exec.            
        stop run.            
    -----------------------------------------------------------------
    (D) RPF free - FULL CODE
     
    ctl-opt  option  ( *srcStmt:  *nodebugIo: *nounref ) ;   
    ctl-opt  dftactgrp ( *no ) ;                             
                                                             
    dcl-s  host_RCOCD   char(01) inz ('R') ;                 
    dcl-s  host_RAGGR   char(01) inz ('C') ;                 
    dcl-s  host_RAGNA   char(07) ;                           
    dcl-s  host_RAGLL   zoned(3:0) ;                         
                                                             
    exec sql                                                 
    select  RAGNA, RAGLL                                     
    into    :host_RAGNA, :host_RAGLL                         
    from    RPFAGE                                           
    where   RCOCD = :host_RCOCD and RAGGR = :host_RAGGR ;    
    *inlr = *on ;                                            
    ---------------------------------------------------------------------
    
    715 pointsBadges:
    report
  • anandx
    Thanks TheRealRaven for your suggestion on alternatives

    1. This embedded RPG pgm of mine doesn't make a static procedure call or a dynamic pgm call, so went for CRTBNDRPG as you suggested, but the compiler won't budge and is adamant. I reproduce his/her woe:

    001000       exec sql                                                        ======>                         aaa     
    *RNF5347 30 a    001000  An assignment operator is expected with the EVAL operation.    
    001100       select  RAGNA, RAGLL                                            001200       into    :host_RAGNA, :host_RAGLL                                
    001300       from    RPFAGE                                                  001400       where   RCOCD = :host_RCOCD and RAGGR = :host_RAGGR ;           001500       *inlr = *on ;                                                  *RNF7030 30   001000  The name or indicator EXEC is not defined.  
    *RNF7030 30    001000  The name or indicator SQL is not defined.              
    To my little knowledge, SQL embedded RPG needs either CRTSQLRPG or CRTSQLRPGI, no other way. The later gives a choice of getting a *module or a *pgm (main) or a *SRVPGM object where the former doesn't give that choice. Going CRTBNDxx or CRTxxxMOD is not resolving

    2. Does the source type make a difference for the compiler? If I choose 'SQLRPGLE' or say, 'TEXT' or 'RPGLE' as the source member type, it makes a difference inside SEU not while compiling, I am using 'TXT' for some members and 'RPGLE' for some members and notice both compile successfully so long as there is no error in the code

    3. Finally where is the hiccup? The code or the OS environment(mine runs under V7R1) or the command for compilation or declarative or ctl-opt or somewhere else I am blind to? Or embedded sql doesn't go with free RPGLE at all (I must be stupid even to think that way though). 
    4 Can I borrow some working code in embedded 'RPGLE free format' with suitable command to compile?

    715 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: