RE: sql function - routine not found when ACTGRP(*NEW)

I was only able to get your example working by specifying an activation group or using *CALLER. *NEW does not work. See Birgitta response there.

Also, the definitions for the last three parms are not correct for VARCHAR.
The first two bytes of the fields contain their varchar length. Each field needs to be increased by 2 in order to have the proper length of the field definition.

Here is an example:

PGM (&CURUSER &RTNNULL &SQLSTATE &FUNCTIONDS &SPECIFICDS &ERRMSGDS)

DCL &CURUSER *CHAR 10
DCL &RTNNULL *INT 2
DCL &SQLSTATE *CHAR 5

DCL VAR(&FUNCTIONDS) TYPE(*CHAR) LEN(519)
DCL VAR(&FUNCTIONLN) TYPE(*INT) STG(*DEFINED) LEN(2) DEFVAR(&FUNCTIONDS 1)
DCL VAR(&FUNCTION) TYPE(*CHAR) STG(*DEFINED) LEN(517) DEFVAR(&FUNCTIONDS 3)

DCL VAR(&SPECIFICDS) TYPE(*CHAR) LEN(130)
DCL VAR(&SPECIFICLN) TYPE(*INT) STG(*DEFINED) LEN(2) DEFVAR(&SPECIFICDS 1)
DCL VAR(&SPECIFIC) TYPE(*CHAR) STG(*DEFINED) LEN(128) DEFVAR(&SPECIFICDS 3)

DCL VAR(&ERRMSGDS) TYPE(*CHAR) LEN(1002)
DCL VAR(&ERRMSGLN) TYPE(*INT) STG(*DEFINED) LEN(2) DEFVAR(&ERRMSGDS 1)
DCL VAR(&ERRMSG) TYPE(*CHAR) STG(*DEFINED) LEN(1000) DEFVAR(&ERRMSGDS 3)

On V7R3 the size of error message text is varchar(1000).

Not using the correct sizes could cause unpredictable results.

At the minimum the variables should be defined with the proper length:

dcl &function *char 519
dcl &specific *char 130
dcl &errmsg *char 1002


Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Steve Richter
Sent: Tuesday, March 12, 2019 8:29 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: sql function - routine not found when ACTGRP(*NEW)

does the activation group of a program matter when creating an SQL function?

I am getting a "routine not found with specified parameters" error when using an SQL function, where the program was created with ACTGRP(*NEW).
When I CRTPGM pgmname ACTGRP(QILE), the sql function works.

CRTPGM PGM(utl0033MC) ACTGRP(*new)

select utl_currentUser( ) user
from sysibm/sysdummy1
Routine UTL0033MC in QGPL not found with specified parameters.

do this and the sql function works:
CRTPGM PGM(utl0033MC) ACTGRP(QILE)


CREATE OR REPLACE FUNCTION utl_currentUser ( )
RETURNS char(10)
language cl
external name qgpl/utl0033mc
parameter style SQL

PGM (&curUser &rtnNull &SqlState +
&function &specific &errmsg)

dcl &curUser *char 10
dcl &rtnNull *int 2
dcl &sqlstate *char 5
dcl &function *char 517
dcl &specific *char 130
dcl &errmsg *char 72

RTVJOBA CURUSER(&CURUSER)

endpgm