RPG subroutine with SQL

[Huddy]

Hi,

Sorry I'm on a real learning curve with SQL/RPG at the moment so sorry to bother you all.


I have just created the following RPG procedure:

0001.00                                                                 
0002.00      p subgeqiv2       b                   export               
0003.00      d subgeqiv2       pi                                       
0004.00      d  branchCode                    2p 0 const                
0005.00      d  fromCurCode                   2p 0 const                
0006.00      d  fromAmount                   15p 2 const                
0007.00      d  toCurCode                     2p 0 const                
0008.00                                                                 
0009.00      d rtnAmount       s             15p 2                      
0010.00      d baseCode                       2p 0                      
0011.00      d baseAmount      s             15p 2                      
0012.00      d spotRate        s             12  8                      
0013.00      d inverted        s              1                         
0014.00                                                                 
0015.00       /free                                                     
0016.00         if fromCurCde = 0 or fromAmount = 0;                    
0017.00           retAmount = 0;                                        
0018.00         endif;                                                  
0019.00                                                                 
0020.00         if fromCurCde = toCurCde;                        
0021.00           retAmount = fromAmount;                        
0022.00         return retAmount;                                
0023.00         endif;                                           
0024.00                                                          
0025.00         Exec SQL select s99bcc into :baseCode            
0026.00          from sdf99P where s99brc = :branchCode;         
0027.00                                                          
0028.00         Exec SQL select cfsprt into :spotRate            
0029.00          from   cflep where cfccd = :baseCode;           
0030.00         baseAmount = fromAmount / spotrate               
0031.00                                                          
0032.00         Exec SQL select cfsprt into :spotRate            
0033.00          from   cflep where cfccd = :toCurCode;          
0034.00         retAmount = baseAmount * spotrate                
0035.00         return retAmount;                                
0036.00                                                          
0037.00       /end-free                                          
0038.00      p                 e

Then use the following to compile;

CRTSQLRPG PGM(BLBTSTLIB2/SUBGEQIV2) SRCFILE(QRPGLESRC)

but i get the following compile errors:

* QRG2001 Severity: 30 Number: 38
Message . . . . : Form-Type entry invalid or out of sequence.
Specification ignored.
* QRG7023 Severity: 40 Number: 1
Message . . . . : The Compiler cannot determine how program can
terminate. Program will loop.

Everything seems in sequence and I've used the return????

What am I doing wrong?

Thanks

Huddy

[kitvb1]

Re: RPG subroutine with SQL

You would need a NOMAIN H-spec.

[Huddy]

Re: RPG subroutine with SQL

Thanks kitvb1

I've just tried the NOMAIN but get the same error

If I look at the compile listing, the SQLCA DS I specs are inserted before anything..

         1  I*  END OF SQLCA                                                                                       SQL       
         1  I              'QSYS/QSQROUTE'       C         SQLRT                                                   SQL       
         1  I              'QSYS/QSQLOPEN'       C         SQLOPN                                                  SQL       
         1  I              'QSYS/QSQLCLSE'       C         SQLCLS                                                  SQL       
         1  I              'QSYS/QSQLCMIT'       C         SQLCMT                                                  SQL       
       100  H NOMAIN                                                                            11/11/13                     
 * 2001    -*                                                                                                                
       200                                                                                      11/11/13                     
 * 2001    -*                                                                                                                
                                                                                                                            M

Am i using the correct compile command?

[Scott M]

Re: RPG subroutine with SQL

If I prompt option 15 in PDM I get CRTSQLRPGI (note the I on the end) and you will want to specify OBJTYPE(*MODULE) if this is only a procedure and not a program.

[Viking]

Re: RPG subroutine with SQL

I assume your source type is SQLRPGLE and not just RPGLE...?

[Huddy]

Re: RPG subroutine with SQL

Thanks scott M. That helped because i released when pressing f4 i defined the wrong program type. Changed to SQLRPGLE.. I had the RPGSQL


thanks gain. rep updated

@ viking - as above embarrassingly

[Scott Klement]

Re: RPG subroutine with SQL

In addition, you are missing some semicolons on these lines:

baseAmount = fromAmount / spotrate

and

retAmount = baseAmount * spotrate

[Huddy]

Re: RPG subroutine with SQL

thanks

Ok I've correct all the errors




here's the updated source.

0001.00 H NOMAIN                                                
0002.00 H DATFMT(*ISO) debug                                    
0003.00 H Option(*SrcStmt: *NoDebugIO)                          
0004.00                                                         
0005.00 p ConCurAmt       b                                     
0006.00 d ConCurAmt       pi                                    
0007.00 d  branchCode                    2p 0 const             
0008.00 d  fromCurCode                   2p 0 const             
0009.00 d  fromAmount                   15p 2 const             
0010.00 d  toCurCode                     2p 0 const             
0011.00                                                         
0012.00 d rtnAmount       s             15p 2                   
0013.00 d baseCode        s              2p 0                   
0014.00 d baseAmount      s             15p 2                   
0015.00 d spotRate        s             12  8                   
0016.00 d inverted        s              1                      
0017.00                                                         
0018.00  /free                                                  
0019.00    if fromCurCode = 0 or fromAmount = 0;       
0020.00      rtnAmount = 0;                                
0021.00    return rtnAmount;                               
0022.00    endif;                                          
0023.00                                                    
0024.00    if fromCurCode = toCurCode;                     
0025.00      rtnAmount = fromAmount;                       
0026.00    return rtnAmount;                               
0027.00    endif;                                          
0028.00                                                    
0029.00    Exec SQL select s99bcc into :baseCode           
0030.00     from sdf99P where s99brc = :branchCode;        
0031.00                                                    
0032.00    Exec SQL select cfsprt into :spotRate           
0033.00     from  cflep where cfccd = :baseCode;           
0034.00    baseAmount = fromAmount / spotrate;             
0035.00                                                    
0036.00    Exec SQL select cfsprt into :spotRate           
0037.00     from   cflep where cfccd = :toCurCode;         
0038.00    rtnAmount = baseAmount * spotrate;              
0039.00    return rtnAmount;                                        
0040.00                            
0041.00  /end-free                 
0042.00 p ConCurAmt       e

Why do i get the following errors:

*RNF5412 20 3 An operand must not be specified for a procedure that does
not return a value.
*RNF1503 30 1 The prototype for the procedure was not previously defined.

Thanks

[tomliotta]

Re: RPG subroutine with SQL

Why do i get the following errors:

You have lines like this:

0021.00    return rtnAmount;

But your procedure interface doesn't declare a return value:

0006.00 d ConCurAmt       pi

It would need to be something like:

0006.00 d ConCurAmt       pi            15p 2

If you are defining a function that returns a value, the value is returned as a replacement for the function call.

[Huddy]

Re: RPG subroutine with SQL

thanks tomliotta,

Understood.

I changed that line but it made no difference.. I still get the same two compile errors

[Viking]

Re: RPG subroutine with SQL

In this source, you have defined the procedure interface (pi), but not the prototype (pr). You need to define the matching prototype in this source or preferably in a copy member that you bring in.

[tomliotta]

Re: RPG subroutine with SQL

For the RNF1503, you are missing the procedure prototype, i.e., the PR specs. You have the PI, but no PR. What release are you compiling on?

[Huddy]

Re: RPG subroutine with SQL

release 6.1

tbh, i coded this from an example which used the pi but didn't show any prototype specs.

http://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%20%26%20Table%20Functions.pdf

slide 9.


I'm a bit confused now as this is a modue. I thought the pi was a list defining the input parameters (equivalent to the old *entry list). Why would you then need a prototype. I thought that was for the program calling the procedure (equivalent to the old plist)

[Huddy]

Re: RPG subroutine with SQL

Ok I've added a prototype and the program compiles

0001.00 H NOMAIN                                                
0002.00 H DATFMT(*ISO) debug                                    
0003.00 H Option(*SrcStmt: *NoDebugIO)                          
0004.00 d ConCurAmt       pr            15p 2                   
0005.00 d  branchCode                    2p 0 const             
0006.00 d  fromCurCode                   2p 0 const             
0007.00 d  fromAmount                   15p 2 const             
0008.00 d  toCurCode                     2p 0 const             
0009.00                                                         
0010.00 p ConCurAmt       b                   export            
0011.00 d ConCurAmt       pi            15p 2                   
0012.00 d  branchCode                    2p 0 const             
0013.00 d  fromCurCode                   2p 0 const             
0014.00 d  fromAmount                   15p 2 const             
0015.00 d  toCurCode                     2p 0 const

Seems daft to me.. The fileds have been defined in the pi so it's just duplication and twice as much coding as the *entry / plist of legacy RPG

Thanks for all your help, tomliotta extended thanks

[danlong005]

Re: RPG subroutine with SQL

In this example you are correct this is just extra coding. That is why on 7.1 the prototype is no longer needed if the procedure interface is coded in the calling program. It is when the calling program is separate from the program that has the procedure in it that you need the prototype. So say you build this procedure into a module and then a service program. Since the service program is not bound into the calling object you need to know how to call the procedure. That is where the prototype is really used.