Midrange News for the IBM i Community

Posted by: clbirk
null fields
has no ratings.
Published: 26 Oct 2012
Revised: 23 Jan 2013 - 3167 days ago
Last viewed on: 23 Sep 2021 (9970 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

null fields Published by: clbirk on 26 Oct 2012 view comments(8)

I set up some tables using inav, and they are written to by php. The web programmer who did them left the "nullable" and default null. I have a few tables I write to from s/36e side of things that way also, never an issue.


However I could not read these tables, got a data mapping error. Looking at the file, there are no binary zeros, just plain jane x'40' but it kept saying reason code 19 which deals with null values.


Now I understand there is something (allow null characters) that you can do in CL and in rpgiv? for such but it seemed like to me the problem was that it must "convert" the stuff during read from the x'40' (space) to something else???


I had to export the stuff out, blow the table contents away, change the sql definitions and re-import. It would not "alter" with stuff in the table.


Can someone shed any light. Needless to say, the web programmer won't be marking them as nullable again...



Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: TFisher
Premium member *
Comment on: null fields
Posted: 8 years 11 months 10 hours 36 minutes ago
Edited: Fri, 26 Oct, 2012 at 10:56:59 (3256 days ago)

Try using ALWNULL(*USRCTL) on your H-spec in RPG and then compare the null capable fields to null (or *ALLx'00') before you reference them. 


I think this technique should work for what you are doing, if not let me know...

// If null then assign 'DEFAULT' as the value...    

If Char = *ALLx'00';

  Char = 'DEFAULT';



If you want to allow null in a database field then you need to use the ALWNULL keyword at the field level.  You may also want to define the default as null  - DFT(NULL)


If reading data from a file then you would ALWNULL in your H-specs and use null indicators.  So if you have a field named FLD01 then you would test for nulls with %NULLIND(FLD01).

Posted by: bobcozzi
Site Admin ****
Comment on: null fields
Posted: 8 years 11 months 9 hours 41 minutes ago

RPG uses the %NULLIND( myField) to check a field's null indicator. If you are reading your data via RPG I/O then using hte ALWNULL(*USRCTL) as Fish indicated and then testing the field with something like:


     If %NULLIND( myField ) = *ON;
        // The field is null!

Of course if you're using RPG36 I do not know of a way to tell the program the database has NULLs in it and "just accept it" as you can do in RPG IV.

If you are reading the data using Embedded SQL, then you have to do something entirely different. For that situation, use the INDICATOR keyword on the Fetch or SELECT INTO statements. Something like:


     D iAmNull    S       5i 0
          EXEC SQL SELECT d1, d2, d3
                     INTO :myData1:INDICATOR:iAmNull,
                          :myData2, :myData3
               FROM webFile
               WHERE d3 = 'IBMIRD';

 The 2-byte integer named IAMNULL is how SQL tells you the field is NULL or is not null. If the IAMNULL field is NOT zero then it is null.

RPG IV's native %NULLIND do not work with embedded SQL fetch's.

You can still read fields from database files that allow nulls by NOT using the INDICATOR keyword in Embedded SQL, and the data will be read. However, if the field ever contains a null, you will get a data mapping error.


Posted by: jjcllhn
Premium member *
Comment on: null fields
Posted: 8 years 11 months 9 hours 21 minutes ago

Could always switch from RLA to SQL, and provide your own defaults for nullable fields using the IFNULL function.


  SELECT IFNULL(d1,'*NULL'), d2, d3 

    INTO :myDATA1, :myData2, :myData3

    FROM webfile

    WHERE d3 ='IBMIRD';

Posted by: clbirk
Premium member *
Comment on: null fields
Posted: 8 years 11 months 7 hours 10 minutes ago

The odd thing was that even program like wrkdbf would "bomb" on the files. He had all the character fields "nullable", yet when you do a wrkf and look the fields all contain spaces. I got the data mapping error upon opening the file and it was #19 wihch is dealing with null characters.

The file was created by php (running on the i), using the db2 functions. And all I did was change the definition (the file was defined in inav, so SQL), and reloaded it from the export I made, and you could have done a character by character comparision and there was NO difference.


Yet this same table/file that blows up in my rpg program upon opening, that you can't do a copy to stream file or can run wrkdbf (blows up that is where the below data mapping was pasted from), I opened in wrkqry just fine.


I have other files that I load and read tables that have been defined as "null" and I don't get any errors, so my guess and it is only a guess is that it is something related to the db2 functions in php that are doing it. But I could be all wet.


There was NOT a single x'00' or for that matter any character below x'40' (the ccsid was 37, ebcdic).


If I was getting in binary zeros or garbage in the fields, that I could understand, but when you see nothing wrong in the raw file and it simply "crashes"...


19 -- An unexpected null field was found
Data mapping error on member STDOECU.
A data mapping error occurred on field EMAIL in record

and it said 2nd record failed which the field is "blank"


The first record had an email.




Posted by: clbirk
Premium member *
Comment on: null fields
Posted: 8 years 11 months 7 hours 4 minutes ago

I guess unless someone has something more definitive (as the web programmer indicated that he had an issue or to in php using the db2_ functions with a "nullable" field) is for me to clear the file, and load some records (with some of those fields empty or blank) into it with inav or with a program, and then try opening it and seeing if it fails. If it doesn't then it is something related to the db2_ php functions. If it does then I guess I will make real sure I never define any as "nullable".

Posted by: clbirk
Premium member *
Comment on: null fields
Posted: 8 years 11 months 7 hours 3 minutes ago

I have a v6r1 machine siitting around, I may try putting the table on that machine and seeing what I get and building it over there also and such.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: null fields
Posted: 8 years 11 months 7 hours ago

Looks like Bob's the only one that noticed the s/36e in the original post Smile

But I've never seen :INDICATOR in there. I've always just done:

EXEC SQL SELECT d1, d2, d2
         INTO :myData1 :iAmNull, :myData2, :myData3 ...

To add the indicator field, you just add the extra host field name before the comma.


Posted by: bobcozzi
Site Admin ****
Comment on: null fields
Posted: 8 years 11 months 5 hours 23 minutes ago

clbirk, again, the fields do NOT contain a "null" that is not possible but to many RPG programmers it seems like the only choice. It is not.

The fields can contain valid data and still be NULL. The NULL is another attribute not stored in the field. Therefore a field can contain 'ABCD' and still be equal to NULL. It is a separate attribute entirely.

Therefore WRKDBF (which was written in RPG) could cause an issue just like any other program that doesn't respect NULLs.