SQL Get environment variable

envvar.sql

-- Get environment variable as a string - set by ADDENVVAR  command
-- You need to have the "ifs_write" procedure found on my gist
-- you need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
-- I doubt this method is a good idea to build huge applications, 
-- however it is a cool example how far you can go with SQL:
----------------------------------------------------------------------------------------------
call qusrsys.ifs_write('/tmp/include.c' , '
{
    #include <stdlib.h>
    char * env;
    
    ENVVAR.ENVIRONMENT_VARIABLE.DAT[ENVVAR.ENVIRONMENT_VARIABLE.LEN] =0;
    env = getenv(ENVVAR.ENVIRONMENT_VARIABLE.DAT);
    if (env) {    
        MAIN.RES.LEN = strlen(env);
        memcpy ( MAIN.RES.DAT , env , MAIN.RES.LEN);
    } else {
        MAIN.RES.LEN = ENVVAR.DEFAULT_VALUE.LEN;
        memcpy ( MAIN.RES.DAT , ENVVAR.DEFAULT_VALUE.DAT , MAIN.RES.LEN);
    }
}       
');


create or replace function qusrsys.envvar (
    environment_variable  varchar(256),
    default_value         varchar(256) default ''
) 
returns  varchar(4095)
external action 
modifies sql data 
deterministic
set option output=*print, commit=*none, dbgview = *source --list
main:begin
  
    declare res varchar(4096) default '';
    include '/tmp/include.c';
    return res;
  
end;

-- Usecases:
---------------------------------------------
cl:ADDENVVAR ENVVAR(MYENVVAR) VALUE('Test String') REPLACE(*YES);

-- Simple return of the environment variable value
values (
    qusrsys.envvar ('MYENVVAR')
); 
 
-- Simple return of the environment variable default value 
values (
    qusrsys.envvar ('DOESNOTEXISTS','A default value')
);

-- Used in a select statement ( 
-- that could i.e. be company code used in  a table view to show user specific rows
-- and bring session/user specific data to view
cl:ADDENVVAR ENVVAR(STATE) VALUE('TX') REPLACE(*YES);

Select * 
from qiws.QCUSTCDT
where state = qusrsys.envvar('STATE');