Skip to content

Instantly share code, notes, and snippets.

@NielsLiisberg
Last active Jun 5, 2020
Embed
What would you like to do?
SQL Get environment variable
-- 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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment