Displays a message.
MESSAGE expression [ TYPE { INFO | ACTION | WARNING | STATUS } ] [ TO { CONSOLE | CLIENT [ FOR { CONNECTION conn-id-number [ IMMEDIATE ] | ALL } ] | [ EVENT | SYSTEM ] LOG } [ DEBUG ONLY ] ]
conn-id : integer
TYPE clause This clause specifies the message type. The client application must decide how to handle the message. For example, if you specify TO CLIENT, Interactive SQL displays messages in the following locations:
INFO The Messages tab. INFO is the default type.
ACTION A window with an OK button.
WARNING A window with an OK button.
STATUS The Messages tab.
TO clause This clause specifies the destination of a message:
CONSOLE Send messages to the database server messages window and the database server message log file if one has been specified. 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 database server message log file specified by the -o option. If EVENT or SYSTEM is specified, the message is also written to the database server messages window and to the Windows event log under event source SQLANY 12.0 Admin and to the Unix Syslog under the name SQLANY 12.0 Admin (servername). Messages in the database server message 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. By default, the connection receives the message the next time a SQL statement or a WAITFOR DELAY statement is executed.
CONNECTION conn-id-number Specify the recipient's connection ID number. If IMMEDIATE is specified, the connection receives the message within a few seconds regardless of when the SQL statement is executed.
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.
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 may result in a small performance penalty.
The MESSAGE statement displays a message, which can be any expression. Clauses can specify the message type and where the message appears.
If the size of expression exceeds the database page size, expression is truncated to fit within the database page size. To check the page size in effect for the database, you can query the PageSize
database property (SELECT DB_PROPERTY( 'PageSize' );
).
The procedure executing a MESSAGE...TO CLIENT statement must be associated with a connection.
For example, the window is not displayed in the following example because the event occurs outside a connection.
CREATE EVENT CheckIdleTime TYPE ServerIdle WHERE event_condition( 'IdleTime' ) > 100 HANDLER BEGIN MESSAGE 'Idle server' TYPE WARNING TO CLIENT; END; |
However, in the following example, the message is written to the database server messages window.
CREATE EVENT CheckIdleTime TYPE ServerIdle WHERE event_condition( 'IdleTime' ) > 100 HANDLER BEGIN MESSAGE 'Idle server' 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, use the IMMEDIATE clause to implement client notification and not multiple concurrent WAITFOR DELAY statements.
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.
Messages sent without the IMMEDIATE clause are only delivered when the client executes a specific request, or a WAITFOR DELAY statement. As a result, the delivery time of messages is unlimited.
The IMMEDIATE clause requires a SQL Anywhere 11 or later DBLib, ODBC, or SQL Anywhere JDBC driver. The IMMEDIATE clause is not supported by non-threaded Unix client libraries. An error message is generated when a message is sent to a destination connection that does not support the IMMEDIATE clause. An error message is generated when an IMMEDIATE message is sent to the same connection executing the MESSAGE statement.
MESSAGE 'Please disconnect' TYPE WARNING TO CLIENT FOR CONNECTION 16 IMMEDIATE; |
A MESSAGE...TO CLIENT expression can be truncated to 2048 bytes. For messages sent with the IMMEDIATE clause, the message expression can be truncated to the smaller of the packet size of the connection or 2048 bytes.
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 SA_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.
SQL/2008 Vendor extension.
The following procedure displays a message in the database server messages window:
CREATE PROCEDURE message_text( ) BEGIN MESSAGE 'The current date and time: ', Now( ); END; |
The following statement displays the string The current date and time
, followed by the current date and time, in the database server messages window.
CALL message_text( ); |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |