• 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
    LaserVault

    Integrate Virtual Tape For Better Backups, Faster Recovery, And More Flexibility

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). See a demo and get a $50 gift card.

    With ViTL you can:

    • Replace physical tape and tape libraries and associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    “The ViTL tapeless solution has truly made my job easier. It has given me more confidence in our full system recovery ability – but at the same time I hope it is never needed.” IBM i Administrator at a financial services company

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    LaserVault.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

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

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.