4

I've been told by our RPG programmer that none of our AS400-based DB2 database tables can permit nulls because nulls "are really difficult to program for" in RPG. I'd like to know if this is actually true and if so, what is it that makes this basic database feature so difficult to use in RPG?

I realize this may be slightly out of scope for Stack Overflow, but this is the best source I know for this kind of information.

mike
  • 1,233
  • 1
  • 15
  • 36
user1071914
  • 3,295
  • 11
  • 50
  • 76
  • 2
    Midrange programmer has a great write-up of the associated problems in this article: [The NULL Nemisis](http://www.itjungle.com/mpo/mpo082803-story02.html). – Carl Groner Oct 09 '13 at 02:27
  • Coalesce/NullVal/et al enable folding of NULLs to values in DB's. So it is easy enough to avoid NULL's returned from queries. – ChuckCottrill Oct 09 '13 at 02:54
  • Note that, with decent table/entity/query design, nulls can be somewhat sidestepped; take the `ShipDate` example in the link @Carl provided - if it was part of a different table (say, `ShippingDetail`), then the entire record wouldn't exist (it would be a 'null' record). Of course, legacy RPG databases aren't always known for adherence to even 1st normal form, so you may be out of luck either way... Personally, create/access new tables with SQL, allow nulls, and just write queries to translate for you. That way the DB doesn't have to change when the language does. – Clockwork-Muse Oct 09 '13 at 03:38

2 Answers2

1

The answer turns out to be "NULLS are allowed but require extra work." User Carl Groner commented above with a link to an excellent article which explains this from the RPG programmer's point of view. Here is the pertinent part which applied to my problem:

RPG/400 doesn't 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.

user1071914
  • 3,295
  • 11
  • 50
  • 76
  • Link to http://www.itjungle.com/mpo/mpo082803-story02.html referred to above is now broken. – mike Mar 16 '17 at 15:08
0

IBM RPG does not currently support handling nulls directly, but they can be handled in a SQL call by using the ISNULL built in function:

exec sql declare X cursor for 
select ISNULL(numfield, 0), 
       ISNULL(alphafield, '')
from table
where field = value
for read only;
Nicholas DeLessio
  • 11
  • 2