Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    134

    Answered: raise warning or informational message from a stored procedure

    Hi all,
    I'm writing a C application that connects via ODBC to a Db2 10.5 database on Linux.

    I need to create some stored procedures which, under certain conditions, return a warning to the C application, but this shouldn't affect the C++ flow, that is the C++ application shouldn't see an exception after the procedure is invoked. Instead, when the procedure is invoked manually from clp, the warning or informational message should be visible.

    From what I see available it seems that SIGNAL is used to communicate an error OR a warning, but is it possible to explicitly raise a warning or an informational message to the application, so that it doesn't cause an exception in the C++ code?

  2. Best Answer
    Posted by mark.bb

    "Hi,

    There is possibility to do the same with DB2.
    SIGNAL statement

    Code:
    --#SET TERMINATOR @
    create or replace procedure raise_something(p_sqlstate char(5))
    begin
      declare l_msg varchar(70);
      set l_msg = case when substr(p_sqlstate, 1, 2) in ('01', '02') then 'Warning' else 'Error' end || ': ' ||p_sqlstate;
      signal sqlstate p_sqlstate set message_text = l_msg;
    end@
    
    -- SQLCODE = +438 is returned (warning)
    call raise_something('01001')@
    
    -- SQLCODE = -438 is returned (error)
    call raise_something('03001')@
    Properly coded C++/ODBC program must handle this."


  3. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    You want to return optional informational (warning) status-information to the caller of an sproc, but you do not want the mechanism to "affect the flow"

    (1) Consider using an optional OUT parameter from the sproc(s), which either contain the empty string or the informational-message.

    (2) Consider using an optional (additional) result-set to contain the informational-message

    (3) Reconsider why you don't want to use a specific exception-handler for warning SQLSTATES, you can simply continue in the C++ code if you wrap the call to the sproc with a suitable exception handler so as not to cause abends. You can do what you like in your C++ exception handler including distinguishing between WARNING sqlstate values and ERROR sqlstates. If your exception handlers cannot distinguish between these two intelligently then you need to make that happen.

  4. #3
    Join Date
    Mar 2012
    Posts
    134
    I can consider all the three things, but just wondering why there's no possibility to do with DB2 what SQL Server does with RAISERROR, where if you specify a severity < 11 it's threated as a warning and doesn't make the C++/ODBC code fail, otherwise as an error and an exception is thrown

  5. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 45
    Hi,

    There is possibility to do the same with DB2.
    SIGNAL statement

    Code:
    --#SET TERMINATOR @
    create or replace procedure raise_something(p_sqlstate char(5))
    begin
      declare l_msg varchar(70);
      set l_msg = case when substr(p_sqlstate, 1, 2) in ('01', '02') then 'Warning' else 'Error' end || ': ' ||p_sqlstate;
      signal sqlstate p_sqlstate set message_text = l_msg;
    end@
    
    -- SQLCODE = +438 is returned (warning)
    call raise_something('01001')@
    
    -- SQLCODE = -438 is returned (error)
    call raise_something('03001')@
    Properly coded C++/ODBC program must handle this.
    Regards,
    Mark.

  6. #5
    Join Date
    Mar 2012
    Posts
    134
    Thanks Mark, this is very useful.

    The only problem is that if I include the statement
    call raise_something('01001')
    into a stored procedure SP and the statement is followed by other select statements, no message is shown when SP is executed.
    It looks like the statement needs to be the last one in the procedure for the warning message to be displayed.

    Is this something expected? How should I do to raise the warning message at the beginning of the procedure?

  7. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 45
    Yes, it’s expected.
    If you want to raise a warning from SP, the SIGNAL statement must be the last statement executed there. Otherwise the warning is “eaten” by the next statement after the SIGNAL.
    Regards,
    Mark.

Posting Permissions

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