msgconsume

Description

Provides a SQL interface to consume messages that are published to different topics.

Syntax

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

Parameters

subscription_name

Name of the subscription from which you are consuming messages.

option_string

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

returns

The clause that you want returned. 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)

basic_character_expression

A Transact-SQL query expression whose datatype is char, varchar, or java.lang.String.

option_string

is one of the following:

Table 5-7: msgconsume option values

Option	Values	Default	Values
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 null 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 requeue is not specified, and the message cannot be processed, an error message appears.The endpoint specified must be on the same messaging provideras msgconsume and msgrecv.

SQL_type

The datatypes used in SQL statements.

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’

Example 2

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

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

Example 3

Declares variables and receives a message from the specified subscription:

declare @mymsg varchar (16384)	
select @mymsg = msgconsume(’subscription_1’, 
     OPTION ’timeout=0’)

Example 4

Forwards a message:

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

Example 5

This example reads a message and returns it as a varbinary:

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

Usage

• msgconsume reads a message from the topic defined by the end_point and message_filter specified by the subscription_name. It returns null if there is a timeout or error, or returns the payload 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 endpoint specified must exist on the same messaging service provider as the endpoint used in msgconsume.

• Adaptive Server issues an error message if the messaging providerissues messages of 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.

Copyright © 2004. Sybase Inc. All rights reserved.

View this book as PDF