Created On:  20 September 2010

Problem:

While trying to insert a new record using MS Access 2007 with AcuXDBC 8.1 the following error was encountered:

NULL not allowed for column(#-nn)

The FD for the file contains fields with COMP-3 data, but work fine with AcuODBC.

Resolution:

The way to work around this error is to insert zero (0) into all COMP-3 data fields that do not already contain a value. This provides an explanation:

COBOL does not have a concept that corresponds directly to SQL’s NULL. The closest candidates in COBOL are data items that contain either SPACES or LOW-VALUES. In SQL, NULL is often used to indicate that the data is missing or not applicable. 

To maintain the integrity of the source data and to ensure that any data written from your application back to the COBOL source is accurate, you must provide a representational mapping between COBOL’s SPACES and LOW-VALUES and the corresponding SQL column values.

Use the NULL_NUMERIC_WRITE variable to indicate how SQL NULLs in numeric data should be translated into COBOL data. For data returned to the COBOL files (WRITEs), indicate whether the NULLs in numeric data should be interpreted as SPACES, LOW-VALUES, or zero.  Valid values are: 

NULL_NUMERIC_WRITE spaces 

NULL_NUMERIC_WRITE low-values 

NULL_NUMERIC_WITE 0 

Numeric fields that take the form of one of the computational types further complicate the situation. For most comp fields, LOW-VALUES and SPACES are valid values. See the configuration variable NULL_NUMERIC_READ for examples. In order to prevent erroneous information being written to the COBOL data files by the insert of a NULL value, tables created with comp fields that could be misinterpreted are created with the NOT NULL constraint on these columns. 

The following describes the null ability of numeric columns: 

Numeric : NULL 

COMP    : NOT NULL 

COMP-1 : NOT NULL 

COMP-2 : NULL 

COMP-3 : NOT NULL 

COMP-4 : NOT NULL 

COMP-5 : NOT NULL 

COMP-6 : NOT NULL

The bottom line is that the columns whose underlying data types require NOT NULL are required and must have a value specified in them for a successful insert or update.

Incident #2459297