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 of char, varchar, or java.lang.String.
is a basic_character_expression where the runtime value is a service_provider_uri. The end_point is 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.
Any message_filter specified to msgrecv is ignored if the provider class is “ibm_mq.”
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 3-9 for MQ, and Table 3-10 for JMS.
Unrecognized option names
result in an error.
is the general format of the option_string as specified on page §. The options for msgrecv are described in Table 3-9 for MQ and Table 3-10 for JMS.
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.
is one of these valid SQL datatypes:
varchar(n)
text
java.lang.String
varbinary(n)
image
univarchar(n)
(MQ) a message is read from the queue Q1 with a specified timeout. If no messages are available on Q1 before the timeout of 3 seconds, a null value is returned:
select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'timeout=3ss')
(MQ) a correlationId is specified without a timeout. The call returns when a message matching the correlationId is available on the queue:
select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'correlationId=x67a12z99')
(MQ) a groupId is specified, as well as allMsgsInGroup, but a timeout is not specified. This call blocks until all the messages for the groupId specified are available on the queue:
select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'groupId=g7853b77,allMsgsInGroup=yes')
(MQ) these messages already exist on the queue:
AA BB CC DD EE FF GG HH
The first three messages (AA – CC) are read in browse mode, and CC is removed. The browse cursor is then set back to the beginning, and three messages (AA – DD) are read in browse mode, and DD is removed. Finally, a read is performed with position set to next, which reads and removes AA. When this example completes, the messages AA, CC, and DD are no longer on the queue.
-- Browse cursor at the beginning, this will return 'AA' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=first') -- Browse the next message, this will return 'BB' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=next') -- Browse the next message, this will return 'CC' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=next') -- Remove the message under the browse cursor, this will return 'CC' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,position=cursor') -- Reposition browse cursor at the beginning, this will return 'AA' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=first') -- Browse the next message, this will return 'BB' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=next') -- Browse the next message, this will return 'DD' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,browse=next') -- Read the message under the cursor, this will return 'DD' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,position=cursor') -- Read the next message in queue order, this will return 'AA' select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)?qmgr=QM,queue=Q1', option 'inputMode=browse+Qdefault,position=next')
Tibco JMS – 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')
SonicMQ JMS – receives a message from the queue Q1 from the specified end_point, using the timeout option:
select msgrecv ('sonicmq_jms:tcp://mysonic:7223?queue=Q1,user=sonic_usr, password=sonic_pwd',option 'timeout=1000')
(JMS) 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''')
(JMS) this msgrecv call consumes only messages from queue.sample when the message property “Name” is “John Smith”:
select msgrecv('my_jms_provider?queue=queue.sample', message selector 'Name=''John Smith''')
(JMS) 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)
(JMS) this example reads a message and returns it as a varbinary:
select msgrecv('my_jms_provider?queue=queue.sample' returns varbinary(500))
(MQ) Table 3-9 lists the available option and option_string values for msgrecv properties.
option values |
option_string values |
Default |
Description |
---|---|---|---|
allMsgsInGroup |
|
no |
This option is ignored unless you specify groupId. When you specify:
|
allSegments |
|
no |
When you specify:
|
browse |
|
null |
If you set the the browse property to:
If you also:
|
browse (continued) |
If you specify the following for browse:
|
||
bufferLength |
sizespec 0, or 1 – value |
bufferLength-sized buffer is used to read the message.
Defaults bufferLength defaults to either the:
0 indicates to use the default. For pub/sub messages, bufferLength must include the length of the message topics, including the MQRF header. |
|
closeAfterRecv |
|
no |
If:
|
completeMsg |
|
yes |
If:
completeMsg should have the same setting for all calls to msgrecv for the same endpoint. |
correlationId |
|
null |
Correlation ID of message to read, used in select statements to select specific messages in your queue. MQ defines this field as “unsigned char” that can support binary values. To enter a binary string as the correlationId, use “0x...” as the value. Do not add quote marks around the value. |
formatName |
|
null |
The name of the expected message format. If specified, and the name formatName field of the message does not match, the message is not read. See the requeue option in this table for more information.MQ limits this string to 8 bytes. |
groupid |
|
null |
Group ID of message to read. This is a select option. MQ defines this field as “unsigned char,” which means that it can support binary values. To enter a binary string as the msgId, use “0x...” as the value. Do not add quote marks around the value. |
inputMode |
|
Qdefault |
The values for inputMode open the MQ queue in the following ways:
inputMode is valid only for msgrecv. For any endpoint, you must specify inputMode either:
Attempting to change the value of inputMode across calls may cause unexpected results. |
msgId |
|
null |
Message ID of message to read. As a selection option, you can use msgId to select specific messages in your queue. MQ defines this field as “BYTE array” that can support binary values. To enter a binary string as the msgId, use “0x...” as the value. Do not add quote marks around value, as that is interpreted as a quoted string. |
offset |
integer between -1, and 0 – maxint |
Offset of message to read. If -1, the offset is not specified. As select option, you can use offset to select specific messages in your queue. |
|
ordering |
|
physical |
When ordering is:
|
position |
|
next |
position controls which message is returned. Depending on the inputMode value you specify, there are one or two “read” positions:
If:
The MQ queue manager applies the following before determining what message to return:
|
requeue |
|
null |
This must be a full URI of the endpoints. The read message is requeued to the queue specified if:
If the message cannot be requeued to the specified queue, the message is left on the queue where it was read, and an exception is raised. MQ limits a requeue to 48 bytes. |
sequenceId |
integer between -1, – 9,999,999 |
-1 |
Sequence ID of message to read. If -1, the sequence ID is not specified. As a selection option, you can use sequenceId to select specific messages in your queue. |
truncationAllowed |
|
no |
You can truncate the message when:
Specify as:
|
timeout |
timespec between -1, 0 – (232–1) |
-1 |
Specifies the timeout. If:
See timespec for more information. |
(JMS) Table 3-10 lists the available option and option_string values for msgrecv properties.
option values |
option_string values |
Default |
Description |
---|---|---|---|
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, you see an error message. The specified endpoint must be on the same messaging provider as msgconsume and msgrecv. |
timeout |
timespec -1, 0 - (231- 1) |
-1 |
By default, msgrecv 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. The values are in numbers of milliseconds. See timespec. |
Unrecognized option names result in an error.
See @@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 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.
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 use msgpropvalue to extract the values of a specific property from a <msgheader> and <msgproperties> document. See msgpropvalue.
The general format of <msgheader> and <msgproperties> is described in “Message-related global variables”.
These statements are valid only if the provider class is “ibm_mq”:
The msgId, correlationId, groupId, sequenceId, and offset options act as match criteria for selecting messages. When specified, the next message matching the values specified are returned. The qualification is performed by the WebSphere MQ queue manager.
If the MQMD.Format field of the message received is “MQSTR,” the data is assumed to be character data, and can be returned as text or varchar. Any other format name can be returned only as image or binary. One special case is if MQMD.Format is “MQHRF.” In this case, the MQRFH.Format field is used instead. If the body of the message cannot be returned in the return type specified, the message is sent to the requeue option if the requeue option is specified; otherwise, the read operation fails. MQ does not enforce that when MQMD.Format is “MQSTR,” the message body contains only character data. Programmers should always specify image or varbinary return types.
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_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 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_jms_provider?queue=queue.sample' message selector '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 selector '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_jms_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 as the sort order 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.
JMS only – if message_filter is specified to msgrecv, it is ignored.
MQ only – you can select particular messages by specifying the correlation and the message IDs in the message options.
You must have messaging_role to run msgrecv.