ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL signals from external SQL proc?

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

  • SQL signals from external SQL proc?

    If I write an SQL procedure in SQL PL, I can signal custom SQL errors using

    declare exit handler for SQLState '77001' resignal;

    and

    signal sqlstate '77001' set message_text = errorMessage;


    But If I wanted to write an RPGLE program, and wrap that with an external SQL procedure, how would I signal custom SQL errors? I tried doing a signal sql state from an exec sql and it didn't do anything.

  • #2
    I don't think I've ever done this, Vector. If I have, I've forgotten.

    Anyway, I think you need to use the SQL parameter style. The SQL state is in the parameter list when you use that style. Look at IBM's documentation for CREATE FUNCTION (external scalar).

    This CREATE FUNCTION (external scalar) statement defines an external scalar function at the current server. A user-defined external scalar function returns a single value each time it is invoked.

    This CREATE FUNCTION (external scalar) statement defines an external scalar function at the current server. A user-defined external scalar function returns a single value each time it is invoked.

    Comment


    • #3
      You can send an escape message from within RPG ... but you cannot set a specific SQL State or SQLCODE. The error message will be returned as generic SQLCODE (IIRK it is -443).

      Comment


      • #4
        Thanks both

        It looks like all the sql external procedures (scalar or otherwise) support additional parameters to return a SQL state and message, for parameter type SQL. I guess this is what you use instead of signalling to show a specific SQL error if the procedure errors.

        Comment

        Working...
        X