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-10 if you are using TIBCO. Use the options in Table 4-11 if you are using MQSeries.

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-12 for MQSeries, and Table 4-13 for TIBCO JMS. 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.

TIBCO JMS only – if you use a property not listed in Table 4-13, it is set as a property in the message properties of the message sent.

Use the options in Table 4-13 for msgsend using TIBCO JMS.

MQSeries only – the values of properties_clause differ based on what you specify in the rhfCommand option:

The properties in Table 4-14 are effective only if rhfCommand is deletePublication.

A deletePublication command message sent to the publication stream instructs the MQ pub/sub broker to delete its copy of any retained publications for the specified topics within the publication stream.

The message_body argument to msgsend is ignored.
The properties in Table 4-15 are effective only if rhfCommand is deregisterPublisher.
The properties in Table 4-16 are effective only if rhfCommand is deregisterSubscriber .

A deregisterPublisher command message sent to the MQ pub/sub broker control queue informs the broker that the publisher will no longer be publishing publications on the topics specified.

The message_body argument to msgsend is ignored.

If the msgType is request, the reply message is sent to replyToQmgr and replyToQueue.
The properties in Table 4-17 are effective only if rhfCommand is publish.

A publish command message is sent to the publication stream queue to publish information on specific topics. The publication data is specified as the message_body argument to msgsend.

If the msgType is request, the reply message is sent to replyToQmgr and replyToQueue.
The properties in Table 4-19 are effective only if rhfCommand is registerSubscriber.

A registerSubscriber command message sent to the MQ pub/sub broker control queue informs the broker that the publisher is publishing, or can, publish data on one or more specified topics. If the publisher is already registered, and there are no other errors, the publisher’s registration is modified accordingly.

If the msgType is request, the reply message is sent to replyToQmgr and replyToQueue.
The properties in Table 4-20 are effective only if rhfCommand is requestUpdate.

A requestUpdate command message sent to the MQ pub/sub broker control queue informs the broker that the subscriber wants the broker to forward all retained publications that match the topic specified.

If the msgType is request, the reply message is sent to replyToQmgr and replyToQueue.

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 Table 4-12 for MQSeries and Table 4-13 for TIBCO JMS. 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

TIBCO JMS – sends the message “Hello” to the specified endpoint:

select msgsend('Hello', 'my_jms_provider?queue=queue.sample,'
    +'user=jms_user1,password=jms_user1_password')

Example 2

TIBCO JMS – 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')

Example 3

TIBCO JMS – 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_jms_host:7222?queue=queue.sample,'
     +'user=jms_user1,password=jms_user1_password')

Example 4

TIBCO JMS – 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')

Example 5

TIBCO JMS – 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.

Example 6

MQSeries – sends a request message, and the reply is expected on the specified queue, in the same Queue Manager.

select msgsend('do something',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    option 'msgType=request'
MESSAGE PROPERTY 'replyToQueue=QUEUE.REPLY')

Example 7

MQSeries – sends a reply message. The correlation ID, and the reply queue were extracted from a previously received request message:

select @correlationId = msgpropvalue("CorrelId", @@msgheader)
select @replyQ = @@msgreplytoinfo
select msgsend('i''m done',
@replyQ
    option 'msgType=report'
    MESSAGE PROPERTY 'correlationId=' + @correlationId)

Example 8

MQSeries – sends a report message. The correlation ID, reply queue, and report message data header were extracted from a previously received request message:

select @correlationId = msgpropvalue("CorrelId", @@msgheader)
select @replyQ = @@msgreplytoinfo
select msgsend(@reportData,
@replyQ
    option 'msgType=report'
    MESSAGE PROPERTY 'correlationId=' + @correlationId)

Example 9

MQSeries – sends four datagram messages. Each message is part of the group “theGroup”, and each message has an increasing sequence number:

begin tran
select msgsend('message 1',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'groupId=theGroup,sequenceId=1')
select msgsend('message 2',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'groupId=theGroup,sequenceId=2')
select msgsend('message 3',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'groupId=theGroup,sequenceId=3')
select msgsend('message 4',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'groupId=theGroup,sequenceId=4,lastMsgInGroup=yes')
    commit

Example 10

MQSeries – sends a datagram message. Various confirmation reports are requested, and they are sent to the “myReplyQueue”:

select msgsend('I want a confirmation',
    'ibm_mq:channel1/TCP/host1(5678)?queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'replyToQueue=myReplyQueue'
        + ',exceptionReport=yes,
        + ',arrivalReport=withData
        + ',deliveryReport=withFullData'

Example 11

MQSeries – publishes a datagram message with topics “A”, “A/B”, “A/B/C”. The publisher is registered to publish on topics “A”, “A/B”, and “A/B/C”, and the publication contains information about topic “A/B”. The default MQ pub/sub broker queue and stream queues are used:

-- First register the publisher
select msgsend(null,
    'ibm_mq:channel1/TCP/host1(5678)?queue=SYSTEM.BROKER.CONTROL.QUEUE
    option 'msgType=datagram,rfhCommand=registerPublisher'
    MESSAGE PROPERTY 'topics=''a:A/B:a/b/c''')

-- Now publish the publication
select msgsend('something about A/B',
    'ibm_mq:channel1/TCP/host1(5678)?queue=SYSTEM.BROKER.DEFAULT.STREAM'
    option 'msgType=datagram,rfhCommand=publish'
    MESSAGE PROPERTY 'topics=A/B'

Example 12

MQSeries – sends multiple messages in a group. Since ordering is set to logical, specify only the msgInGroup, lastMsgInGroup, msgSegment, msgLastSegment options. The Queue Manager selects a name for the group since it is not specified:

begin tran
select msgsend('first logical message of the group',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,msgInGroup=yes')

select msgsend('second logical message of the group',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,msgInGroup=yes')

select msgsend('third logical message of the group, first segment',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,msgInGroup=yes,msgSegment=yes')

select msgsend('third logical message of the group, second segment',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,msgInGroup=yes,msgSegment=yes')

select msgsend('third logical message of the group, third segment',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,msgInGroup=yes,msgLastSegment=yes')

select msgsend('fourth logical message of the group',
    'ibm_mq:chnl1/TCP/host1(5678)?qmgr=QM,queue=QUEUE.COMMAND',
    MESSAGE PROPERTY 'ordering=logical,lastMsgInGroup=yes')
commit

Example 13

Uses msgsend to register, then deregister a subscriber. The subscriber is interested in all publications that match the topics “A” or “A/B/*”. Matching publications are forwarded to the queue “Q2” by the MQ pub/sub broker:

-- Register the subscriber
select msgsend(null,
    'ibm_mq:channel1/TCP/host1(5678)'
           + '?qmgr=QM,queue=SYSTEM.BROKER.CONTROL.QUEUE'
    option 'msgType=datagram,rfhCommand=registerSubscriber'
    MESSAGE PROPERTY 'topics=''A:A/B/*'',streamName=stream1,queueName=Q2')

-- Publish a message to the stream queue, let it do implicit registration
select msgsend('happy birthday',
    'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,
            queue=stream1'
    option 'msgType=datagram,rfhCommand=publish'
    MESSAGE PROPERTY 'topics=''A''')

-- Read a message forwared to us by the MQ pub/sub
select msgrecv(
    'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,
            queue=Q2'
    option 'timeout=50ss')

-- Deregister the subscriber
select msgsend(null,
    'ibm_mq:channel1/TCP/host1(5678)'
            + ?qmgr=QM,queue=SYSTEM.BROKER.CONTROL.QUEUE'
    option 'msgType=datagram,rfhCommand=deregisterSubscriber'
    MESSAGE PROPERTY 'topics=''A:A/B/*'',streamName=stream1,queueName=Q2')

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-3.
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_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.
Unrecognized options or properties are ignored, but unrecognized option or property values are flagged as an error.

This behavior is new with version 12.5.3a, and differs from previous versions.

msgsend option option_string parameter values

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

Table 4-10: Valid **TIBCO JMS 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.

Table 4-11 lists the available msgsend option parameters for MQSeries.

Table 4-11: Valid **MQSeries option** ***option_string*** types and values for ***msgsend***
Tyes	Values	Default	Description
msgType	datagram request reply report	datagram	If the type of the message is: request – you must also specify the replyQueue property. report – you must also specify the reportDataHeader and feedback properties.
rfhCommand	null deletePublication deregisterPublisher deregisterSubscriber publish registerPublisher registerSubscriber requestUpdate	null	MQRF headers, for MQ pub/sub, are control messages that are sent to a queue and read by the MQ pub/sub broker. The broker acts upon the message it reads from the queue. If rfhCommand is null, the message does not include the MQRF header. The message includes the MQRF header with any other value for rfhCommand, with the MQPSCommand set to the following: deletePublication – set to DeletePub. The endpoint is the endpoint to the publishing stream queue. See Table 4-14. deregisterPublisher – set to DeregPub. See Table 4-15. deregisterSubscriber – set to DeleteSub. See Table 4-16. publish – set to Publish. The endpoint is the endpoint to the publishing stream queue. See Table 4-17. registerPublisher – set to RegPub. See “msgsend properties if rfhCommand is set to deletePublications”. registerSubscriber – set to RegSub. See Table 4-19. requestUpdate – set to ReqUpdate. See Table 4-20. The message is sent to the endpoint you specify. For these options, specify the endpoint to the publishing stream queue: publish deletePublication For these options, specify the endpoint to the MQ pub/sub broker control queue: deregisterPublisher deregisterSubscriber registerPublisher registerSubscriber requestUpdate

msgsend properties_clause parameter values

Table 4-12 lists the available msgsend properties_clause parameters for MQSeries.

Table 4-12: Valid MQSeries message property *property_option_clause* types and values for **msgsend**
Types	Values	Default	Description
arrivalReport	yes withData withFullData no	no	Arrival of this message to the final destination should generate a confirm-on-arrival (COA) report. You must specify replyToQueue. If you specify: yes – the COA report generates without data from the received message. withData – the COA report generates with the first 100 bytes of the data from the received message. withFullData – the COA report generates with the full data from the received message. no – the COA report is not generated.
correlationId	null *string*	null	Clients set correlation ID to link messages together. MQ limits this string to 24 bytes. MQ defines this field as unsigned char, which indicates that it can support binary values. To enter a binary string as the correlationId, use “0x…” as the value. Do not use quotes around the value. If rfhCommand is not null: If correlationId is not null, a new correlation ID is not requested. If correlationAsId is yes, and correlationId is null, this is a separate traditional identity (one where correlation ID is empty). For rfhCommands of deletePublication, deregisterPublisher, publish, and registerPublisher, the correlation ID specified is as part of the publisher’s traditional identity.
deliveryReport	yes withData withFullData no	no	Delivery of this message from the final destination generates a confirm-on-delivery (COD) report. You must specify replyToQueue. If: yes – the COA report generates without data from the received message. withData – the COA report generates with the first 100 bytes of the data from the received message. withFullData – the COA report generates with the full data from the received message. no – the COA report is not generated.
exceptionReport	yes withData withFullData no	no	Expiration of this message or failure of this send generates an exception report. You must specify replyToQueue. If: yes – the exception report generates without data from the received message. withData – the exception report generates with the first 100 bytes of the data from the received message. withFullData – the exception report generates with the full data from the received message. no – the exception report is not generated.
expirationReport	yes withData withFullData no	no	The failure of this send generates an exception report. You must specify replyToQueue. If: yes – the exception report generates without data from the received message. withData – the exception report generates with the first 100 bytes of the data from the received message. withFullData – the exception report generates with the full data from the received message. no – the exception report is not generated.
expiry	timespec between -1, 0-(2³² -1)	-1, no expiration	The message’s time-to-live on the Queue Manager. Units are in milliseconds if the timespec is an integer. Values are: 0 – message does not expire. -1 – uses the default defined for the queue. The MQ expiry is in tenths of a second, so this number is rounded to the tenths of a second before being passed to MQ. See timespec for more information.
feedback	*integer* Must range within MQFB_APPL_FIRST (65536) to MQFB_APPL_LAST (999999999)	0	For report messages, feedback is a code that indicates the nature of the report message. MQ defines one feedback code range each for: System report messages Application report messages
formatName	null *string*	null	Application-defined property to pass information about the message formats. This property allows sending applications to set a format name that describes the message data. A receiving application can check formatName in *@@msgheader* to decide how to process the message data. Names beginning with “MQ” are reserved. MQ limits this string to 8 bytes.
groupID	null *string*	null	User-defined group. MQ limits this string to 24 bytes. MQ defines this field as unsigned char, which indicates that it can support binary values. To enter a binary string as the groupId, use “0x…” as the value. Do not use quotes around the value, or it is interpreted as a quoted string. If groupId is not specified and one of the grouping properties is specified, the Queue Manager generates the group name. Ignored if ordering is set to logical. All messages of a group must be sent in the same transaction.
lastMsgInGroup	yes no	no	If the value is yes, marks a message as being the last logical message of a group. To have a single logical message in a group by itself, you must set lastMsgInGroup to yes. You must send all messages of a group in the same transaction.
mode	persistent non-persistent default	default	If mode is: persistent – the message is backed by the messaging provider, using stable storage. If the messaging provider crashes before the message can be 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. default – the default defined for the queue is used.
msgId	null *string*	null	When specified, WebSphere MQSeries replaces any existing message ID with the value specified for msgId. MQ limits this string to 24 bytes. MQ defines this field as “unsigned char,” which indicates that it can support binary values. To enter a binary string as the msgId, use “0x…” as the value. Do not use quotes around the value.
msgInGroup	yes no	no	If the value is yes, this message is a logical message of a message group. For messages in a group, you must set this property to yes for all logical messages of the group, except the last one, which should have lastMsgInGroup set to yes. You must send all messages of a group in the same transaction.
msgLastSegment	yes no	no	If the value is yes, this message is the last segment of a segmented message. To have a segment message in a local message by itself, the message must have msgLastSegment set to yes. When the value is yes and ordering is set to physical, you must also set the offset property. You must send all messages in a group in the same transaction.
msgSegment	yes no	no	If the value is yes, this message is a segment of a segmented message. For messages that are part of a single segment, you must set this property to yes for all segments except the last one, which should be have msgLastSegment set to yes. When the value is yes and ordering is set to physical, you must also set the offset property. You must send all messages in a group in the same transaction.
negativeActionReport	yes no	no	You must specify replyToQueue. If: yes – when the retrieving application reads this message and acts negatively on it, a negative-action (NAN) report is generated. no – the NAN report is not generated.
offset	*integer* between -1, 0 – maxint	-1	When the message is a segment of a segmented message, you should set offset to the byte offset of the current message within the logical message. -1 indicates that the offset is not specified. offset is ignored unless msgSegment, or msgLastSegment are also specified. Ignored by msgpublish. Ignored if ordering is set to logical. You must send all messages of a group in the same transaction.
onNoDelivery	deadLetter discard	deadLetter	If: deadLetter – if the message cannot be delivered, the message is put on the dead-letter queue. discard – the message is discarded by the Queue Manager.
ordering	logical physical	physical	When this property is: physical – the application can send messages that are part of a group (or segmented message) in any order. The Queue Manager returns errors if it detects missing segments, or holes in the sequence Identifiers. logical – the application needs only to set the msgInGroup, lastMsgInGroup, msgSegment, and lastMsgSegment options appropriately. The Queue Manager automatically sets the group name, sequence identifier, and segment offset.
positiveActionReport	yes no	no	You must specify replyToQueue. If: yes – when the retrieving application reads this message and acts positively on it, a positive-action notification (PAN) report is generated. no – the PAN report is not generated.
priority	*integer: -1, 0 to queue manager configured max priority*	-1	Controls the priority of the message. If: -1 – the default priority as defined for the queue is used. priority specified is greater than the max priority defined for the Queue Manager – the max priority defined for the Queue Manager is used. This is implemented by MQ.
replyCorrelationId	msgId correlationId	msgId	If: msgId – the correlation ID in the report message should use the message ID of the received message. correlationId – the correlation ID in the report message uses the correlation ID of the received message.
replyMsgId	new original	new	If: new – the generated report message contains a new message ID. original – the report message should use the same message ID as the message received.
replyToInputMode	browse Qdefault shared exclusive browse+Qdefault browse+shared browse+exclusive	Qdefault	The mode that the replyToQueue is opening. When you specify replyToQueue, the queue is automatically opened for subsequent input. This mode specifies the input mode that the replyToQueue is opening. This property is ignored if you do not specify replyToQueue. The modes have the following meanings: browse – the queue is opened for browsing only. You get an error from the Queue Manager if you attempt to do a destructive read. Qdefault – the queue is opened in the default input mode as defined for the queue. shared – the queue is opened in shared input mode. You see an error if the queue is already opened in exclusive mode by another MQ handle. exclusive – the queue is opened in exclusive input mode. You see an error if the queue is already opened in shared or exclusive mode by another MQ handle. browse+Qdefault – the queue is opened for browsing, as well as for the default input mode as defined for the queue. browse+shared – the queue is opened for browsing, as well as for shared input mode. You see an error if the queue is already opened in exclusive mode by another MQ handle. browse+exclusive – the queue is opened for browsing, as well as for exclusive input mode. You see an error if the queue is already opened in shared or exclusive mode by another MQ handle.
replyToModel	null *string*	null	The name of the model queue from which the reply queue is created, when the replyToQueue is a dynamic queue. If you do not specify replyToQueue, this property is ignored. MQ limits this string to 48 bytes.
replyToQmgr	null *string*	null	The Queue Manager where replyToQueue resides. If you do not specify replyToQueue, this property is ignored. MQ limits this string to 48 bytes.
replyToQueue	null *string*	null	The queue where the application expects a reply to a request message. The message type sent does not have to be request, as MQ does not enforce this. If replyToQmgr is not specified, replyToQueue is assumed to be the same Queue Manager as the current endpoint. If the queue name specified ends with a “”, a system-generated dynamic queue name is generated with the specified prefix. If replyToModel* and a dynamic queue name are specified, the dynamic queue is created from the model queue specified for replyToModel. You can obtain system-generated dynamic queue names after the send operation via the *@@msgreplytoinfo* session variable. When you specify a dynamic queue name, the current Adaptive Server login must have “crt” authorization in the Queue Manager to create the dynamic queue. When a dynamic queue name is specified, you must manually delete the dynamic queue that is created if the receiving application does not do so. When rfhCommand is not null, you can specify replyToQueue to get responses from the MQ pub/sub broker.
rfhCommand	null deletePublication deregisterPublisher deregisterSubscriber publish registerPublisher registerSubscriber requestUpdate	null	MQRF headers, for MQ pub/sub, are control messages that are sent to a queue and read by the MQ pub/sub broker. The broker acts upon the message that it reads from the queue. If rfhCommand is null, the message does not include the MQRF header. The message includes the MQRF header with any other value for rfhCommand, with the MQPSCommand set to the following: deletePublication – set to DeletePub. The endpoint is the endpoint to the publishing stream queue. See Table 4-14. deregisterPublisher – set to DeregPub. See Table 4-15. deregisterSubscriber – set to DeleteSub. See Table 4-16. publish – set to Publish. The endpoint is the endpoint to the publishing stream queue. See Table 4-17. registerPublisher – set to RegPub. See “msgsend properties if rfhCommand is set to deletePublications”. registerSubscriber – set to RegSub. See “msgsend properties if rfhCommand is set to deletePublications”. requestUpdate – set to ReqUpdate. See “msgsend properties if rfhCommand is set to deletePublications”. The message is sent to the endpoint you specify. For these options, specify the endpoint to the publishing stream queue: publish deletePublication For these options, specify the endpoint to the MQ pub/sub broker control queue: deregisterPublisher deregisterSubscriber registerPublisher registerSubscriber requestUpdate
sequenceId	*integer* between -1 – 9,999,999	-1	Used to sequence logical messages that are part of a group. -1 indicates that the sequenceId is not specified. sequenceId is ignored unless msgInGroup or lastMsgInGroup are also specified. Ignored by msgpublish. Ignored if ordering is set to logical. You must send all messages of a group in the same transaction.

Table 4-13 lists the available msgsend properties_clause parameters for TIBCO JMS.

**Table 4-13: Valid TIBCO JMS 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. ttl uses the timespec option. See timespec for more information on timespec.
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

For MQSeries, properties in Table 4-14 are effective only if rhfCommand is deletePublication.

Table 4-14: msgsend properties if rfhCommand is set to **deletePublications**
Property	Values	Default	Description
local	yes no	no	If: yes – only the retained publications published locally at this broker are deleted. no – globally retained publications are deleted from all brokers in the network.
streamName	null *string*	null	Name of the publication stream for the specified topics. If not specified, the default is the stream queue to which this MQRFH command message is sent. MQ limits this string to 48 bytes.
topics	*string*	none	Use the format detailed in “Syntax for topics”. Retained messages matching this topic are deleted. At least one topic must be supplied. This is a required property, and is an error if omitted.

For MQSeries, properties in Table 4-14 are effective only if rhfCommand is deregisterPublisher.

Table 4-15: msgsend properties if rfhCommand is set to **deregisterPublisher**
Property	Values	Default	Description
deregAll	yes no	no	If: yes – all topics registered for this publisher are deregistered, and the topics property is ignored. no – no registered topics are deregistered. Adaptive Server returns an error if you specify topics.
streamName	null *string*	null	If: Not null – this is the name of the publication stream. null – SYSTEM.BROKER.DEFAULT.STREAM is assumed. MQ limits this string to 48 bytes.
topics	null *string*	null	Use the format detailed in “Syntax for topics”. These are the topics that this publisher deregisters. Adaptive Server returns an error if: The deregAll property is set to yes. topics is not null.
qmgrName	null *string*	null	This is the publisher’s Queue Manager name, used to establish the publisher’s traditional identity. Specify it as the same value you specified when you registered the publisher. If null, defaults to replyToQmgr.
queueName	null *string*	null	This is the publisher’s queue name, used to establish the traditional identity of the publisher. Specify it as the same value you specified when you registered the publisher. If null, defaults to the replyToQueue.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the publisher’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the publisher’s traditional identity. generate – a system-generated correlationId is used as part of the publisher’s traditional identity.

For MQSeries, the properties in Table 4-16 are effective only if rhfCommand is deregisterSubscriber .

**Table 4-16: msgsend properties if rfhCommand is set to deregisterSubscriber**
Property	Values	Default	Description
deregAll	yes no	no	If: yes – all topics for this subscriber are deregistered. The topics property is ignored. no – no subscriber topics are deregistered. Adaptive Server returns an error if topics are not null
streamName	null *string*	null	If: Not null – this is the name of the publication stream. null – SYSTEM.BROKER.DEFAULT.STREAM is assumed. MQ limits this string to 48 bytes.
topics	null *string*	null	Use the format detailed in “Syntax for topics”. These are the topics that this subscriber deregisters. Adaptive Server returns an error if: deregAll is Yes. topics are not null.
qmgrName	null *string*	null	This is the subscriber’s Queue Manager name, used to establish the traditional identity of the subscriber. You should specify it as the same value that was specified when you registered the subscriber. If null, it defaults to the replyToQmgr.
queueName	null *string*	null	This is the subscriber’s queue name, used to establish the traditional identity of the subscriber. You should specify it as the same value that was specified when you registered the subscriber. If null, it defaults to the replyToQueue.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the publisher’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the publisher’s traditional identity. generate – a system-generated correlationId is used as part of the publisher’s traditional identity.

For MQSeries, the properties in Table 4-17 are effective only if rhfCommand is publish.

**Table 4-17: msgsend properties if rfhCommand is set to publish**
Property	Values	Default	Description
topics	*string*	none	Use the format detailed in “Syntax for topics”. Wildcards are not allowed. These are the topics on which this publication has information. This is a required property, and an error if omitted.
anon	yes no	no	If: yes – the identity of the publisher is not divulged by the MQ pub/sub broker. Ignored if noReg is yes. no – the identity of the publisher is divulged by the MQ pub/sub broker.
local	yes no	no	If: yes – the MQ pub/sub broker sends this publication only to subscribers that registered specifying local. Ignored if noReg is yes. no – the MQ pub/sub broker sends this publication to all subscribers.
directReq	yes no	no	If: yes – the publisher is willing to accept direct request for publication information from other applications. Ignored if noReg is yes. Do not set this option to yes if the anon property is also set to yes, since the MQ pub/sub broker responds with an error. no – the publisher is not willing to accept direct request for publication information from other applications.
noReg	yes no	no	If the publisher is not already registered with the MQ pub/sub broker as a publisher for this stream and topic and the value of NoReg is: yes – the MQ pub/sub broker does not perform an implicit registration. The anon, local, and directReq properties are ignored. no – the MQ pub/sub broker performs an implicit registration, using the values set by anon, local, and directReq. If the publisher is already registered, and anon, local, or directReq are set to yes, the existing registration is altered according to those properties.
otherSubsOnly	yes no	no	If: yes – the MQ pub/sub sends this publication to this publisher if this publisher has a subscription on this publication. no – the MQ pub/sub broker does not send this publication to this publisher, even if this publisher has a subscription on this publication.
publishSequenceId	*number* between -1, 0–(2³² – 1)	-1	If: Not -1, this is the sequence number of the publication. It should increase with each publication, but the MQ pub/sub broker does not validate it. If -1, the sequence number is not set.
publishTimeStamp	null *integer*	null	If: Not null, this is the publication timestamp in the form of YYYYMMDDHHMMSSTH, using universal time. The format is not validated. null – the publication timestamp is not set.
qmgrName	null *string*	null	This is the Queue Manager used to determine the publisher’s traditional identity. This is also where subscribers can send direct requests to this publisher. MQ limits this string to 48 bytes.
queueName	null *string*	null	This is the queue used to determine the publisher’s traditional identity. This is also where subscribers can send direct requests to this publisher. MQ limits this string to 48 bytes.
retainPub	yes no	no	If: yes – the MQ pub/sub broker does not send this publication to this publisher, even if this publisher has a subscription on this publication. no – the MQ pub/sub sends this publication to this publisher if this publisher has a subscription on this publication.
stringData	null *string*	null	If not null, this is optional publisher-defined information that is included in the publication’s MQRF header. Although MQ pub/sub allows multiple stringData tags in the MQRF header, RTMS supports only one.
integerData	*number* between -1, 0–(2³² – 1)	-1	If not -1, this is optional-publisher-defined information that is included in the publication’s MQRF header. Although MQ pub/sub allows multiple integerData tags in the MQRF header, RTMS supports only one.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the publisher’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the publisher’s traditional identity. generate – a system-generated correlationId is used as part of the publisher’s traditional identity.

For MQSeries the properties in Table 4-18 are effective only if rhfCommand is registerPublisher.

**Table 4-18: MQSeries msgsend properties if rfhCommand is set to registerPublisher**
Property	Values	Default	Description
anon	yes no	no	If: yes – MQ pub/sub broker does not divulge the identity of the publisher. no – MQ pub/sub broker divulges the identity of the publisher.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the puiblisher’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the publisher’s traditional identity. generate – a system-generated correlationId is used as part of the publisher’s traditional identity.
directReq	yes no	no	If: yes – the publisher is willing to accept direct request for publication information from other applications. Do not set this option to yes if the anon property is also set to yes, since the MQ pub/sub broker responds with an error. no – the publisher is not willing to accept direct request for publication information from other applications.
local	yes no	no	If: yes – the MQ pub/sub broker only sends this publication to subscribers that registered specifying Local. no – the MQ pub/sub broker sends this publication to all subscribers.
qmgrName	null *string*	null	This is the Queue Manager used to determine the publisher’s traditional identity. This is also where subscribers can send Direct Request requests to this publisher. MQ limits this string to 48 bytes.
queueName	null *string*	null	This is the queue used to determine the publisher’s traditional identity. This is also where subscribers can send Direct Request requests to this publisher. MQ limits this string to 48 bytes.
streamName	null *string*	null	If: Not null – this is the stream where the publisher publishes publications. null – the default is SYSTEM.BROKER.DEFAULT.STREAM. MQ limits this string to 48 bytes.
topics	*string*	none	Use the format detailed in “Syntax for topics”. Wildcards are not allowed. These are the topics on which the publisher provides information on. This is a required property, and generates an error if omitted.

For MQSeries the properties in Table 4-19 are effective only if rhfCommand is registerSubscriber.

**Table 4-19: MQSeries msgsend properties if rfhCommand is set to registerSubscriber**
Property	Values	Default	Description
topics	*string*	none	Use the format detailed in “Syntax for topics”. These are the topics on which the subscriber wants to receive publications. This is a required property, and generates an error if omitted.
anon	yes no	no	If: yes – MQ pub/sub broker does not divulge the identity of the subscriber. no – MQ pub/sub broker divulges the identity of the subscriber.
local	yes no	no	If: yes – the subscription is not distributed to other brokers in the network. Only publications published from this node by a publisher specifying Local are sent to this subscriber. no – the subscrption is not specified in the RFH command.
newPubsOnly	yes no	no	If: yes – the broker sends this publication only to this subscriber, and retained publications that exist at registration time are not sent. no – the publication is not specified in the RFH command.
pubOnReqOnly	yes no	no	If: yes – the broker only sends new publications to this subscriber, retained publications that exist at registration time are not sent. no – the publication is not specified in the RFH command.
inclStreamName	yes no	no	If: yes – the broker adds the publication stream name in the MQRF header to each message that is forwarded to the subscriber. no – the publication is not specified in the RFH command.
informIfRet	yes no	no	If: yes – the broker informs the subscriber if the publication is retained, by setting the MQPSPubsOptsIsRetainedPub in the MQRF header of the message sent to the subscriber. no – the publication is not specified in the RFH command.
dupsOk	yes no	no	If: yes – the broker is allowed to occasionally deliver a duplicate publication to the subscriber. no – the publication is not specified in the RFH command.
pubsPersistence	non-persistent persistent asPublication asQueue	asQueue	If: non-persistent – the publication is placed on the subscriber queue as a nonpersistent message. persistent – the publication is placed on the subscriber queue as a persistent message. asPublication – the publication is placed on the subscriber queue with the same persistence as the original publication. asQueue – the publication is placed on the subscriber queue with the default persistence of the subscriber queue.
streamName	null *string*	null	If: Not null – this is the stream where the publisher publishes publications. null – the subscription is identified by its traditional identity.
qmgrName	null *string*	null	This is the Queue Manager used to determine the subscriber’s traditional identity. MQ limits this string to 48 bytes.
queueName	null *string*	null	This is the queue used to determine the subscriber’s traditional identity. MQ limits this string to 48 bytes.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the subscriber’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the subscriber’s traditional identity. generate – a system-generated correlationId is used as part of the subscriber’s traditional identity.

The properties in Table 4-20 are effective only if rhfCommand is requestUpdate.

**Table 4-20: MQSeries msgsend properties if rfhCommand is set to requestUpdate**
Property	Values	Default	Description
topics	*string*	none	Use the format detailed in “Syntax for topics”. The topic that the subscriber is requesting. Only one topic can be supplied. This is a required property, and generates an error if omitted.
streamName	null *string*	null	If: Not null – this is the stream where the publisher publishes publications. null – the default is SYSTEM.BROKER.DEFAULT.STREAM.
qmgrName	null *string*	null	This is the Queue Manager name used to establish the subscriber’s traditional identity. Specify it as the same value you specified when you registered the subscriber. MQ limits this string to 48 bytes.
queueName	null *string*	null	This is the queue used to establish the subscriber’s traditional identity. Specify it as the same value you specified when you registered the subscriber. MQ limits this string to 48 bytes.
correlationAsId	yes no generate	no	If: yes – correlationId is used as part of the subscriber’s traditional identity. You must specify correlationId, but not as 0x00. no – correlationId is not used as part of the subscriber’s traditional identity. generate – a system-generated correlationId is used as part of the subscriber’s traditional identity.

Unrecognized options are ignored if you use message property. If you use message header for the msgsend or msgpublish functions, you see an error when you specify unrecognized options.
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.

MQSeries

The status returned by msgsend is the completion status from sending the message to the specified queue. It is not the completion status from the MQ pub/sub broker. To get the completion status from the MQ pub/sub broker, specify a replyToQueue, then send a request message or request a negativeActionReport. The MQ pub/sub broker sends a response or report MQRFH message to replyToQueue. In both cases, you must explicitly read the response or report message from the replyToQueue, and check the MQPSCompCode, MQPSReason, and MQPSReasonText properties in the received message.
When you specify msgSegment or msgLastSegment, if the application that is reading the message (by specifying MQGMO_COMPLETE_MSG for a non-Adaptive Server application, or completeMsg=yes for an Adaptive Server application), all the messages making up that logical message must be sent in a unit of work, so you must send all of the messages that need to be grouped in a single transaction.

Permissions

You must have messaging_role to run msgsend.

View this book as PDF