Prendre en charge une erreur sql [Résolu]

machipot · 01/10/2007, 21h06

Bonjour,

Je suis confrenté a un probleme... de l'aide s.v.p
1- je cherche comment recuperer le resultat du sqlerror dans SQLERRCD sans que la stored se casse. Portez directement vos ajouts sur le code. merci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
CREATE PROCEDURE FSMS.NOTICE_UPDT_SAVE (
                                    IN  I_notice_name       char(20),
                                    IN  I_Start_dt          char(10),
                                    IN  I_End_dt            char(10),
                                    IN  I_French_html       varchar(4000),
                                    IN  I_English_html      varchar(4000),
                                    IN  I_person_id         decimal(10,0),
                                    out O_error_nr          integer,
                                    OUT SQLERRCD    INTEGER)
--
 
    RESULT SETS 1
    LANGUAGE SQL
--    WLM ENVIRONMENT DB2DWAPP
    MODIFIES SQL DATA
    COLLID FSMS
    asutime no LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&137.122.105.233:*)'
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
-- 1000 end_dt is less or equal then start_dt
--
P1: BEGIN
 
    DECLARE V_Existance INT DEFAULT 0;
    DECLARE V_Person_ky INT DEFAULT 0;
 
--     DECLARE cursor1 CURSOR WITH RETURN FOR
 
    -- if the notice name exists...
    IF V_Existance = 1 THEN
        -- update the notice
        UPDATE  GAF.NOTICE
        SET
                NOTICE_NAME     = I_notice_name,
                START_DT        = I_Start_dt,
                END_DT          = I_End_dt,
                FRENCH_HTML     = I_French_html,
                ENGLISH_HTML    = I_English_html,
                UPDATE_TMST     = CURRENT_TIMESTAMP,
                UPDATE_PERSONKY = V_Person_ky
 
        WHERE  NOTICE_NAME = I_notice_name ;
    ELSE
        -- insert a new notice row
        INSERT INTO  GAF.NOTICE
              (
                NOTICE_NAME,
                START_DT,
                END_DT,
                ENGLISH_HTML,
                FRENCH_HTML,
                UPDATE_TMST,
                UPDATE_PERSONKY,
                LOG_COUNTER     )
        VALUES(
                I_notice_name,
                I_Start_dt,
                I_End_dt,
                I_English_html,
                I_French_html,
                CURRENT_TIMESTAMP,
                V_Person_ky ,
                1       );
    END IF;
 
 
--    if  I_End_dt <= I_start_dt then
--         set O_error_nr = 1000;
--    end if;
 
 
--   DECLARE EXIT HANDLER FOR SQLEXCEPTION
--     SET SQLERRCD = SQLCODE;
 
--    SET SQLERRCD = 0;
 
    --  get Person_Ky for a related Emp_ID
    SELECT  person_ky
    INTO    V_Person_ky
    FROM    UOTT.PERSON UO
    WHERE   UO.Emp_ID = I_Person_Id
    FETCH FIRST 1 ROW ONLY
    WITH UR;
 
--  verify if a notice name is already
--  in the table
    SELECT  1
    INTO    V_Existance
    FROM    GAF.NOTICE
    WHERE   NOTICE_NAME = I_notice_name
    FETCH FIRST 1 ROW ONLY
    WITH UR;
 
 
 
 
--  open cursor1;
 
 
END P1
-- --------------------------------------------------------------------
-- date         sr#     user
-- -----------  ----    -------- --------------------------------------

quand j'execute, je reçoi le message:
A database manager error occurred.[IBM][CLI Driver][DB2] SQL0545N The requested operation is not allowed because a row does not satisfy the check constraint "END_DT ". SQLSTATE=23513

FSMS.NOTICE_UPDT_SAVE - Roll back completed successfully.
FSMS.NOTICE_UPDT_SAVE - Run failed.

Mercure · 02/10/2007, 13h47

J'essaierais qqch dans ce goût-là (en rouge). Mais c'est à déboguer.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE PROCEDURE FSMS.NOTICE_UPDT_SAVE (
                                    IN  I_notice_name       char(20),
                                    IN  I_Start_dt          char(10),
                                    IN  I_End_dt            char(10),
                                    IN  I_French_html       varchar(4000),
                                    IN  I_English_html      varchar(4000),
                                    IN  I_person_id         decimal(10,0),
                                    out O_error_nr          integer,
                                    OUT SQLERRCD    INTEGER)
--
 
    RESULT SETS 1
    LANGUAGE SQL
--    WLM ENVIRONMENT DB2DWAPP
    MODIFIES SQL DATA
    COLLID FSMS
    asutime no LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&137.122.105.233:*)'
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
-- 1000 end_dt is less or equal then start_dt
--
P1: BEGIN
 
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ’00000’;
DECLARE msgtxt CHAR(70);
DECLARE msgtxtlen INTEGER;

GET DIAGNOSTICS EXCEPTION 1
msgtxt=MESSAGE_TEXT, msgtxtlen=MESSAGE_LENGTH;


    DECLARE V_Existance INT DEFAULT 0;
    DECLARE V_Person_ky INT DEFAULT 0;
 
--     DECLARE cursor1 CURSOR WITH RETURN FOR
 
    -- if the notice name exists...
    IF V_Existance = 1 THEN
        -- update the notice
        UPDATE  GAF.NOTICE
        SET
                NOTICE_NAME     = I_notice_name,
                START_DT        = I_Start_dt,
                END_DT          = I_End_dt,
                FRENCH_HTML     = I_French_html,
                ENGLISH_HTML    = I_English_html,
                UPDATE_TMST     = CURRENT_TIMESTAMP,
                UPDATE_PERSONKY = V_Person_ky
 
        WHERE  NOTICE_NAME = I_notice_name ;
    ELSE
        -- insert a new notice row
        INSERT INTO  GAF.NOTICE
              (
                NOTICE_NAME,
                START_DT,
                END_DT,
                ENGLISH_HTML,
                FRENCH_HTML,
                UPDATE_TMST,
                UPDATE_PERSONKY,
                LOG_COUNTER     )
        VALUES(
                I_notice_name,
                I_Start_dt,
                I_End_dt,
                I_English_html,
                I_French_html,
                CURRENT_TIMESTAMP,
                V_Person_ky ,
                1       );
    END IF;
 
 
--    if  I_End_dt <= I_start_dt then
--         set O_error_nr = 1000;
--    end if;
 

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET SQLERRCD = SQLCODE;;
DECLARE CONTINUE HANDLER FOR  -- Constraint violation
SQLSTATE ’23505’,
SQLSTATE ‘23510’,
SQLSTATE ‘23511’,
SQLSTATE ‘23512’,
SQLSTATE ‘23513’
SET SQLERRCD = SQLCODE; 


 
--    SET SQLERRCD = 0;
 
    --  get Person_Ky for a related Emp_ID
    SELECT  person_ky
    INTO    V_Person_ky
    FROM    UOTT.PERSON UO
    WHERE   UO.Emp_ID = I_Person_Id
    FETCH FIRST 1 ROW ONLY
    WITH UR;
 
--  verify if a notice name is already
--  in the table
    SELECT  1
    INTO    V_Existance
    FROM    GAF.NOTICE
    WHERE   NOTICE_NAME = I_notice_name
    FETCH FIRST 1 ROW ONLY
    WITH UR;
 
 
 
 
--  open cursor1;
 
 
END P1

machipot · 02/10/2007, 15h42

Merci mercure, j'ai arrangé un peu le code et ça marche super bien.
A+

02/10/2007, 15h42	#3
machipot Membre à l'essai Inscription : septembre 2006 Messages : 115 Détails du profil Informations forums : Inscription : septembre 2006 Messages : 115 Points : 23 Points : 23	Merci mercure, j'ai arrangé un peu le code et ça marche super bien. A+
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email