Pages

Wednesday, October 5, 2022

Do not SQL insert if there is already a row present with the same key

The need was understandable. Every time I made a change to a file I need to insert the file's library into another table. I only needed the library name in the table once. And I did not want my statement to error.

The table is every simple, just one column for the library name:

01  CREATE TABLE MYLIB.TESTTABLE
02  (LIBRARY CHAR(10),
03   PRIMARY KEY(LIBRARY)) ;

Inserting the first record is not a problem:

INSERT INTO TESTTABLE VALUES('MYLIB')

The "Message environment" in my ACS Run SQL Scripts session reports:

Statement ran successfully   (18 ms)
1 rows were affected by the statement

The table has a unique key, because of the PRIMARY KEY, when I try to insert a second row with the same library name:

INSERT INTO TESTTABLE VALUES('MYLIB')

This time I get a red message in the "Message environment":

SQL State: 23505
Vendor Code: -803
Message: [SQL0803] Duplicate key value specified.

I use the Run SQL command, RUNSQL, a lot and I am always frustrated how badly it handles errors.

If I try to insert a duplicate row into the table with the following statement:

 RUNSQL SQL('INSERT INTO TESTTABLE +        
             VALUES(''MYLIB'')') COMMIT(*NC)

I get the SQL9010 that I do when any SQL statement fails in RUNSQL, and I have to look in the job log for the reason:

CALL PGM(TESTCL)
Duplicate record key in member TESTTABLE.
Duplicate key on access path.
Duplicate key on access path.
Duplicate key value specified.
RUNSQLSTM or RUNSQL command failed.
Function check. SQL9010 unmonitored by TESTCL at statement
  0000000200, instruction X'0000'.
SQL9010 received by procedure TESTCL. (C D I R)
SQL9010 received by procedure TESTCL. (C D I R)

RPG will allow a failed SQL statement to pass you by, unless you check for it. The code snippet below is the crudest way to capture that something errored in the previous SQL statement:

01  exec sql INSERT INTO TESTTABLE VALUES('MYLIB') ;
02  if (SQLCOD <> 0) ;
03    dsply ('SQLCOD = ' + %char(SQLCOD)) ;
04  endif ;

Lines 2 – 4: If the SQL code, SQLCOD, is not zero I will display the code.

After the program has completed when I look in the job log I see:

Duplicate record key in member TESTTABLE.
Duplicate key on access path.
?
Duplicate key on access path.
?
Duplicate key value specified.
DSPLY  SQLCOD = -803

I always recommend everyone should use the GET DIAGNOSTICS as that allows you to get more information about previous SQL statement:

01  dcl-s MessageId char(7) ;
02  dcl-s MessageText char(40) ;

03  exec sql INSERT INTO TESTTABLE VALUES('MYLIB') ;

04  exec sql GET DIAGNOSTICS CONDITION 1
               :MessageId = DB2_MESSAGE_ID,
               :MessageText = MESSAGE_TEXT ;

Line 4: After the SQL Insert I execute the GET DIAGNOSTICS to retrieve the system message id the text for the message.

Here I used debug to view the contents of the two variables:

MESSAGEID = 'SQL0803'
MESSAGETEXT = 'Duplicate key value specified.          '

I am sure you all have your ways of stopping this error in your SQL statements. I am going to share a simple statement I came up with to prevent a "duplicate key" error from occurring.

01  INSERT INTO TESTTABLE
02  VALUES('MYLIB')
03  EXCEPT
04  SELECT LIBRARY FROM TESTTABLE

Line 3: I have written about using the EXCEPT before. EXCEPT acts like a "not in the following sub-statement". In this case the value 'MYLIB' will only be inserted if it is not already present in TESTTABLE.

When I run the statement in Run SQL Scripts I get the following in the "Message environment":

Statement ran successfully   (37 ms)
0 rows were affected by the statement

Zero rows were affected as a row was not inserted into the table.

I can put the same statement into the RUNSQL command in a CL program:

01  DCL VAR(&SQL) TYPE(*CHAR) LEN(80)
02  DCL VAR(&LIBRARY) TYPE(*CHAR) LEN(10) VALUE('MYLIB')

03  CHGVAR VAR(&SQL) +
             VALUE('INSERT INTO TESTTABLE +
                    VALUES(''' || &LIBRARY |< ''') +
                    EXCEPT +
                    SELECT LIBRARY FROM TESTTABLE')
                                                    
04  RUNSQL SQL(&SQL) COMMIT(*NC)

I need to explain two things that make this program so different from the first CL program I showed:

  1. I want to give an example where the name of the library is in a variable, &LIBRARY, line 2.
  2. I could build the statement to be executed in the RUNSQL statement, but I prefer not to do that. If the program errors with a SQL error I have no idea if I have something wrong in my statement. I prefer to build the statement in a variable, here in &SQL. If RUNSQL fails I can check that my SQL statement is valid or not.

I add a debug breakpoint at line 4, and this gives me the opportunity to check that my SQL statement is valid:

EVAL &SQL
&SQL =
     ....5...10...15...20...25...30...35...40...45...50...55...60
 1  'INSERT INTO TESTTABLE VALUES('MYLIB') EXCEPT SELECT LIBRARY '
61  'FROM TESTTABLE      '

When the RUNSQL is executed there is nothing written to job log as it completed successfully.

I can do the same using RPG:

01  dcl-s Library char(10) inz('MYLIB') ;

02  exec sql INSERT INTO TESTTABLE
             VALUES(:Library)
             EXCEPT
             SELECT LIBRARY FROM TESTTABLE ;

03  if (SQLCOD <> 0) ;
04    dsply ('SQLCOD = ' + %char(SQLCOD)) ;
05  endif ;

Line 1: The library is in a variable, Library.

Lines 3 – 5: Again this is the basic version of the program that just checks the SQLCOD.

After running this program I find no error entries in the job log, I just find the following:

DSPLY  SQLCOD = 100

In the next program I use GET DIAGNOSTICS to get better messages about the insert.

01  dcl-s Library char(10) inz('MYLIB') ;
02  dcl-s MessageId char(7) ;
03  dcl-s MessageText char(40) ;

04  exec sql INSERT INTO TESTTABLE
               VALUES(:Library)
               EXCEPT
               SELECT LIBRARY FROM TESTTABLE ;

05  exec sql GET DIAGNOSTICS CONDITION 1
               :MessageId = DB2_MESSAGE_ID,
               :MessageText = MESSAGE_TEXT ;

The messages returned from GET DIAGNOSTICS are:

MESSAGEID = 'SQL0100'
MESSAGETEXT = 'Row not found for INSERT.               '

I think the Message Text is little confusing, but you get the idea of the information you can get.

I am now using this, and just wanted to share in case anyone else has the same need.

 

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

3 comments:

  1. This is great and something I have never known about. I always use a where clause with not exists.
    I wonder how this works with more than 1 key and if it’s more efficient.
    This old dog keeps learning new tricks from Simon. Thanks!

    ReplyDelete
  2. Excellent! As the prior comment, I've always used the where not exists clause. Would also like to know the syntax for more than one key.

    ReplyDelete
  3. I had a file with 4fields keyed on fielda unique and did an insert using "insert into filename (fielda,fieldb,fieldc,fieldd) values (:a, :b, :c, :d) except select * from file name" and it worked

    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.