Allow null values

[gregwga50]

I have a program that has a combination of SQL and non SQL code. It CHAINS to a file with a that has a field that sometimes contains a null value. It produces an I/O error on that CHAIN command when it encounters this. I've been able to get around this in non SQL programs by using the ALLWNULS (sp) option on the compile command. There doesn't appear to be an option to do this on the CRTSQLRPGI command. One solution is to change that CHAIN operation to an SQL select (I believe there are some SQL options for handling nulls). I'd rather leave the CHAIN operation in place if possible.

Any ideas?

[JonBoy]

You need to use the COMPILEOPT of SET OPTION. Here's an example:

exec sql SET OPTION COMMIT = *NONE,
         DATFMT = *ISO,
         COMPILEOPT = 'OPTION(*SRCSTMT *NODEBUGIO *NOUNREF) +
                                           ALWNULL(*USRCTL) +
                                           DFTACTGRP(*NO) +
                                           BNDDIR(*LIBL/TEST) +
                                           DBGVIEW(*LIST)' ;

That was borrowed from Simon's article on RPGPGM.COM. If you want to know more you can find the article here: https://www.rpgpgm.com/2020/05/addin...-into-sql.html

[B.Hauser]

Adding ALWNULL(*USRCTL) in the H-Specs should also work