2E - Are null values supported by 2E RPG programs?

Products

• CA 2E

Issue/Introduction

How can I force some columns in SQL table definition to accept null values? 

Environment

CA 2E 8.7

Cause

Customer needs to define a 2E file as SQL Table with Columns that allows Null values or generate an Edit File (RP4) to be able to update that file, but did not find any "nulls" reference in the 2E documentation.

Resolution

All current 2E releases do not have support for "Null" value out of the box.  The NOT NULL keyword is always generated in the SQL column definitions. Also, 2E SQL programs do not expect NULL values in the fields during processing.

Additional research indicates that RPG/400 does not support processing NULLs in a database file. If a file contains NULLs, specifying the ALWNULL(*YES) compiler option on the Create RPG Program (CRTRPGPGM) command allows the program to access the file as input only, with the caveat that all NULL capable fields contain the "default" value when a NULL is encountered. This means the RPG/400 program will have no way of distinguishing a NULL from a blank, for example.

WORKAROUND

If it is desired to make changes to fields definitions to allow NULL values, the only way is to manually make that change.

The following articles show how to define NULL capable fields in RPGLE and work with them:

https://www.ibm.com/support/pages/how-can-null-capable-variables-be-defined-rpgle-program

https://code400.com/forum/forum/iseries-programming-languages/sql/318-null-values-and-sql-fetch

Additional Information

RPG/400 and NULLs

RPG/400 does not support processing NULLs in a database file. If a file contains NULLs, specifying the ALWNULL(*YES) compiler option on the Create RPG Program (CRTRPGPGM) command allows the program to access the file as input only, with the caveat that all NULL-capable fields contain the "default" value when a NULL is encountered.

This means the RPG/400 program will have no way of distinguishing a NULL from a blank, for example.

Only use the ALWNULL(*YES) compiler option if the program will process a field's default value the same as it would if it were a NULL. To be able to work with "nullable" database fields in an RPG/400 program, you must use embedded SQL with indicator variables.