You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > RPG

Nulls: Dealing With Nothing Can Really Be Something!


We’ve seen several discussions recently about the topic of dealing with Nulls when embedding SQL into RPG. So this month, we’re covering the basics of what’s required when null capable columns are being retrieved and/or updated from RPG using SQL. And we’ll point you to a technique we like that makes it a little easier.

The concept of null doesn’t come up often in many RPG shops, in part because we have traditionally used DDS to define our database tables (aka files). While DDS allows for columns (aka fields) to be defined as null-capable, it’s not the default. When using SQL to define tables though, columns are null-capable by default (although you can specify “not null” if you want). As more shops are beginning to transition from using DDS to using SQL’s DDL to define tables, it’s natural to expect that we’ll see more use of null capability in the future.

Even if you have no null-capable columns at all in your databases, you may still need to learn to deal with nulls if you’re doing outer or exception joins as they occur when a row in one table has no match in a secondary table.

The Basics

It’s important to understand that null is not the same as zero or blank—or indeed any other value such as 999,999 that we might have used to indicate that a value has not been supplied. Null is the absence of a value. It therefore can be a bit challenging to interrogate the “nullness” of a column and/or to set the value of a column to null. RPG supplies the %NULLIND (Null Indicator) built-in function to handle this when using RPG’s native I/O operations. But that cannot be used when using embedded SQL I/O.

Embedded SQL uses a null indicator, but not the kind that can be queried or set via an RPG built-in—much like we can’t use %EOF or %FOUND to query the status an embedded SQL I/O statement. Instead, we’ll use a separately defined “null indicator” variable for retrieving and/or setting the “nullness” of a column.

Another point of confusion for some RPGers surrounds the use of the term “null indicator” since the SQL null indicators aren’t indicator data types. Instead they’re integer data types—2-byte integers, to be specific, which are defined in RPG as 5 I 0 (or in the new V7 free format definition Int(5). A value of zero indicates “not null” and a value of -1 indicates null..

In an SQL statement, the receiver variable for a null-capable column must be followed by one of these null indicators fields in the INTO portion of the SELECT statement. A simple example that follows shows how to define the null indicator (CustSalesInd) and how to use it in the Select statement. In this example, only the CustSales column is null-capable. Note that there must be no comma between :CustSales and :CustSalesInd in the Into clause. The appearance of two variable names with no comma separating them indicates that the second one is to be used to retrieve the null indicator.

 D CustID          S              7P 0
 D CustName        S             30A
 D CustSales       S              7P 2
 D CustSalesInd    S              5I 0

 D NullValue       C                  -1

  /Free

    Exec SQL
      Select CustID, CustName, CustSales
        Into :CustID, :CustName, :CustSales :CustSalesInd
        From Customer
        Where CustID = :InputCust;

    If SQLState = '00000';

      If CustSalesInd = NullValue;
          PrintSales = 'No Sales';
      Else;
          PrintSales = ('Sales were $' + %Char(CustSales));              
      EndIf; 

    EndIf;
 

Note that after the SQL statement, the null indicator value is compared to a constant with a value of -1, which as we noted earlier is the indication that there’s no value for CustSales. A value of 0 would indicate that the value is not null and it’s therefore safe to use the value in the receiving host variable. Why do we say “safe”? It’s because there’s no guarantee that the value in the field will be blank or zero, as you might expect. If there had been a value present before the column was set to null, it may still be there.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters