Wednesday, June 29, 2016

Easy way to initialize columns when using SQL insert

use default keyword in sql

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.

11 comments:

  1. To support the very important decoupling of data from the HLL code, it is always a best practice to specify the column names above the values in the insert SQL command. In this way, changes to the table do not cause a train wreck to the insert statements found all over the code base. This matters the same way not using SELECT * matters. The actual columns required should always be specified; again for decoupling of the DB from the HLL. In this way, no worry when the tables change; the HLL will not break. In a data-centric app, the code must always be decoupled from the DB.

    ReplyDelete
  2. If you're using SQL Insert instead of RPG Write, I agree with explicitly specifying column names above values. For example:

    Insert Into SSTU100P(
    NAME,
    PERSON,
    BDATE,
    BPLACE,
    ETHNIC,
    GENDER,
    GLEVEL,
    STATUS,
    XREF) Values (
    :NAME,
    :PERSON,
    :BDATE,
    :BPLACE,
    :ETHNIC,
    :GENDER,
    :GLEVEL,
    :STATUS,
    :XREF) ;

    But I don't see how that "decouples" the DB from the HLL. The point is to explicitly couple column names with program variables in order to avoid future errors caused by changes in DB layout.

    ReplyDelete
    Replies
    1. Nathan, the decoupling allows the underlining tables to be changed with little or no impact to the HLL code base. This is done by (1) never letting HLL access tables direction, (2) HLL only get data via data services. In this way the underlying tables can be significantly refactored to the point of re-ordering columns, re-naming columns, re-naming tables, adding new columns, removing columns, repurposing columns with little or no impact to the HLL. For example data services have interfaces, SQL views have interfaces that are static even if the underlying tables get changed in the ways I listed above. For example, VIEW XYZ is built over 1 table, but one could replace that table with 5 joined together and as long as the view's interface is not changed, the HLL that use that view are not impacted. This can only happen when the HLL and the DB are decoupled. Look at it this way: Sub-Proces, Stored Procedures, UDF's, table functions, VIEWS all have interfaces, and as long as those interfaces stay static, the UNDERLYING code/joins/tables can change left and right without impacting the HLL clients. Decoupling is the way to go so that components of an app can be snapped on and snapped off. We are doing this very thing at my gig now.

      Delete
  3. Nathan, look at it this way: data-services all have interfaces. Data service can be an SQL view, stored procedure, UDF, table function, even a sub-proc. And by interface I mean parm list, and even in the case of an SQL VIEW they have an interface too. So since all these data services have static interfaces, you could change their internal implementations left and right but as long as those interfaces remain static, the HLL clients will not care. This decoupling can only happen best if HLL clients are forbidden from accessing the TABLES directly; they only get their data, and get their data process via data services. For example, take an SQL VIEW, if that view access TABLE X, you could replace that query of TABLE X with a join of TABLES A, B, C and since the interface of the view is static (not changed) the calling HLL clients are not impacted and do not care. And in fact I just changed a view today that was accessing 1 table, but I replaced that with a UNION of 7 tables, and the interface was left unchanged, so how many HLL pgms did I need to change or recompile? None. Level checks? none. When you place a deep abyss between the HLL code and the DB, you have a lot of fantastic options. We forbid all HLL clients from accessing tables directly; they only get their data and their data processed via data services which are: web services, sub-proc, stored procedures, UDF's, table functions, and especially VIEWs. VIEWS can have interfaces just like a callable service, and like the parms list of a sub-proc or stored procedure, their interface acts like a contract between client callers and the data service.

    ReplyDelete
  4. Moreover, when the HLL code is decoupled completely from the DB, you could hypothetically change the name of the underlying tables, change the spelling of the columns, change the order of the columns, move columns from one table to another, normalize the DB so that the number of tables goes up for say 100 to 149 and not have to recompile or tweak the code base. This is what decoupling provides. The main rule however is that no HLL/Caller/Client should be allowed to access tables direction, nor process data directly...all this is provided only be encapsulate data services with are stateless, and preferable caller-agnostic. This latter preference is awesomely provided by RESTful web services w/JSON payloads.

    ReplyDelete
  5. You know you have a decoupled code/DB when (1) the code base does not need to know the structure nor the names of the underlying tables of the DB, (2) the code base does not need to know where the data services live, whether they're local or remote, nor what platform/language they in, and (3) you can do significant refactoring of the DB, and as long as contracts are respected (interfaces) no need to recompile or tweak the code base. De-coupling + the application of SOA + MVC patterns = World Class App that is very easy to support, and upgrade, and will is conducive to agile methodologies.

    ReplyDelete
  6. Hello, nice article.
    Is the same "DEFAULT NOT NULL" that "NOT NULL WITH DEFAULT"?

    ReplyDelete
  7. @Dan Lovell

    Just wow... that was the best and most instructive argument for decoupling I have ever read. I hope you don't mind, but I am going to quote you...

    ReplyDelete
  8. I'm not a fan of this approach. Here's my thinking:
    Does your table have a primary key? If not you are possibly looking at inserts with a key of all blanks. If you have a constraint on the table you might get lucky and only bang into this once. Otherwise it's too easy to shoot oneself in the foot. If a column is meant to have data then take the default off and allow the database to do the work of requiring data or not allowing the record to be written.

    ReplyDelete
  9. Just omitting a column name assigns the default value. And yes, always specify the column names or the INSERT will fail in the future (incorrect number of values specified) if you add a new column to the table.

    Insert Into MYLIB.TESTFILE
    (XDATE, XPACKED, XKEY)
    Values('1816-12-11', 123, 'HI MOM')

    XCHAR, XTIME, XTIMESTAMP, XWITHDFT, XNULLABLE have their default values assigned.

    Chris Ringer

    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.