CODEWITHSUNDEEP
sqldb2
Daniel
Daniel

Reputation: 1

How to catch exceptions in functions use DB2

I am reporting an error while running this code. For some reason, I have to add ATOMIC after BEGIN to execute the function, but it conflicts with SQLEXCEPT in the function body. What should I do?

When I call the function, I always report the SQL0390N error. Someone told me to add ATOMIC to the function after BEGIN, but I have encountered an exception in the function.

CREATE OR REPLACE FUNCTION IS_DATE(INDATE VARCHAR(10)) 
  RETURN INTEGER
  LANGUAGE SQL
BEGIN ATOMIC
  DECLARE V_DATE DATE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
      RETURN 0;
  END;
  SET V_DATE = TO_DATE(INDATE,'YYYYMMDD');

  RETURN 1;
END;

These statements will create conflicts “BEGIN ATOMIC” and "DECLARE EXIT HANDLER FOR SQLEXCEPTION" Please tell me what to do....

Upvotes: 0

Views: 2170

Answers (1)

mao
mao

Reputation: 12267

If you want to use an exception handler then you must use "Compound SQL (compiled)", and one way to specify that is to use BEGIN and not BEGIN ATOMIC. Other ways are possible.

IBM Db2-LUW has two different kinds of compound-SQL blocks to group multiple SQL PL statements.

Each kind has its own advantages and restrictions.

The first kind is inlined (it becomes part of the calling statement after compilation) and in the documentation this is called "Compound SQL (inlined)".

Compound-SQL (inlined) only offers a subset of SQL statements, and requires a BEGIN ATOMIC syntax at the start of the block.

In your example you show BEGIN ATOMIC , and you need to know that this prohibits declaring handlers inside the block, because it is the block (or statement) that invokes the inlined function that must handle errors. The documentation shows the statements that are allowed in a compound inlined block.

The second kind is not inlined (it is compiled and stored separately from the statement(s) that invoke it). In the documentation this is called "Compound SQL (compiled)".

Compound SQL (compiled) allows a bigger range of SQL PL statements compared to the inlined kind, and Compound SQL (compiled) is not required to be atomic (i.e. it can have savepoints , commits and rollbacks and exception handlers etc). The documentation is here.

Upvotes: 1

Related Questions