Wednesday, August 21, 2024

Using SQL to create, update, and get from Environmental Variables

Within the latest database PTFs that came as part of the last Technology Refresh were a couple of new Scalar functions that can be used to do things with Environmental Variables. They are named after the APIs that can be used to put and get information from the Environmental Variables. These new SQL Scalar functions are:

  • PUTENV:  Creates or changes the Environmental Variable. Equivalent of the following commands: ADDENVVAR and CHGENVVAR, and the putenv API
  • GETENV:  Retrieves the value from the Environmental Variable. Equivalent of the getenv API

Both of these Scalar functions are found in the library SYSTOOLS.

For these first examples I used ACS Run SQL Scripts, RSS.

I am going to use the ENVIRONMENT_VARIABLE_INFO to list the Environmental Variables that already exists for this job:

01  SELECT ENVIRONMENT_VARIABLE_TYPE AS "Type",
02         ENVIRONMENT_VARIABLE_NAME AS "Name",
03         ENVIRONMENT_VARIABLE_VALUE AS "Value"
04    FROM QSYS2.ENVIRONMENT_VARIABLE_INFO

Which returns the following results:

Type   Name                    Value
-----  ----------------------  ------------------------------
JOB    LANG                    /QSYS.LIB/EN_US.LOCALE
JOB    ILE_DEBUGGER_1          ALLOW_WIDE_SCREEN

The two above were added as part of my personal start up program.

Now I want to create a new Environment Variable using the SQL PUTENV Scalar function:

01  VALUES SYSTOOLS.PUTENV(
02    ENVIRONMENT_VARIABLE_NAME => 'MY_TEST',
03    ENVIRONMENT_VARIABLE_VALUE => 'Test data from Simon'
04  )

Line 1: As PUTENV is a Scalar function I can execute it using the VALUES statement. If the Environmental variable does not exist PUTENV will create it.

Line 2: The first parameter used by the PUTENV is the name of the Environmental variable.

Line 3: Second parameter is value for the Environmental variable.

The Scalar function returns a ERRNO value:

00001
------
     0

The ERRNO of zero indicates that the statement completely successfully.

I can use the ENVIRONMENT_VARIABLE_INFO statement, I gave above, to confirm that I now have three Environmental Variables:

Type   Name                    Value
-----  ----------------------  ------------------------------
JOB    LANG                    /QSYS.LIB/EN_US.LOCALE
JOB    ILE_DEBUGGER_1          ALLOW_WIDE_SCREEN
JOB    MY_TEST                 Test data from Simon

If I had been returned a different ERRNO I would not know what it means. This is where I can use the ERRNO_INFO Scalar function to translate the code to a description:

01  VALUES SYSTOOLS.ERRNO_INFO(
02    SYSTOOLS.PUTENV(
03      ENVIRONMENT_VARIABLE_NAME => 'MY_TEST',
04      ENVIRONMENT_VARIABLE_VALUE => 'Changed data for Simon'
05    )
06  )

Line 1: The ERRNO_INFO Scalar function surrounds the PUTENV.

Line 2: The PUTENV Scalar function.

Line 3: This is the same Environmental Variable name as before, therefore, it will be changed to be the value that follows.

Line 4: New value for the Environmental Variable.

I get the same ERRNO as before, zero, and the ERRNO_INFO has returned the ERRNO description.

00001
------------------
There is no error.

I retrieve data from the Environmental Variable I created using the GETENV Scalar function. I just need to pass to it the Environmental Variable name, and it returns the value:

01  VALUES SYSTOOLS.GETENV('MY_TEST')

The result is:

00001
------------------------
Changed data for Simon

While what I have shown are good examples. When I use this it is going to be in a RPG program. Below is an example of how I could code a program to use these new Scalar functions:

01  **free
02  dcl-s ErrNo int(5) ;
03  dcl-s String varchar(40) ;

04  exec sql SET :ErrNo = SYSTOOLS.PUTENV('MY_TEST','Test data from Simon') ;
05  if (ErrNo <> 0) ;
06    exec sql SET :String = SYSTOOLS.ERRNO_INFO( :ErrNo ) ;
07    String = 'ErrNo: ' + String ;
08  else ;
09    exec sql SET :String = SYSTOOLS.GETENV('MY_TEST') ;
10  endif ;

11  dsply String ;
12  *inlr = *on ;

Line 2: Declaring an integer variable that will contain the ERRNO.

Line 3: Defining a variable length character variable for text.

Line 4: I use the SQL Set statement to move the value from the PUTENV scalar function into the integer variable, ErrNo. I am using the same Environmental Variable as I did before.

Line 5: If the value in the variable ErrNo is not zero...

Line 6: I use the Set statement to retrieve the ERRNO description into the variable String using the ERRNO_INFO scalar function.

Line 7: I do this to append the string "ErrNo:" to the start of the string in String.

Line 8: Else...

Line 9: I retrieve value from the Environmental Variable into the variable String.

Line 11: I display the contents of the variable String.

The following is display. This shows that I was able to change the Environmental Variable, and I was able to retrieve from it.

DSPLY  Test data from Simon

I do like these Scalar function, and would prefer to use them in place of the putenv and getenv APIs.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.