Chapter 4: SQL Reference

msgpublish

Description

Provides a SQL interface to publish messages to topics.

Syntax

message_publish_call ::=
	msgpublish(message_body, subscription_name 
		[options_and_properties])
options_and_properties ::= [option_clause] [properties_clause] 
		[header_clause]
option_clause ::= [,] option option_string
header_clause ::= [,] message header option_string
properties_clause ::= [,] message property option_string
message_body ::= scalar_expression | (select_for_xml)

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.

subscription_name: is the name of the subscription to which you are publishing messages.

option_clause

is the general format of the option name and an option_string, specified in the section option_string.

The options you can specify for msgsend are in Table 4-5.

properties_clause

is either an option_string or one of the options listed in the following tables. The options described in Table 4-5 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 4-6, 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.

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

header_clause

allows users to specify only header properties You see an error if you enter an unrecognized header property.

If a recognized header property is specified in both 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 options in the option_clause.

All previously recognized header properties are accepted in the message header clause.

Examples

Example 1

To publish messages, you must define a subscription on the server to which the client is connected:

sp_msgadmin 'register', 'subscription', 'subscription_1',
     'my_ems_provider?topic=topic.sample,user=user1,password=pwd',
     'Supplier=12345',null, 'durable1', 'client'

The client server can then publish a message to a specified subscription:

select msgpublish
     ('Sending order', 'subscription_1',
     MESSAGE PROPERTY 'Supplier=12345')

Usage

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 subscription_name must have been specified in a call to:
```
sp_msgadmin 'register', 'subscription'
```
It should not be specified in a subsequent call to:
```
sp_msgadmin 'remove', 'subscription'
```

Table 4-5 lists the options you can specify for msgsend.

Table 4-5: ***option_string*** values for **msgpublish**
Option	Values	Default	Comments
schema	no yes *“user_schema”*	no	Enter one of these values: *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 select_for_xml parameter. 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 ASE_MSGBODY_SCHEMA property.
type	text or bytes	text	The message type to send.

Table 4-6 lists the options and values for the properties_clause parameter. If you use a property not listed in Table 4-6, it is set as a property in the message properties of the message sent.

**Table 4-6: Values for the msgpublish *properties_clause* parameter**
Option	Values	Default	Disposition	Comments
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	When you enter: persistent – the message is backed by the JMS 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.
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.
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
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, which is the duration of time during which the 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.

Permissions

You must have messaging_role to run msgpublish.

View this book as PDF