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 Updates > Db2 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.
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:
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;
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]
Was this topic helpful?
Document Information
Modified date:
06 October 2020
UID
ibm16340675