Shell and environment variables in CLPPlus

You can use shell and environment variables in CLPPlus commands and queries.

Description

With this support, any shell or environment variable value can be used in a CLPPlus command or query. This use is identical to how these variables are accessed in shell or command windows.

Shell or environment variable substitution can be enabled or disabled with the SET ENVVARSUBST CLPPlus command. For details on this command, see the Related reference.

Any variable prefixed with "$" is treated as a shell or environment variable. This character provides operating system independence to scripts which are used across differing operating systems. Environment variables on Microsoft platforms can also be accessed by wrapping "%" around the variable name, for example, %VAR%.

CLPPlus understands and resolves a shell or environment variable if it includes the characters a-z, A-Z, 0-9, and "_". If any other character is encountered, then it is treated as the end of the variable name. The following examples highlight this naming rule:
$clpplus&version    <==== "clpplus" followed by "&"
$clpplus[version]   <==== "clpplus" followed by "["
$clpplus version    <==== "clpplus" followed by " "(space)
$clpplus#version    <==== "clpplus" followed by "#"
$clpplus-version    <==== "clpplus" followed by "-"
$clpplus(version)   <==== "clpplus" followed by "("

If CLPPlus attempts to use a variable and it is defined, then its value is retrieved and used as expected. If CLPPlus attempts to use a variable and it is not defined, an empty string value is substituted and an informational message is logged. The informational message is visible when CLPPlus is operating in verbose mode.

You can access shell and environment variables in CLPPlus with the following methods:
  • While starting CLPPlus, you can pass shell or environment variables as arguments to a script. These passed variables are converted into position parameters which are accessed with the "&" character and their position (number)
  • Shell or environment variables can be directly accessed in CLPPlus with the "$" character followed by the variable name. This method is irrespective of the operating system on which the script is run.

Examples

  1. The following example shows how you can use shell or environment variables while starting CLPPlus:
    On UNIX and Linux® platforms:
    Export TABLE_NAME=employee
Export SCRIPT_PATH=/home/user

clpplus -nw @$SCRIPT_PATH/script.sql  $TABLE_NAME
    On Windows platforms:
    Set  TABLE_NAME=employee
Set  SCRIPT_PATH=c:\testfiles

clpplus -nw @%SCRIPT_PATH%\script.sql  %TABLE_NAME%
    where as script.sql contains 
    Select * from &1
    and &1 resolves to the TABLE_NAME variable value.
  2. The following example shows how you can use shell or environment variables directly in CLPPlus:
    On UNIX and Linux platforms:
    select * from $TABLE_NAME;
insert into test values ($NAME, $ID, $DESG);
    On Windows platforms:
    select * from %TABLE_NAME%;
insert into test values (%NAME%, %ID%, %DESG%);