Retrieving information from a SQL proc about a failed insert

[DayLaborer]

I have the following procedure that attempts an insert.

CREATE PROCEDURE CRDQRS.NATION_Insert(
      IN p_MULTINATIONALAREAID CHAR (38),
      IN p_NATIONID CHAR (38),
      OUT p_SQLCODE INTEGER NULL,
      OUT p_SQLERRMC VARCHAR(70) NULL)

 EXTERNAL NAME QRSP0000
 WLM ENVIRONMENT DB2BSP
 COLLID QRS
 DYNAMIC RESULT SETS 0
 MODIFIES SQL DATA
 LANGUAGE SQL
 BEGIN

 DECLARE SQLCODE   INTEGER;--
 DECLARE SQLERRMC VARCHAR(70);--

 INSERT INTO NATION (
      MULTINATIONALAREAID,
      NATIONID)
 VALUES (
      p_MULTINATIONALAREAID,
      p_NATIONID);

SET p_SQLCODE = SQLCODE;--
SET p_SQLERRMC = SQLERRMC;--

 END

Occassionally, this insert fails due to a duplicate record.

Originally, I had an error check prior to attempting the insert:

DECLARE TEMP INTEGER;
SELECT MULTINATIONALAREAID INTO TEMP
FROM MULTINATIONALAREA
WHERE MULTINATIONALAREAID = p_MULTINATIONALAREAID
IF SQLCODE <> 0 THEN
(I HAD ANOTHER PARAMETER) p_IFSUCCESS=0
ELSE
(DO THE INSERT)

so theoretically, the insert would never happen if there was an error.

The problem is that my DBA told me that we shouldn't do that; rather, let DB2 attempt the insert. So now if the insert does fail, my output parameters do not return the needed information. Namely:[ul]
[li]the name of the table into which the insert was attempted[/li]
[li]the duplicate primary key[/li]
[li]the name of the stored procedure[/li][/ul]

I found some information online about "handler conditions". Is this the right path? If so, what's the proper way to use them? How can I get the needed information about the failed insert?

Thank you very much,
Eliezer

 

[DayLaborer]

Any ideas?


Eliezer

 

[blom0344]

DEpending on settings you may check the db2dialog file.
Should be somewhere in the root of the DB2 install directory.

Ties Blom

 

[DayLaborer]

Here's the solution that worked!

Eliezer

CREATE PROCEDURE CRDCRM.NATION_Insert(
        IN p_MULTINATIONALAREAID CHAR (38),
    IN p_NATIONID CHAR (38),
        OUT p_SQLCODE INTEGER,
        OUT p_SQLERRMC  VARCHAR(250),
        OUT p_SQLSTATE  CHAR(5))
 
   EXTERNAL NAME CRMP0000
   WLM ENVIRONMENT DB2BSP
   COLLID CRM
   DYNAMIC RESULT SETS 0
   MODIFIES SQL DATA
   LANGUAGE SQL
   BEGIN
 
    DECLARE EXIT  HANDLER FOR  SQLEXCEPTION
    GET DIAGNOSTICS CONDITION
      1 p_SQLERRMC = MESSAGE_TEXT,
        p_SQLCODE  = DB2_RETURNED_SQLCODE,
        p_SQLSTATE = RETURNED_SQLSTATE;
 
 
    INSERT INTO NATION (
         MULTINATIONALAREAID,
         NATIONID)
    VALUES (
         p_MULTINATIONALAREAID,
               p_NATIONID);
          END