/*
15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009

Confidential property of Sybase, Inc.
Copyright 1987, 2009
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government's rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
*/

declare @retval int
exec @retval = sp_version 'installmsgsvss', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'start'
if (@retval != 0) select syb_quit()
go

declare @script_versnum int
select @script_versnum = 15500
if (@@version_number < @script_versnum)
begin
	print "'installmsgsvss' is being run on an older ASE installation. ASE version '%1!', install scripts version '%2!'.",
		@@version_number, @script_versnum
	select syb_quit()
end
go

declare @do_quit int
select @do_quit = 0
if (check_db_upgrade('master') = 0)
begin
	print "'installmsgsvss' cannot continue, because 'master' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsystemprocs') = 0)
begin
	print "'installmsgsvss' cannot continue, because 'sybsystemprocs' database is not upgraded to correct version."
	select @do_quit = 1
end
if (@do_quit = 1)
	select syb_quit()
go


use master
go

sp_configure "allow updates", 1
go

set proc_return_status OFF
go

set nocount on
go


declare @dflt_status smallint 
declare @newclass smallint 
declare @newsrvid smallint 
if not exists (select 1 from sysservers where srvname like 'SYB_RTMS')
begin
	if exists (select 1
        	from sysobjects
                	where name = 'sp_addserver')
	begin
		exec sp_addserver 'SYB_RTMS', 'TIBCO_JMS', 'SYB_RTMS'
	end
	else
	begin
		select @dflt_status = number from master.dbo.spt_values   
			where type = "A" and 
				name = "rpc security model A"
		select  @newclass = number from  master.dbo.spt_values
			where  type = 'X' and 
				lower(name) = lower('TIBCO_JMS')
		select @newsrvid = max(srvid) + 1 from sysservers
			where srvid < 999
		insert into master.dbo.sysservers
                	(srvid, srvstatus, srvname, srvnetname ,srvclass)
				values (@newsrvid, @dflt_status, 
					'SYB_RTMS', 'SYB_RTMS', @newclass)
	end
end
go
use master
go
	
/*
** Messages for rtm_recreate
**
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation." 
*/

/*
** This is the first file of rtm* sprocs that gets loaded in installdbextend
** script. Stick in here the initial setup stuff that needs to happen in 
** this script.
**
** This file is copied from "dbxt*"
*/
print "Installing procedures from rtm_common ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

-- ===========================================================================
-- Create the procedure to drop other sprocs first. This will also report
-- the message that the proc is being dropped and recreated.
--
if exists (select 1 from sysobjects
	   where name = "sp_rtm_recreate_proc"
	     and type = 'P'
	     and sysstat & 7 = 4
	   )
	drop procedure sp_rtm_recreate_proc
go

/*
{
*/
create procedure sp_rtm_recreate_proc (@procname varchar(256)) as
begin
	if exists (select 1 from sysobjects
		    where id = object_id(@procname)
		      and type = 'P'
		      and sysstat & 7 = 4
	)
	begin
		print "Re-installing %1!", @procname
		exec ("drop procedure " + @procname)
	end
	else
	begin
		print "Installing %1!", @procname
	end
end
go
go

/*
** Messages for rtm_help
** 
** 19283, "Usage:  sp_msgadmin 'list', 'provider' [, provider_name] "
** 19284, "Usage:  sp_msgadmin 'list', 'login' [, provider_name [,login_name]] "
** 19301, "Usage:  sp_msgadmin 'list', 'subscription' [,subscription_name]
** 19285, "Usage:  sp_msgadmin 'register', 'provider' , provider_name, provider_class, messaging_provider_URL "
** 19286, "Usage:  sp_msgadmin 'register', 'login' , provider_name, local_login , provider_login, provider_password [,role_name] "
** 19287, "Usage:  sp_msgadmin 'register', 'subscription', subscription_name, endpoint
**                    [,selector [,delivery_option [, durable_name, client_id]]]  "
** 19289, "Usage:  sp_msgadmin 'default', 'login', provider_name, provider_login, provider_password "
** 19290, "Usage:  sp_msgadmin 'remove', 'provider', provider_name "
** 19291, "Usage:  sp_msgadmin 'remove', 'login', provider_name, local_login [,role]  "
** 19292, "Usage:  sp_msgadmin 'remove', 'subscription', subscription_name  " 
** 19467, "Usage:  sp_msgadmin 'config', 'jvmlogging', logging_level "
** 19468, "Usage:  sp_msgadmin 'config', 'jvmpropertyfile', filepath "
** 19469, "Usage:  sp_msgadmin 'config', 'jvmlogfile', filepath "
** 19470, "Usage:  sp_msgadmin 'config', 'jvmmaxthreads', thread number "
** 19471, "Usage:  sp_msgadmin 'config', 'jvmminthreads', thread number "
** 19472, "Usage:  sp_msgadmin 'config', 'jvmthreadtimeout', thread timeout "
** 19473, "Usage:  sp_msgadmin 'config', 'jvm' , jvm_parameter "
** 
** 19293, "Unrecognized command for 'sp_msgadmin, %1! %2!' call "
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
** 19824, "Usage: sp_msgadmin 'config', 'ibmmq_keystore', key_repository"
*/

print "Installing procedures from rtm_help ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_help
go

/*
**	*** Help procedure ***
**    exec sp_msgadmin 'help'
**    exec sp_msgadmin 'help', 'list'
**    exec sp_msgadmin 'help', 'list', 'provider'
**    exec sp_msgadmin 'help', 'list', 'login'
**    exec sp_msgadmin 'help', 'list', 'subscription'
**
**    exec sp_msgadmin 'help', 'register'
**    exec sp_msgadmin 'help', 'register', 'provider'
**    exec sp_msgadmin 'help', 'register', 'login''
**    exec sp_msgadmin 'help', 'register', 'subscription'
**
**    exec sp_msgadmin 'help', 'default'
**    exec sp_msgadmin 'help', 'default', 'login''
**
**    exec sp_msgadmin 'help', 'remove'
**    exec sp_msgadmin 'help', 'remove', 'provider'
**    exec sp_msgadmin 'help', 'remove', 'login''
**    exec sp_msgadmin 'help', 'remove', 'subscription'
**
**    exec sp_msgadmin 'help', 'config'
**    exec sp_msgadmin 'help', 'config', 'jvm'
**    exec sp_msgadmin 'help', 'config', 'jvmlogging'
**    exec sp_msgadmin 'help', 'config', 'jvmpropertyfile'
**    exec sp_msgadmin 'help', 'config', 'jvmlogfile'
**    exec sp_msgadmin 'help', 'config', 'jvmmaxthreads'
**    exec sp_msgadmin 'help', 'config', 'jvmminthreads'
**    exec sp_msgadmin 'help', 'config', 'jvmthreadtimeout'
**    exec sp_msgadmin 'help', 'config', 'ibmmq_keystore'
**
**    exec sp_msgadmin 'help', 'show' 
**    exec sp_msgadmin 'help', 'show', 'qmgr'
**    exec sp_msgadmin 'help', 'show', 'queues'
**    exec sp_msgadmin 'help', 'show', 'channels'	
**
{
*/
create procedure sp_rtm_help(@command varchar(30)	= NULL, @subcommand varchar(30)	= NULL) as
begin

	declare @cmds_list	varchar(256)
	      , @cmdcode	int

        declare @list_provider varchar(250)
	exec sp_getmessage 19283, @list_provider output 
        declare @list_login varchar(250)
	exec sp_getmessage 19284, @list_login output 
        declare @list_subscription varchar(250)
	exec sp_getmessage 19301, @list_subscription output 

        declare @reg_provider varchar(250)
	exec sp_getmessage 19285, @reg_provider output 
        declare @reg_login varchar(250)
	exec sp_getmessage 19286, @reg_login output 
        declare @reg_subscription varchar(250)
	exec sp_getmessage 19287, @reg_subscription output 

        declare @default_login varchar(250)
	exec sp_getmessage 19289, @default_login output 

        declare @rem_provider varchar(250)
	exec sp_getmessage 19290, @rem_provider output 
        declare @rem_login varchar(250)
	exec sp_getmessage 19291, @rem_login output 
        declare @rem_subscription varchar(250)
	exec sp_getmessage 19292, @rem_subscription output         

	declare @jvmlogging varchar(250)
	exec sp_getmessage 19467, @jvmlogging output
	declare @jvmpropertyfile varchar(250)
	exec sp_getmessage 19468, @jvmpropertyfile output
	declare @jvmlogfile varchar(250)
	exec sp_getmessage 19469, @jvmlogfile output
	declare @jvmmaxthreads varchar(250)
	exec sp_getmessage 19470, @jvmmaxthreads output
	declare @jvmminthreads varchar(250)
	exec sp_getmessage 19471, @jvmminthreads output
	declare @jvmthreadtimeout varchar(250)
	exec sp_getmessage 19472, @jvmthreadtimeout output
	declare @jvm varchar(250)
	exec sp_getmessage 19473, @jvm output
	declare @ibmmqkeystore varchar(250)
	exec sp_getmessage 19824, @ibmmqkeystore output

	declare @show_qmgr varchar(250)
	exec sp_getmessage 19783, @show_qmgr output
	declare @show_queues varchar(250)
	exec sp_getmessage 19784, @show_queues output
	declare @show_channels varchar(250)
	exec sp_getmessage 19785, @show_channels output
	
	if (@command is NULL or @command='all')  
	begin
		-- Usage list
		print @list_provider 
		print @list_login 
		print @list_subscription

		-- Usage register
		print @reg_provider 
		print @reg_login 
		print @reg_subscription 

		-- Usage default
		print @default_login 

		-- Usage remove
		print @rem_provider 
		print @rem_login 
		print @rem_subscription 

		-- Usage config
		print @jvm
		print @jvmlogging
		print @jvmpropertyfile
		print @jvmlogfile
		print @jvmmaxthreads
		print @jvmminthreads
		print @jvmthreadtimeout
		print @ibmmqkeystore

		-- Usage show
		print @show_qmgr
		print @show_queues
		print @show_channels
		
		return 0
	end

	if (@command='list' and @subcommand is null)
	begin
		print @list_provider 
		print @list_login 
		print @list_subscription 
                return 0
	end

	if (@command='register' and @subcommand is null)
	begin
		print @reg_provider 
		print @reg_login 
		print @reg_subscription 
                return 0
	end

	if (@command='default' and @subcommand is null)
	begin
		print @default_login 
                return 0
	end

	if (@command='remove' and @subcommand is null)
	begin
		print @rem_provider 
		print @rem_login 
		print @rem_subscription 
                return 0
	end
	if (@command='config' and @subcommand is null)
	begin
		print @jvm
		print @jvmlogging
		print @jvmpropertyfile
		print @jvmlogfile
		print @jvmmaxthreads
		print @jvmminthreads
		print @jvmthreadtimeout
		print @ibmmqkeystore

		return 0
	end
	if (@command='show' and @subcommand is null)
	begin
		print @show_qmgr
		print @show_queues
		print @show_channels
		
		return 0
	end
	
	if (@command='list' and @subcommand = "provider") 
		begin print @list_provider return 0 end 
	if (@command='list' and @subcommand = "login") 
		begin print @list_login  return 0 end
	if (@command='list' and @subcommand = "subscription") 
		begin print @list_subscription  return 0 end
	if (@command='register' and @subcommand = "provider") 
		begin print @reg_provider  return 0 end
	if (@command='register' and @subcommand = "login") 
		begin print @reg_login  return 0 end
	if (@command='register' and @subcommand = "subscription") 
		begin print @reg_subscription  return 0 end
	if (@command='default' and @subcommand = "login") 
		begin print @default_login return 0 end 
	if (@command='remove' and @subcommand = "provider") 
		begin print @rem_provider return 0 end 
	if (@command='remove' and @subcommand = "login") 
		begin print @rem_login return 0 end 
	if (@command='remove' and @subcommand = "subscription") 
		begin print @rem_subscription return 0 end 
	if (@command='config' and @subcommand = "jvm") 
		begin print @jvm return 0 end
	if (@command='config' and @subcommand = "jvmlogging") 
		begin print @jvmlogging return 0 end
	if (@command='config' and @subcommand = "jvmlogfile") 
		begin print @jvmlogfile return 0 end
	if (@command='config' and @subcommand = "jvmpropertyfile") 
		begin print @jvmpropertyfile return 0 end
	if (@command='config' and @subcommand = "jvmmaxthreads") 
		begin print @jvmmaxthreads return 0 end
	if (@command='config' and @subcommand = "jvmminthreads") 
		begin print @jvmminthreads return 0 end
	if (@command='config' and @subcommand = "jvmthreadtimeout") 
		begin print @jvmthreadtimeout return 0 end
	if (@command='config' and @subcommand = "ibmmqkeystore")
		begin print @ibmmqkeystore   return 0 end
	if (@command='show' and @subcommand = 'qmgr')
		begin print @show_qmgr return 0 end
	if (@command='show' and @subcommand = 'queues')
		begin print @show_queues return 0 end
	if (@command='show' and @subcommand = 'channels')
		begin print @show_channels return 0 end
	
	else  -- unrecognized command
	begin raiserror 19293, @command, @subcommand  return 1 end
	return 1

end
go	-- }

if (@@error != 0) 
	select syb_quit()
go

go

/*
** Messages for rtm_check_extra_args
**
** 19295, "Extra argument(s) for 'sp_msgadmin %1! %2!' call  "
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
** 
*/



print "Installing procedures from rtm_check_extra_args ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_check_extra_args
go

/*
**	*** check_extra_args procedures ***
{
*/

/*    
 * sp_msgadmin is called with command and subcommand arguments
 * and up to 4 others.
 * Each subcommand calls sp_check_extra_args with whatever args
 * are not needed for that subcommand.
 * If any of those extra args is non-null, sp_rtm_check_extra_args
 * raises an exception.
 */
create procedure sp_rtm_check_extra_args(
			@command	varchar(30) 	= NULL
		      , @subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
        declare @retval int

        if (@arg1 is null
		and @arg2 is null
		and @arg3 is null
		and @arg4 is null
		and @arg5 is null)
        return 0
        else
        begin

		-- Extra arguments
		raiserror 19295, @command, @subcommand
		exec @retval = sybsystemprocs.dbo.sp_rtm_help @command, @subcommand                
                return 1
	end
end
go	-- }

if (@@error != 0) 
	select syb_quit()
go

go

/*
** Messages for rtm_check_required_arg
** 
** 19296, "'sp_msgadmin %1! %2!' requires the '%3!' argument.  "
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
** 
*/

print "Installing procedures from rtm_check_required_arg ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_check_required_arg
go

/*
**	*** check_required_arg procedures ***
{
*/

/*    
 * sp_msgadmin is called with command and subcommand arguments
 * and up to 4 others.
 * Each subcommand calls sp_check_required_arg with whatever args
 * are required for that subcommand.
 * If that arg is null, sp_rtm_check_required_arg raises an exception.
 */
create procedure sp_rtm_check_required_arg(
			@command	varchar(30) 	= NULL
		      , @subcommand	varchar(30) 	= NULL
		      , @argname	varchar(30)	= NULL
		      , @argval		varchar(256)	= NULL

) as
begin

        declare @retval int

        if (@argval is not null) 
		return 0

	-- Required argument omitted
	raiserror 19296, @command, @subcommand, @argname
	exec @retval = sybsystemprocs.dbo.sp_rtm_help @command, @subcommand                
        return 1
end
go	-- }

if (@@error != 0) 
	select syb_quit()
go

go

/*
** Messages for "rtm_helpserver"
**
** 17270, "There is not a server named '%1!'."
** 17680, "There are no remote servers defined."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

print 'Installing procedures from rtm_helpserver ...'
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_helpserver
go


/*  
 ** This procedure is a copy of 'sp_helpserver' that displays only messaging providers 'srvclass=12' or 'srvclass=13'
 ** or 'srvclass=14' It is intended for internal use by 'rtm_list' to list messaging providers,
 ** I.e. to list only servers with 'srvclass=12' or 'srvclass=13' or 'srvclass=14'.
 ** The only change in this copy of 'sp_helpserver' is the one clause 'and srvclass=12 or srvclass=13 or srvclass=14'
 ** added to the SELECT	that populates #spt_server
*/

create procedure sp_rtm_helpserver
   @server varchar(255) = NULL		/* server name */
as

declare @msg	varchar(1024)
declare @len1 int, @len2 int


if @@trancount = 0
begin
	set chained off
end

set transaction isolation level 1

set nocount on

/*
**  If no server name given, get 'em all.
*/
if @server is null
begin
	select @server = "%"
end

/*
**  Does the server exist?
*/
if not exists (select *
		from master.dbo.sysservers 
			where srvname like @server
				or srvnetname like @server)
begin
	if @server = "%"
	begin
		/* 17680, "There are no remote servers defined." */
		exec sp_getmessage 17680, @msg output
		print @msg
	end
	else 
		/* 17270, "There is not a server named '%1!'." */
		raiserror 17270, @server
	return (1)
end
	
/*
**  Display server information.
**  First put it into a temp table so we can examine the status 
**  bits and turn them into english.
*/
create table #spt_server
(
	name	varchar(255),
	network	varchar(768),
	class	varchar(28) NULL,
)

/*
**  Initialize #spt_server from sysservers.
*/
insert into #spt_server
	select s.srvname, s.srvnetname,  v.name
	from master.dbo.sysservers s, master.dbo.spt_values v 
		where (s.srvname like @server
			or s.srvnetname like @server)
			and ( s.srvclass = 12
				OR s.srvclass = 13
				OR s.srvclass = 14 
				OR s.srvclass = 15)
			and s.srvclass *= v.number
			and v.type = 'X' and v.name != 'access_server'
			and not exists (select 1 from master.dbo.sysattributes 
					where  object = s.srvid
						and class = 21
						and attribute = 10
						and object_type= 'PR'
						and int_value in (12,13,14,15))


/*
** insert server information which are registed on RTDS4.6 or later
** into @spt_server
** All information of theses servers' are stored in sysattributes.
*/
insert into #spt_server
        select s.object_cinfo, s.char_value,  v.name
        from master.dbo.sysattributes s, master.dbo.spt_values v,
		master.dbo.sysservers sr
        where (s.object_cinfo like @server
                or s.char_value like @server)
        and ( s.int_value = 12
                OR s.int_value = 13
                OR s.int_value = 14 
		OR s.int_value = 15 )
	and s.object = sr.srvid
        and s.int_value *= v.number
        and v.type = 'X' and v.name != 'access_server'
        and s.class = 21 and s.attribute = 10 and s.object_type = 'PR'


/*
**  Display the information we got.
*/

exec sp_autoformat @fulltabname = #spt_server,
	@selectlist="provider_name=name, provider_URL = network, provider_class=class",
	@orderby = "order by name"
drop table #spt_server
return (0)
go
/*

**
** Messages for "rtm_helpexternlogin"
**
** 17270, "There is not a server named '%1!'."
** 17271, "'%1!' is the local server - remote login not applicable."
** 18294, "User '%1!' is not a local user -- request denied."
** 19328, "Argument '%1' is not a message provider."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

print 'Installing procedures from rtm_helpexternlogin ...'
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_helpexternlogin
go


/*  
 ** This procedure is a copy of 'sp_helpexternlogin' that displays only messaging logins 'srvclass=12'
 ** It is intended for internal use by 'rtm_list' to list messaging logins,
 ** I.e. to list only logins with 'srvclass=12'.
*/

create procedure sp_rtm_helpexternlogin
	@server		varchar(255) ,	/* name of remote server */
	@loginame	varchar(255) 	/* user's local name */

AS
BEGIN
	declare @srvid		smallint,
		@srvclass	smallint  

/*
**  Check that the server name is valid.
*/
	IF @server is not null
	BEGIN
		select @srvid = srvid, @srvclass=srvclass 
		from master.dbo.sysservers
		where srvname = @server
		
		if @@rowcount = 0
		BEGIN
			/*		
			** 17270  "There is not a server named %1!"  
			*/
			raiserror 17270, @server
			return (1)
		END

		if ((@srvclass != 12) and (@srvclass != 14) and (@srvclass != 15))
		BEGIN
			/*	
			** 19328, "Argument '%1!' is not a message provider."	
			*/
			raiserror 19328, @server
			return (1)
		END

		/*
		**  Check for local server.
		*/
		if @srvid = 0
		BEGIN
			/*		
			** 17271 "'%1!' is the local server - remote login
			** not applicable."
			*/
			raiserror 17271, @server
			return (1)
		END
	END
	ELSE
	BEGIN
		select @server = "%"
	END

/*
**  Check that the @loginame is valid.
*/
	if @loginame is not null
	BEGIN
		if not exists (select * from master.dbo.syslogins
			where name = @loginame)
		BEGIN
			/*		
			** 18294 "User '%1!' is not a local user --
			** request denied."
			*/
			raiserror 18294, @loginame
			return (1)
		END
	END
	ELSE
	BEGIN
		select @loginame = "%"
	END


/*
**  Display external logins that are mapped to local login names as well as
**  external logins that are mapped to a NULL login name. If a specific
**  local login name was requested, only display its entries.
*/

      
	SELECT	"Provider" = s.srvname, 
		"Login" = l.name, 
		"Externlogin" = a.object_cinfo
	FROM	master.dbo.sysattributes a,
		master.dbo.sysservers s,
		master.dbo.syslogins l
	WHERE	a.class = 21 AND		
		a.object_type = "EL" AND
		a.object_info1 = s.srvid AND
		s.srvname LIKE @server AND
		a.object = l.suid AND
		l.name LIKE @loginame
	UNION
	SELECT	s.srvname, 
		NULL, 
		a.object_cinfo
	FROM	master.dbo.sysattributes a,
		master.dbo.sysservers s
	WHERE	a.class = 21 AND			
		a.object_type = "EL" AND
		a.object_info1 = s.srvid AND
		s.srvname LIKE @server AND
		a.object_info2 = -1 AND
		a.object = -1 AND
		@loginame = "%"
	ORDER BY "Provider", "Login"		

        -- If a specific loginame was specified, don't display roles
	if (@loginame != "%") return 0

	SELECT	"Provider" = s.srvname, 
		"Role" = r.name, 
		"Externlogin" = a.object_cinfo
	FROM	master.dbo.sysattributes a,
		master.dbo.sysservers s,
		master.dbo.syssrvroles r
	WHERE	a.class = 21 AND
		a.object_type = "EL" AND
		a.object_info1 = s.srvid AND
		s.srvname LIKE @server AND
		a.object_info2 = r.srid 
	ORDER BY "Provider", "Role"	
	
	return 0

END
go
/*
** Messages for rtm_default
** 
** 19294, "Unrecognized subcommand for 'sp_msgadmin %1!, %2!' call  "
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
** 
*/

print "Installing procedures from rtm_default ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_default
go

/*
**	*** Default procedures ***
**  Usage:  sp_msgadmin 'default', 'login' , provider_name, provider_login , provider_password 
**
{
*/
create procedure sp_rtm_default(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
        declare @retval int

	/*
	** check if user has sso role, proc_role will also do auditing
	** if required. proc_role will also print error message if required.
	*/
	if (proc_role("sso_role") = 0)
		return (1)

	if (@subcommand = 'login')
	begin
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args "default", "login", @arg4, @arg5
                if (@retval != 0 ) 
			return @retval 
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"default", "login", "provider_name", @arg1
                if (@retval != 0 ) 
			return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"default", "login", "provider_login", @arg2
                if (@retval != 0 ) 
			return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"default", "login", "provider_password", @arg3
                if (@retval != 0 ) 
			return @retval             

		--default login provider_name(@arg1) login(@arg2) password(@arg3)
		exec sp_addexternlogin @arg1, NULL, @arg2, @arg3, NULL
		return 0
	end

	else
	begin
                exec @retval = sybsystemprocs.dbo.sp_rtm_help "default"
                if (@retval != 0 ) 
			return @retval             
	end
	set nocount off
	return 1

end
go	-- }

if (@@error != 0) 
		select syb_quit()
go

go
/*
** Messages for rtm_list
**   
** 19294, "Unrecognized subcommand for 'sp_msgadmin %1!, %2!' call  "
*/

print "Installing procedures from rtm_list ..."
go

use sybsystemprocs
go

exec sp_rtm_recreate_proc sp_rtm_list
go

/*
**	*** List procedures ***
**
** Usage:  sp_msgadmin 'list', 'provider' [, provider_name] 
** Usage:  sp_msgadmin 'list', 'login' [, provider_name [,login_name]] 
** Usage:  sp_msgadmin 'list', 'subscription'
**
{
*/
create procedure sp_rtm_list(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
        declare @retval 	int

	if (@subcommand = 'provider')
	begin
		--list provider provider_name(@arg1)
                exec @retval =  sybsystemprocs.dbo.sp_rtm_check_extra_args 
			"list", "provider", @arg2, @arg3, @arg4, @arg5
                if (@retval != 0 ) 
			return @retval 

		-- Call 'rtm_helpserver'.
		-- This is identical to 'sp_helpserver', 
		-- except that it lists only messaging servers,
		-- I.e. those with 'srvclass=12'
		exec sybsystemprocs.dbo.sp_rtm_helpserver @arg1
		return 0
	end

	else if (@subcommand = 'login')
	begin
		--list login provider_name(@arg1) login_name(@arg2)
                exec @retval =  sybsystemprocs.dbo.sp_rtm_check_extra_args "list", "login", 
			@arg3, @arg4, @arg5
                if (@retval != 0 ) 
			return @retval 
                exec sybsystemprocs.dbo.sp_rtm_helpexternlogin @arg1, @arg2
		return 0
	end

	else if (@subcommand = 'subscription')
	begin
		--list the name, end-point, filter, and name-and-client-id for each subscription
    
		--check if any superfluous arguments were specified 
                exec @retval =  sybsystemprocs.dbo.sp_rtm_check_extra_args "list", "subscription", 
			 @arg2, @arg3, @arg4, @arg5
                if (@retval != 0 ) 
			return @retval 

		/*	
		** The "comments" column contains the 'durable_name' and 'client_id' separated by a "?".
		** See the code in "rtm_register".
		** The following SELECT contains a substring/CASE/charindex expression 
		** to split the "comments" column into the 'durable_name' and 'client_id' parts.
		** This expression is designed to handle the case where the 'comments' is NULL, or an empty string,
 		** or is a single '?', or is a string with no '?'.  Most of those cases should not occur,
		** but we don't want the proc to fail.
		** The same substring/CASE/charindex expression occurs several times in the following code,
		** and this explanation applies to all of them.
		*/
                
		if (@arg1 is null)   -- no subscription_name was specified, so list all subscriptions
		begin

         	       SELECT "Subscription" = object_cinfo, 
			 "End_point" = char_value,
			 "Filter" = text_value,
                         "Delivery_option" = 
				(select name from master.dbo.spt_values
				where type='Rt' and number = sa.int_value),
       			 "Durable_name" = 
				substring(comments, 1, 
					CASE WHEN (charindex("?", comments) != 0) 
					THEN (charindex("?", comments)-1) 
					ELSE 255 END),
			 "Client_id" = substring(comments, charindex("?", comments)+1, 255)
			into #rtmlist1rs	
			FROM master.dbo.sysattributes sa
 			WHERE class=21 and attribute = 1 and object_type='SU' 
			exec sp_autoformat @fulltabname = #rtmlist1rs	
			drop table #rtmlist1rs
			return 0
		end

                else   -- a subscription_name was specified, so list only info for it

		begin  

         	       SELECT  "End_point" = char_value,
			  "Filter" = text_value,
			  "Durable_name" = 
					substring(comments, 1, 
					CASE WHEN (charindex("?", comments) != 0) 
					THEN (charindex("?", comments)-1) 
					ELSE 255 END ) ,
			  "Client_id" = substring(comments, charindex("?", comments)+1, 255)
			into #rtmlist2rs
			FROM master.dbo.sysattributes
	 		WHERE class=21 and attribute = 1 and object_type='SU' 
				      and object_cinfo=@arg1
			exec sp_autoformat @fulltabname = #rtmlist2rs
			drop table #rtmlist2rs
			return 0
		end
		
	end

	else
	begin
		exec @retval = sybsystemprocs.dbo.sp_rtm_help "list"
		if (@retval != 0)
			return @retval
	end
	set nocount off
	return 1

end
go	-- }

if (@@error != 0) 
		select syb_quit()
go

go
	
/*
** Messages for rtm_register
**  
** 19290, "Usage:  sp_msgadmin 'remove', 'provider', provider_name "
** 19294, "Unrecognized subcommand for 'sp_msgadmin %1!, %2!' call  "
** 19297, "The subscription name  '%1!' is already registered.  " 
** 19298, "Unable to register subscription name  ('%1!')   "
** 19299, "The length of '%1!' cannot be greater than 127 bytes."
** 19300, "There is already an existing subscription with the same durable name and client id."
** 19303, "The 'delivery_option' argument must be 'local', 'nolocal', or null. "
** 19306, "If the 'client_id' argument is specified, the 'durable_name' argument must also be specified."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

print 'Installing procedures from rtm_register ...'
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_register
go

/*
**	*** Register procedures ***
**
** Usage:  sp_msgadmin 'register', 'provider' , provider_name, provider_class, messaging_provider_URL 
** Usage:  sp_msgadmin 'register', 'login' , provider_name, local_login , 
**                       provider_login, provider_password [,role_name] 
** Usage:  sp_msgadmin 'register', 'subscription' , subscription_name, endpoint 
**                       [,selector [,delivery_option [, durable_name, client_id]]]  "f
** 
**
{
*/
create procedure sp_rtm_register(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL
		      , @arg6		varchar(256)	= NULL

--  @arg6 is included only so that we can determine when the user passes too many arguments.

) as
begin
	declare @retval		int
	declare @comment_val	varchar(255)
	declare @srvclass	int



	/*
	** check if user has sso role, proc_role will also do auditing
	** if required. proc_role will also print error message if required.
	*/
	if (proc_role("sso_role") = 0)
		return (1)

	if (@subcommand = 'provider')
	begin
		--register provider name(@arg1) class(@arg2) URL(@arg3) 
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args 'register', 'provider', 
			@arg4, @arg5
		if (@retval != 0 ) return @retval
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'provider', 'provider_name', @arg1
                if (@retval != 0 ) return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'provider', 'provider_class', @arg2
                if (@retval != 0 ) return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'provider', 'provider_URL', @arg3
                if (@retval != 0 ) return @retval             
		
		/*
		** check if the provider class is supported rtds type
		*/
		select @srvclass = number
		from master.dbo.spt_values
		where type = 'X' and lower(name) = lower(@arg2)
		if @@rowcount = 0
		begin
			/*
			** 17296, "Unknown server class '%1!'"
			*/
			raiserror 17296, @arg2
			return (1)
		end
		if not (@srvclass in (12, 13, 14, 15))
		begin
			/*
			** 19416 "Provider class '%1!' is not a recognized
			** messaging provider class. "
			*/
			raiserror 19416, @arg2
			return (1)
		end
		exec @retval = sp_addserver @arg1, @arg2, @arg3
		if (@retval != 0) return @retval
		return 0
	end

	else if (@subcommand = 'login')
	begin

		--register login name(@arg1) 
                -- local_login(@arg2) provider_login(@arg3) 
                -- provider_password(@arg4) role_name(@arg5)

                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args 'register', 'login', @arg6 
		if (@retval != 0 ) 
			return @retval
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'login', 'provider_name', @arg1
                if (@retval != 0 ) 
			return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'login', 'provider_login', @arg3
                if (@retval != 0 ) 
			return @retval             

		exec sp_addexternlogin @arg1, @arg2, @arg3, @arg4, @arg5
		return 0
	end


	else if (@subcommand = 'subscription')
	begin

		-- register subscription name(@arg1) 
		--    endpoint(@arg2) selector(@arg3) delivery_option(@arg4)
		--    durable_name(@arg5) client_id(@arg6)


                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'subscription', 'subscription_name', @arg1
                if (@retval != 0 ) 
			return @retval             
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			'register', 'subscription', 'endpoint', @arg2
                if (@retval != 0 ) 
			return @retval             
		if (@arg6 is not null and @arg5 is null)
		   begin
		  	-- If client_id is specified, then durable_name is requited.
			raiserror 19306, "client id"
			return 1
		   end
		-- If the 'endpoint' parameter has a password, encrypt it.
		declare @psw varchar(255)
		declare @pswstart int
		declare @pswend int
		set @pswstart = charindex('password=', @arg2)

		if (@pswstart != 0)
		begin
			set @pswstart = @pswstart + 9
			set @psw = substring(@arg2, @pswstart, char_length(@arg2))
			set @pswend =  charindex(",", @psw)
			if (@pswend = 0)
			begin
				set @pswend = char_length(@arg2)
			end
			set @psw = substring(@psw, 1, @pswend-1)
			set @arg2 = substring(@arg2, 1, @pswstart-1) + '*****'
				+ substring(@arg2, @pswstart+@pswend-1, char_length(@arg2)-@pswend)
			-- At this point, @psw is either null or contains the extracted password,
			-- and @arg2 is either the original endpoint,
			-- or the endpoint with the password replaced with '*****'
		end
		-- End of processing the 'endpoint' password

		declare @delivery_option_number int
 		-- @delivery_option is 0 or 1 for local or nolocal

                if (@arg4 is null)
 		begin
			select @delivery_option_number = number
			FROM master.dbo.spt_values 			
			where type = 'Rt' and lower(name) = 'local'
		end
		
		else
		
		begin
	                SELECT @delivery_option_number = number 
			FROM master.dbo.spt_values WHERE type='Rt' AND lower(name) = lower(@arg4)
	
			if (@@rowcount = 0)
			begin
				-- "The 'delivery_option' argument must be 'local', 'nolocal', or null. "
				raiserror 19303
				return 1
			end
		end

		-- If @arg5 is present, @arg6 must also be there.
                if (@arg5 is not null)  --I.e. a durable subscription
                begin
			exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
				'register', 'subscription', 'client_id', @arg6
			if (@retval != 0 ) 
				return @retval          

			if ((select datalength(@arg5)) > 127)
			begin
			    raiserror 19299, "durable name"
			end

			if ((select datalength(@arg6)) > 127)
			begin
			    raiserror 19299, "client id"
			end
		end
   
		-- Check if this subscription already exists
		if (select count(*) from master.dbo.sysattributes
		    where object_cinfo=@arg1
		      and object_type='SU'
		      and class=21
		      and attribute=1) !=0
		begin
		    -- There is already a subscription of the given name
		    raiserror 19297, @arg1
		    return 1
		end
      
      		-- If this is durable, the client-id and durable-name are
		-- stored as 'client-id?durable-name'
		-- Length has already been checked above.
  		-- Note:  The "rtm_list" proc assumes that 'comments' values will contain the '?'.
		--        It uses the "?" to separate the durable name and client id.
                if (@arg5 is not null)  --I.e. a durable subscription
		begin
			select @comment_val = @arg5 + "?" + @arg6

			-- Check if there is an existing subscription with
			-- the same client-id and durable name.
			if (select count(*) from master.dbo.sysattributes
			    where comments=@comment_val
			      and object_type='SU'
			      and class=21
			      and attribute=1) !=0
			begin
			    raiserror 19300
			    return 1
			end
		end
		else
		begin
			select @comment_val = NULL
		end

		/*  
		**  Start a transaction.
		**  We'll use this both to roll back the main action
		**  and also to roll back any HA action
		*/
		begin transaction reg_subscription

		insert into master.dbo.sysattributes
		(class, attribute, object_type, object_cinfo, char_value, text_value, int_value, image_value, comments)	
   		       values (21, 1, 'SU', @arg1, @arg2, @arg3, @delivery_option_number, internal_encrypt(@psw), @comment_val)

		if (attrib_notify(21, 1, 'SU', NULL, NULL, NULL, NULL, 
			@arg1, @delivery_option_number, @arg2, @arg3, NULL, @comment_val, 1) = 0)
		begin
		        raiserror 19298, @arg1
			-- repeat 'attrib_notify' call with same args except last is '3'
 			select attrib_notify(21, 1, 'SU', NULL, NULL, NULL, NULL, 
				@arg1, @delivery_option_number, @arg2, @arg3, NULL, @comment_val, 3)
			rollback transaction reg_subscription
			return 1
		end
                     
		/*  Do the insert for HA.  */



		commit transaction reg_subscription		   
		return 0
	end

	else  
	begin
                exec @retval = sybsystemprocs.dbo.sp_rtm_help 'register'
                if (@retval != 0 ) 
			return @retval          
	end
	set nocount off
	return 1

end
go	-- }

if (@@error != 0) 
	select syb_quit()

go

/*
** Messages for rtm_remove
**   
** 19294, "Unrecognized subcommand for 'sp_msgadmin %1!, %2!' call  "
** 19304, "Only one of the 'local_login' and 'role' parameters can be non-null"
** 19360, "There is no subscription named '%1!' "
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

print "Installing procedures from rtm_remove ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_remove
go

/*
**	*** Remove procedures ***
** 
** Usage:  sp_msgadmin 'remove', 'provider' , provider_name 
** Usage:  sp_msgadmin 'remove', 'login' , provider_name , local_login  
** Usage:  sp_msgadmin 'remove', 'subscription' , subscription_name   
** 
** 
{
*/
create procedure sp_rtm_remove(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
	declare @retval		int
	declare @error_occurred integer
	declare @num_rows_deleted integer
	declare @srvclass 	int
	declare @class          varchar(28)



	/*
	** check if user has sso role, proc_role will also do auditing
	** if required. proc_role will also print error message if required.
	*/
	if (proc_role("sso_role") = 0)
		return (1)

	if (@subcommand = 'provider')
	begin
		--remove provider provider_name(@arg1)
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args "remove", "provider", 
			@arg2, @arg3, @arg4, @arg5
		if (@retval != 0 ) 
			return @retval
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"remove", "provider", "provider_name", @arg1
                if (@retval != 0 ) 
			return @retval             
		/*
		** Check to see if this is a rtds provider
		*/
		select @srvclass = srvclass from master.dbo.sysservers
		where srvname = @arg1
		if @@rowcount = 0
		begin
			/*
			** 17270, "There is not a server named '%1!'."
			*/
			raiserror 17270, @arg1
			return (1)
		end

		if not (@srvclass in (12,13,14,15))
		begin
			select @class = name
			from master.dbo.spt_values
			where type = 'X' and number = @srvclass
			if @@rowcount = 0
			begin
				/*
				** 17296, "Unknown server class '%1!'"
				*/
				raiserror 17296, @class
				return (1)
			end
			else
			begin
				/*
				** 19416 "Provider class '%1!' is not 
				** a recorgnized messaging provider class. "
				*/
				raiserror 19416, @class
				return (1)
			end
			return (1)
		end
		exec @retval = sp_dropserver @arg1
		if (@retval != 0 )
			return @retval
		return 0
	end

	if (@subcommand = 'login')
	begin
		--remove login provider_name(@arg1) local_login(@arg2) role_name(@arg3)
		-- Allow only one of @arg2 and @arg3 to be non-null
		if (@arg2 is not null and @arg3 is not null)
		begin
			raiserror 19304
			return 1
		end
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args "remove", "login", 
			@arg4, @arg5
		if (@retval != 0 ) 
			return @retval
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"remove", "login", "provider_name", @arg1
                if (@retval != 0 ) 
			return @retval
		exec sp_dropexternlogin @arg1, @arg2, @arg3
		return 0
	end

	if (@subcommand = 'subscription')
	begin
		--remove subscription subscription_name(@arg1)
                 
		-- Check for extra args
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args "remove", "subscription", 
			@arg2, @arg3, @arg4, @arg5
		if (@retval != 0 ) 
			return @retval

		-- Check for missing required args
                exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 
			"remove", "subscription", "subscription_name", @arg1
                if (@retval != 0 ) 
			return @retval

		-- Now remove the subscription
		begin transaction rem_subscription
		delete master.dbo.sysattributes
    	        where class=21 and attribute=1 and object_type='SU' and object_cinfo=@arg1

		/*
		** We need to save @@error and @@rowcount in ONE statement
		** because breaking it into two will change their values.
		*/
		select @error_occurred = @@error, @num_rows_deleted = @@rowcount

		/*  If the delete failed, we don't want to repeat it for HA  */
		if (@error_occurred != 0) 
		begin 
			rollback transaction rem_subscription	
			return 1
		end 	

		/*  If there was no subscription of that name, raise an exception */
		if (@num_rows_deleted = 0)
		begin
			rollback transaction rem_subscription	
			raiserror 19360, @arg1  -- "There is no subscription named '%1!' "
			return 1
		end


		if (attrib_notify(21, 1, "SU", NULL, NULL, NULL, NULL, NULL, NULL, 
			@arg1, NULL, NULL, NULL, 3) = 0)
		begin
			rollback transaction rem_subscription	
			return 1
		end

		/*  Perform the delete for HA */



		commit tran rem_subscription
		return 0
	end

	else
	begin
                exec @retval = sybsystemprocs.dbo.sp_rtm_help "remove"
                if (@retval != 0 ) 
			return @retval
	end
	set nocount off
	return 1


end
go	-- }

if (@@error != 0) 
		select syb_quit()
go

go

/*
** Messages for rtm_config
**   
** 19462, "Configuration value of jvmmaxthreads should not be less than 1. "
** 19463, "Configuration value of jvmmaxthreads '%1!' can not be less than the configuration value of jvmminthreads '%2!'. "
** 19464, "Configuration value of jvmminthreads should not be less than 0. "
** 19465, "Configuration value of jvmthreadtimeout should not be less than 0. "
** 19466, "The value '%1!' is error for '%2!' configuration. "
** 19474: "Illegal logging level '%1!' specified, legal logging level are off, all, debug, info, fatal, error, warn. "
*/

print "Installing procedures from rtm_config ..."
go

use sybsystemprocs
go

exec sp_rtm_recreate_proc sp_rtm_config
go

/*
**	*** Config procedures ***
** 
** Usage:  sp_msgadmin 'config', 'jvm' , jvm_parameter 
** Usage:  sp_msgadmin 'config', 'jvmlogging' , logging_level  
** Usage:  sp_msgadmin 'config', 'jvmlogfile' , logfilename  
** Usage:  sp_msgadmin 'config', 'jvmminthreads' , minthreads number  
** Usage:  sp_msgadmin 'config', 'jvmmaxthreads' , maxthreads number
** Usage:  sp_msgadmin 'config', 'jvmthreadtimeout' , threadtimeout number
** Usage:  sp_msgadmin 'config', 'jvmpropertyfile' , propertyfile pathname
** Usage:  sp_msgadmin 'config', 'ibmmq_keystore' , keystore_filepath
** 
** 
{
*/
create procedure sp_rtm_config(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
	declare @retval		int
	declare @error_occurred integer
	declare @num_rows_updated integer
	declare @notify		int 		--Should it notify JVM ?
	declare @option		int		--Which config we are in ?
	declare @intvalue	int		--Is the attribute int?
	declare @intval		int		--The int value.
	declare @int_temp	int



	/*
	** check if user has sso role, proc_role will also do auditing
	** if required. proc_role will also print error message if required.
	*/
	if (proc_role("sso_role") = 0)
	begin
		return (1)
 	end

	-- Check for extra args
           exec @retval = sybsystemprocs.dbo.sp_rtm_check_extra_args "config", "value", 
		@arg2, @arg3, @arg4, @arg5
	if (@retval != 0 ) 
		return @retval

	select @notify = 1
	select @intvalue = 0
	select @subcommand = lower(@subcommand)

	if (@subcommand = 'jvm')
	begin
		if (@arg1 is null)
		begin
			if not exists (select char_value from master.dbo.sysattributes
							where class =21
							and  attribute = 2
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, char_value)
					values (21, 2, 'SU', '-Xmx500m')
			end
			select "Config Value" = char_value from master.dbo.sysattributes
								where class =21
								and  attribute = 2
								and  object_type = "SU"
			return (0)
		end
		select @option = 2
		select @notify = 0
	end
	else if (@subcommand = 'jvmlogging')
	begin
		if (@arg1 is null)
		begin
			if not exists (select char_value from master.dbo.sysattributes
							where class =21
							and  attribute = 3
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, char_value)
					values (21, 3, 'SU', 'INFO')
			end
			select "Config Value" = char_value from master.dbo.sysattributes
								where class =21
								and  attribute = 3
								and  object_type = "SU"

			return (0)
		end
		/* Lowercase and check for legal values. */
		select @arg1 = lower(@arg1)
		if not ( @arg1 in ('off', 'all', 'debug', 'info', 'fatal', 'error', 'warn' ) )
		begin
			raiserror 19474, @arg1
			return (1)
		end
		select @option = 3
	end
	else if (@subcommand = 'jvmlogfile')
	begin
		if (@arg1 is null)
		begin
			if not exists (select char_value from master.dbo.sysattributes
							where class =21
							and  attribute = 4
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, char_value)
					values (21, 4, 'SU', '$SYBASE/$SYBASE_ASE/lib/jrtms.log')
			end
			select "Config Value" = char_value from master.dbo.sysattributes
								where class =21
								and  attribute = 4
								and  object_type = "SU"

			return (0)
		end
		select @option = 4
	end
	else if (@subcommand = 'jvmpropertyfile')
	begin
		if (@arg1 is null)
		begin
			if not exists (select char_value from master.dbo.sysattributes
							where class =21
							and  attribute = 5
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, char_value)
					values (21, 5, 'SU', '$SYBASE/$SYBASE_ASE/lib/rtms.properties')
			end
			select "Config Value" = char_value from master.dbo.sysattributes
								where class =21
								and  attribute = 5
								and  object_type = "SU"

			return (0)
		end
		select @option = 5
	end
	else if (@subcommand = 'jvmmaxthreads')
	begin
		if (@arg1 is null)
		begin
			if not exists (select int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 6
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, int_value)
					values (21, 6, 'SU', 10)
			end
			select "Config Value" = int_value from master.dbo.sysattributes
								where class =21
								and  attribute = 6
								and  object_type = "SU"

			return (0)
		end
		select @option = 6
		select @intvalue = 1
		if (patindex('%[^0-9]%', @arg1) > 0)
		begin
			raiserror 19462
			return (1)
		end
		select @intval = convert(int, @arg1)
		if not exists (select int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 7
							and  object_type = "SU")
		begin
			insert master.dbo.sysattributes
				(class, attribute, object_type, int_value)
				values (21, 7, 'SU', 0)
		end
		select @int_temp = int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 7
							and  object_type = "SU"
		if ( @intval < 1)
		begin
			raiserror 19462
			return (1)
		end

		if ( @int_temp > @intval)
		begin
			raiserror 19463, @intval, @int_temp
			return (1)
		end
	end
	else if (@subcommand = 'jvmminthreads')
	begin
		if (@arg1 is null)
		begin
			if not exists (select int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 7
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, int_value)
					values (21, 7, 'SU', 0)
			end
			select "Config Value" = int_value from master.dbo.sysattributes
								where class =21
								and  attribute = 7
								and  object_type = "SU"

			return (0)
		end
		select @option = 7
		select @intvalue = 1
		if (patindex('%[^0-9]%', @arg1) > 0)
		begin
			raiserror 19464
			return (1)
		end
		select @intval = convert(int, @arg1)
		if not exists (select int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 6
							and  object_type = "SU")
		begin
			insert master.dbo.sysattributes
				(class, attribute, object_type, int_value)
				values (21, 6, 'SU', 10)
		end
		select @int_temp = int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 6
							and  object_type = "SU"

		if ( @int_temp < @intval )
		begin
			raiserror 19463, @int_temp, @intval
			return (1)
		end
	end
	else if (@subcommand = 'jvmthreadtimeout')
	begin
		if (@arg1 is null)
		begin
			if not exists (select int_value from master.dbo.sysattributes
							where class =21
							and  attribute = 8
							and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, int_value)
					values (21, 8, 'SU', 600)
			end
			select "Config Value" = int_value from master.dbo.sysattributes
								where class =21
								and  attribute = 8
								and  object_type = "SU"

			return (0)
		end
		select @option = 8
		select @intvalue = 1
		if (patindex('%[^0-9]%', @arg1) > 0)
		begin
			raiserror 19465
			return (1)
		end
		select @intval = convert(int, @arg1)
	end
	else if (@subcommand = 'ibmmq_keystore')
	begin
		if (@arg1 is null)
		begin
			if not exists (select char_value 
				from master.dbo.sysattributes
				where class =21
				and  attribute = 9
				and  object_type = "SU")
			begin
				insert master.dbo.sysattributes
				(class, attribute, object_type, char_value)
				values (21, 9, 'SU', '')
			end
			select "Config Value" = char_value 
				from master.dbo.sysattributes
				where class =21
				and  attribute =9
				and  object_type = "SU"
			return (0)
		end
		select @option = 9
		select @notify = 0
        end
	else
		select @option = 0

	if (@option = 0)
	begin
                exec @retval = sybsystemprocs.dbo.sp_rtm_help "config"
		return @retval
	end
	else
	begin
		--setting config value(@arg1)

		begin transaction rtms_config
		if (@intvalue = 1)
		begin
			if attrib_valid(21, @option, 'SU', NULL, NULL, NULL, NULL, NULL, @intvalue, NULL, NULL, NULL, NULL, 2) = 0
			begin
				rollback transaction rtms_config
				raiserror 19466, @intvalue, @option
				return 1
			end
		end
		else
		begin
			if attrib_valid(21, @option, 'SU', NULL, NULL, NULL, NULL, NULL, NULL, @arg1, NULL, NULL, NULL, 2) = 0
			begin
				rollback transaction rtms_config
				raiserror 19466, @arg1, @option
				return 1
			end
		end

		if not exists (select * from master.dbo.sysattributes 
					 where class =21
					  and  attribute = @option
					  and  object_type = "SU")
		begin
			if (@intvalue = 1)
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, int_value)
					values (21, @option, 'SU', @intval)
			end
			else
			begin
				insert master.dbo.sysattributes
					(class, attribute, object_type, char_value)
					values (21, @option, 'SU', @arg1)
			end
		end
		else
		begin
			if (@intvalue = 1)
			begin
				update master.dbo.sysattributes 
					set int_value = @intval
					where class = 21
					 and  attribute = @option
					 and  object_type = "SU"
			end
			else
			begin
				update master.dbo.sysattributes 
					set char_value = @arg1
					where class = 21
					 and  attribute = @option
					 and  object_type = "SU"
			end
		end
		/*
		** We need to save @@error and @@rowcount in ONE statement
		** because breaking it into two will change their values.
		*/
		select @error_occurred = @@error, @num_rows_updated = @@rowcount

		/*  If the delete failed, we don't want to repeat it for HA  */
		if (@error_occurred != 0) 
		begin 
			rollback transaction rtms_config
			return 1
		end 	

		/*  If there was no subscription of that name, raise an exception */
		if (@num_rows_updated = 0)
		begin
			rollback transaction rtms_config
			return 1
		end
          
		/*  Perform the delete for HA */


		if (@notify = 1)
		begin
			if (@intvalue = 1)
			begin
				if (attrib_notify(21, @option, "SU", NULL, NULL, NULL, NULL, NULL, @intval, NULL, NULL, NULL, NULL, 2) = 0)
				begin
					rollback transaction rtms_config
					raiserror 19466, @intvalue, @option
					return 1
				end
			end
			else
			begin
				if (attrib_notify(21, @option, "SU", NULL, NULL, NULL, NULL, NULL, NULL, @arg1, NULL, NULL, NULL, 2) = 0)
				begin
					rollback transaction rtms_config
					raiserror 19466, @arg1, @option
					return 1
				end
			end
		end

		commit tran rtms_config
		return 0
	end

	set nocount off
	return 1
end
go	-- }

if (@@error != 0) 
		select syb_quit()
go



go
/*
** Messages for rtm_show
** 
** 
*/

print "Installing procedures from rtm_show ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_rtm_show
go

/*
**	*** Default procedures ***
**  Usage:  sp_msgadmin 'default', 'login' , provider_name, provider_login , provider_password 
**
*/
create procedure sp_rtm_show(
			@subcommand	varchar(30) 	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL


) as
begin
        declare @retval int

	/*
	** check if user has sso role, proc_role will also do auditing
	** if required. proc_role will also print error message if required.
	*/
	if (proc_role("sso_role") = 0)
		return (1)

	select @subcommand = lower (@subcommand)

	if (@subcommand = 'queues')
	begin
		
		exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 'show', 'queues', 'provider_name', @arg1
		if (@retval != 0 ) return @retval
		
		if(@arg2 != NULL)
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"queues", OPTION @arg2, returns TEXT )
			columns  Name varchar(48) path 'QName',Type varchar(20) path 'QType') as Queues
		end
		else
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"queues", returns TEXT )
			columns  Name varchar(48) path 'QName',Type varchar(20) path 'QType') as Queues
		end
	end
	else
	if (@subcommand = 'channels')
	begin
		exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 'show', 'channels', 'provider_name', @arg1
		if (@retval != 0 ) return @retval
				
		if(@arg2 != NULL)
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"channels", OPTION @arg2, returns TEXT )
			columns  Name varchar(48) path 'ChannelName',Type varchar(20) path 'ChannelType') as Channels
		end
		else
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"channels", returns TEXT )
			columns  Name varchar(48) path 'ChannelName',Type varchar(20) path 'ChannelType') as Channels
		end
	end
	else
	if (@subcommand = 'qmgr')
	begin
		exec @retval = sybsystemprocs.dbo.sp_rtm_check_required_arg 'show', 'qmgr', 'provider_name', @arg1
		if (@retval != 0 ) return @retval
		
		if(@arg2 != NULL)
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"qmgr",  OPTION @arg2, returns TEXT )
			columns  Name varchar(48) path 'QMgrName') as QueueManager
		end
		else
		begin
			select * from xmltable('/MQAIResultSet/MQAIRow' passing
			msginq(@arg1+"?alter_user=yes" ,"qmgr", returns TEXT )
			columns  Name varchar(48) path 'QMgrName') as QueueManager
		end
	end
	else
	begin
		exec @retval = sybsystemprocs.dbo.sp_rtm_help 'show'
                if (@retval != 0 )
                        return @retval
	end
	return (0)
end
go

if (@@error != 0) 
		select syb_quit()
go

go

	
/*
** Messages for rtm_msgadmin
**   
** 17260, "Can't run %1! from within a transaction. "
** 19293, "Unrecognized command for 'sp_msgadmin, %1! %2!' call "
** 19302, "Cannot run this command because real time messaging is not enabled.
**         A user with System Administrator (SA) role must reconfigure the system to enable 
**         real time messaging."
** 19529, "Cannot open database '%1!'. Check the availability of this database and 
**         retry the installation."
*/

print "Installing procedures from rtm_msgadmin ..."
go

use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go

exec sp_rtm_recreate_proc sp_msgadmin
go

/*
** ***************************************************************************
** sp_msgadmin
**
**	Top-level user-interface procedure for real-time messaging sp_admin procedure.
**
**	This procedure is the command-control for the entire interface.
**	It receives the command from the user, parses it, and then calls other
**	sub-procs to execute on each command.
**
** Returns:
**	0	- All checks are ok. Execution is ok.
**	1	- Some errors (arguments, usage) or permission(s) issues.
**
**
** Usage:  sp_msgadmin 'list', 'provider' [, provider_name] 
** Usage:  sp_msgadmin 'list', 'login' [, provider_name [,login_name]] 
**  
** Usage:  sp_msgadmin 'register', 'provider' , provider_name, provider_class, 
**                     messaging_provider_URL 
** Usage:  sp_msgadmin 'register', 'login' , provider_name, local_login , provider_login, 
**                     provider_password [,role_name] 
** Usage:  sp_msgadmin 'register', 'subscription' , subscription_name, 
**                     endpoint, filter [, durable_name, client_id]   
** 
** Usage:  sp_msgadmin 'default', 'provider' , provider_name 
** Usage:  sp_msgadmin 'default', 'login' , provider_name, provider_login , provider_password 
**  
** Usage:  sp_msgadmin 'remove', 'provider' , provider_name 
** Usage:  sp_msgadmin 'remove', 'login' , provider_name , local_login  
** Usage:  sp_msgadmin 'remove', 'subscription' , subscription_name   
** Usage:  sp_msgadmin 'config', 'jvm' , jvm_parameter
** Usage:  sp_msgadmin 'config', 'jvmlogging' , logging_level
** Usage:  sp_msgadmin 'config', 'jvmlogfile' , logfilename
** Usage:  sp_msgadmin 'config', 'jvmminthreads' , minthreads number
** Usage:  sp_msgadmin 'config', 'jvmmaxthreads' , maxthreads number
** Usage:  sp_msgadmin 'config', 'jvmthreadtimeout' , threadtimeout number
** Usage:  sp_msgadmin 'config', 'jvmpropertyfile' , propertyfile pathname
** 
**
*/
create procedure sp_msgadmin (
			@command	varchar(30)	= NULL
		      , @subcommand	varchar(30)	= NULL
		      , @name		varchar(256)	= NULL
		      , @arg1		varchar(256)	= NULL
		      , @arg2		varchar(256)	= NULL
		      , @arg3		varchar(256)	= NULL
		      , @arg4		varchar(256)	= NULL
		      , @arg5		varchar(256)	= NULL
		      , @arg6		varchar(256)	= NULL
)
as
begin
	declare @retval		int	-- From called sub-procedure.
	      , @cmdcode	int	-- Ordinal # for command
	      , @whoami		varchar(255)
	      , @currdbname	varchar(256)

	-- Raise exception if real time messaging not enabled
	if ( @@instanceid IS NULL )
	begin 
		if ( 0 = (select value from master.dbo.syscurconfigs 
							where config=429) )
		begin
			raiserror 19302
			return 1
		end
	end


	select @retval = 1	-- Expect failure.
		-- Simple name, as it's used for error msgs etc.
		, @whoami = object_name(@@procid, db_id('sybsystemprocs'))

	if (@@trancount > 0)
	begin
		-- Cannot run this sproc from a transaction.
		raiserror 17260, @whoami
		return 1
	end

	set transaction isolation level 1
	set chained off
	set nocount on

	-- Save the user's current db, as we need that to determine whether
	-- user has DBO privileges. Most sprocs are executed using 
	-- sybsystemprocs.dbo. prefix to ensure that we are running the
	-- right version of the sproc that this feature ships with.
	--
	select @currdbname = db_name()

	-- Allow non-sa users (or even non-dbo users) to run without any
	-- arguments, at least so that the dbo can get some help info before
	-- working on a particular command. (It is difficult to further restrict
	-- this to regular users when we have absolutely no arguments.)
	-- Remaining permissions checks will be done shortly hereafter.
	--
	if (@command IS NULL)
	begin
		exec @retval = sybsystemprocs.dbo.sp_rtm_help @subcommand
		return 0
	end


	/*
	** ****************************************************************
	** No top-level permissions checks are being done in this procedure
	** as the permissions vary depending on the command being run and
	** granularity of the operation. Permission checking will be
	** implemented by each sub-command's procedure.
	** ****************************************************************
	*/
	if (@command = 'list')
	begin
           exec @retval = sybsystemprocs.dbo.sp_rtm_list @subcommand, @name, @arg1, @arg2, @arg3, @arg4
	end


	else if (@command = 'register')
	begin
           exec @retval = sybsystemprocs.dbo.sp_rtm_register @subcommand, 
				@name, @arg1, @arg2, @arg3, @arg4, @arg5, @arg6
	end

	else if (@command = 'default')	
	begin
           exec @retval = sybsystemprocs.dbo.sp_rtm_default @subcommand, 
				@name, @arg1, @arg2, @arg3, @arg4
	end

	else if (@command = 'remove')	
	begin

           exec @retval = sybsystemprocs.dbo.sp_rtm_remove @subcommand, 
				@name, @arg1, @arg2, @arg3, @arg4
	end

	else if (@command = 'config')	
	begin

           exec @retval = sybsystemprocs.dbo.sp_rtm_config @subcommand, 
				@name, @arg1, @arg2, @arg3, @arg4
	end

	else if (@command = 'show')	
	begin

           exec @retval = sybsystemprocs.dbo.sp_rtm_show @subcommand, @name, @arg1

	end

	else if (@command = 'help')
	begin
	   exec @retval = sybsystemprocs.dbo.sp_rtm_help @subcommand, @name, @arg1, @arg2, @arg3, @arg4
	end

	else

	begin
		--  Unrecognized command
		raiserror 19293, @command, @subcommand
		return 1
	end
	set nocount off
	return @retval
end


go


if (@@error != 0) 
	select syb_quit()
go

go
exec sp_procxmode 'sp_msgadmin', 'anymode'
go
grant execute on sp_msgadmin to public
go

use master
go

sp_configure "allow updates", 0
go

set proc_return_status ON
go

set nocount off
go

print "Loading of Real Time messaging procedures is complete."
go


declare @retval int
exec @retval = sp_version 'installmsgsvss', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'end'
if (@retval != 0) select syb_quit()
go

