How to solve null indicator sqlcode - 305?

[pra_passion]

Hi,

Can any one tell me how to solve null indicator sqlcode - 305?

Thanks.

[zum13]

Hello.

A null column is one which can have no value. In order to handle a nullable column, an additional field needs to be defined which will allow DB2 to provide an indication as to whether the column is, in fact, null. This takes the form of a halfword integer (i.e. PIC S9(4) COMP) which needs to be defined as a host variable. For example,

Code: Select all

           EXEC SQL BEGIN DECLARE SECTION.
       01  TEST-COLUMN                 PIC X(20). 
       01  TEST-COLUMN-IND             PIC S9(4) COMP. 
           EXEC SQL END DECLARE SECTION. 

When you specify the field in an SQL statement, both variables will be specified (note that there is no comma between the output field and the indicator):

Code: Select all

           EXEC SQL
                SELECT TEST_COLUMN
                INTO   :TEST-COLUMN :TEST-COLUMN-IND
                WHERE  TEST-KEY = '12345'
           END-EXEC.

Upon execution, you will either get back the value of the column and the null indicator, or just the value of the null indicator. If the null indicator is negative, then the column is null (the host variable for the value will be unchanged, so don't use it). Zero indicates that the field has a value.

The same thing applies when using null columns in statements such as FETCH, INSERT, UPDATE, or DELETE. Where a predicate for a nullable column is using a value specified in a host variable, the indicator variable needs to be specified there too:

Code: Select all

                WHERE  TEST-COLUMN = :TEST-COLUMN :TEST-COLUMN-IND

Any time that you are supplying a value rather than expecting one to be returned, the null indicator will need to be initialised correctly by the program.

If you are using tables which have a DCLGENed member available for them (which I would recommend as it saves a lot of work), you should find that any null columns automatically have a null indicator field defined for them.