ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Function Error Handling

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Function Error Handling

    OS 7.1 with all PTFs

    I have a condition handler in an sql function that returns a warning, but I am having issues trapping the error in the calling stored procedure.

    Here is a snippet of the sql function returning the warning.

    Code:
    create or replace function ediShipmentStatusXref
    (
      pEDIpartnerId           varchar(15),
      pStatus                 varchar(2),
      pStatusType             varchar(2),
      pStatusDateType         varchar(3)
    )
    returns integer
    
    begin
    ...
    declare lStatusTypeId                         integer       default null;
    
    declare shipment_status_xref_not_found    condition for '01H01';
    
    declare exit handler for sqlexception, sqlwarning, not found
    begin
      set retCode = sqlCode;
      set lMsgText = 'No Shipment Status Type Cross Reference';
      signal shipment_status_xref_not_found set message_text = lMsgText;
      return lStatusTypeId;
    end;
    
    select status_type_id into lStatusTypeId from if_edi_shipment_status_xref where *** some criteria ***;
    
    return lStatusTypeId;
    end;
    The joblog shows that the warning is being successfully returned when a record is not found.

    Here is what I am *currently* trying in the calling stored procedure; I've tried multiple strategies and permuations.

    Code:
    ...
    begin
    ...
    declare lStatusTypeId                    integer       default null;
    declare retCode                          integer       default 0;
    ...
    declare exit handler for sqlexception, sqlwarning
    begin
      set retCode = sqlcode;
      get diagnostics exception 1 sqlerrm = message_text;
    
      /* record error message to sql message log */
    
      resignal;
    end;
    
      begin
       -- declare exit handler for shipment_status_xref_not_found
        declare exit handler for sqlexception, sqlwarning, not found
        begin
          set retCode = sqlcode;
          get diagnostics exception 1 sqlerrm = message_text;
    
          /* record error message to sql message log */
    
          resignal;
        end;
    
    select ediShipmentStatusXref(pEDIpartnerId, pStatus, pStatusType, pStatusDateType) into lStatusTypeId from sysibm/sysdummy1;
    
    ...
      end;
    return retCode;
    end;
    I've tried various strategies to grab the warning being returned, but I have been unsuccessful.

    Help please?

    Thanks,
    Walt

  • #2
    I may be too late to offer any help...
    I may be missing something in the context of your code above but if you want to continue processing something after the error/warning, either in the erriing function or calling procedure, you might try a CONTINUE handler instead of an EXIT handler?

    Best of luck!
    Cliff

    Comment


    • #3
      *deleted
      Last edited by wegrace; March 29, 2018, 04:01 PM.

      Comment


      • #4
        Thanks for the input Cliff.

        I had to change my custom warning to an error, and I was able to move forward that way.

        The issue I was having though, is that the condition handler did not seem to be trapping my custom warning being returned from ediShipmentStatusXref().

        I have a statement in the condition handler of the calling procedure that writes out any errors/warnings to an sql message log table. And, nothing was being written out even though I was specifically trapping for my custom condition, shipment_status_xref_not_found.

        It worked fine as soon as I changed it to an error.

        Walt


        Comment

        Working...
        X