New data type for SQL - Boolean

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.