RE: UDTF/Stored Procedure & Activation Groups

Joel,

The example is run sql in Ops Nav. I did this to test the stored procedure NewScafChg which calls program NewScaf3. Normally it is called from one of our Java apps.

The stored procedure runs a program that creates parm 7 & 8, which is a date and time stamp, and returns it to the calling Java program. These are part of the key fields to the file that program NewScaf3 populates. Then Crystal Reports queries the file using the timestamp and one other field.

The UDTF runs the same program NewScaf3 but it is called from Business Objects.

The Stored Procedure:

-- Generate SQL
-- Version: V6R1M0 080215
-- Generated on: 07/27/12 08:49:54
-- Relational Database: ERPSERV
-- Standards Option: DB2 for i
CREATE PROCEDURE ERSDS.NEWSCAFCHG (
INOUT PARMRCID CHAR(19) ,
INOUT PARMEXTRA1 CHAR(19) ,
INOUT PARMEXTRA2 CHAR(19) ,
INOUT PARMEXTRA3 CHAR(19) ,
INOUT PARMFROMDATE CHAR(10) ,
INOUT PARMTODATE CHAR(10) ,
INOUT PARMCYYMMDD CHAR(7) ,
INOUT PARMHHMMSSMMM CHAR(9) )
LANGUAGE RPGLE
SPECIFIC ERSDS.NEWSCAFCHG
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'ERSDS/NEWSCAF3'
PARAMETER STYLE GENERAL ;

COMMENT ON SPECIFIC PROCEDURE ERSDS.NEWSCAFCHG
IS 'Extract eRSDS Billing Scaffold Charges & Daily Rental & Cost To Date' ;

Hope that helps explain it a little more.

Doug


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joep Beckeringh
Sent: Friday, July 27, 2012 1:16 PM
To: RPG programming on the IBM i / System i
Subject: Re: UDTF/Stored Procedure & Activation Groups

Doug,

You say that you call program newscafchg, but what you show us suggests that you build a string with the CALL command and probably run it through QCMDEXC of system(). You say that newscafchg returns information in parms 7 and 8, but you pass two empty literals. So the question is, why don't you use CALLP and pass parameters to the program?

Joep Beckeringh



Op 27-07-12 19:08, Lewis, Doug schreef:

Hi All,

I have a couple of question on activation groups.

I have an UDTF that calls a procedure in a service program. There is a
program call in the procedure which failed with a - return from main() has occurred in a program that runs in a *NEW Activation Group. The program did run but erred on the return. So I changed to a named activation group and it worked.

So my first question is why did it fail with the *new activation group?

Secondly, this program is also called from a stored procedure. When I changed to the named activation group either the calling Java program or Crystal Reports erred with a parameter mismatch error. The java guys were not sure which threw the exception. Even though calling this stored procedure in Ops Nav showed the parameters returning correctly albeit with warnings.


The program populates a file and returns the key in parm 7 & 8. All parms are char.

Named Act Grp:

call newscafchg('615', '','', '', '2012-06-01', '2012-06-30', '', '')

Return Code = 0

Output Parameter #1 = 615
Output Parameter #2 =
Output Parameter #3 =
Output Parameter #4 =
Output Parameter #5 = 2012-06-01
Output Parameter #6 = 2012-06-30
Output Parameter #7 = 1120727
Output Parameter #8 = 090434882

SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure NEWSCAFCHG in *N. Cause . . . . . : Procedure NEWSCAFCHG in *N was called and has returned one or more result sets. Recovery . . . : None.

Statement ran successfully, with warnings (2915 ms = 2.915 sec)


Act Grp *NEW:

[ Fri Jul 27 15:59:05 GMT 2012 ] Run All

call newscafchg('615', '','', '', '2012-06-01', '2012-06-30', '', '')

Return Code = 0

Output Parameter #1 = 615
Output Parameter #2 =
Output Parameter #3 =
Output Parameter #4 =
Output Parameter #5 = 2012-06-01
Output Parameter #6 = 2012-06-30
Output Parameter #7 = 1120727
Output Parameter #8 = 105904586

Statement ran successfully (3281 ms = 3.281 sec)

So why the difference?

We are at V6R1. If you need more info or code snippets please let me know.

Thanks in advance for any help you can give in helping me understand.


Doug

--
This is the RPG programming on the IBM i / System i (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.