Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

MESSAGE statement

Use this statement to display a message.

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

conn_id : integer

TYPE clause    This clause specifies the message type. Acceptable values are INFO, ACTION, WARNING, and STATUS. The client application must decide how to handle the message. For example, Interactive SQL displays messages in the following locations:

• INFO    The Messages tab. INFO is the default type.

• ACTION    A Message box with an OK button.

• WARNING    A Message box with an OK button.

• STATUS    The Messages tab.

TO clause    This clause specifies the destination of a message:

• CONSOLE    Send messages to the Server Messages window. CONSOLE is the default.

• CLIENT    Send messages to the client application. Your application must decide how to handle the message, and you can use the TYPE as information on which to base that decision.

• LOG    Send messages to the server log file specified by the -o option. If EVENT or SYSTEM is specified, the message is also written to the console and to the Windows event log under event source SQLANY 10.0 Admin and to the Unix SysLog under the name SQLANY 10.0 Admin (servername). Messages in the server log are identified as follows:

  • i  Messages of type INFO or STATUS.

  • w  Messages of type WARNING.

  • e  Messages of type ACTION.

FOR clause    For messages TO CLIENT, this clause specifies which connections receive notification about the message:

• CONNECTION conn_id    Specify the recipient's connection ID for the message.

• ALL    Specify that all open connections receive the message.

DEBUG ONLY    This clause allows you to control whether debugging messages added to stored procedures and triggers are enabled or disabled by changing the setting of the debug_messages option. When DEBUG ONLY is specified, the MESSAGE statement is executed only when the debug_messages option is set to On.

The MESSAGE statement displays a message, which can be any expression. Clauses can specify the message type and where the message appears.

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

For example, the message box is not displayed in the following example 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 the following 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.

The FOR clause can be used to notify another application of an event detected on the database 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 that 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.

Embedded SQL and ODBC clients receive messages via message callback functions. In each case, these functions must be registered. In embedded SQL, the message callback is registered with db_register_a_callback using the DB_CALLBACK_MESSAGE parameter. In ODBC, the message callback is registered with SQLSetConnectAttr using the ASA_REGISTER_MESSAGE_CALLBACK parameter.

DBA authority is required to execute a MESSAGE statement containing a FOR clause or a TO EVENT LOG or TO SYSTEM LOG clause.

None.

• CREATE PROCEDURE statement
• debug_messages option [database]
• db_register_a_callback function

• SQL/2003    Vendor extension.

1. The following procedure displays a message on the Server Messages window:

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

   The statement following statement displays the string The current date and time, followed by the current date and time, on the database Server Messages window.

   CALL message_text();

2. To register a callback in ODBC, declare the message handler:

   void SQL_CALLBACK my_msgproc(
      VOID * sqlca,
      UNSIGNED CHAR msg_type,
      LONG code,
      UNSIGNED SHORT len,
      CHAR* msg )
   { ... }

   Note that msg is not null-terminated. Your application must be designed to hand this.

3. Install the declared message handler by calling the SQLSetConnectAttr function:

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

1. To register a callback in embedded SQL, first define the message handler:

   void SQL_CALLBACK my_msgproc(
      SQLCA * sqlca,
      UNSIGNED CHAR msg_type,
      LONG code,
      UNSIGNED SHORT len,
      CHAR* msg ) // msg is NOT null terminated
   { ... }

2. Install the declared message handler by calling the db_register_a_callback function:

   db_register_a_callback( &sqlca, DB_CALLBACK_MESSAGE, (SQL_CALLBACK_PARM)&my_msgproc );