Calling RPG programs from externally..

[Huddy]

Is there a way I can call existing RPG from on external servers?

We have a reporting application and it would be handy if we could call some of our proven routines.

I was think of creating them as *modules and creating a service program.. but how do i then access these?

thanks

[tomliotta]

Re: Calling RPG programs from externally..

DB2 stored procedures can provide a fairly standard way of calling programmed functions. There are a few alternatives, but I'd see if stored procs can work for the need first.

[Huddy]

Re: Calling RPG programs from externally..

hmm thanks

tbh, I was hoping to use existing routines rather than re-creating new ones from scratch. Some of the RPG programs we have are legacy routines that are complicated with key business logic so don't really want to go through re testing them.

[B.Hauser]

Re: Calling RPG programs from externally..

Why do you think you have to rewrite everything?
Existing (RPG/Cobol/CL) programs can be registered as (SQL) Stored Procedures.
Existing RPG/Cobol procedures can be registered as either (SQL) Stored Procedures (if there is no return value) or as User Defined Function (UDF) if there is a return value.
After those programs and procedures can be called from any interface where SQL statements can be processed.

Birgitta

[B.Hauser]

Re: Calling RPG programs from externally..

Why do you think you have to rewrite everything?
Existing (RPG/Cobol/CL) programs can be registered as (SQL) Stored Procedures.
Existing RPG/Cobol procedures can be registered as either (SQL) Stored Procedures (if there is no return value) or as User Defined Function (UDF) if there is a return value.
After those programs and procedures can be called from any interface where SQL statements can be processed.

Birgitta

[dhanuxp]

Re: Calling RPG programs from externally..

or you can convert old program to webservice !

[Huddy]

Re: Calling RPG programs from externally..

Why do you think you have to rewrite everything?
Existing (RPG/Cobol/CL) programs can be registered as (SQL) Stored Procedures.
Existing RPG/Cobol procedures can be registered as either (SQL) Stored Procedures (if there is no return value) or as User Defined Function (UDF) if there is a return value.
After those programs and procedures can be called from any interface where SQL statements can be processed.

Birgitta

I was assuming that i'd have to create a stored procedure but this is why i'm asking.

What's a User Defined Function and who do i go about doing that? I thought a service program could be called externally?

edit another dup post.. Looks like you have some vBulletin sync problems

[tomliotta]

Re: Calling RPG programs from externally..

I was assuming that i'd have to create a stored procedure but this is why i'm asking.

You can have either SQL stored procedures or 'external' stored procedures. An external procedure is essentially a program that has been registered to DB2 as a stored procedure. See the Defining an external procedure topic in the Information Center for an introduction.

[danlong005]

Re: Calling RPG programs from externally..

A UDF(User Defined Function) is a function such as MAX() that you created. You could do the following to build them.

CREATE FUNCTION DECRYPT (ENCRYPTEDTEXT CHAR(50))
RETURNS CHAR(50)
LANGUAGE RPGLE
DETERMINISTIC
NO SQL
EXTERNAL NAME 'MYLIBRARY/MYDECRYPTSRV(DECRYPT)'
PARAMETER STYLE GENERAL
PROGRAM TYPE SUB

In this case though. I think you will want to use a stored procedure. You can do that almost the same way as this UDF is defined.

CREATE PROCEDURE DECRYPT
(
IN ENCRYPTEDTEXT CHAR(50)
, OUT CLEARTEXT CHAR(50)
)
LANGUAGE RPGLE
SPECIFIC DECRYPTE
EXTERNAL NAME MYLIBRARY/MYDECRYPTSRV(DECRYPT)
PARAMETER STYLE GENERAL

[Huddy]

Re: Calling RPG programs from externally..

Thanks to everyone who suggested UDF.

It appears from some reading up I can use this even with older PM code which would be great.

I've created a service program on an old RPGLE program we have which has the following parameters:

branchCode decimal(2, 0)
inCurrencyCode decimal(2, 0)
inCurrencyAmount decimal(15, 2)
outCurrencyCode decimal(2, 0)
outAmount decimal(15, 2)

the outAmount is the returned value


here's the source for the create function:

0001.00 CREATE FUNCTION GetGeqiv (                           
0002.00  branchCode decimal (2, 0),                          
0003.00  inCurrencyCode decimal (2, 0),                      
0004.00  inCurrencyAmount decimal (15,2),                    
0005.00  outCurrencyCode decimal (2, 0)                      
0006.00  )                                                   
0007.00  RETURNS decimal (15, 2)                             
0008.00 LANGUAGE RPGLE                                       
0009.00 NOT DETERMINISTIC                                    
0010.00 NO SQL                                               
0011.00 EXTERNAL NAME SRVPGM('BLBTSTLIB5/GEQIV2')            
0012.00 PARAMETER STYLE GENERAL

1. How do I added the parameters to the extrnal name call?

2. the sRUNSQLSTM comes back with the follwoing error:

SQL0491 30 1 Position 1 Clause not correct for procedure or function.
SQL0491 30 1 Position 1 Clause not correct for procedure or function.
SQL0487 30 1 Position 1 SQL statements not allowed.


I've got no idea what any of that means.

Any idea?

Thanks in advance.

[RayGilliesJones]

Re: Calling RPG programs from externally..

Some of your SQL definitions dont match your description, for example

branchCode decimal(15,2)

Is that just a typo? If not, you'll have problems if you pass a decimal 2,0 instead

[Huddy]

Re: Calling RPG programs from externally..

good spot cheers.. .all corrected but still get the same errors though.

[Huddy]

Re: Calling RPG programs from externally..

Hi,

I manged to get the function created.

0001.00 CREATE FUNCTION  GetCurEqiv (                
0002.00  branchCode decimal (2,0),                   
0003.00  fromCurrencyCode dec (2,0),                 
0004.00  fromCurrencyAmount dec (15,2),              
0005.00  toCurrencyCode dec (2,0)                    
0006.00  )                                           
0007.00  returns decimal (15, 2)                     
0008.00 EXTERNAL NAME BLBSYSLIB/GEQIV2               
0009.00 LANGUAGE RPGLE                               
0010.00 PROGRAM TYPE MAIN

To test i ran the following sql script from iNav:

select QGPL.getcureqiv(0, 50, 12345.52, 4) from sysibm.sysdummy1

but i get the following error:

QL State: 38501
Vendor Code: -443
Message: [SQL0443] *N Cause . . . . . : Either a trigger program, external procedure, or external function detected and returned an error to SQL. If the error occurred in a trigger program, the trigger was on table GEQIV2 in schema BLBSYSLIB. If the error occurred in an external procedure or function, the external name is GEQIV2 in schema BLBSYSLIB. The associated text is *N. If the error occurred in a trigger program, the associated text is the type of trigger program. If the error occurred in an external function, the associated text is the text of the error message returned from the external function. Recovery . . . : Refer to the joblog for more information regarding the detected error. Correct the error and try the request again

Any ideas?

GEQIV2 in BLBSYSLIB is an old OPM and working program.

0112.00      C***************************************************************** 
0113.00      C* PARAMETER LIST.                                                 
0114.00      C           *ENTRY    PLIST                                        
0115.00      C                     PARM           BRCODE  20                    
0116.00      C                     PARM           CC1     20                    
0117.00      C                     PARM           AMT1   152                    
0118.00      C                     PARM           CC2     20                    
0119.00      C                     PARM           AMT2   152

In the joblog i get the following:

Message ID . . . . . . : MCH0802 Severity . . . . . . . : 40
Message type . . . . . : Escape
Date sent . . . . . . : 12/11/13 Time sent . . . . . . : 10:46:33

Message . . . . : Total parameters passed does not match number required.
Cause . . . . . : Program QQQSVUSR attempted to call program GEQIV2 with 14
parameters. Program GEQIV2 expects a minimum of 0 and a maximum of 5
parameters.
Recovery . . . : Use the Display Program (DSPPGM) command to determine the
correct number of parameters to pass.

Why is GEQIV2 expecting 14 parameters??????

[DeadManWalks]

Re: Calling RPG programs from externally..

In running a test you have to cast each parm.

This is an example;

select   getfreightstandard(                
         cast( 913100410 as dec(9,0))     , 
         cast(3439       as dec(6,0))     , 
         cast(993        as dec(5,0))     , 
         cast(0          as dec(2,0))     , 
         cast('V'        as char(1))        
                                          ) 
                                            
From SYSIBM/SYSDUMMY1

If I dont do the cast then I get the same errors as you.

[RayGilliesJones]

Re: Calling RPG programs from externally..

I believe SQL is passing additional parameters because you are using a program rather than a procedure.

A little googling turns up this gem from Scott Klement - http://www.scottklement.com/presenta...0Functions.pdf