Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    USA
    Posts
    4

    Question Unanswered: Raise Error in DB2 Stored Procedure?

    I am a SQL Server guy trying to write some DB2 Stored Procedures, needless to say, I am having a tough go at it.

    The SPs are being called by ADO and I would like to raise custom, non-SQL errors to our application from the Stored Procedure.

    All I see that is available to me for use in an SP are the SQL HANDLERS. I don't seem to be able to call SQL's RAISE_ERROR in the procedure.

    I am creating the procedures using DB2 Stored Procedure Builder.

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Posts
    1

    Re: Raise Error in DB2 Stored Procedure?

    Try looking for the DB2 Command SIGNAL. The value of the error that you can raise is constrained (see the documentation for details) but you can set a custom (string) message (returned in the SQLERRMC field of SQLCA).

    Cheers,
    Mike

    Originally posted by mhornak
    I am a SQL Server guy trying to write some DB2 Stored Procedures, needless to say, I am having a tough go at it.

    The SPs are being called by ADO and I would like to raise custom, non-SQL errors to our application from the Stored Procedure.

    All I see that is available to me for use in an SP are the SQL HANDLERS. I don't seem to be able to call SQL's RAISE_ERROR in the procedure.

    I am creating the procedures using DB2 Stored Procedure Builder.

    Thanks!

  3. #3
    Join Date
    Dec 2002
    Location
    USA
    Posts
    4

    Re: Raise Error in DB2 Stored Procedure?

    Mike,

    Thanks for the information. I looked into it and the DB2 Documentation states that SIGNAL can only be used triggers.

    I gave it the college try anyway and the compiler still complains. I tried implementing it in a HANDLER as well with no luck.

    My theory of 'Nothing is every easy" still holds true!

    Mark



    Originally posted by mginou
    Try looking for the DB2 Command SIGNAL. The value of the error that you can raise is constrained (see the documentation for details) but you can set a custom (string) message (returned in the SQLERRMC field of SQLCA).

    Cheers,
    Mike

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Try creating the procedure below. Run it passing in either 'X', 'Y' or 'Z' to raise an error (note the different erro message for value 'Z').

    CREATE PROCEDURE SP_TEST (
    IN P_TEST VARCHAR(15)
    )
    LANGUAGE SQL
    SPECIFIC V01_00_TEST

    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE v_errLabel VARCHAR(10) DEFAULT 'stmt 0';
    DECLARE v_errMsg VARCHAR(70);
    DECLARE v_errState CHAR(5);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SET v_errState = SQLSTATE;
    CASE v_errState
    WHEN '38T01' THEN RESIGNAL;
    WHEN '38T02' THEN RESIGNAL;
    ELSE SET v_errMsg = 'SP_TEST: SQL error at location: '
    CONCAT v_errLabel
    CONCAT ' SQLSTATE: '
    CONCAT v_errState;
    SIGNAL SQLSTATE '38T00' SET MESSAGE_TEXT = v_errMsg;
    END CASE;
    END;

    SET v_errLabel = 'stmt 1';

    IF ( P_TEST = 'X' )
    THEN
    SIGNAL SQLSTATE '38T01'
    SET MESSAGE_TEXT = 'SP_TEST: Invalid parameter on input';
    ELSEIF ( P_TEST = 'Y' )
    THEN
    SIGNAL SQLSTATE '38T02'
    SET MESSAGE_TEXT = 'SP_TEST: Invalid parameter on input';
    ELSEIF ( P_TEST = 'Z' )
    THEN
    SIGNAL SQLSTATE 'XXXXX'
    SET MESSAGE_TEXT = 'SP_TEST: YOU WILL NOT SEE THIS MESSAGE';
    END IF;

    END P1
    Last edited by Damian Ibbotson; 12-19-02 at 06:08.

Posting Permissions

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