It depends on the naming.
The default for STRSQL is System Naming (*SYS). With System naming the
Library List is searched to find the stored procedures or UDFs.
The default for JDBC, ODBC is SQL Naming. With SQL Naming the SQL path is
searched to find the stored procedures or UDFs.
The SQL Path and the library list are not the same. With SQL Naming you have
to set the SQL path explicitly by executing the SQL statement SET CURRENT
PATH. When executing SET CURRENT PATH the special value *LIBL is allowed to
use the current library list.
With SQL naming the CURRENT SCHEMA is used for unqualified access of tables
or views, but NOT for finding stored procedures or UDFs.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von James Lampert
Gesendet: Wednesday, 30. March 2011 01:28
An: Midrange Systems Technical Discussion
Betreff: More SQL UDF help
Ok. Now I have working scalar UDFs to implement my "Display Value"
conversion, and I've even got a VIEW that incorporates a call to it.
But here's something interesting (in the sense of "May you live in
interesting times"):
Given my WHICHTBL function defined thusly:
create function whichtbl(i_fldid NUMERIC)
returns varchar(3)
language sql deterministic not fenced
begin
declare tbl char(11); declare tbl2 char(3);
declare cur01 cursor for select TBLID
from wtfldd where dfid = i_fldid;
open cur01; fetch cur01 into tbl; close cur01; set tbl2 = tbl;
return tbl2; end
and my DISPLAYVAL function defined thusly:
create function displayval(i_fldid NUMERIC, i_key VARCHAR(5))
returns varchar(50)
language sql deterministic not fenced
begin
declare result char(50);
declare cur02 cursor for select DEF from WTTBLE
where PTBLE = whichtbl(i_fldid)
and PCODE = i_key and STBLE = ' ' and SCODE = ' ';
open cur02;
fetch cur02 into result; close cur02; return result; end
if I call the DISPLAYVAL function from either a stored procedure called
from STRSQL, or a SELECT statement executed in STRSQL, or by opening the
aforementioned VIEW from STRSQL, I get the expected (and desired)
results, as long as WTFLDD and WTTBLE exist somewhere in my *LIBL.
But if I use SQuirreL SQL (which uses JDBC), any attempt to use the
functions will result in this:
Error: [SQL0204] WTTBLE in *LIBL type *FILE not found.
SQLState: 42704
ErrorCode: -204
(I also get the same message if I try to use the UDF from STRSQL without
the *LIBL set properly)
I've tried SET SCHEMA, and that doesn't seem to do a damned thing.
I'd like to have DISPLAYVAL automatically recognize, and use, whatever
library is used for the data it's being fed.
--
JHHL
midrange.com
