Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    18

    Unanswered: Why the exception handling doesn't work?

    CREATE PROCEDURE proc_zujbd11(INP_DATE CHAR(8),INP_SHNAME VARCHAR(20) )
    BEGIN
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE msg_text VARCHAR(200);
    DECLARE sql_code INTEGER DEFAULT 0 ;
    DECLARE sql_msg VARCHAR(200);
    DECLARE sdate DATE ;
    DECLARE cnt INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK;
    SET sql_code = SQLCODE ;
    GET DIAGNOSTICS EXCEPTION 1 msg_text = MESSAGE_TEXT ;
    SET sql_msg =
    SUBSTR( CHAR(sql_code) ||':' || char(msg_text),1,200 );
    DELETE FROM ZUDTB03 WHERE SH_NAME = INP_SHNAME;
    INSERT INTO ZUDTB03 VALUES (INP_SHNAME , 'ERCD'||':'||sql_msg);
    COMMIT ;
    END;
    -- メッセージテーブルを削除 --
    DELETE FROM ZUDTB03 WHERE SH_NAME = INP_SHNAME;

    -- ①処理基準日(削除日)を取得する --
    IF INP_DATE = '00000000' THEN
    -- 処理基準日取得 --
    SET sdate = (SELECT TODATE( CHAR( SYORIDATE ), 'YYYYMMDD' ) from ZUDTX04);
    ELSE
    -- パラメータの日付 --
    SET sdate = (SELECT TODATE( INP_DATE, 'YYYYMMDD' ) from SYSIBM.SYSDUMMY1);
    END IF;

    -- ②アクセスログテーブルの日付が①の日付以前のレコードをカウントする --
    SET CNT =( SELECT COUNT(*) from PODTX03
    WHERE TRNDATE < INT( TOCHAR( sdate, 'YYYYMMDD' ) )) ;

    -- メッセージテーブルに格納する --
    INSERT INTO ZUDTB03 VALUES
    ( INP_SHNAME ,'ERCD'||':'||'0000'||':'||CHAR(cnt));

    -- ②で選択したレコードを削除 --
    DELETE FROM PODTX03
    WHERE TRNDATE < INT( TOCHAR( sdate, 'YYYYMMDD' ));

    COMMIT ;

    END
    -----------------------------------------
    The table definition is :
    create table PODTX03(trndate int)
    create table zudtb03(sh_name char(20),message varchar(200))
    create table ZUDTX04(SYORIDATE int,SYORITIME int)

    I rename a table zudtx04:
    db2 rename zudtx04 to ss

    But I cannot get error message and error code from zudtb03.

    can anyone help me to determine why the exception handling doesn't work?

    tochar.sql:
    CREATE FUNCTION TOCHAR (D DATE, FMT Varchar(50)) \
    RETURNS Varchar(254) \
    SPECIFIC TO_CHARTimestamp \
    LANGUAGE SQL \
    CONTAINS SQL \
    NO EXTERNAL ACTION \
    NOT DETERMINISTIC \
    BEGIN ATOMIC \
    DECLARE Retv VARCHAR(254) DEFAULT ' '; \
    DECLARE Pos INTEGER DEFAULT 1; \
    DECLARE Len INTEGER DEFAULT 2; \
    \
    SET Retv = FMT; \
    \
    SET Pos = posstr(upper(Retv),'DD'); \
    IF Pos > 0 THEN \
    SET Retv = CASE \
    WHEN upper(substr(Retv,Pos+2,1)) = 'D' THEN \
    insert(Retv,Pos,3,substr(digits(dayofyear(D)),8,3) ) \
    ELSE \
    insert(Retv,Pos,2,substr(digits(day(D)),9,2)) \
    END; \
    END IF; \
    \
    SET Pos = posstr(upper(Retv),'MM'); \
    IF Pos > 0 THEN \
    SET Retv = insert(Retv,Pos,2,substr(digits(month(D)),9,2)); \
    END IF; \
    \
    SET Pos = posstr(upper(Retv),'YY'); \
    IF Pos > 0 THEN \
    SET Len = 2 + posstr(upper(substr(Retv,Pos+2,1)),'Y') + \
    posstr(upper(substr(Retv,Pos+3,1)),'Y'); \
    SET Retv = insert(Retv,Pos,Len,substr(digits(year(D)),11-Len,Len)); \
    END IF; \
    \
    RETURN Retv; \
    \
    END

    todate.sql:

    CREATE FUNCTION TODATE(inpdat VARCHAR(8), fmt VARCHAR(10)) \
    RETURNS DATE \
    LANGUAGE SQL \
    READS SQL DATA \
    SPECIFIC TO_DATEfmt \
    DETERMINISTIC \
    --RETURNS NULL ON NULL INPUT \
    BEGIN ATOMIC \
    DECLARE outdat DATE; \
    DECLARE str CHAR(10); \
    DECLARE tmp VARCHAR(10); \
    SET tmp = inpdat; \
    IF UPPER(fmt) = 'YYYYMMDD' \
    THEN \
    SET str = SUBSTR(tmp,1,4) || '-' || \
    SUBSTR(tmp,5,2) || '-' || SUBSTR(tmp,7,2); \
    ELSE \
    SIGNAL SQLSTATE '38Z01' \
    SET MESSAGE_TEXT = 'INVALID FORMAT SPECIFIED.'; \
    END IF; \
    SET outdat = DATE(str); \
    RETURN OUTDAT; \
    END

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    That's an interesting approach ... ;-)

    Have you tried using the Development centre Debugger, to see how far it goes ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •