msgconsume

Description

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 is 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 4-4:

Table 4-4: option and option_string values for msgconsume

option values	option_string values	Default	Description
timeout	-1, 0 – (231– 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.
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

Examples

Example 1

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

sp_msgadmin 'register', 'subscription', 'subscription_1',
     'my_ems_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=2000')

Forwards a message:

select msgsend
     (msgconsume('subscription_1'), 'my_ems_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.

• 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 message 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 “Messaging-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.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF