RE: UDTF with SQL and JDBC call

["Himes, Jay" <jehimes@xxxxxxxxxxx>]

A problem I have had with UDFs is that they can be very picky about the
input parameters.

For example GETRELSEQ takes an integer but c1nmid is numeric(9,0) - Here are
the results:

select GETRELSEQ(c1nmid)              
 from nammsp where c1nmid = 1         
GETRELSEQ in *LIBL type *N not found. 

select GETRELSEQ(int(c1nmid))    
 from nammsp where c1nmid = 1    
SELECT statement run complete. 

Char parms in particular often need to be cast to the right length.

Jay Himes
Liberty University

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Monday, March 22, 2004 2:38 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: UDTF with SQL and JDBC call

I doubt that USRPRF plays into this problem in any way.  I still think it's
the search path.

Perhaps in SQLRPGLE you perform EXEC SQL SET PATH kind of statement?

For sake of simplicity, let's just focus on the interactive SQL for now.  
Obviously, the message "fn in *LIBL type *N not found" means that system
cannot find the "fn".
Perform SELECT * FROM SYSFUNCS in the interactive sql and check if your "fn"
is entered fully qualified or not.

You can also perform "SET PATH QGPL" (change QGPL to where your UDTF is
located), and then try the original statement.  PATH is used to find UDFs
and stored procedures.

If you are using SQL naming convention then you should be able to set
default library DFTRDBCOL (default relational database collection - i.e.
library) using SET OPTION syntax (check SQL Reference book for details).
This will be used as search path for tables as well.

Or you can make sure you are using system naming and then the *LIBL list
search will work for tables, but not stored procedures and UDTFs.

Elvis

-----Original Message-----
 Subject: RE: UDTF with SQL and JDBC call

I am using the same user profile that has the same library list. I even
tried to declare my input parms as 'varying' in my program.  I still get the
errors in both interactive sql and java servlet.  

Sql gets - Select * from table(fn(parm1,parm2,parm3)) x
                fn in *LIBL type *N not found.


Java - SQLCall = "{select * from table(" +  funcname + "(?,?,?))}";
         CallableStatement stmt = con.prepareCall(SQLcall);
        
        I know I get my error here since I have println all over the place.

        java.sql.SQLException: A character, token, or clause is not
valid or        is missing.

Thanks for your help,
Sudha



_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.