× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.