Re: Problem with SQL UDF

Dr.UgoGagliardelli wrote:
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?


Okay, here's what I did.

First, I modified your function code a little:

CREATE FUNCTION ball.GETLIB (RFIL VARCHAR(10)) RETURNS VARCHAR(10)
LANGUAGE SQL NOT DETERMINISTIC
BEGIN
DECLARE RLIB VARCHAR(10);
DECLARE RVAR CHAR(90) default ' ';
/* The RLEN and RFMT variables are now
specified in the 'wrapper' procedure */

-- DECLARE RLEN int DEFAULT 90;
-- DECLARE RFMT CHAR(8) DEFAULT 'OBJD0100';
DECLARE ROBJ CHAR(20);
DECLARE RTYP CHAR(10) DEFAULT '*FILE ';
SET ROBJ = CHAR(RFIL, 10) || CHAR('*LIBL ', 10);
CALL ball.getobjd (RVAR, ROBJ, RTYP);
SET RLIB = TRIM(SUBSTR(RVAR, 39, 10));
RETURN RLIB;
END;


I created a CLLE program to serve as a "wrapper" to the QUSROBJD API:

pgm parm(&rvar &robj &rtyp)
dcl var(&rvar) type(*char) len(90)
dcl var(&robj) type(*char) len(20)
dcl var(&rtyp) type(*char) len(10)
dcl var(&rfmt) type(*char) len(8) value(OBJD0100)
dcl var(&rlen) type(*int) value(90)
call qsys/qusrobjd parm(&rvar &rlen &rfmt &robj &rtyp)
return
endpgm


I registered a procedure GETOBJD in my library that calls the CLLE:

create procedure ball.getobjd
(inout rvar char(90), in robj char(20), in rtyp char(10))
parameter style general no sql
external name 'BALL/QUSROBJD';


This works. You could modify the CLLE a little to accept the length and format parameters from function, but I don't think there's any point to that, since you have those values hard-coded in the function.

I have to think there's something funny about the variable parameters that QUSROBJD takes that makes it not suitable to be called directly as a procedure by DB2.
.

Relevant Pages

  • Re: Problem with SQL UDF
    ... LANGUAGE SQL NOT DETERMINISTIC ... DECLARE RLIB VARCHAR; ... DECLARE RVAR CHARDEFAULT ' '; ...
    (comp.sys.ibm.as400.misc)
  • Re: Problem with SQL UDF
    ... I have not had a chance to debug this further, but I can confirm the same results on v5r3 & v5r4. ... The RVAR is reset to blanks sometime after the call to the API and before the source statement immediately following; ... DECLARE RLIB VARCHAR; ... If the file is not found the function ends in error, due to the missing API error structure (with a CPF9812 in the joblog as expected), running under debug I can see in the joblog that QUSROBJD was called successfully, 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, so I'm pretty sure that the call takes place. ...
    (comp.sys.ibm.as400.misc)
  • Re: Problem with SQL UDF
    ... Debug shows all is correct, *except* the representation of the result by the SQL. ... That would indicate a result of C-null-string for declarations of the data as a string -- which a SQL CHAR declaration presumably, and as I recall, is; though I did not review the listing. ... Try instead, DECLARE RVAR BINARY, and see how that functions. ...
    (comp.sys.ibm.as400.misc)
  • Re: Problem with SQL UDF
    ... DECLARE RLIB VARCHAR; ... DECLARE RVAR CHARDEFAULT ' '; ... 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. ...
    (comp.sys.ibm.as400.misc)
  • Re: Problem with SQL UDF
    ... DECLARE RLIB VARCHAR; ... DECLARE RVAR CHARDEFAULT ' '; ... 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. ...
    (comp.sys.ibm.as400.misc)