Message

Valid in: DBProc

The Message statement returns message text and a message number from a database procedure to an application program. The message statement can only be issued from a database procedure.

Syntax

The Message statement has the following format:

MESSAGE message_text | message_number | message_number message_text

              [WITH DESTINATION =([SESSION][, ERROR_LOG] [, AUDIT_LOG]]);

• message_text

  Specifies a string literal or a non-null host string variable.

• Message_number

  Specifies an integer literal or a non-null host integer variable.

  Neither message_text nor message_number can be expressions. The values for these parameters do not correspond to DBMS Server error codes or messages; the message statement simply returns the specified values to the receiving application. If the message_number parameter is omitted, the DBMS Server returns a value of 0.

• WITH DESTINATION

  Changes the default destination of the message, which is a window at the bottom of the screen. Alternate destinations are as follows:

  • AUDIT_LOG

    Directs the output of the message statement to the security audit log.

    For example: WITH DESTINATION = (AUDIT_LOG)

  • ERROR_LOG

    Directs the output of the message statement to the error log.

    The message number and text is written to errlog.log with message identifier E_QE0300.

  • SESSION

    Restores the default behavior.

    To both log and return messages to the application, specify WITH DESTINATION = (SESSION, ERROR_LOG).

To specify an action to be performed when an application receives a message from a database procedure, use the WHENEVER SQLMESSAGE statement. For details, see Whenever.

To specify a routine that is called when an application receives a message from a database procedure, use the SET_SQL(messagehandler) statement. For details, see Set_sql.

Permissions

All users are permitted to use this statement.

Related Statements

Create Procedure

Inquire_sql

Examples: Message

The following are Message statement examples:

1. The following example turns debugging text to the application.

   message 'Inserting new row';
       insert into tab values (:val);
       message 'About to commit change';
       commit;
       message 'Deleting newly inserted row';
       delete from tab where tabval = :val;
       message 'Returning with pending change';
       return;

2. The following example returns a message number to the application. The application can extract the international message text out of a message file.

   if iierrornumber > 0 then
           message 58001;
       elseif iirowcount <> 1 then
           message 58002;
       endif;

3. The following example sends a message to the error log file.

   message 'User attempting to update payroll table'
       with destination = (error_log);