Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Unanswered: SQLSTATE and SQLCODE not correctly filled in a Stored Procedure

    Hello, I have the following stored procedure but the error variables are not populated with the output error code.
    In my case it should abort for duplicates , if I execute the SQL I have the folling error messages

    --One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because ---the primary key,
    --unique constraint or unique index identified by "1" constrains table "BDV.SAT_PERSON_GC" from having duplicate values for the index key..
    --SQLCODE=-803, SQLSTATE=23505, DRIVER=4.18.60


    But with my stored procedure, it abort but the SQLCODE and the SQLSTATE have the initial values ...





    P2: BEGIN

    ------------------ VARIABLES --------------------

    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLERRMC VARCHAR(70);


    DECLARE v_Sqlerr INTEGER DEFAULT 0;
    DECLARE v_errLabel VARCHAR(10) DEFAULT 'stmt 0';
    DECLARE v_errMsg VARCHAR(70) DEFAULT null;
    DECLARE v_errState CHAR(5);



    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SET EOF = 1;
    SET v_errState = SQLSTATE;
    SET v_Sqlerr = SQLCODE;
    SET v_errMsg = SQLERRMC;

    SET v_errMsg = 'SQL error at location: '
    CONCAT v_errLabel
    CONCAT ' SQLSTATE: '
    CONCAT v_errState
    CONCAT SQLERRMC;
    SIGNAL SQLSTATE '38T00' SET MESSAGE_TEXT = v_errMsg;
    END;



    INSERT INTO BDV.SAT_PERSON_GC (META_LDTS, META_LOADED_BY, META_SRC, PER_DATE_OF_BIRTH, PER_DECEASED_FLAG, PER_DISABILITY_CAT_CODE, PER_FIRST_INITIAL, PER_FORENAME, PER_GC_HUB_KEY, PER_GENDER_CODE, PER_MARITAL_STATUS_CODE, PER_NATIONAL_ID,PER_NATIONAL_ID_CAT_CODE, PER_PREFERRED_LANG_CODE, PER_PREFERRED_NAME, PER_SURNAME, PER_TITLE_CODE)
    VALUES (META_LDTS_BACK, META_LOADED_BY_BACK, META_SRC_BACK, PER_DATE_OF_BIRTH_BACK, PER_DECEASED_FLAG_BACK, PER_DISABILITY_CAT_CODE_BACK, PER_FIRST_INITIAL_BACK, PER_FORENAME_BACK, PER_GC_HUB_KEY_BACK, PER_GENDER_CODE_BACK, PER_MARITAL_STATUS_CODE_BACK, PER_NATIONAL_ID_BACK, PER_NATIONAL_ID_CAT_CODE_BACK, PER_PREFERRED_LANG_CODE_BACK, PER_PREFERRED_NAME_BACK, PER_SURNAME_BACK, PER_TITLE_CODE_BACK);

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is sqlstate 38t00 a valid value ??? -->DB21050E "38T00" is not a valid SQLSTATE
    see spserver.db2 in samples directory to have some correct samples...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Answer: symptom is caused by your programming mistakes. If you debugged your code you should find them.


    Two mistakes are obvious, there might be more...

    (1) to capture the sqlstate and sqlcode don't use your assignments, but instead
    use something like this in the exit-handler

    values(SQLCODE, SQLSTATE) into v_Sqlerr, v_errState ;

    (2) when you concat with NULL you get NULL so use COALESCE(SQLERRMC,'') for example, and
    do this for any variable that might be null when you concat.

    Warning: The SIGNAL SQLSTATE truncates the message after 70 bytes.
    Consider using DBMS_OUTPUT.PUT_LINE useful for debugging


    Separately :
    Your jdbc driver is V10.5 FP4.
    You should upgrade to V10.5 FP5.

  4. #4
    Join Date
    Mar 2015
    Posts
    31
    Quote Originally Posted by przytula_guy View Post
    is sqlstate 38t00 a valid value ??? -->DB21050E "38T00" is not a valid SQLSTATE
    see spserver.db2 in samples directory to have some correct samples...

    ... well using "get diagnostics exception" i get the information i need in my variables ...


    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    get diagnostics exception 1 v_msg = MESSAGE_TEXT;


    SET v_errState = SQLSTATE;
    SET v_Sqlerr = SQLCODE;
    SET v_errMsg = SQLERRMC;

    SET v_errMsg = 'SQL error at location: '
    CONCAT v_errLabel
    CONCAT ' SQLSTATE: '
    CONCAT v_errState
    CONCAT ' MSG: '
    CONCAT v_msg;

    SET EOF = 1;
    END;

  5. #5
    Join Date
    Mar 2015
    Posts
    31
    thank you for your Help

Posting Permissions

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