7.1 TR7

When I create an SQL function using SQL naming, it appears to remember
the CURRENT SCHEMA that was in effect when the function was created
rather than the one that is in effect when the function executes.

Inside the function is an unqualified SELECT statement. My reading of
the manual indicates that an unqualified table reference in SQL naming
would use the CURRENT SCHEMA. Here is the test environment, created in
IBM i Navigator in SQL naming:

create table buck.sqltest (text char(10));
create table buck00001.sqltest (text char(10));

insert into buck.sqltest values('Production');
insert into buck00001.sqltest values('Test');

set schema buck;
create or replace function buck.sqltest() returns varchar(50)
language sql
begin
declare rtnval varchar(50);
select text into rtnval from sqltest;
set rtnval = rtnval concat ' CURRENT SCHEMA:' concat current schema;
return(rtnval);
end;

set path buck;
set schema buck;
select sqltest() from sysibm.sysdummy1;
-- returns 'Production CURRENT SCHEMA:BUCK'

set schema buck00001;
select sqltest() from sysibm.sysdummy1;
-- returns 'Production CURRENT SCHEMA:BUCK00001'

-- Now recreate the function with the test schema as CURRENT
set schema buck00001;
create or replace function buck.sqltest() returns varchar(50)
language sql
begin
declare rtnval varchar(50);
select text into rtnval from sqltest;
set rtnval = rtnval concat ' CURRENT SCHEMA:' concat current schema;
return(rtnval);
end;

set path buck;
set schema buck;
select sqltest() from sysibm.sysdummy1;
-- returns 'Test CURRENT SCHEMA:BUCK'

set schema buck00001;
select sqltest() from sysibm.sysdummy1;
-- returns 'Test CURRENT SCHEMA:BUCK00001'

I know that if I change my CREATE FUNCTION time naming to *SYS that the
function will use *LIBL to find the table (as expected) but I am
surprised that the function does not use the CURRENT SCHEMA at runtime
to locate the table.

I must be overlooking something but my hours in the manual haven't
revealed it and searching the internet has a high noise to signal ratio.
--buck




Return to Archive home page | Return to MIDRANGE.COM home page