Provides a SQL interface to send messages to different service endpoints. The endpoints are of type queue.
message_send_call ::= msgsend (message_body, end_point [options_and_properties] )
options_and_properties ::= [option_clause] [properties_clause] option_clause::= [,] option option_string properties_clause ::= [,] message property option_string message_body := scalar_expression | (select_for_xml) end_point := basic_character_expression
The message you are sending. The message body can contain any string of characters. It can be binary data, character data, or SQLX data.
The queue to which a message is addressed. It is a basic_character_expression whose runtime value is a service_provider_uri.
The general format of an option_string is specified in the section option_string—general format.
The options you can specify for msgsend are in Table 5-1:
Is either an option_string or one of the options listed in Table 5-2. The options described in Table 5-2 are set as a property in the message header or message properties, as indicated in the disposition column of the table. The option value is the property value.
Property names are case sensitive.
If you use a property not listed in Table 5-2, it is set as a property in the message properties of the message sent.
If a message is a SQL scalar_expression, it can be of any datatype.
If the type option is not specified, the message type is text if the scalar_expression evaluates to a character datatype; otherwise the message type is bytes.
If the datatype of the scalar_expression is not character, it is converted to varbinary using the normal SQL rules for implicit conversion. The binary value of the datatype is included in the message according to the byte ordering of the host machine.
A Transact-SQL query expression whose datatype is char, varchar, or java.lang.String.
A select expression that specifies
a for xml
clause.
Sends the message “hello” to the specified endpoint:
select msgsend(’Hello’,’my_jms_provider?queue=queue.sample,’ +’user=jms_user1,password=jms_user1_password’)
Sends the message “Hello Messaging World!” to the specified endpoint:
declare @mymsg varchar (255) set @mymsg = ’Hello Messaging World!’ select msgsend(@mymsg, +’my_jms_provider?queue=queue.sample,user=jms_user1,’ +’password=jms_user1_password’)
Sends a message whose body is a SQLX-formatted representation of the SQL result set, returned by the SQL query to the specified endpoint:
select msgsend ((select * from pubs2..publishers FOR XML), ’tibco_jms:tcp://my_jms_host:7222?queue=queue.sample,’ +’user=jms_user1,password=jms_user1_password’)
Sets two properties and generates an XML schema for the message:
select msgsend ((select pub_name from pubs2..publishers where pub_id = ’1389’ FOR XML), my_jms_provider?queue=queue.sample’, MESSAGE PROPERTY ’priority=6, correlationID=MSG_001’, OPTION ’schema=yes’)
Shows user-specified values for message properties:
select msgsend ( ’hello’, ’my_jms_provider?queue=queue.sample’ MESSAGE PROPERTY ’ttl=30,category=5, rate=0.57, rank=’’top’’, priority=6’)
ttl and priority are internally set as header properties. category, rate, and rank are set as user-specified properties in the message properties.
The result of a msgsend call is a varchar string. If the message succeeds, the returned value is the message ID. If the message is not sent, the return value is null.
In a message_body that is a select_for_xml parameter, select_for_xml generates a SQLX-formatted representation of the SQL result set.
You can specify select_for_xml only if Adaptive Server is configured for the native XML feature. You can reference select_for_xml only as a scalar expression from a msgsend call.
You must surround select_for_xml with parentheses, as shown in the Syntax section.
The following restrictions apply to a runtime format for service_provider_uri:
service_provider_uri ::= provider_name ?destination [,user=username, password=password] provider_name ::= local_name | full_name local_name ::= identifier full_name ::= service_provider_class:service_provider_url
The local_name is a provider identifier, previously registered in a call to sp_msgadmin 'register’, ’provider’, which is shorthand for the full_name specified in that call.
The service_provider_class currently supported is TIBCO_JMS.
The service_provider_url has the form “tcp://hostname:port”. The host name can be a name or an IP address.
A service_provider_url cannot have spaces.
If the destination has the form queue=queue_name
,
the message is sent to this queue.
The service_provider_class and the words “user”, and “password” are case insensitive. local_name, hostname, port, queue_name, user_name, and password parameters are case-sensitive.
You can set message properties specific to Adaptive Server according to Table 6-2.
Option string usage in msgsend:
Empty option strings are ignored.
You can separate option strings with commas or white space (there is no limit on the amount of white space before first option, after the last option, between options, and surrounding the equal signs).
Quoted strings are formed according to SQL conventions for embedded quotation marks.
If
you specify multiple options with the same name, only the option
listed last is processed. For example, in the following statement,
only the value 7 is used or validated for ’priority’
; the
other values are ignored:
select msgsend( ’Hello Messaging World!’, ’my_jms_provider?queue=queue.sample’, MESSAGE PROPERTY ’priority=’’high’’, priority=yes, priority=7’)
After you execute msgsend, the values of the global variables are set with information for that call. For more details, see “Message-related global variables”.
Use single apostrophes ('), not double quotation marks ("), around quoted option or property values.
msgsend also allows messages to be sent to a topic,
if you specify
topic=topic_name
as
the destination. Sybase does not recommend this practice, as it
may cause unexpected behavior.
Copyright © 2004. Sybase Inc. All rights reserved. |
![]() |