Easy way to initialize columns when using SQL insert

Regular readers of this blog know that I like to keep things simple and easy. Partly for my own peace of mind, and partly if it is simple then other programmers can quickly understand what my code does. A colleague calls this the K.I.S.S. methodology, which stands for "Keep It Simple Stupid", although she often changes the last "S" to "Simon" (I am not sure what she is trying to imply by this).

A good example is when using SQL to insert a row/record into a table/file. If not all the columns/fields need to be filled I need to give them a default value. This means that I need to look at the table and determine the data type of each column and whether it can be null or not, which takes time and can leave to errors if I try and accidentally insert a columns/field with the wrong default value.

All the time spent identifying column/field defaults, etc. can be saved if I use the DEFAULT keyword for the column in the Insert statement. It will come as no surprise that the DEFAULT keyword inserts the columns default value into the column, whatever that default maybe.

This is my test table:

01 CREATE TABLE MYLIB/TESTTABLE ( 02 XKEY CHAR(5) DEFAULT NOT NULL, 03 XCHAR CHAR(10) DEFAULT NOT NULL, 04 XINT SMALLINT DEFAULT NOT NULL, 05 XDATE DATE DEFAULT NOT NULL, 06 XTIME TIME DEFAULT NOT NULL, 07 XTIMESTAMP TIMESTAMP DEFAULT NOT NULL, 08 XWITHDEFAULT CHAR(1) DEFAULT 'A', 09 XNULLABLE CHAR(5) DEFAULT NULL 10 ) ;

The first thing to notice is that all of the columns/fields are defined with the DEFAULT keyword.

Lines 2 – 7: As these are defined as DEFAULT NOT NULL their default value cannot be null, therefore, it will be the default value of that data type. In the case of the date, time, and timestamp columns these will be defaulted to the current date and time.

Line 8: Just to do something different this column's default value is the letter "A".

Line 9: This column is defined with the DEFAULT NULL, therefore, its default will be null rather than the default value for a character column.

To insert a row/record into this table I used CL's RUNSQL statement. I could have used a SQL embedded in a RPG program instead if I wanted, and the result would have been the same.

01 RUNSQL SQL('INSERT INTO MYLIB.TESTTABLE + 02 VALUES(''FIRST'',DEFAULT,DEFAULT,+ 03 DEFAULT,DEFAULT,DEFAULT,+ 04 DEFAULT,DEFAULT)') + 05 COMMIT(*NC) NAMING(*SQL)

The only column/field I am giving a value to is the first one, XKEY, the other seven columns/fields are being initialized with their default values. What does this look like:

XKEY XCHAR XINT XDATE XTIME FIRST 0 2016-06-08 05.00.00 XTIMESTAMP XWITHDEFAULT XNULLABLE 2016-06-08-05.00.00.000000 A -

I can do the same with a DDS file. This is the source for my file:

01 A R TESTFILER 02 A XKEY 5A 03 A XCHAR 10A 04 A XPACKED 5P 0 05 A XDATE L 06 A XTIME T 07 A XTIMESTAMP Z 08 A XWITHDFT 1A DFT('A') 09 A XNULLABLE 5A ALWNULL

Line 8: Has a default value of "A".

Line 9: The ALWNULL keyword allows the field to contain nulls.

This time I am putting the SQL statement in a RPG program. It is pretty much the same as the previous example, the only difference is the name of the table/file:

01 exec sql SET OPTION NAMING = *SQL ; 02 exec sql INSERT INTO MYLIB.TESTFILE 03 VALUES('FIRST',DEFAULT,DEFAULT, 04 DEFAULT,DEFAULT,DEFAULT, 05 DEFAULT,DEFAULT) ;

Line 1: I am setting the naming convention used by this program to SQL. If you are unfamiliar with the SET OPTION you need to check out Putting the SQL options into the source.

Lines 2 – 5: As I said this is the same apart from the name of the file, TESTFILE.

The results are the same as the previous:

XKEY XCHAR XPACKED XDATE XTIME FIRST 0 2016-06-08 05.00.00 XTIMESTAMP XWITHDFT XNULLABLE 2016-06-08-05.00.00.000000 A -

If you use the DEFAULT keyword it is going to save you a lot of time, and help others to understand that the column/field is being initialized with its default value.

This can also be used when using other SQL statments too, the Update for example.

UPDATE TESTABLE SET XCHAR = DEFAULT

 

You can learn more about the SQL Insert statement with the DEFAULT keyword from the IBM website here.

 

This article was written for IBM i 7.2, and should work for earlier releases too.