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.