Chapter 4: SQL Reference

msgsend

Description

Provides a SQL interface to send messages to different service endpoints. The endpoints are of type queue.

Syntax

message_send_call ::=
	msgsend(message_body, end_point [options_and_properties])
options_and_properties ::= [option_clause] [properties_clause] 
	[header_clause]
option_clause ::= [,] option option_string
properties_clause ::= [,] message property property_option_string
header_clause ::= [,] message header header_option_string
message_body ::= scalar_expression | (select_for_xml)
end_point ::= basic_character_expression

Parameters

message_body: is the message you are sending. The message body can contain any string of characters. It can be binary data, character data, or SQLX data.

endpoint: is the queue to which a message is addressed. endpoint is a basic_character_expression where the runtime value is a service_provider_uri.

option: allows you to specify options for msgsend. Use the options in Table 4-8 if you are using TIBCO.

option_string

specifies the general syntax and processing for option_string. Individual options are described in the functions that reference them.

option_string ::= basic_character_expression 
option_string_value ::= option_and_value [ [,] option_and_value]
option_and_value ::=  option_name = option_value 
option_name ::= simple_identifier
option_value ::=  simple_identifier
	| quoted_string | integer_literal | float_literal | byte_literal
	| true | false | null

Parameter	Description
*option_string*	String describing the option you want to specify.
*simple_identifier*	String that identifies the value of an *option*.
*quoted_string*	String formed using the normal SQL conventions for embedded quotation marks.
*integer_literal*	Literal specified by normal SQL conventions.
*float_literal*	Literal specified by normal SQL conventions.
true	A Boolean literal.
false	A Boolean literal.
null	A null literal.
byte_literal	Has the form 0xHH, where each H is a hexadecimal digit.

properties_clause

is a property_option_string, or one of the options listed in Table 4-9 for TIBCO EMS. The options described in these two tables 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.

Use the options in Table 4-9 for msgsend. If you use a property not listed in Table 4-9, it is set as a property in the message properties of the message sent.

scalar_expression

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.

basic_character_expression: A Transact-SQL query expression with datatype that is char, varchar, or java.lang.String.

select_for_xml: A select expression that specifies a for xml clause.

header_clause

allows users to specify only those header properties that are specified in d Table 4-9. You see an error if you enter an unrecognized header property.

If a recognized header property is specified both in the message property and the message header clauses, the one in the message header clause takes precedence.

You get an error when you specify any unrecognized names in the message header parameter.

Examples

Example 1

Sends the message “Hello” to the specified endpoint:

select msgsend('Hello', 'my_ems_provider?queue=queue.sample,'
    +'user=ems_user1,password=ems_user1_password')

Example 2

Sends the message “Hello Messaging World!” to the specified endpoint:

declare @mymsg varchar (255) 
set @mymsg = 'Hello Messaging World!'
select msgsend(@mymsg,
    +'my_ems_provider?queue=queue.sample,user=ems_user1,'
    +'password=ems_user1_password')

Example 3

Sends a message with a body that 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_ems_host:7222?queue=queue.sample,'
     +'user=ems_user1,password=ems_user1_password')

Example 4

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_ems_provider?queue=queue.sample',
     MESSAGE PROPERTY 'priority=6, correlationID=MSG_001', 
option 'schema=yes')

Example 5

Shows user-specified values for message properties:

select msgsend ('hello', 'my_ems_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.

Usage

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 4-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'; other values are ignored:
```
select msgsend( 'Hello Messaging World!', 
     'my_ems_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 “Messaging-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.
Unrecognized options or properties are ignored, but unrecognized option or property values are flagged as an error.

msgsend option option_string parameter values

Table 4-8 lists the available msgsend option parameters for TIBCO.

Table 4-8: Valid **TIBCO EMS option** ***option_string*** types and values for **msgsend**
Types	Values	Default	Description
schema	no yes *“user_schema”*	no	*user_schema* is a user-supplied schema describing the *message_body. no indicates that no schema* is generated and sent out as part of the message. yes indicates that Adaptive Server generates an XML schema for the message. yes is meaningful only in a *message_body* that uses the parameter *select_for_xml. select_for_xml* generates a SQLX-formatted representation of the SQL result set. The generated XML schema is a SQLX-formatted schema that describes the result set document. The schema is included in the message as the ASE_MSGBODY_SCHEMA property.
type	text, bytes	text	The type of message to send.

msgsend properties_clause parameter values

Table 4-9 lists the available msgsend properties_clause parameters for TIBCO EMS.

**Table 4-9: Valid message property *properties_option_string* types and values for msgsend**
Option	Values	Default	Disposi-tion	Description
ttl	0 - (2⁶³- 1)	0	header	ttl refers to time-to-live on the messaging bus. Adaptive Server is not affected by this. Expiry information is the duration of time during which a message is valid, in milliseconds. For instance, 60 indicates that the life of the message is 60 milliseconds. A value of 0 indicates that the message never expires.
priority	1 to 10	4	header	The behavior of priority is controlled by the underlying message bus. The values mentioned here apply to “tibco_jms”. Priorities from 0 to 4 are normal; priorities from 5 to 9 are expedited.
correlation	*string*	none	header	Client applications set correlation IDs to link messages together. Adaptive Server sets the correlation ID the application specifies.
mode	persistent non-persistent	persistent	header	If the mode is: persistent – the message is backed by the JMS provider, using stable storage. If the messaging provider crashes before the message is consumed, the message is lost, unless mode is set to persistent. non-persistent and the messaging provider crashes – you may lose a message before it reaches the desired destination.
replyqueue	A string containing a *queue_name*	none	header	The value of *queue_name* or *topic_name* must be syb_temp. The type of the temporary destination, queue or topic, depends on whether you specify replyqueue or replytopic. Only the option listed last is used. Adaptive Server creates a temporary destination and sends information related to the newly created temporary destination as a part of the header information.
replytopic	A string containing a *topic_name*	none	header

msgsend properties and rfhCommand

Unrecognized options are ignored if you use message property.
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 only 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.

Permissions

You must have messaging_role to run msgsend.

View this book as PDF