AW: SQL CREATE FUNCTION uses schema from create time

Hi Buck,

You may check the following articles about System and SQL Naming
Conventions:
System versus SQL Naming - Part 1
Object authority and privileges for SQL database objects
http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html

System versus SQL Naming - Part 2
Accessing database objects
http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html

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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Buck
Calabro
Gesendet: Friday, 06.6 2014 00:08
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL CREATE FUNCTION uses schema from create time

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
--
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.