Getting SQL0204 in CLI on V5R1 machine -- MIDRANGE-L

Hi all

I am befuddled.

I have an external user-defined function and an external storedprocedure that uses the UDF in a WHERE clause.

If I call the stored procedure in the iSeries Access statementprocessor, it works fine - the UDF is found and does what I want.

If I use the UDF in interactive SQL, it is cool, too, so long as Ihave the library list set to include the library (RJSIMAGE) Ispecified on the CREATE FUNCTION statement.

But I need to use the stored procedure in RPG, so I am using CLI,because the stored procedure returns a result set. And I get theSQL0204 message


Message ID . . . . . . :   SQL0204       Severity . . . . . . . :   30
Message type . . . . . :   Diagnostic
Date sent  . . . . . . :   09/29/06      Time sent  . . . . . . :   05:28:39

Message . . . . :   FOLDERISVALID in *LIBL type *N not found.
Cause . . . . . :   FOLDERISVALID in *LIBL type *N was not found. If this is
  an ALTER TABLE statement and the type is *N, a constraint was not found. If
  this is not an ALTER TABLE statement and the type is *N, a function,
  procedure, or trigger was not found.
Recovery  . . . :   Change the name and try the request again.  If the object
  is a node group, ensure that the DB2 Multisystem product is installed on
  your system and create a nodegroup with the CRTNODGRP CL command.

Job log (top level) looks like this (with my comments interspersed):

CALL VERN/TESTSQLCLI
Table SQLSC0000 created in QTEMP.
    (Part of CLI - they disappear)
Table SQLST0000 created in QTEMP.
PREPARE of statement SQLSTATEMENT000003 completed.
    (I use SQLExecDirect - implicit PREPARE)
Library RJSIMAGE removed from library list.
Library RJSIMAGE added to library list.
    (These are in the stored procedure external program)
FOLDERISVALID in *LIBL type *N not found.
    (From & To program are both QSQRUN1)
Prepared statement STMTGETFOLDERLIST not found.
    (From & To programs are both QSQOPEN)
CALL statement complete.


The CALL looks like this:

call rjsimage/getfolderlist (?, ?, ?, ?)

I have debugged the external program for the stored procedure, andthe parameters arrive as expected.

I pass the library name to the stored procedure and also to the UDF,and each of those adds it to the library list - just in case.

In the RPG program that is using CLI, I have also set environment andconnection default library to this library - the docs say it isignored if using system naming, which I have usually done. Butsetting SQL naming does not matter - I still get the error.

The CREATE statements follow - the external objects are, at present,in a different library (VMHDEVLIB). I duped them to RJSIMAGE - stillcan't find the UDF. The location of the external program does nothave to be the same as the library named in the CREATE, anyway, butit was worth a try.


So does anyone have an idea what to do? Are UDFs verboten in CLI?

Thanks
Vern

CREATE PROCEDURE RJSIMAGE/GETFOLDERLIST (
 IN USERNAME VARCHAR(20) ,
 IN SORTBYDESC VARCHAR(1) ,
 IN PGMSCHEMA VARCHAR(10) ,
 IN DBSCHEMA VARCHAR(10) )
 DYNAMIC RESULT SETS 1
 LANGUAGE RPGLE
 SPECIFIC RJSIMAGE/GETFOLDERLIST
 DETERMINISTIC
 READS SQL DATA
 CALLED ON NULL INPUT
 EXTERNAL NAME 'VMHDEVLIB/DOC450R'
 PARAMETER STYLE GENERAL ;

CREATE FUNCTION RJSIMAGE/FOLDERISVALID (
 FOLDER1 VARCHAR(100) ,
 FOLDER2 VARCHAR(100) ,
 FOLDER3 VARCHAR(100) ,
 FOLDER4 VARCHAR(100) ,
 FOLDER5 VARCHAR(100) ,
 PGMSCHEMA VARCHAR(10) ,
 DBSCHEMA VARCHAR(10) )
 RETURNS SMALLINT
 LANGUAGE RPGLE
 SPECIFIC RJSIMAGE/FOLDERISVALID
 DETERMINISTIC
 NO SQL
 RETURNS NULL ON NULL INPUT
 NO EXTERNAL ACTION
 EXTERNAL NAME 'VMHDEVLIB/DOC453R(DOC453R)'
 PARAMETER STYLE GENERAL ;