Provides a SQL interface to receive messages from different service endpoints, which must be queues.
msgrecv receives a message from the specified service_provider and service_destination, and returns that message. The value returned is the message body returned by the service provider, converted to the specified return type.
msgrecv_call ::= msgrecv (end_point options_filter_and_returns) options_filters_and_return ::= [option_clause] [filter_clause] [returns_clause] option_clause::= [,] option option_string filter_clause::= [,] message selector message_filter message_filter::=basic_character_expression returns_clause ::= [,] returns sql_type end_point := basic_character_expression sql_type ::= varchar(integer) | java.lang.String | text | varbinary( integer ) | image
A basic_character_expression whose runtime value is a service_provider_uri. The destination of a message.
The general format of the option_string is specified in option_string—general format. The options for msgrecv are described in Table 5-3.
Passes a message_filter directly to a specified message provider, which determines its use.
A filter parameter and basic_character_expression. The filter value is passed directly to the message provider. Its use depends on the message provider. See the Usage section below for a discussion of message filters.
A SQL query expression whose datatype is char, varchar, or java.lang.String.
The datatype that you want returned.
If you do not specify a returns_clause, the default is varchar(16384).
If you specify a returns_clause of type varbinary or image, the data is returned in the byte ordering of the message.
The SQL datatype. The legal SQL datatypes are:
varchar(n)
text
java.lang.String
varbinary(n)
image
univarchar(n)
Receives a message from the specified service_provider and service_destination, and returns that message. The value returned is the message body returned by the service provider, converted to the specified return type. msgrecv option values are shown in Table 5-3.
Receives a message from the specified end_point:
select msgrecv (’tibco_jms:tcp://my_jms_host:7222?queue=queue.sample,’ +’user=jms_user1,password=jms_user1_password’)
Receives a message from the specified end_point, using the timeout option and specifying a message selector:
declare @mymsg varchar (16384) select @mymsg = msgrecv(’my_jms_provider?queue=queue.sample’, OPTION ’timeout=1000’ MESSAGE SELECTOR ’correlationID = ’’MSG_001’’’)
Forwards a message to the specified endpoint:
select msgsend(msgrecv(’my_jms_provider?queue=queue.sample’), ’another_jms_provider?queue=queue2’)
This msgrecv call only consumes messages from queue.sample when the message property “Name” is equal to “John Smith”:
select msgrecv(’my_jms_provider?queue=queue.sample’, MESSAGE SELECTOR ’Name=’’John Smith’’’)
Illustrates how to insert a text message into a table:
create table T1(c1 numeric(5,0)identity, m text)
insert into T1
select msgrecv('my_jms_provider?queue=queue.sample',
RETURNS text)
This example reads a message and returns it as a varbinary.
select msgrecv(’my_jms_provider?queue=queue.sample’ returns varbinary(500))
msgrecv receives a message from a specified service_provider and service_definition, and returns that message.
By default, msgrecv is a blocking command, which blocks the message until it reads the next message from the message bus. If timeout is not -1, msgrecv returns null when the timeout interval lapses without reading a message. Its values are in number of milliseconds.
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 msgrecv.
The message includes the binary value of the datatype according to the byte ordering of the host machine.
If you specify a filter parameter, the filter value is passed directly to the message provider. How it is used depends on the message provider.
Comparisons specified in the message filter use the sort order specified by the message provider, which may not be the same used by Adaptive Server.
JMS message providers use a JMS message selector as a filter. The rules for JMS message selectors are:
The syntax for the message selector is a subset of conditional expressions, including not, and, or, between, and like.
Identifiers are case sensitive.
Identifiers must designate message header fields and property names.
Put apostrophes (') around option values to treat them as strings. If you omit the apostrophes , the option value is treated as another property name, and the expression is only true if the two properties have the same value.
If your application uses quoted identifiers, the message selector must be enclosed in apostrophes ('). This means that if there are string values in your selectors, you must surround these values with double apostrophes (''). For example:
set quoted_identifier on select msgrecv (’my_jms_provider?queue=queue.sample’, MESSAGE SELECTOR ’color = ’’red’’’)
If your application does not use quoted identifiers, the message selector can be enclosed by ordinary double quotation marks. For example:
set quoted_identifier off select msgrecv(’my_jms_provider?queue=queue.sample’, MESSAGE SELECTOR "color=’red’")
In this example, a messaging client application sends a message expressing a property named “color” to have the value “red”, and a property named “red” to have the value “color”.
select msgsend (’Sending message with property color’, ’my_jms_provider?queue=queue.sample’ MESSAGE PROPERTY ’color=red, red=color’)
A client application that wants to consume only messages containing a property named “color” having the value “red” must place double apostrophes ('') around the selector value. For example:
select msgrecv(’my_jms_provider?queue=queue.sample’ MESSAGE SELECTOR ’color=’’red’’’)
However, the message is not received if the client application uses the following syntax, because “red” is treated as a property name:
select msgrecv(’my_jms_provider?queue=queue.sample’, MESSAGE SELECTOR ’color=red’)
In another example, a client sends a message that selects and filters for more than one property:
select msgsend(’Sending message with properties’, ’my_jms_provider?queue=queue.sample’, MESSAGE PROPERTY ’color=red, shape=square’
If another client wants to select messages in which property “color” equals “red” and property “shape” equals “square”, that client must execute the following:
select msgrecv(’my_jms_provider?queue=queue.sample’, MESSAGE SELECTOR ’color=’’red’’ and shape=’’square’’’)
Calling msgrecv 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 those of <msgheader> and <msgproperties> documents, which contain the properties of the message returned by msgrecv.
You can extract the values of a specific property from a <msgheader> or <msgproperties> document with msgpropvalue. For details, see msgpropvalue.
The general format of <msgheader> and <msgproperties> is described in “Message-related global variables”.
| Copyright © 2004. Sybase Inc. All rights reserved. |
|
|
