Problem with SQL UDF - IBM AS400

This is a discussion on Problem with SQL UDF - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 10 of 10

Thread: Problem with SQL UDF

  1. Problem with SQL UDF

    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ñe joAlcoolInside
    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'

  2. 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?


    I'm pretty sure it has something to do with the
    parameters being passed to QUSROBJD. That program has
    two optional parameters, but usually, a procedure has a
    fixed number of parameters. I played around with this
    at length, and at one point I thought I was getting
    close. I explicitly created a procedure specifying
    five parameters - the receiver variable, the length,
    the format, the object, and the object type - and for a
    moment, I thought it was going to work. But strangely,
    what it returned in the receiver variable was the last
    CL command run from my QZDASOINIT job!

    I tried recreating the procedure with a sixth variable
    (the error structure), and then a seventh (the ASP
    info), but nothing worked. If I have time, I'll try
    again with a CLP wrapper that does the call to
    QUSROBJD, and see if that works; or, you might try that.

  3. 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.

  4. Re: Problem with SQL UDF

    il 29/01/2008 23.09, Scrive Jonathan Ball 40654864:
    > 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.

    It's just a way. I was wondering why my udf doesn't run while manuals
    says it should.
    If I liked to wrap the API in an external program object I would use this:

    CREATE FUNCTION GETLIB (RFIL VARCHAR(10)) RETURNS VARCHAR(10)
    EXTERNAL NAME 'library/servicepgm(function)'
    LANGUAGE C NOT DETERMINISTIC NO SQL
    PARAMETER STYLE DB2SQL CALLED ON NULL INPUT
    NO EXTERNAL ACTION
    ;

    shere EXTERNAL external functiom prototype is always the same:
    void function(void *input,
    void *output,
    void *input_ind,
    void *output_ind,
    char sqlstate[6],
    char functionname[140],
    char specificfunctionname[129],
    char msgtext[71]);
    this way you can keep using UDF polimorphism and set sqlstate as it were
    an SQL UDF.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    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'

  5. Re: Problem with SQL UDF

    I have not had a chance to debug this further [somehow I managed to
    get my process in a deadlock; which I did not yet start to debug], but I
    can confirm the same results on v5r3 & v5r4. I had assigned RLIB and
    RVAR to have non-blank defaults, for a quick review before further
    debug. The RVAR is reset to blanks sometime after the call to the API
    and before the source statement immediately following; not back to it yet.

    Thus for /Why?/, it would seem reasonable to just report this as a
    defect.

    FWiW to confirm visually that the CALL transpires to QUSROBJD for the
    case of /returns blanks/ versus /gives error/, can be achieved easily
    with debug using ADDBKP '/1' or review of trace output.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    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 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 (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?


  6. Re: Problem with SQL UDF

    il 30/01/2008 16.03, Scrive CRPence 40654864:
    > I have not had a chance to debug this further [somehow I managed to
    > get my process in a deadlock; which I did not yet start to debug], but I
    > can confirm the same results on v5r3 & v5r4. I had assigned RLIB and
    > RVAR to have non-blank defaults, for a quick review before further
    > debug. The RVAR is reset to blanks sometime after the call to the API
    > and before the source statement immediately following; not back to it yet.
    >
    > Thus for /Why?/, it would seem reasonable to just report this as a
    > defect.

    I observed the same behaviour from V5R2 through V5R4 me too (with all DB
    PTF available).
    I will try the support. Thank you.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    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'

  7. Re: Problem with SQL UDF

    Dr.UgoGagliardelli wrote:
    > il 29/01/2008 23.09, Scrive Jonathan Ball 40654864:
    >> 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.

    > It's just a way. I was wondering why my udf doesn't run while manuals
    > says it should.
    > If I liked to wrap the API in an external program object I would use this:
    >
    > CREATE FUNCTION GETLIB (RFIL VARCHAR(10)) RETURNS VARCHAR(10)
    > EXTERNAL NAME 'library/servicepgm(function)'
    > LANGUAGE C NOT DETERMINISTIC NO SQL
    > PARAMETER STYLE DB2SQL CALLED ON NULL INPUT
    > NO EXTERNAL ACTION
    > ;
    >
    > shere EXTERNAL external functiom prototype is always the same:
    > void function(void *input,
    > void *output,
    > void *input_ind,
    > void *output_ind,
    > char sqlstate[6],
    > char functionname[140],
    > char specificfunctionname[129],
    > char msgtext[71]);
    > this way you can keep using UDF polimorphism and set sqlstate as it were
    > an SQL UDF.


    Well, if you know C, then fine. I don't know it, so I
    have to use something else, and CLLE seemed easiest here.

    But QUSROBJD is an OPM program, not a service program,
    and it seems to deal with a variable number of
    parameters, so I have to think there's some inherent
    problem with calling it directly as an unregistered
    procedure, and your method of registering it isn't
    going to work.

  8. Re: Problem with SQL UDF

    il 30/01/2008 19.21, Scrive Jonathan Ball 40256720:
    > Dr.UgoGagliardelli wrote:
    >> il 29/01/2008 23.09, Scrive Jonathan Ball 40654864:
    >>> 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.

    >> It's just a way. I was wondering why my udf doesn't run while manuals
    >> says it should.
    >> If I liked to wrap the API in an external program object I would use
    >> this:
    >>
    >> CREATE FUNCTION GETLIB (RFIL VARCHAR(10)) RETURNS VARCHAR(10)
    >> EXTERNAL NAME 'library/servicepgm(function)'
    >> LANGUAGE C NOT DETERMINISTIC NO SQL
    >> PARAMETER STYLE DB2SQL CALLED ON NULL INPUT
    >> NO EXTERNAL ACTION
    >> ;
    >>
    >> shere EXTERNAL external functiom prototype is always the same:
    >> void function(void *input,
    >> void *output,
    >> void *input_ind,
    >> void *output_ind,
    >> char sqlstate[6],
    >> char functionname[140],
    >> char specificfunctionname[129],
    >> char msgtext[71]);
    >> this way you can keep using UDF polimorphism and set sqlstate as it
    >> were an SQL UDF.

    >
    > Well, if you know C, then fine. I don't know it, so I have to use
    > something else, and CLLE seemed easiest here.


    It's not matter of language, the same can be done in clle as well.
    That's only the interface you should use building an EXTERNAL UDF,
    where output will be the value to return, intut will be the function
    arguments input_ind whether the input arg is null (-1) or not (0) the
    same use for output_ind, the other parameters are used by SQL to tell
    the program the function name invoked the specific name (that may be
    different) the message text to use with sqlstate if any.
    The EXTERNAL UDF can be a program as well, EXTERNAL NAME will be
    'library/pgmname'

    > But QUSROBJD is an OPM program, not a service program, and it seems to
    > deal with a variable number of parameters, so I have to think there's
    > some inherent problem with calling it directly as an unregistered
    > procedure, and your method of registering it isn't going to work.

    Not really, if I pass incorrect argument or try to call an unexistan
    program I see it in the joblog and the funtion fails, if api parameters
    ar correct nothing visible happens.
    CRPence says its a defect maybe of SQL precompiler that doesn't evaluate
    the parameters back.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    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'

  9. Re: Problem with SQL UDF

    Ughhh! I think I might know what is wrong. Debug shows all is
    correct, *except* the representation of the result by the SQL.

    I have not tested this theory yet... going home presently... But the
    RVAR declared as CHAR, if treated as a C-string type, because the first
    byte would invariably be 0x00 [as part of the BINARY(4) "Bytes returned"
    data]. 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. If so, then all
    48-bytes of the declared CHAR would be considered blanks [a null-string
    padded], thus the substring of that padded string would also be blanks.

    Try instead, DECLARE RVAR BINARY(48), and see how that functions. I
    seem to recall the mappings from BINARY are very limited, so the RETURN
    value and its representation might have to be adjusted.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Dr.UgoGagliardelli wrote:
    > il 30/01/2008 16.03, Scrive CRPence 40654864:
    >> 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 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 (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?

    >>
    >> I have not had a chance to debug this further [somehow I managed to
    >> get my process in a deadlock; which I did not yet start to debug], but
    >> I can confirm the same results on v5r3 & v5r4. I had assigned RLIB
    >> and RVAR to have non-blank defaults, for a quick review before further
    >> debug. The RVAR is reset to blanks sometime after the call to the API
    >> and before the source statement immediately following; not back to it
    >> yet.
    >>
    >> Thus for /Why?/, it would seem reasonable to just report this as a
    >> defect.

    >
    > I observed the same behaviour from V5R2 through V5R4 me too (with all DB
    > PTF available).
    > I will try the support. Thank you.


  10. Re: Problem with SQL UDF

    A more complete reply, including the below /code/ snippet with same
    function name [for search], was given on it.comp.as400



    DROP FUNCTION GetFnLibB2
    ;
    CREATE FUNCTION GetFnLibB2
    (RFIL VARCHAR(10)) RETURNS VARBINARY(10)
    LANGUAGE SQL NOT DETERMINISTIC
    SET OPTION
    DBGVIEW = *SOURCE, DYNUSRPRF = *OWNER, USRPRF= *OWNER,
    COMMIT=*NONE, SQLCURRULE = *STD, EVENTF=*NO
    BEGIN
    DECLARE RLIB VARBINARY(10) DEFAULT X'00' NOT NULL;
    DECLARE RVAR BINARY(48) DEFAULT X'00' NOT NULL;
    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 qsys.QUSROBJD (RVAR, RLEN, RFMT, ROBJ, RTYP);
    SET RLIB = TRIM(TRAILING X'40' FROM SUBSTR(RVAR, 39, 10));
    RETURN RLIB;
    END
    ;



    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    CRPence wrote:
    > <>
    >
    > Try instead, DECLARE RVAR BINARY(48), and see how that functions.
    > I seem to recall the mappings from BINARY are very limited, so the
    > RETURN value and its representation might have to be adjusted.


+ Reply to Thread