MESSAGE Statement

Syntax

MESSAGE expression, …
   [ TYPE { INFO | ACTION | WARNING | STATUS } ]
   [ TO { CONSOLE 
      | CLIENT [ FOR { CONNECTION conn_id  [ IMMEDIATE ] | ALL } ]
      | [ EVENT | SYSTEM ] LOG } 
   [ DEBUG ONLY ] ]

Parameters

• FOR – specifies which connections receive notification about the message:
  • CONNECTION conn_id – the recipient's connection ID for the message.
  • IMMEDIATE – the connection receives the message within a few seconds regardless of when the SQL statement is executed.

    Typically, messages sent using the IMMEDIATE clause are delivered in less than five seconds, even if the destination connection is not making database server requests. Message delivery could be delayed if the client connection makes several requests per second, receives very large BLOB data, or if the client's message callback executes for more than a second. In addition, sending more than one IMMEDIATE message to a single connection every two seconds could delay message delivery or generate an error message. If the client connection is disconnected, a successful MESSAGE...IMMEDIATE statement may not be delivered.

  • ALL – all open connections receive the message.

  The FOR clause can be used to notify another application of an event detected on the server without the need for the application to explicitly check for the event. When the FOR clause is used, recipients receive the message the next time they execute a SQL statement. If the recipient is currently executing a SQL statement, the message is received when the statement completes. If the statement being executed is a stored procedure call, the message is received before the call is completed.

  If an application requires notification within a short time after the message is sent and when the connection is not executing SQL statements, you can use a second connection. This connection can execute one or more WAITFOR DELAY statements. These statements do not consume significant resources on the server or network (as would happen with a polling approach), but permit applications to receive notification of the message shortly after it is sent.

• TYPE – has an effect only if the message is sent to the client. The client application must decide how to handle the message. Interactive SQL displays messages in these locations:
  • INFO – (default) The Message window.
  • ACTION – a Message box with an OK button.
  • WARNING – a Message box with an OK button.
  • STATUS – the Messages pane.

• TO – specifies the destination of a message:
  • CONSOLE – (default) Send messages to the database server window.
  • CLIENT – send messages to the client application. Your application must decide how to handle the message, and you can use the TYPE clause as information on which to base that decision.
  • LOG – send messages to the server log file specified by the -o option.

• DEBUG ONLY – controls whether debugging messages added to stored procedures are enabled or disabled by changing the setting of the DEBUG_MESSAGES database option. When DEBUG ONLY is specified, the MESSAGE statement is executed only when the DEBUG_MESSAGES option is set to ON.
  Note: DEBUG ONLY messages are inexpensive when the DEBUG_MESSAGES option is set to OFF, so these statements can usually be left in stored procedures on a production system. However, they should be used sparingly in locations where they would be executed frequently; otherwise, they might result in a small performance penalty.

Examples

• Example 1 – display the string The current date and time, and the current date and time, on the database server message window:

  CREATE PROCEDURE message_test ()
  BEGIN
  MESSAGE 'The current date and time: ', Now();
  END;
  CALL message_test();

• Example 2 – to register a callback in ODBC, first declare the message handler:

  void SQL_CALLBACK my_msgproc(
    void *    sqlca, 
    unsigned char     msg_type,
    long              code,
    unsigned short    len, 
    char*              msg ) 
  { … }

  Install the declared message handler by calling the SQLSetConnectAttr function:

  rc = SQLSetConnectAttr(    
    dbc,
    ASA_REGISTER_MESSAGE_CALLBACK,
    (SQLPOINTER) &my_msgproc, SQL_IS_POINTER );

Usage

The procedure issuing a MESSAGE … TO CLIENT statement must be associated with a connection.

For example, the message box is not displayed because the event occurs outside of a connection:

CREATE EVENT CheckIdleTime TYPE ServerIdle 
WHERE event_condition( 'IdleTime' ) > 100 
HANDLER 
BEGIN    
  MESSAGE 'Idle engine' type warning to client; 
END;

However, in this example, the message is written to the server console:

CREATE EVENT CheckIdleTime TYPE ServerIdle 
WHERE event_condition( 'IdleTime' ) > 100 
HANDLER 
BEGIN   
  MESSAGE 'Idle engine' type warning to console; 
END;

Valid expressions can include a quoted string or other constant, variable, or function. However, queries are not permitted in the output of a MESSAGE statement, even though the definition of an expression includes queries.

ESQL and ODBC clients receive messages via message callback functions. In each case, these functions must be registered. To register ESQL message handlers, use the db_register_callback function.

ODBC clients can register callback functions using the SQLSetConnectAttr function.

Standards

Permissions

Must be connected to the database.

• FOR clause – Requires one of:
  • SERVER OPERATOR system privilege.
  • DROP CONNECTION system privilege.
• TO EVENT LOG or TO SYSTEM LOG clause – Requites the SERVER OPERATOR system privilege.