• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: One Way To Deal With Two Null Formats

    July 8, 2019 Ted Holt

    Hey, Ted:

    We are building a new system and want to use modern programming and database techniques. I have had quite a time trying to get nulls to act right. It gets confusing fast because RPG handles them differently than the way embedded SQL does. When using SQL for I/O, how do we handle the two null formats?

    — Brian

    There are several ways to go about the “problem” of nulls. Let me give you one simple method, but keep in mind that it’s not the only way.

    First, let’s create a table and put some data into it.

    create table omembers
      ( id      dec (7),
        name    varchar(20),
        born    date,
       primary key (id))
    
    insert into omembers values
    ( 1, 'Billy Rubin', '1992-05-07'),
    ( 2, 'Sally Varygland', NULL),
    ( 3, NULL, '2001-12-25'),
    ( 4, NULL, NULL)
    

    So far so good. All three columns supposedly are permitted to be null — the Display File File Description (DSPFFD) command says they are. However, if you try to insert a row with a null ID, you’ll get message SQL0545 (INSERT, UPDATE, or MERGE not allowed by CHECK constraint) because a primary key can’t be null.

    Before tackling SQL, let’s look at native I/O. Here’s a simple program that reads the file and displays the data.

    **free
    ctl-opt actgrp(*new)
            option(*srcstmt: *nodebugio)
            alwnull(*usrctl);
    
    dcl-f   oMembers   keyed  rename(oMembers: Member);
    
    dcl-ds  MemberInfo   likerec(Member);
    
    *inlr = *on;
    dow '1';
       read Member MemberInfo;
       if %eof();
          leave;
       endif;
       DoIt (MemberInfo);
    enddo;
    return;
    
    dcl-proc DoIt;
       dcl-pi *n;
         inMember  likerec(Member) options(*nullind);
       end-pi;
    
       dcl-ds Message    len(52)   qualified;
          ID         char ( 7);
          *n         char ( 1);
          IDNull     ind;
          *n         char ( 1);
          Name       char (20);
          *n         char ( 1);
          NameNull   ind;
          *n         char ( 1);
          Born       char (10);
          *n         char ( 1);
          BornNull   ind;
       end-ds Message;
    
       Message . ID       = %char(inMember.ID);
       Message . IDNull   = %nullind(inMember.ID);
       Message . Name     = inMember.Name;
       Message . NameNull = %nullind(inMember.Name);
       Message . Born     = %char(inMember.Born);
       Message . BornNull = %nullind(inMember.Born);
    
       dsply Message;
    
    end-proc DoIt;
    

    Let me point out a few things.

    First, notice ALWNULL(*USRCTL) in the header (CTL-OPT) specs. You need this option in order to work directly with nulls in externally-described database tables.

    Second, data structure MemberInfo includes a map of the nulls — one indicator per field. You do not have to define another data structure just to store nulls.

    Third, subprocedure DoIt receives the null map because of OPTIONS(*NULLIND). Without this option, DoIt would not be able to determine when a field is null. That is, within the DoIt subprocedure, the %NULLIND function would always return *OFF, never *ON.

    Here’s the output from running the program. Notice the zeros and ones that indicate whether or not a field is null.

    1  0 Billy Rubin     0 1992-05-07 0
    2  0 Sally Varygland 0 0001-01-01 1
    3  0                 1 2001-12-25 0
    4  0                 1 0001-01-01 1
    

    Let’s do the same thing with SQL. (I regret dropping back to fixed-format code, but the SQL precompiler did not like some of my free-form code.)

    H dftactgrp(*no) actgrp(*new)
    H option(*srcstmt: *nodebugio)
    H alwnull(*usrctl)
    
    D MemberInfo    e ds                  extname(OMEMBERS)
    D                                     qualified
    D NullIndicators  s              5i 0 dim(3)
    
      *inlr = *on;
    
      exec sql declare cWJR0011R cursor for
                 select * from oMembers;
      exec sql open cWJR0011R;
      dow '1';
         exec sql
            fetch cWJR0011R into :MemberInfo :NullIndicators;
         if sqlstate >= '02000';
            leave;
         endif;
         %nullind(MemberInfo.ID)   = (NullIndicators (1) < *zero);
         %nullind(MemberInfo.Name) = (NullIndicators (2) < *zero);
         %nullind(MemberInfo.Born) = (NullIndicators (3) < *zero);
         DoIt (MemberInfo);
      enddo;
      exec sql close cWJR0011R;
    
      return;
    
    P DoIt            b
    (as before)
    P DoIt            e
    

    Now we’re using a cursor. Notice the differences:

    • MemberInfo is defined using the EXTNAME keyword, since there is no longer a file declaration of OMEMBERS. It includes the null map, as before.
    • NullIndicators is a three-element array of two-byte signed integers for SQL use. A value of zero means that the column (field) is not null. Negative one means that it is null.
    • Converting the SQL 0/-1 to the RPG *OFF/*ON is easy. Use a simple assignment.
         %nullind(MemberInfo.ID)   = (NullIndicators (1) < *zero);
         %nullind(MemberInfo.Name) = (NullIndicators (2) < *zero);
         %nullind(MemberInfo.Born) = (NullIndicators (3) < *zero);
    

    This answers half of your question. What about converting the RPG null format to the SQL null format? It’s slightly more work, but not difficult. Here’s a short demo of how to insert a row using null indicators.

    H alwnull(*usrctl)
    
    D MemberInfo    e ds                  extname(OMEMBERS)
    D                                     qualified
    D NullIndicators  s              5i 0 dim(3)
    
    D NullValue       c                   const(-1)
    
      // Plug data for one row into MemberInfo.
      // This would usually be done elsewhere.
      MemberInfo . ID     = 7;
      MemberInfo . Name   = 'Polly Unsaturated';
    
      %nullind (MemberInfo . ID)   = *off;
      %nullind (MemberInfo . Name) = *off;
      %nullind (MemberInfo . Born) = *on; // <-- null!
    
      // There is data in MemberInfo. Insert it!
      NullIndicators (*) = *zero;    // assume no nulls
      if %nullind(MemberInfo . ID) = *on;
         NullIndicators (1) = NullValue;
      endif;
      if %nullind(MemberInfo . Name) = *on;
         NullIndicators (2) = NullValue;
      endif;
      if %nullind(MemberInfo . Born) = *on;
         NullIndicators (3) = NullValue;
      endif;
    
      exec sql insert into omembers (ID, Name, Born)
                 values (:MemberInfo :NullIndicators);
    

    Begin by setting all elements of the SQL null indicators array to not-null (zero). Then test each column in turn and set the corresponding null indicator if necessary.

      NullIndicators (*) = *zero; // assume no nulls
      if %nullind(MemberInfo . ID) = *on;
         NullIndicators (1) = NullValue;
      endif;
      if %nullind(MemberInfo . Name) = *on;
         NullIndicators (2) = NullValue;
      endif;
      if %nullind(MemberInfo . Born) = *on;
         NullIndicators (3) = NullValue;
      endif;
    
    

    Did it work? Of course it did.

    ID NAME BORN
    1 Billy Rubin 1992-05-07
    2 Sally Varygland –
    3 – 2001-12-25
    4 – –
    7 Polly Unsaturated –

    Certainly having two different conventions of null indication is a nuisance, but it isn’t difficult. I’m sure you’ve tackled more difficult challenges many times.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, I/O, IBM i, RPG, SQL

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Paper Or Plastic Skytap Bullish On Its Hourly Billing In New IBM i Cloud

    2 thoughts on “Guru: One Way To Deal With Two Null Formats”

    • Trevor Briggs says:
      July 11, 2019 at 8:40 am

      All good stuff, but I find the easiest way to deal with nulls is not to deal with them, at least when just reading a table. Obviously you never access a table directly, but always through a view, right? So for all null-capable fields you do a COALESCE to replace a null with a user-friendly value and your program never needs to know the truth.

      Reply
    • Ted Holt says:
      July 21, 2019 at 10:39 am

      Thanks, Trevor. I use COALESCE too — when it’s appropriate. If you always coalesce nulls into other values, you may as well not allow nulls in the database.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 40

This Issue Sponsored By

  • Maxava
  • T.L. Ashford
  • RPG & DB2 Summit
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • Eradani Bridges The Gap Between Legacy And Open Source
  • Skytap Bullish On Its Hourly Billing In New IBM i Cloud
  • Guru: One Way To Deal With Two Null Formats
  • As I See It: Paper Or Plastic
  • IBM i PTF Guide, Volume 21, Number 26

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4
  • Power Systems Did Indeed Grow Revenues Last Year
  • The IBM Power Trap: Three Mistakes That Leave You Stuck
  • Big Blue Decrees Its 2023 IBM Champions
  • As I See It: The Good, the Bad, And The Mistaken
  • IBM i PTF Guide, Volume 25, Number 5

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.