Problem with SQL UDF
- From: "Dr.UgoGagliardelli" <do.not.spam.me.please@xxxxxxxxxx>
- Date: Sat, 26 Jan 2008 11:21:17 +0100
I created this simple UDF that should return the library of a file in library list:
CREATE FUNCTION GETLIB
(RFIL VARCHAR(10)) RETURNS VARCHAR(10)
LANGUAGE SQL NOT DETERMINISTIC
SET OPTION DBGVIEW = *SOURCE, DYNUSRPRF = *OWNER, USRPRF= *OWNER,
COMMIT=*NONE, EVENTF=*NO
BEGIN
DECLARE RLIB VARCHAR(10);
DECLARE RVAR CHAR(48) DEFAULT ' ';
DECLARE RLEN INT DEFAULT 48;
DECLARE RFMT CHAR(8) DEFAULT 'OBJD0100';
DECLARE ROBJ CHAR(20) DEFAULT ' ';
DECLARE RTYP CHAR(10) DEFAULT '*FILE ';
SET ROBJ = CHAR(RFIL, 10) CONCAT CHAR('*LIBL', 10);
CALL QUSROBJD (RVAR, RLEN, RFMT, ROBJ, RTYP);
SET RLIB = TRIM(SUBSTR(RVAR, 39, 10));
RETURN RLIB;
END;
The problem is that RVAR, after the call is always empty disregarding the file was found or not. If the file is not found the function ends in error, due to the missind API error structure (with a CPF9812 in the joblog as expected), running under debug I can see in the joblog that QUSROBJD was called succesfully, if I change the proc name with something that doesn't match a program or service-program name, I can see in the joblog that the program was not found in *LIBL (SQL0204), so I'm pretty sure that the call takes place.
In SQL reference, I found that, if not explicitly declared in a declare procedure statement, all arguments inside a variable will be considered of type INOUT, so I assumed that should run as is, but it's not.
Why?
--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñejoAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'
.
- Follow-Ups:
- Re: Problem with SQL UDF
- From: CRPence
- Re: Problem with SQL UDF
- From: Jonathan Ball
- Re: Problem with SQL UDF
- From: Jonathan Ball
- Re: Problem with SQL UDF
- Prev by Date: Re: Current SQL being processed for a job
- Next by Date: Prometric and Certification Tests Question
- Previous by thread: Condition fields in subfile when SFLPAG <> SFLSIZ
- Next by thread: Re: Problem with SQL UDF
- Index(es):
Relevant Pages
|