msgrecv

Description

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.

Syntax

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

Parameters

basic_character_expression: is a SQL query expression with a datatype of char, varchar, or java.lang.String.

end_point: is a basic_character_expression where the runtime value is a service_provider_uri. The end_point is the destination of a message.

filter_clause: passes a message_filter directly to a specified message provider, which determines its use.

message_filter: 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.”

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.

option: is a value shown in Table 3-9 for MQ, and Table 3-10 for JMS.

Unrecognized option names result in an error.

option_string: 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.

returns_clause: 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.

sql_type: is one of these valid SQL datatypes:

varchar(n)

text

java.lang.String

varbinary(n)

image

univarchar(n)

Examples

Example 1

(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')

Example 2

(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')

Example 3

(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')

Example 4

(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')

Example 5

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')

Example 6

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')

Example 7

(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''')

Example 8

(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''')

Example 9

(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)

Example 10

(JMS) this example reads a message and returns it as a varbinary:

select msgrecv('my_jms_provider?queue=queue.sample'
     returns varbinary(500))

Usage

(MQ) Table 3-9 lists the available option and option_string values for msgrecv properties.

Table 3-9: MQ ***option*** and ***option_string*** values for **msgrecv**
*option* values	*option_string* values	Default	Description
allMsgsInGroup	yes no	no	This option is ignored unless you specify groupId. When you specify: yes – all logical messages of a group must be present on the queue before the first message of a group is returned. no – not all logical messages of a group are required to be present on the queue before returning the first message of a group.
allSegments	yes no	no	When you specify: yes – all messages of a segmented message must be present on the queue before the first message segment is returned. no – not all messages of a segmented message are required to be present before returning the first message segment.
browse	next next+Lock first first+Lock cursor cursor+Lock reopen reopen+Lock unlock null	null	If you set the the browse property to: null – the message is read and removed from the queue. The position option controls which message is read. anything other than null – the message is read but not removed from the queue. The ordering depends on the default ordering of the queue (first-in, first-out, or priority) If you also: Specify msgId, correlationId, groupId, sequenceId or offset – MQ browses or reads the next message that matches to the selection criteria that you specify. Specify timeout, and a message matching the selection criteria is not found – the return is a null value. Do not specify timeout – the msgrecv operation blocks until a message appears in the queue that matches the selection criteria.
browse (continued)			If you specify the following for browse: next – the next message is returned. next+Lock – the message is returned, and the message is locked so that other readers cannot remove it. first – the first message is returned. If you specify browse=first after you issue one or more browse=next options, the browse cursor repositions to the starting position where the queue was opened. first+Lock – the first message is returned, and the message is locked so that other readers cannot remove it. cursor – the message under the browse cursor is returned. Do not use browse=cursor without first performing browse=first, browse=first+Lock, browse=next, or browse=next+Lock. Repeating browse=cursor returns the same message. cursor+Lock – the message under the cursor is returned, and the message is locked so that other readers cannot remove it. reopen – the browse cursor is closed, reopened, and positioned at the start. For priority queues, if a higher priority message comes in since the last open, that message appears at the start of the queue. reopen+Lock – the browse cursor is closed, reopened, positioned at the start, and the first message is locked so that other readers cannot remove it. unlock – the message under the cursor is unlocked and returned.
bufferLength	sizespec 0, or 1 – value		bufferLength-sized buffer is used to read the message. The messaging built-in function attempts to allocate a buffer of this length. The command fails if there is not enough memory to allocate the buffer. When you specify msgrecv to return text or image, msgrecv assumes that the message size is the largest message that the specifed queue can accommodate, and uses the maxMsgLength queue property. Increase messaging memory if you set maxMsgLength at: Its default of 4MB, or A value that is much larger than the actual length of the messages. Sybase recommends you set the maxMsgLength queue property to the minimum allowed for the application so Adaptive Server can use the least amount of memory to read the message. To set maxMsgLength, use the MQ commands (MQSC) tool to change the MAXMSGL attribute on the queue.� Defaults bufferLength defaults to either the: Minimum of the maxMsgLength that is defined for the queue manager and the target queue, or The length of the return type if it is not text, image or java.lang.String. 0 indicates to use the default. For pub/sub messages, bufferLength must include the length of the message topics, including the MQRF header.
closeAfterRecv	yes no	no	If: yes – the queue closes after the current msgrecv operation, allowing the queue to be reopened with a different input mode on subsequent msgrecv calls. no – the queue remains open after the current msgrecv operation.
completeMsg	yes no	yes	If: yes – segmented messages are returned as a single message. no – if there are segmented messages, each segment is returned as a separate message. completeMsg should have the same setting for all calls to msgrecv for the same endpoint.
correlationId	null *string*	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 *string*	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 *string*	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	browse Qdefault shared exclusive browse+Qdefault browse+shared browse+exclusive	Qdefault	The values for inputMode open the MQ queue in the following ways: browse – opened for browsing only. The queue manager produces an error when you attempt a destructive read. Qdefault – opened in the default input mode as defined for the queue. shared – opened in shared input mode. You receive an error if the queue is already opened in exclusive mode by another MQ handle. exclusive – opened in exclusive input mode. You receive an error if the queue is already opened in shared or exclusive mode by another MQ handle. browse+Qdefault – opened for browse- and shared-input mode. browse+shared – opened for browse- and shared-input mode. You get an error if the queue is already opened in exclusive mode by another MQ handle. browse+exclusive – opened for browse- and exclusive-input mode. You get an error if the queue is already opened inshared or exclusive mode by another MQ handle. inputMode is valid only for msgrecv. For any endpoint, you must specify inputMode either: On the first msgrecv operation, or After you specify closeAfterRecv. Attempting to change the value of inputMode across calls may cause unexpected results.
msgId	null *string*	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	logical physical	physical	When ordering is: logical – the messages are read in logical order according to groupId, sequenceId, and offsets. physical – the messages are read in the order in which they appear on the queue.
position	next cursor	next	position controls which message is returned. Depending on the inputMode value you specify, there are one or two “read” positions: “Normal” – the default read position where destructive reads normally occur. When a queue is opened, the “normal” read position is positioned on the first message in the queue. “Browse cursor” – where the read position has been positioned by a previous call where browse was specified. When a queue is opened for browse, the “browse cursor” is positioned before the first message in the queue. “Browse cursor” is used only for browse+Qdefault, browse+shared, and browse+exclusive If: next – the current message at the “normal” read position is returned. The “normal” read position is moved forward to the message after the message returns. cursor – the current message at the “browse cursor” is returned. MQ queue manager raises an error if the “browse cursor” has not yet been positioned. The “browse cursor” is moved forward to the message after the message returns. The MQ queue manager applies the following before determining what message to return: The default ordering of the queue (priority, first-in, first-out) Any selection criteria specified (messageId, correlationId, groupId, seqenceId, or offset)
requeue	null *string*	null	This must be a full URI of the endpoints. The read message is requeued to the queue specified if: msgrecv reads a message when formatName is specified. The read message has a different formatName. requeue is not null. 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	yes no	no	You can truncate the message when: The buffer used to read the message (bufferLength, or length of the returned datatype). The buffer is smaller than the length of the message. Specify as: yes – to allow truncation. no – to not allow truncation. The read fails when the value is no and message is truncated.
timeout	timespec between -1, 0 – (2³²–1)	-1	Specifies the timeout. If: -1 – there is no timeout. timeout is specified as an integer – the value is to be taken in milliseconds. See timespec for more information.

(JMS) Table 3-10 lists the available option and option_string values for msgrecv properties.

Table 3-10: JMS ***option*** and ***option_string*** values for **msgrecv**
*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 - (2³¹- 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”.

MQ and msgrecv

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.

Quoting property or option values

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''')
```

Message filters

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.

Permissions

You must have messaging_role to run msgrecv.