msgconsume

Description

EAServer JMS only – provides a SQL interface to consume messages that are published to different topics.

Syntax

msgconsume_call ::=
	msgconsume (subscription_name, option_and_returns)
		subscription_name:= basic_character_expression
		option_and_returns ::= [option_clause] [returns_clause]
			option_clause::= [,] option option_string
			returns_clause ::= [,] returns sql_type
				subscriber_name ::= basic_character_expression
				sql_type ::=  
					varchar(integer) | java.lang.String | text)
					| varbinary(integer) |  image

Parameters

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

option_string

is the general format of option_string is specified in option_string. The special options to use when consuming a message are described in Table 3-5:

Table 3-5: ***option*** and ***option_string*** values for **msgconsume**
*option* values	*option_string* values	Default	Description
timeout	timespec between -1, 0 – (2³¹– 1)	-1	By default, msgconsume is a blocking command, which blocks the message until it reads the next message from the message bus. If timeout is not -1, msgconsume returns a null value when the timeout interval lapses without reading a message.The values are in number of milliseconds. timeout uses the timespec option. See timespec for more information.
requeue	*string*	None	The name of a destination, queue, or topic on which to requeue messages that Adaptive Server cannot process. If you do not specify requeue, and the message cannot be processed, an error message appears.The endpoint specified must be on the same messaging provider as msgconsume and msgrecv.

subscription_name: is the name of the subscription from which you are consuming messages.

returns: specifies the clause that you want returned.

SQL_type

is the datatype used in SQL statements.

If you do not specify a datatype to be returned, the default is varchar(16384). The legal SQL datatypes are:

varchar(n)
text
java.lang.String
varbinary(n)
image
univarchar(n)

Examples

Example 1

Defines a subscription on the client server, before consuming a message:

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

Before consuming messages from a subscription, the client first subscribes to the subscription:

select msgsubscribe('subscription_1')
declare @mymsg varchar(16384)
select @mymsg = msgconsume('subscription_1')

Example 2

Declares variables and receives a message from the specified subscription:

declare @mymsg varchar (16384)	
select @mymsg = msgconsume('subscription_1', 
     option 'timeout=0')

Forwards a message:

select msgsend
     (msgconsume('subscription_1'), 'my_jms_provider?queue=queue.sample')

Reads a message and returns it as a varbinary:

select msgconsume('subscription_1' returns varbinary(500))

Usage

Unrecognized option names result in an error.

This behavior changed with Adaptive Server version 12.5.3a, and differs from earlier versions.
msgconsume reads a message from the topic defined by the end_point and message_filter specified by the subscription_name. It returns a null value if there is a timeout or error, or returns the body of the message it reads.
Adaptive Server handles only messages of types message, text, or bytes. If Adaptive Server encounters a message it cannot process, and requeue is not specified, the message is left on the original queue. Subsequent reads encounter the same message, with the same effect. To prevent this behavior, specify requeue.When requeue is specified, messages that Adaptive Server cannot handle are placed on the queue specified.

The specified endpoint must exist on the same messaging service provider as the endpoint used in msgconsume.
Adaptive Server issues an error message if the messaging provider issues messages of types other than message, text, or bytes, and if requeue is not specified.
Calling msgconsume has these results:
- The value returned is the message_body value returned by the message provider, converted to the specified returns type.
- The values of @@msgheader and @@msgproperties are set to <msgheader> and <msgproperties> documents, which contain the properties of the message that is returned by msgconsume.
  
  The general format of <msgheader> and <msgproperties> documents are described in <msgheader> and <msgproperties> documents. See “Message-related global variables”.
- You can extract the values of a specific property from XML documents <msgheader> and <msgproperties> , and other related functions, with msgpropvalue. For more details, see msgpropvalue, below.

Permissions

You must have messaging_role to run msgconsume.