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ñ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'
.

Relevant Pages

  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • Re: Sync production db with test
    ... Declare @strTableSchema Nvarchar ... DECLARE @SQL varchar ... CREATE TABLE #SQLtemp ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance Issues With Query
    ... SQL Server MVP ... > DECLARE @Timestamp datetime ... > DECLARE @CallEventName varchar ... > UPDATE CallByCallYesterdayFinal SET Script = SUBSTRING(@Destination, ...
    (microsoft.public.sqlserver)
  • Re: MSSQL$SBSMONITORING - problem since KB948110 install
    ... Many many thanks for posting a reply, running the SQL command in SQL ... DECLARE @work_to_do TABLE ( ... , indexid int ... DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do ...
    (microsoft.public.windows.server.sbs)
  • 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)