IBM Support

SQL Precompiler additional support for WHENEVER

News


Abstract

The SQL Precompilers for ILE RPG, ILE COBOL, ILE C, and ILE C++ are enhanced to support the WHENEVER SQL statement.

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements >  SQL Precompiler additional support for WHENEVER

Before the WHENEVER SQL statement was enhanced, Embedded SQL programmers had to use a GOTO <label> to achieve common code for handling SQL errors, warnings, and not found conditions. 
With WHENEVER, the (ILE RPG, ILE COBOL, ILE C, and ILE C++) Embedded SQL programmer has an easy to use SQL statement to implement well defined SQL error handling.
Note: The WHENEVER SQL statement is allowed when precompiling with any Target Release (TGTRLS) value.
WHENEVER Syntax:
WHENEVER SQL statement syntax

See the SQL Reference for details: WHENEVER


ILE RPG example of using WHENEVER:
The first example RPG program demonstrates an existing use of the WHENEVER statement.  If any SQL statement gets a negative (error) SQLCODE, the error will be ignored and processing will continue.  This handles situations where the object already exists, but could also ignore other errors.
    exec sql WHENEVER SQLERROR CONTINUE;                                                                                                                                                exec sql                                                        CREATE TABLE SalesSummary(SalesDate Date,                                             TotalSales Bigint);           exec sql                                                        CREATE INDEX SalesSumIndex on SalesSummary(SalesDate);                                                                *INLR = *ON;                                                                           
In the second example, when an error is encountered an internal procedure is called.  It determines what the error is and writes a message to the joblog using the LPRINTF  SQL procedure.  This procedure can be a common error handling routine.  The logic is kept out of the mainline, keeping the main code clean and concise.
     DCL-PR PROC_ERROR;                                                  END-PR;                                                             EXEC SQL WHENEVER SQLERROR DO PROC_ERROR();                                                                                             exec sql insert into SalesSummary                                                 select current date, sum(sales), 'ExtraColumn' from              sales where sales_date = current date;                                                                                         *INLR = *ON;                                                                                                                            DCL-PROC PROC_ERROR;                                                   DCL-PI PROC_ERROR;                                                  END-PI;                                                             DCL-S local_sqlcode int(10);                                        DCL-S local_sqlstate char(5);                                       DCL-S v_message_text varchar(200);           exec sql get diagnostics condition 1                                           :local_sqlcode  = db2_returned_sqlcode,                               :local_sqlstate = returned_sqlstate,                                  :v_message_text = message_text;                              exec sql call systools.lprintf('SalesSum failed with SQLCODE='                    concat :local_sqlcode                                                 concat ' SQLSTATE=' concat :local_sqlstate concat                     ' MESSAGE= ' concat                                                   substring( :v_message_text, 1, 70 ));                  END-PROC;                                                                                 

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SS9QQS","label":"IBM i 7.4"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"IBM i 7.4","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
06 October 2020

UID

ibm16340675