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 message_filter ::= basic_character_expression
is a SQL query expression with a datatype is char, varchar, or java.lang.String.
is a basic_character_expression where the runtime value is a service_provider_uri. The destination of a message.
passes a message_filter directly to a specified message provider, which determines its use.
is 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.
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.
is a value shown in Table 4-7.
is the general format of the option_string is specified in option_string. The options for msgrecv are described in Table 4-7.
is 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
Receives a message from the specified end_point:
select msgrecv
('tibco_jms:tcp://my_ems_host:7222?queue=queue.sample,'
+'user=ems_user1,password=ems_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_ems_provider?queue=queue.sample',
option 'timeout=1000'
MESSAGE SELECTOR 'correlationID = ''MSG_001''')
Forwards a message to the specified endpoint:
select msgsend(msgrecv('my_ems_provider?queue=queue.sample'),
'another_ems_provider?queue=queue2')
Calls only consumes messages from queue.sample when the message property “Name” is equal to “John Smith”:
select msgrecv('my_ems_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_ems_provider?queue=queue.sample',
RETURNS text)
Reads a message and returns it as a varbinary.
select msgrecv('my_ems_provider?queue=queue.sample',
returns varbinary(500))
Table 4-7 lists the available option and option_string values for properties of msgrecv.
Unrecognized option names result in an error.
See section “@@msgheader” regarding properties read from the message header.
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 a null value 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 you use requeue, messages that Adaptive Server cannot handle are placed on the specified queue.
The specified endpoint must exist on the same message 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.
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> and <msgproperties> document with msgpropvalue. For details, see msgpropvalue.
The general format of <msgheader> and <msgproperties> is described in “Messaging-related global variables”.
Place 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 true only 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_ems_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_ems_provider?queue=queue.sample',
MESSAGE SELECTOR "color='red'")
In this next 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_ems_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_ems_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_ems_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_ems_provider?queue=queue.sample',
MESSAGE PROPERTY 'color=red, shape=square'
If another client wants to select messages in which the property “color” equals “red” and the property “shape” equals “square”, that client must execute the following:
select msgrecv('my_ems_provider?queue=queue.sample',
MESSAGE SELECTOR 'color=''red'' and shape=''square''')
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.
If message_filter is specified to msgrecv, it is ignored.
You must have messaging_role to run msgrecv.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
