Chapter 4: SQL Reference

sp_msgadmin

Description

Configures and administers messaging-related information.

Syntax

sp_msgadmin 'default', 'login', provider_name, provider_login,
		provider_password

sp_msgadmin 'help'[, 'list' | 'register' | 'default' | 'remove']

sp_msgadmin 'list', [| 'login'[, provider_name, [login_name] 
	| 'provider' [, provider_name]
	| 'subscription' [, subscription_name]]

sp_msgadmin 'register', 
	['provider', provider_name, provider_class,
		messaging_provider_URL 
	| 'login', provider_name, local_login, provider_login,
		provider_password [, role_name] 
	| 'subscription', subscription_name, endpoint[, selector 
		[, delivery_option [, durable_name, client_id]]]]
	| ['publisher', publisher_name, endpoint, topic
		[, broker_queue[, request_queue[, options]]]

sp_msgadmin 'remove', 
	['provider', provider_name 
	| 'login', provider_name, local_login [, role] 
	| 'subscription', subscription_name

Parameters

client_id

is the identification used by the messaging provider to identify the subscription as durable. client_id is a character string value. If you specify either client_id or durable_name, you must also specify the other, and the subscription is a durable subscription. Otherwise, it is a nondurable subscription.

The client_id and durable_name combination identifies durable subscriptions with the message provider, and must be unique. No two subscriptions can have the same client_id and durable_name.

client_id uniqueness extends across the messaging provider. JMS allows a particular client_id to be connected only once at any given time. For instance, if one application already has a durable subscription using a specified client_id, the client_id specified by another application cannot be the same if the applications are to be connected at the same time.

A durable subscription exists even when the client is not connected. The messaging provider saves messages that arrive even while the client is not connected.

A nondurable subscription exists only while the client is connected. The messaging provider discards messages that arrive while the client is not connected.

default: specifies a default. In the case of sp_msgadmin 'list', lists the syntax to specify the default login for a specified message provider.

You cannot use sp_msgadmin 'default', 'login' if endpoint is an MQ Queue Manager.

delivery_option

species whether a SQL session can consume messages that it publishes. The valid values are:

local – the SQL session can consume messages that it publishes.
nonlocal – the SQL session cannot consume messages that it publishes.
null – assumes the value is local.

durable_name: is a character string value. See the description of client_id.

endpoint: is the topic to which the subscription is addressed. See the description of endpoint in msgsend.

help: provides syntax information about this stored procedure or about particular parameters.

list: lists syntax information about message providers, logins, or subscriptions.

local_login: is an Adaptive Server login that maps to the local login.

login

lists information about a particular messaging provider login mapping or about all messaging provider logins. When used with:

register – registers a login mapping.

You cannot use sp_msgadmin 'register', 'login' if endpoint is an MQ Queue Manager.
default – specifies a default login.
remove – removes the mapping previously created between an Adaptive Server login and a service provider login, defined by this call:
```
sp_msgadmin 'register', 'login', local_login,... 
```

login_name: is a login name.

messaging_provider_URL: is the URL of the messaging provider you are registering.

provider

specifies the message provider. When used with:

register – registers a message provider.
list – lists information about a particular messaging provider or about all message providers.
remove – removes a messaging provider previously defined by this call:
```
sp_msgadmin 'register', 'provider', provider_name
```

provider_class

is the class of the messaging provider you are adding. Valid values are:

TIBCO_JMS
IBM_MQ

provider_login: is the login name of the messaging provider that local_login maps to when connecting to the message provider. It is also the login the provider uses as the default login when sending or receiving messages from the messaging provider specified by provider_name when using sp_msgadmin 'default'.

provider_name: is an alias referring to the messaging provider you are adding, which can be as many as 30 characters in length. In the case of sp_msgadmin 'register', 'provider', provider_name is an alias for messaging_provider. In the case of sp_msgadmin 'register', 'login', provider_name is the name of a previously registered provider.

provider_password: is the messaging provider password of the provider_login.

register: provides stored procedure syntax to register a message provider, login, or subscription.

You cannot use sp_msgadmin 'register', 'login' or sp_msgadmin 'register', 'subscription' if endpoint is an MQ Queue Manager.

remove: lists the stored procedure syntax to remove a message provider, login, or subscription.

role_name: is a SQL role name. If you specify a role_name, the local_login is ignored, and the provider_login and provider_password apply to the role_name.

selector: is a message filter that allows a client to select messages of interest. See the description of filters in msgrecv.

subscription

lists information about a particular subscription or about all subscriptions. Specifies the message provider. When used with:

register – registers a subscription.

You cannot use sp_msgadmin 'register' 'subscription' if the endpoint is an MQ Queue Manager.
list – lists information about a particular subscription or about all subscriptions.

remove – removes a subscription previously created by:

sp_msgadmin 'register' 'subscription', subscription_name, ...

subscription_name: is a subscription name.

Examples

Example 1

MQSeries – registers the “mq_provider_1” messaging provider, which has a class of IBM_MQ and a URL of chanl1/TCP/host1(5678):

sp_msgadmin 'register', 'provider', 'mq_provider_1', 'ibm_mq', 
    'chanl1/TCP/host1(5678)'

Example 2

TIBCO JMS – specifies the default login that applies to all unmapped Adaptive Server logins, when using a specified messaging provider for either sending or receiving:

sp_msgadmin 'default', 'login', 'my_jms_provider',
     'jms_user1', 'jms_user1_password'

You must first register the provider_name by calling sp_msgadmin 'register', 'provider'.

Example 3

TIBCO JMS – specifies the default login:

sp_msgadmin 'default', 'login', 'one_jms_provider', 
    'loginsa', 'abcdef123456'

Example 4

TIBCO JMS – describes the syntax for sp_msgadmin 'list':

sp_msgadmin 'help', 'list'

Example 5

TIBCO JMS – checks the default login:

sp_msgadmin 'list', 'login', 'my_jms_provider'

Example 6

TIBCO JMS – lists the details for the user with a login of “loginsa”:

sp_msgadmin 'list', 'login', 'my_jms_provider', 
    'loginsa'

Example 7

TIBCO JMS – lists the details for the “my_jms_provider” message provider:

sp_msgadmin 'list', 'provider', 'my_jms_provider'

Example 8

TIBCO JMS – lists the details for subscription “subscription_1”:

sp_msgadmin 'list', 'subscription', 
    'subscription_1'

Example 9

TIBCO JMS – registers the login “ase_login1”, using messaging provider login “jms_user1”, and messaging provider name “my_jms_provider”:

sp_msgadmin 'register', 'login', 'my_jms_provider', 
    'ase_login1', 'jms_user1', 'jms_user1_password'

Example 10

TIBCO JMS – registers a login using the messaging provider login “jms_user1”, and a specified password used for all unmapped Adaptive Server logins:

sp_msgadmin 'register', 'login', 'my_jms_provider', null, 'jms_user1',
     'jms_user1_password'

Example 11

TIBCO JMS – registers a login with the messaging provider login “jms_user1”, and a specified password used for all Adaptive Server logins that have sa_role permissions:

sp_msgadmin 'register', 'login', 'my_jms_provider',
    null, 'jms_user1', 'jms_user1_password', 
    'sa_role'

Example 12

TIBCO JMS – registers the “my_jms_provider” messaging provider, which has a class of TIBCO_JMS and an IP of 10.23.233.32:4823 as its address:

sp_msgadmin 'register', 'provider', 'my_jms_provider', 'TIBCO_JMS',
     'tcp://10.23.233.32:4823'

Example 13

TIBCO JMS – registers a durable subscription named “durable_sub1”, then sp_msgadmin 'list' displays information about the new subscription.

sp_msgadmin
    'register', 'subscription', 'durable_sub1',
    'my_jms_provider?topic=topic.sample',
     null, null, 'durable1', 'client1'
sp_msgadmin 'list', 'subscription', 'durable_sub1'

Example 14

TIBCO JMS – registers “subscription_1”, a nondurable subscription.

sp_msgadmin 'register', 'subscription', 'subscription_1',
     'my_jms_provider?topic=topic.sample'

You must first use sp_msgadmin register, provider to register “my_jms_provider”.

Example 15

TIBCO JMS – removes the default login:

sp_msgadmin 'remove', 'login', 'my_jms_provider'

Example 16

TIBCO JMS – removes the messaging provider “my_jms_provider”:

sp_msgadmin 'remove', 'provider', 'my_jms_provider'

Example 17

TIBCO JMS – removes the Adaptive Server login “ase_login1” associated with the messaging provider “my_jms_provider”:

sp_msgadmin 'remove', 'login', 'my_jms_provider', 
    'ase_login1'

Example 18

TIBCO JMS – removes the default login, indicated by a null login parameter:

sp_msgadmin 'remove', 'login', 'my_jms_provider', 
    null

Example 19

TIBCO JMS – removes all logins for role sa_role on “my_jms_provider”:

sp_msgadmin 'remove', 'login', 'my_jms_provider', 
    null, 'sa_role'

Example 20

TIBCO JMS – removes “subscription_1”:

sp_msgadmin 'remove', 'subscription', 
    'subscription_1'

Usage

You cannot use sp_msgadmin inside a transaction.

sp_msgadmin 'register'

When a login name is used to connect to the message provider, login names are resolved in the following order:
1. Explict login names and passwords, specified in the endpoint, if provided.
2. Explicit login mapping for the current Adaptive Server login.
3. The default login name and password for the message provider, and the role corresponding to the Adaptive Server login.
4. The default login name and password for the message provider, with no specific role association.
5. Null login name and password if none of the above apply.
You can modify the login mapping between the Adaptive Server login and the messaging provider login only by removing and reregistering it with a different set of mappings.
MQSeries only – if you enter an endpoint using a registered provider, using msgsubscribe, msgunsubscribe, msgpublish, and msgconsume return errors.
See sp_msgadmin for usage common to the variants of sp_msgadmin.

sp_msgadmin 'remove'

Removing a messaging provider does not affect messages that are in transit (that is, messages that are in the process of being sent or received) to this message provider.
sp_msgadmin 'remove' does not affect any current connections to the message provider. This means that if a message provider, login, or default is removed while there is a current connection to the specified message provider, the connection is not affected. However, Sybase does not recommend this practice.
You must specify local_login as null if you specify role_name.

Permissions

You must have messaging_role to run the msgsend and msgrecv functions.

You must have messaging_role and sso_role permissions to issue:

sp_msgadmin 'default'
sp_msgadmim 'register'
sp_msgadmin 'remove'

Any user can issue:

sp_msgadmim 'help'
sp_msgadmin 'list'

View this book as PDF