Wednesday, June 15, 2022

New data type for SQL - Boolean

sql boolean data type

A new data type has been added to Db2 for i's Data Definition Language, DDL, as part of IBM i 7.5, but was not added to IBM i 7.4 TR6. A Boolean data type.

Being Boolean it should only contain two values, but this can contain three possible values:

  1. True
  2. False
  3. Null – when this contains no data

I love this. For many, many years I have been creating "Boolean" like columns or fields to contain a sorta kinda true/false logic. But as the columns/fields were either character or numeric they could contain any valid value of that data type. Now I can have a column to denote things like:

  • Item is in-stock
  • Item is in a backlog status
  • Certain information has been provided or accompanies whatever

You get the idea.

A Boolean column can be defined in a SQL table as simply as:

01  CREATE TABLE MYLIB.TESTTABLE
02  (COLUMN1 BOOLEAN,
03   COLUMN2 CHAR(10)) ;

What does this column look like when I check it? I can use SQL View SYSCOLUMNS to do so:

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,LENGTH
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'MYLIB'
   AND TABLE_NAME = 'TESTTABLE' ;

Two rows are returned, one for each column in the Table:

TABLE_NAME  COLUMN_NAME  DATA_TYPE  LENGTH
----------  -----------  ---------  ------
TESTTABLE   COLUMN1      BOOLEAN         1
TESTTABLE   COLUMN2      CHAR           10

I can even use the DSPFFD command:

DSPFFD FILE(TESTTABLE)

At the bottom of the output I can find the "Field Level Information":

Field Level Information
             Data        Field
  Field      Type       Length
  COLUMN1    BOOLEAN         1
    Allows the null value
  COLUMN2    CHAR           10

When the Boolean data type contains data it will only return:

  • true
  • false

Notice that these values are lower case.

How do I insert values into this new data type? Using the Insert statement of course.

INSERT INTO TESTTABLE VALUES(TRUE,'1'),
                            (false,'2'),
                            (DEFAULT,'3') ;

This SQL Insert statement will insert three rows into the table. I have used upper case "TRUE", lower case "false", and the columns default value for the Boolean column. What does this look like when I retrieve the rows from the Table:

SELECT * FROM TESTTABLE ;

As I use ACS's Run SQL Scripts I am returned:

COLUMN1   COLUMN2
-------   -------
true      1
false     2
<NULL>    3

The Boolean values will always be shown in lower case, no matter how they are inserted or updated.

If I ever wanted to just return true rows I could use any of the following:

SELECT * FROM TESTTABLE WHERE COLUMN1 IS TRUE ;

SELECT * FROM TESTTABLE WHERE COLUMN1 = TRUE ;

SELECT * FROM TESTTABLE WHERE COLUMN1 ;

If I substitute TRUE with FALSE I will return only rows with a value of false.

Be warned if I was to use:

SELECT * FROM TESTTABLE WHERE COLUMN1 IS NOT TRUE ;

SELECT * FROM TESTTABLE WHERE COLUMN1 <> TRUE ;

SELECT * FROM TESTTABLE WHERE NOT COLUMN1 ;

I get more than the rows with the value of false, I also will return those rows that are null too.

There may be a time where I do not want to return true and false in my results. I can use the integer function, INT, to convert the Boolean values to integer values:

SELECT A.*,INT(COLUMN1) FROM TESTTABLE A ;


COLUMN1   COLUMN2   00003
-------   -------   -----
true      1             1
false     2             0
<NULL>    3         <NULL>

There are other values I can use in place of TRUE and FALSE when inserting data into a Boolean column. Here are some other examples:

01  INSERT INTO TESTTABLE VALUES('t','4'),('f','5'),
02                              ('1','6'),('0','7'),
03                              (on,'8'),(off,'9'),
04                              ('y','10'),('NO','11'),
05                              (NULL,'12') ;

06  SELECT * FROM TESTTABLE ;

The first row on each line, except line 5, inserts a true value. The second a false value. Line 5 will insert null into the Boolean column. The Select statement, line 6, returns all of the inserted rows:

COLUMN1   COLUMN2
-------   -------
true      1
false     2
<NULL>    3
true      4
false     5
true      6
false     7
true      8
false     9
true      10
false     11
<NULL>    12

All of the above is OK, but does RPG cope with the new data type. Let me start with a simple program that just reads TESTTABLE:

01  **free
02  ctl-opt option(*srcstmt) alwnull(*usrctl) ;

03  dcl-f TESTTABLE rename(TESTTABLE:INPUT) ;

04  dou (%eof) ;
05    read INPUT ;
06    dsply ('Boolean = ' + COLUMN1 + ' Other = ' + %trimr(COLUMN2) +
             ' EOF = ' + %eof) ;
07  enddo ;

08  *inlr = *on ;

Line 1: Modern RPG is always totally free.

Line 2: I need the ALWNULL control option as COLUMN1 can contain null.

Line 3: File definition for TESTTABLE. I have had to use the RENAME as the record format for TESTTABLE has the same name as the file.

Lines 4 – 7: This Do loop will read all the rows from the Table, and then display the values from the columns in the Table and the End of File indicator.

When I compile the source code into a program I can see that the compile has translated the Boolean column to be an Indicator column:

IINPUT
 *----------------------------------------------------------
 * RPG record format  . . . . :  INPUT
 * External format  . . . . . :  TESTTABLE : MYLIB/TESTTABLE
 *----------------------------------------------------------
I (ALWNULL)                   N    1    1  COLUMN1
I (ALWNULL)                   A    2   11  COLUMN2

When the program is called the following is displayed.

DSPLY  Boolean = 0 Other = 2 EOF = 0
DSPLY  Boolean = 0 Other = 3 EOF = 0
DSPLY  Boolean = 1 Other = 4 EOF = 0
DSPLY  Boolean = 0 Other = 5 EOF = 0
DSPLY  Boolean = 1 Other = 6 EOF = 0
DSPLY  Boolean = 0 Other = 7 EOF = 0
DSPLY  Boolean = 1 Other = 8 EOF = 0
DSPLY  Boolean = 0 Other = 9 EOF = 0
DSPLY  Boolean = 1 Other = 10 EOF = 0
DSPLY  Boolean = 0 Other = 11 EOF = 0
DSPLY  Boolean = 0 Other = 12 EOF = 0
DSPLY  Boolean = 0 Other = 12 EOF = 1

I rarely use RPG's native I/O. I use embedded SQL to get the data from the Tables and Files I am interested in. In this example I am using a multiple row fetch into a data structure array. This RPG source code looks like:

01  **free
02  ctl-opt option(*srcstmt) alwnull(*usrctl) ;

03  dcl-ds Data extname('TESTTABLE') qualified dim(12) ;
04  end-ds ;

05  dcl-ds Nulls qualified dim(12) ;
06    Ind int(5) dim(2) ;
07  end-ds ;

08  exec sql DECLARE C0 CURSOR FOR
09             SELECT * FROM TESTTABLE
10             FOR READ ONLY ;

11  exec sql OPEN C0 ;

12  exec sql FETCH C0 FOR 12 ROWS INTO :Data :Nulls ;

13  exec sql CLOSE C0 ;

14  *inlr = *on ;

Lines 3 and 4: The data structure array to contain the data from TESTTABLE.

Lines 5 – 7: I want to know which rows have a null value in COLUMN1, therefore, I need to use an array with one element for each column in the table, line 6. And I want to make this a "two dimensional" array to hold the same number of elements are my previous data structure array.

Lines 8 – 10: Definition of the cursor I will be using.

Line 11: Open the cursor.

Line 12: Fetch the 12 rows from the table into the data structure array Data. There is no comma before Nulls, therefore, the null indicators are moved to that data structure array.

Line 13: Close the cursor.

After compiling this source code into a program, I add a debug breakpoint at line 14. Then I call the program.

When I stop at the breakpoint I can view the contents of the Data data structure array:

  DATA.COLUMN1(1) = '1'
  DATA.COLUMN2(1) = '1         '
  DATA.COLUMN1(2) = '0'
  DATA.COLUMN2(2) = '2         '
  DATA.COLUMN1(3) = '0'
  DATA.COLUMN2(3) = '3         '
  DATA.COLUMN1(4) = '1'
  DATA.COLUMN2(4) = '4         '
  DATA.COLUMN1(5) = '0'
  DATA.COLUMN2(5) = '5         '
  DATA.COLUMN1(6) = '1'
  DATA.COLUMN2(6) = '6         '
  DATA.COLUMN1(7) = '0'
  DATA.COLUMN2(7) = '7         '
  DATA.COLUMN1(8) = '1'
  DATA.COLUMN2(8) = '8         '
  DATA.COLUMN1(9) = '0' 
  DATA.COLUMN2(9) = '9         ' 
  DATA.COLUMN1(10) = '1'
  DATA.COLUMN2(10) = '10        '
  DATA.COLUMN1(11) = '0'
  DATA.COLUMN2(11) = '11        '
  DATA.COLUMN1(12) = '0'
  DATA.COLUMN2(12) = '12        '

And then I can look at the null indicator data structure array to see which rows' COLUMN1

> EVAL nulls
  NULLS.IND(1,1) = 0
  NULLS.IND(1,2) = 0
  NULLS.IND(2,1) = 0
  NULLS.IND(2,2) = 0
  NULLS.IND(3,1) = -1
  NULLS.IND(3,2) = 0
  NULLS.IND(4,1) = 0
  NULLS.IND(4,2) = 0
  NULLS.IND(5,1) = 0
  NULLS.IND(5,2) = 0
  NULLS.IND(6,1) = 0
  NULLS.IND(6,2) = 0
  NULLS.IND(7,1) = 0
  NULLS.IND(7,2) = 0
  NULLS.IND(8,1) = 0
  NULLS.IND(8,2) = 0
  NULLS.IND(9,2) = 0  
  NULLS.IND(10,1) = 0 
  NULLS.IND(10,2) = 0
  NULLS.IND(11,1) = 0
  NULLS.IND(11,2) = 0
  NULLS.IND(12,1) = -1
  NULLS.IND(12,2) = 0

Which is the third and twelfth rows.

There is another way I can handle nulls from a Table when fetching from a cursor, by using IFNULL.

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-ds Data extname('TESTTABLE') qualified dim(12) ;
04  end-ds ;

05  exec sql DECLARE C0 CURSOR FOR
06             SELECT IFNULL(COLUMN1,'0'),COLUMN2
07               FROM TESTTABLE
08             FOR READ ONLY ;

09  exec sql OPEN C0 ;

10  exec sql FETCH C0 FOR 12 ROWS INTO :Data ;

11  exec sql CLOSE C0 ;

12  *inlr = *on ;

No null indicator array is needed.

Line 6: I have to list the columns in the table so I can use the IFNULL with COLUMN. If COLUMN1 is null then I am going to replace that with a value of '0', which is the same as false.

I am only going to show the first three array elements from Data as the third row was null:

> EVAL data
  DATA.COLUMN1(1) = '1'
  DATA.COLUMN2(1) = '1         '
  DATA.COLUMN1(2) = '0'
  DATA.COLUMN2(2) = '2         '
  DATA.COLUMN1(3) = '0'
  DATA.COLUMN2(3) = '3         '

As you can see it is now the same as the false element, the second one.

I can insert rows into TESTTABLE using the values I described above for the Boolean column. But as RPG converted that to an indicator I wondered if I could insert into COLUMN1 using a RPG indicator.

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-s Indicator ind ;

04  exec sql SET OPTION COMMIT = *NONE ;

05  exec sql INSERT INTO TESTTABLE VALUES(:Indicator,'A') ;

06  Indicator = *on ;
07  exec sql INSERT INTO TESTTABLE VALUES(:Indicator,'B') ;

08  *inlr = *on ;

Line 3: I have defined an indicator variable.

Line 4: I have used the SET OPTION to show that I will not be using commitment control when I am inserting my rows.

Line 5: First insert. At this point the indicator is off.

Line 6: Set the indicator to on.

line 7: Insert a second row into the Table.

I did try this with the *INXX indicators but they are not allowed in the Insert statement.

After compiling and calling the program the two rows were inserted.

COLUMN1   COLUMN2
-------   -------
false     A
true      B

In my last example I wanted to learn how CL would handle the Boolean data type. This was the example CL program:

01  PGM

02  DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')

03  DCLF FILE(TESTTABLE) ALWNULL(*YES)

04  DOWHILE COND(&LOOP)
05    RCVF
06    MONMSG MSGID(CPF0864) EXEC(LEAVE)

07    DMPCLPGM
08  ENDDO

09  ENDPGM

Line 3: When declaring my file, TESTTABLE, I need to add the ALWNULL keyword so that the program will not error when null is encountered in COLUMN1.

Lines 4 – 8: Is a Do loop to read all of the rows from the file.

Line 6: When end of file is encountered the loop is exited.

Line 7: I perform a program dump to capture the values in the file's variables.

After compiling the program, I call it. Twelve dumps are produced, but I am only interested in the first three. When I go to the bottom of those I can see the values in the file's variables:

Variable               Type        Length         Value
                                                   *...+....1.
&COLUMN1                 *CHAR            1       '1'
&COLUMN2                 *CHAR           10       '1         '


Variable               Type        Length         Value
                                                   *...+....1.
&COLUMN1                 *CHAR            1       '0'
&COLUMN2                 *CHAR           10       '2         '


Variable               Type        Length         Value
                                                   *...+....1.
&COLUMN1                 *CHAR            1       '0'
&COLUMN2                 *CHAR           10       '3         '

This shows that CL has translated the Boolean column to a single character variable, and the third row's null has been translated to '0'.

Above I mentioned I was using ACS's Run SQL scripts to work with the Boolean data. That is because it shows true and false. If I had used the STRSQL command I would see:

COLUMN1   COLUMN2
-------   ------
   1      1
   0      2
   -      3

If you are still using STRSQL this is another reason to move over to Run SQL scripts.

If anyone uses WRKQRY or RUNQRY commands they will see the same result as was generated by STRSQL.

 

You can learn more about the Boolean data type from the IBM website here.

 

This article was written for IBM i 7.5 only.

5 comments:

  1. Thanks for another interesting article! This is a great enhancement and will help you keeping your database design consistent across your tables. Lately, in our shop, we agreed on using CHAR(1) with a check constraint of IN ('0', '1') in our new tables for boolean-like fields.
    How we did it in the past? Don’t ask: You will find a variety of implementations from 'Y'/'N' (yes/no), 'A'/'I' (active/inactive), 'J'/'N' (ja/nein), 'X'/'*blank to the predominant variant '/*blank (e.g. 'S' meaning sent and ' ' meaning not sent).
    Switching to real Boolean reminds me of the introduction of real date fields.

    Best regards,
    Markus

    PS: I guess you could have chosen "SELECT IFNULL(COLUMN1, false)" instead of "SELECT IFNULL(COLUMN1,'0')" in your example with the same result, right? The former looks a little bit better to me. ;-)

    ReplyDelete
  2. Finally!! All the other DB's have had it for decades...Even DB2 for Windows, and Linux had for ages...good news!

    ReplyDelete
  3. This is awesome! It's a small thing but not having to worry about a flag (indicator) field being filled with something other than 'Y' or 'N' (or did you use blanks? Don't forget to use UPPER()!) will be very beneficial.

    ReplyDelete
  4. This will be fantastic...

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.