/*
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 'installpcidb', 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 "'installpcidb' 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 "'installpcidb' cannot continue, because 'master' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybpcidb') = 0)
begin
	print "'installpcidb' cannot continue, because 'sybpcidb' database is not upgraded to correct version."
	select @do_quit = 1
end
if (@do_quit = 1)
	select syb_quit()
go

-- Header for PCI_DB
use master
go
sp_dboption sybpcidb, 'trunc log', 'true'
go
use sybpcidb
go
checkpoint
go
-- END PCI_DB Header

exec sp_drop_object 'sp_ext_loader_insert_trigger', 'procedure'
go

exec sp_drop_object 'sp_workdir_insert_trigger', 'procedure'
go

exec sp_drop_object 'sp_generic_insert_trigger', 'procedure'
go

exec sp_drop_object 'sp_ext_loader_update_trigger', 'procedure'
go

exec sp_drop_object 'sp_workdir_update_trigger', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

use master
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_ext_loader_insert_trigger')
begin
	drop procedure sp_ext_loader_insert_trigger
end
go
print "Installing sp_ext_loader_insert_trigger"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_workdir_insert_trigger')
begin
	drop procedure sp_workdir_insert_trigger
end
go
print "Installing sp_workdir_insert_trigger"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_generic_insert_trigger')
begin
	drop procedure sp_generic_insert_trigger
end
go
print "Installing sp_generic_insert_trigger"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_ext_loader_update_trigger')
begin
	drop procedure sp_ext_loader_update_trigger
end
go
print "Installing sp_ext_loader_update_trigger"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_workdir_update_trigger')
begin
	drop procedure sp_workdir_update_trigger
end
go
print "Installing sp_workdir_update_trigger"
go


/*
** WARNING WARNING !!!
**
** This file contains table definitions in create table statements that
** match table structures in these files:
** 	$DMBS/generic/pci/bridge/pcidb.c
** 	$DMBS/generic/pci/include/pcidb.h
** When updating the create table statements in this file make sure
** that the table structures in pcidb.c and pcidb.h are also updated
** to match your changes.
*/

use sybpcidb
go

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

set nocount on
go

/*
** Create Table: pci_slotinfo
*/
if exists (select 1 from sysobjects where name = 'pci_slotinfo')
begin
	drop table pci_slotinfo
end
go

if not exists (select 1 from sysobjects where name = 'pci_slotinfo')
begin
	exec ("create table pci_slotinfo
	(
	slot_number			int			not null,
	slot_name			varchar(255)		not null,
	slot_pca_directives_table_name	varchar(255)		not null,
	slot_pca_arguments_table_name	varchar(255)		not null,
	slot_status			int	default 0	not null
	)")
end
go

if exists (select 1 from sysobjects where name = 'pci_slotinfo')
begin
	create unique    clustered index idx_slot_name   on pci_slotinfo(slot_name)
	create unique nonclustered index idx_slot_number on pci_slotinfo(slot_number)
end
go

grant all on pci_slotinfo to sa_role
go

/*
** Create Table: pci_slot_syscalls
*/
if exists (select 1 from sysobjects where name = 'pci_slot_syscalls')
begin
	drop table pci_slot_syscalls
end
go

if not exists (select 1 from sysobjects where name = "pci_slot_syscalls")
begin
	exec ("create table pci_slot_syscalls
	(
	syscall_slot_number	int			not null,
	syscall_system_call	varchar(255)		not null,
	syscall_dispatch_name	varchar(255)		not null,
	syscall_enabled		int			not null,
	syscall_status		int	default 0	not null

	constraint check_syscalls_enabled check (syscall_enabled in (0, 1))
	)")
end
go

if exists (select 1 from sysobjects where name = "pci_slot_syscalls")
begin
	create unique clustered index idx_slot_syscalls on pci_slot_syscalls(syscall_slot_number, syscall_system_call)
end
go

grant all on pci_slot_syscalls to sa_role
go

/*
** Populate Table: pci_slotinfo
**
** RESOLVE - resolution 467130-5 has been opened to generate these insert statements
** 	from information in a header file instead of hard coded here
*/
if exists (select 1 from sysobjects where name = "pci_slotinfo")
begin
	insert into pci_slotinfo values (1,
			'JVM',
			'pca_jre_directives',
			'pca_jre_arguments',
			0)
end
go

/*
** Create Table: pci_directives
*/
if exists (select 1 from sysobjects where name = "pci_directives")
begin
	drop table pci_directives
end
go

if not exists (select 1 from sysobjects where name = "pci_directives")
begin
	exec ("create table pci_directives
	(
	pci_directives_index		int				not null,
	pci_directives_name		varchar(255)			not null,
	pci_directives_description	varchar(255)			not null,
	pci_directives_enabled		int		default 1	not null,
	pci_directives_status		int		default 0	not null

	constraint check_pci_directives_enabled check (pci_directives_enabled in (0, 1))
	)")
end
go

if exists (select 1 from sysobjects where name = "pci_directives")
begin
	create unique    clustered index idx_pci_directives_name  on pci_directives(pci_directives_name)
	create unique nonclustered index idx_pci_directives_index on pci_directives(pci_directives_index)
end
go

grant all on pci_directives to sa_role
go

/*
** Create Table: pci_arguments
*/
if exists (select 1 from sysobjects where name = "pci_arguments")
begin
	drop table pci_arguments
end
go

if not exists (select 1 from sysobjects where name = "pci_arguments")
begin
	exec ("create table pci_arguments
	(
	pci_args_directive_index	int		not null,
	pci_args_name			varchar(255)	not null,
	pci_args_units			varchar(255)	not null,
	pci_args_number_value		int		not null,
	pci_args_string_value		varchar(255)	not null,
	pci_args_description		varchar(255)	not null,
	pci_args_enabled		int		not null,
	pci_args_status			int		not null

	constraint check_pci_args_units check (pci_args_units in ('switch', 'string', 'number')),
	constraint check_pci_args_enabled check (pci_args_enabled in (0, 1))
	)")
end
go

if exists (select 1 from sysobjects where name = "pci_arguments")
begin
	create unique clustered index idx_pci_arguments on pci_arguments(pci_args_directive_index, pci_args_name)
end
go

grant all on pci_arguments to sa_role
go


/*
** Populate Table: pci_directives
**
** RESOLVE - resolution 467130-5 has been opened to generate these insert statements
** 	from information in a header file instead of hard coded here
*/
if exists (select 1 from sysobjects where name = "pci_directives")
begin
	insert into pci_directives values (
		0,
		'PCI_BRIDGE_X_OPT',	
		'PCI Bridge configuration parameters',
		1,
		0)
	insert into pci_directives values (
		1,
		'PCI_BRIDGE_LOGOPT',	
		'Plug-in diagserver report facility',
		1,
		0)
	insert into pci_directives values (
		2,
		'PCI_BRIDGE_INSTR',	
		'PCI Bridge Instrumentation settings',
		1,
		0)
end
go

/*
** Populate Table: pci_arguments
**
** RESOLVE - resolution 467130-5 has been opened to generate these insert statements
** 	from information in a header file instead of hard coded here
**
** The pci_xopt_maxthread is set to 1056 while the default is 1024. This is done
** to force the reconfiguration of the slotring being tested on boot. We need
** to put this back to the default before we deliver (reneq)
*/
if exists (select 1 from sysobjects where name = "pci_arguments")
begin
	insert into pci_arguments values ( 0,
			'pci_xopt_maxthreads',
			'number',
			1056,
			'',
			'Maximum PCI Bridge PLB controlled threads',
			1,
			0)
	insert into pci_arguments values ( 0,
			'pci_xopt_runtime_alloc_escape',
			'number',
			1,
			'',
			'Allow run-time escapes on memory allocation requests above PCI Bridge max memory alloc unit',
			1,
			0)
	insert into pci_arguments values ( 0,
			'pci_xopt_slotring_cycle',
			'number',
			-1,
			'',
			'Disable PCI Bridge slotring washing',
			1,
			0)
	insert into pci_arguments values ( 0,
			'pci_xopt_slotring_wash_th',
			'number',
			76,
			'',
			'Default PCI Bridge slot-ring washing treshold percentage',
			1,
			0)
	insert into pci_arguments values ( 0,
			'pci_xopt_failover_engine',
			'number',
			-1,
			'',
			'Default engine a slot should failover to',
			1,
			0)
	insert into pci_arguments values ( 0,
			'pci_xopt_event_scheduling',
			'number',
			0,
			'',
			'Default PCI Bridge scheduling to',
			1,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_pci',
			'switch',
			0,
			'',
			'Generic PCI Bridge logging (probe [pci/pca])',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_runtime',
			'switch',
			0,
			'',
			'PCI Bridge run-time dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_jvm',
			'switch',
			0,
			'',
			'PCI Bridge JVM dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_asehi',
			'switch',
			0,
			'',
			'PCI Bridge ASE Host Interface dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_jst',
			'switch',
			0,
			'',
			'PCI Bridge Job Scheduler Task dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_omni',
			'switch',
			0,
			'',
			'PCI Bridge OMNI dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 1,
			'pci_logopt_xml',
			'switch',
			0,
			'',
			'PCI Bridge XML dispatch logging',
			0,
			0)
	insert into pci_arguments values ( 2,
			'BRIDGE',
			'number',
			1,
			'',
			'Force full instrumentation (noisy)',
			0,
			0)
	insert into pci_arguments values ( 2,
			'SYNC',
			'number',
			1,
			'',
			'Force all SYNChronization code to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'PLB',
			'number',
			1,
			'',
			'Force all PLB code to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'TPM',
			'number',
			1,
			'',
			'Force all TPM code to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'CELL',
			'number',
			1,
			'',
			'Force all CELL synchronization to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'PCIS',
			'number',
			1,
			'',
			'Force all PCI Service code to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'SLOTRING',
			'number',
			1,
			'',
			'Force all "slot-ring" code to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'JVMJNI',
			'number',
			1,
			'',
			'Force JVM JNI external entries to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'JVMHOST',
			'number',
			1,
			'',
			'Force ASE JVM Host API entries to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'JCS',
			'number',
			1,
			'',
			'Force JCS entries to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'JDBC',
			'number',
			1,
			'',
			'Force JDBC entries to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'JAVA',
			'number',
			1,
			'',
			'Force all Java related entries to Report',
			0,
			0)
	insert into pci_arguments values ( 2,
			'fetch_classdata',
			'number',
			1,
			'',
			'Force all fetch_classdata hits to Report',
			1,
			0)
	insert into pci_arguments values ( 2,
			'pcis_service',
			'number',
			2,
			'',
			'Force all pcis_service hits to Freeze',
			0,
			0)
end
go

/*
** Create Table: pca_jre_directives
*/
if exists (select 1 from sysobjects where name = "pca_jre_directives")
begin
	drop table pca_jre_directives
end
go

if not exists (select 1 from sysobjects where name = "pca_jre_directives")
begin
	exec ("create table pca_jre_directives
	(
	jre_directives_index		int				not null,
	jre_directives_name		varchar(255)			not null,
	jre_directives_description	varchar(255)			not null,
	jre_directives_enabled		int				not null,
	jre_directives_status		int		default 0	not null

	constraint check_jre_directives_enabled check (jre_directives_enabled in (0, 1))
	)")
end

if exists (select 1 from sysobjects where name = "pca_jre_directives")
begin
	create unique    clustered index idx_jre_directives_name  on pca_jre_directives(jre_directives_name)
	create unique nonclustered index idx_jre_directives_index on pca_jre_directives(jre_directives_index)
end
go

grant all on pca_jre_directives to sa_role
go

/*
** Populate Table: pca_jre_directives
**
** RESOLVE - resolution 467130-5 has been opened to generate these insert statements
** 	from information in a header file instead of hard coded here
*/
if exists (select 1 from sysobjects where name = "pca_jre_directives")
begin
	insert into pca_jre_directives values ( 0,
		'PCA_JVM_OPT',
		'PCA/JVM Configuration Options',
		1 ,
		0)
	insert into pca_jre_directives values ( 1,
		'PCA_JVM_DIR_OPTIONS',
		'PCA/VM - Configure the directory definitions used by the JVM',
		1 ,
		0)
	insert into pca_jre_directives values ( 2,
		'PCA_JVM_WORK_DIR',
		'PCA/VM - Configure the trusted directories for the JVM',
		1 ,
		0)
	insert into pca_jre_directives values ( 3,
		'PCA_JVM_SYS_DEVICE_PATH',
		'System Device directories - specified per platform and not required by all platforms',
		1 ,
		0)
	insert into pca_jre_directives values ( 4,
		'PCA_JVM_MIN_JNI_VERSION',
		'Minimum backward compatible JNI version',
		1 ,
		0)
	insert into pca_jre_directives values ( 5,
		'PCA_JVM_MODULE_PATH',
		'extends $SYBASE/$SYBASE_JAVA pointing to the Java VM Module',
		1 ,
		0)
	insert into pca_jre_directives values ( 6,
		'PCA_JVM_LOGGING',
		'JRE/VM Logging options',
		1 ,
		0)
	insert into pca_jre_directives values ( 7,
		'PCA_JVM_EXT_CLASS_LOADER',
		'Configuration for Global and Database Extension Class Loaders',
		1 ,
		0)
	insert into pca_jre_directives values ( 8,
		'PCA_JVM_JAVA_OPTIONS',
		'Java options - includes all Java Startup options including any -D, -X and -XX options',
		1 ,
		0)
	insert into pca_jre_directives values ( 9,
		'PCA_JVM_JAVA_DBG_AGENT_PORT',
		'Java VM Debug Agent Port Number (DEBUG only)',
		1 ,
		0)
end
go

/*
** Create Table: pca_jre_arguments
*/
if exists (select 1 from sysobjects where name = "pca_jre_arguments")
begin
	drop table pca_jre_arguments
end
go

if not exists (select 1 from sysobjects where name = "pca_jre_arguments")
begin
	exec ("create table pca_jre_arguments
	(
	jre_args_directive_index	int				not null,
	jre_args_name			varchar(255)			not null,
	jre_args_units			varchar(255)			not null,
	jre_args_number_value		int				not null,
	jre_args_string_value		varchar(255)			not null,
	jre_args_description		varchar(255)			not null,
	jre_args_enabled		int				not null,
	jre_args_status			int		default 0	not null

	constraint check_jre_args_units check (jre_args_units in ('switch', 'string', 'number', 'array')),
	constraint check_jre_args_enabled check (jre_args_enabled in (0, 1))
	)")
end
go

if exists (select 1 from sysobjects where name = "pca_jre_arguments")
begin
	create unique clustered index idx_jre_arguments
		on pca_jre_arguments(jre_args_directive_index, jre_args_name, jre_args_string_value)
end
go

grant all on pca_jre_arguments to sa_role
go

/*
** Stored Procedure:  sp_ext_loader_insert_trigger
** 	This stored procedure is called by a trigger for inserts into the pca_jre_arguments table
** 	specifically for arguments that are one of the two Extension Class Loader properties:
** 		pca_jvm_ext_class_loader_global
** 		pca_jvm_ext_class_loader_dbase
**
** Purpose: The purpose of this trigger is to validate inserted values that configure the 
** Extension Class Loaders only. No other configuration properties need this type of validation.
**
** This trigger will be called whether a row is inserted with the stored procedure sp_jreconfig or with
** manual 'insert into' statements. Either way we need to ensure that a few rules are followed for any
** new rows coming into the table that configure the Global Extension Class Loader.
**
** Rules:
** 	1. For the Global type of Extension Class Loader only---
** 	   one database can be configured but it can have as many Jars as necessary
** 		We can have all of these:
** 			'db1:jar1'
** 			'db1:jar2'
** 			'db1:jar3'
** 		But we cannot also have this: (because it uses a different database 'db2')
** 			'db2:jar4'
** 	   for the Database type of Extension Class Loaders it is ok to have as many different databases
** 	   as needed and this particular rule does not apply.
**
** 	2. If the supplied string contains whitespace, we need to trim it (tabs and spaces are removed)
** 		'db1   :    jar2' -> 'db1:jar2'
**
** 	3. If the database is configured to include ALL of the Jars (using 'db1:*') then it must be the only row
** 		that is configured for that database. It is not allowed to have both 'db1:*' and 'db1:jar1'. 
** 		Either we configure each Jar separately, or we configure for all of Jars registered to a database.
** 		So, if 'db1:*' is already in the table, we don't allow 'db1:jar1', and conversly, if 'db1:jar1'
** 		is already in the table, we don't allow 'db1:*' to be added either.
**
** 	4. string validation is also done to ensure the following:
** 		- no commas appear in the string - (this is invalid: 'db1:jar1,jar2,jar3')
** 		- only one colon can appear which separates the database and jar ('db1:jar1')
** 		- both sides of the colon have at least 1 char (ie, we have both a database name and a jar name)
**
**
** By doing this validation in a trigger, the stored procedure sp_jreconfig does not need to worry about invalid inserts that 
** are done outside of the stored procedure (using 'insert into' statements for example), and therefore does not have to do
** this type of validation.
*/
if exists (select 1 from sysobjects where name = 'sp_ext_loader_insert_trigger')
	drop proc sp_ext_loader_insert_trigger
go

create procedure sp_ext_loader_insert_trigger
	@arg_name   varchar(255) = NULL,
	@new_stringval varchar(255) = NULL
as
begin
	declare @dbname varchar(255),
		@jarname varchar(255),
		@notab_str varchar(255),
		@trimmed_val varchar(255),
		@tempstr varchar(255),
		@count int,
		@dblen int,
		@jarlen int

	/*
	** Make sure we have at least one ':' character
	*/
	if (charindex(':', @new_stringval) = 0)
	begin
		return (1)
	end
	/*
	** Make sure we do not have any comma ',' characters
	*/
	if (charindex(',', @new_stringval) != 0)
	begin
		return (1)
	end

	/*
	** remove tabs and then trim spaces and split up the string into dbname and jarname parts
	** and also create @trimmed_val which is a trimmed up version of the original string
	*/
	select @notab_str = str_replace(str_replace(str_replace(@new_stringval, char(10), ''), char(13), ''), char(9), '')
	select @dbname  = rtrim(ltrim(substring(@notab_str, 1, charindex(':', @notab_str) - 1)))
	select @jarname = rtrim(ltrim(substring(@notab_str, charindex(':', @notab_str) + 1, len(@notab_str) - charindex(':', @notab_str))))
	select @dblen = len(@dbname)
	select @jarlen = len(@jarname)
	select @trimmed_val = @dbname + ':' + @jarname
	/*
	** Check for a zero-length database name, like: ':jar1'
	*/
	if (@dblen IS NULL)
	begin
		return (1)
	end
	/*
	** Check for a zero-length Jar name, like: 'db1:'
	*/
	if (@jarlen IS NULL)
	begin
		return (1)
	end

	/*
	** Check for colon chars ':' in the jar name, like 'd1:jar1:jar2'
	*/
	if (charindex(':', @jarname) != 0)
	begin
		return (1)
	end
	/*
	** If the supplied value has a '*' for Jar Name and
	** there are already other rows in the table, we report an error
	** and return (1) to rollback the transaction
	*/
	if (@jarname = '*') and
		(select count(*)
			from pca_jre_arguments
			where jre_args_name = @arg_name and
				jre_args_enabled = 1 and
				(substring(jre_args_string_value, 1, charindex(':', jre_args_string_value)) = 
				(substring(@new_stringval, 1, charindex(':', @new_stringval)))) and
				jre_args_string_value != @new_stringval) > 0
	begin
		return (1)
	end
	/*
	** If there is already a wildcard entry (db1:*) and we are trying
	** to insert another row, we report that this is not allowed and
	** return (1) to rollback the transaction
	*/
	else if (@jarname != "*") and
		(select count(*)
		from pca_jre_arguments
			where jre_args_name = @arg_name and
				jre_args_enabled = 1 and
				jre_args_string_value = @dbname + ':*') > 0
	begin
		return (1)
	end
	/*
	** If the supplied value has tabs and/or spaces in it and 
	** the trimmed value is not in the table,
	** we can safely update the new row to remove the tabs and spaces
	*/
	else if (@new_stringval != @trimmed_val) and
		(select count(*)
		from pca_jre_arguments
			where jre_args_name like "pca_jvm_ext_class_loader" + "%" and
				jre_args_string_value = @trimmed_val) = 0
	begin
		update pca_jre_arguments
			set jre_args_string_value = @trimmed_val
				where jre_args_string_value = @new_stringval
		if (@@error != 0) and (@@transtate > 1)
		begin
			return (1)
		end
	end
	/*
	** If the trimmed value is already in the table, we cannot do the 
	** update and instead we report a duplicate and return 1 to rollback
	** the transaction
	** This will happen if the user first inserts 'db1:jar1' and then later
	** adds 'db1   :   jar1' which are initially different but ultimately
	** a duplicate. This check here has determined that 'db1:jar1' is already
	** in the table, so we cannot update the new row since the version without
	** spaces and/or tabs is already there as well.
	*/
	else if (@new_stringval != @trimmed_val)
	begin
		return (1)
	end

	/*
	** Global Extension Class Loader only -----
	** If the new value uses a different database name than the existing
	** rows, we need to report the mistmatch and return (1) to rollback
	** the transaction
	*/
	else if (select count(distinct substring(t1.jre_args_string_value, 1, charindex(':', t1.jre_args_string_value)))
		from pca_jre_arguments t1, pca_jre_arguments t2
			where t1.jre_args_name = 'pca_jvm_ext_class_loader_global' and
				(substring(t1.jre_args_string_value, 1, charindex(':', t1.jre_args_string_value)) !=
				substring(t2.jre_args_string_value, 1, charindex(':', t2.jre_args_string_value)))) > 1
	begin
		return (1)
	end

	if exists (select name from master..sysdatabases where name = @dbname)
	begin
		if (@jarname != '*')
		begin
			select @tempstr = "select @count = count(*) from [" + @dbname + "]..sysjars where jname = '" + @jarname + "'"
			exec (@tempstr)
			if (@count = 0)
			begin
				return (1)
			end
		end
	end
	else /* Error: @dbname does not exist in sysdatabases */
	begin
		return (1)
	end
	return (0)
end
go

/*
** Stored Procedure:  sp_workdir_insert_trigger 
** 	This stored procedure is called by a trigger for inserts into the pca_jre_arguments table
** 	specifically for the pca_jvm_work_dir argument
**
** Purpose: The purpose of this trigger is to identify duplicate Working (aka Trusted) Directories
** as they are inserted. The need for a trigger comes from the fact that a work_dir entry can contain
** an optional permissions mask. The idea is that we need to identify entries as duplicates when they
** differ only in the permission mask while the directory path is identical. This is a list of
** entries that will all be identified as duplicates since the directory paths are the same (notice
** that trailing separators are also handled correctly).
** 	/some/directory/path(u+rw)
** 	/some/directory/path/(u+rw)
** 	/some/directory/path(ugo+rwx)
** 	/some/directory/path
** 	/some/directory/path/
**
** note: trailing separators will be removed if specified (by both the 'add' and 'update' commands)
**
** A duplicate entry is defined as any work_dir entry whose string_value differs from another entry only
** in the permission mask and where the two directory path portions are identical.
**
** Validation Rules:
**	1. duplicates are based on the path and do not involve the mask - a path can only be
**		specified once, with or without a permissions mask
**	2. if a path is added with a trailing separator ( such as '/tmp/' or '/tmp/(u+rw)' ), the 
**		trailing separator is removed. This is done to make paths consistent and simplify 
**		the process of detecting duplicates.
**	3. when an entry is added, the new value will be checked against all existing values whether
**		they are enabled or not. No duplicates are allowed and any attempt to add an 
**		entry so that it becomes a duplicate will result in a rollack
*/
if exists (select 1 from sysobjects where name = 'sp_workdir_insert_trigger')
	drop proc sp_workdir_insert_trigger
go

create procedure sp_workdir_insert_trigger
	@new_workdir varchar(255) = NULL
as
begin
	declare @workdir_no_mask varchar(255),
		@the_mask	varchar(255),
		@the_path	varchar(255),
		@unix_sep	varchar(3),
		@win_sep	varchar(3),
		@last_char	varchar(3),
		@count		int,
		@rollback	int,
		@lparen		int,
		@lparen2	int,
		@rparen		int,
		@length		int

	/*
	** Unix and Win path separators
	** char(47) = '/'
	** char(92) = '\'
	*/
	select @unix_sep = char(47)
	select @win_sep = char(92)

	/*
	** First we validate the parens to make sure our new value is at least reasonable
	** 1. either it has no parens at all (no permission mask)
	** 2. or if it has parens, we check the following:
	** 	- the first right paren IS the last character of the string
	** 	- the first left paren IS NOT the first charcter of the string
	** 	- there is not a second left paren
	*/

	select @lparen = charindex(char(40), @new_workdir)
	select @rparen = charindex(char(41), @new_workdir)
	select @length = len(@new_workdir)
	select @lparen2 = charindex(char(40), substring(@new_workdir, @lparen + 1, len(@new_workdir)))
	if (@lparen = 0) and (@rparen != 0)
	begin
		/* Error: only a right paren found*/
		return (1)
	end
	else if (@rparen = 0) and (@lparen != 0)
	begin
		/* Error: only a left paren found*/
		return (1)
	end
	else if (@rparen != 0) and (@rparen != @length)
	begin
		/* Error: the first right paren is not on the end of the string */
		return (1)
	end
	else if (@lparen = 1)
	begin
		/* Error: left paren is the first character of the string! */
		return (1)
	end
	else if (@lparen != 0) and (@lparen2 != 0)
	begin
		/* Error: found 2 left parens */
		return (1)
	end
	else if (@lparen != 0) and (@rparen = @length)
	begin
		/*
		** user value is ok and does have a mask
		** we have to strip the mask off for our duplicate check below
		*/
		select @the_path = substring(@new_workdir, 1, @lparen - 1)
		select @the_mask = substring(@new_workdir, @lparen, len(@new_workdir))
		select @last_char = substring(@the_path, len(@the_path), len(@the_path) + 1)
		if (@last_char = @unix_sep) or (@last_char = @win_sep)
		begin
			/*
			** remove trailing separator to ensure that all paths
			** are equal in this respect (in order to validate against
			** duplicates below
			*/
			select @the_path = substring(@the_path, 1, len(@the_path) - 1)
			/*
			** update the value in the table to strip the trailing separator
			** unless the stripped value is already in the table.
			*/
			if (select count(*) from pca_jre_arguments 
					where jre_args_name = "pca_jvm_work_dir" and
					jre_args_string_value = @the_path + @the_mask) = 0
			begin
				update pca_jre_arguments
					set jre_args_string_value = @the_path + @the_mask
						where jre_args_name = "pca_jvm_work_dir" and
							jre_args_string_value like @new_workdir
			end
			else
			begin
				return (1)
			end
		end
		select @workdir_no_mask = @the_path
	end
	else if (@lparen = 0) and (@rparen = 0)
	begin
		/*
		** The user value is ok - but does NOT have a mask
		*/
		select @the_path = @new_workdir
		select @last_char = substring(@the_path, len(@the_path), len(@the_path) + 1)
		if (@last_char = @unix_sep) or (@last_char = @win_sep)
		begin
			/*
			** remove trailing separator to ensure that all paths
			** are equal in this respect (in order to validate against
			** duplicates below
			*/
			select @the_path = substring(@the_path, 1, len(@the_path) - 1)
			/*
			** update the value in the table to strip the trailing separator
			** unless the stripped value is already in the table.
			*/
			if (select count(*) from pca_jre_arguments 
					where jre_args_name = "pca_jvm_work_dir" and
					jre_args_string_value = @the_path) = 0
			begin
				update pca_jre_arguments
					set jre_args_string_value = @the_path
						where jre_args_name = "pca_jvm_work_dir" and
						jre_args_string_value like @new_workdir
			end
			else
			begin
				return (1)
			end
		end
		select @workdir_no_mask = @the_path
	end
	else
	begin
		/* Error: unknown error - we should never land here but if we do, something is clearly wrong */
		return (1)
	end

	/*
	** Check for duplicates using the path value without permission mask
	*/
	select @count = count(*) 
		from pca_jre_arguments
		where jre_args_name = "pca_jvm_work_dir" and
			(jre_args_string_value = @workdir_no_mask or
			jre_args_string_value like @workdir_no_mask + "[(]%[)]")
	if (@count > 1)
	begin
		return (1)
	end
	else if (@count < 1)
	begin
		/* 
		** @count = 0
		**
		** we should never reach this
		*/
		return (1)
	end

	return (0)
end
go

/*
** Stored Procedure:  sp_generic_insert_trigger
** 	This stored procedure is called by a trigger for inserts into the pca_jre_arguments table
** 	for all properties other than the Extension Class Loaders
**
** Purpose: The purpose of this trigger is to validate inserted values are not duplicates. There are
** 	constraints on the table to catch most types of duplicates but for string and number type
** 	arguments they would not catch the case where the string/number values differ. For these types
** 	we must ensure that there is only one of each and cannot permit a 2nd from being entered.
**
** This trigger will be called whether a row is inserted with the stored procedure sp_jreconfig or with
** manual 'insert into' statements. Either way we need to ensure that a few rules are followed for any
** new rows coming into the table that configure the Database Extension Class Loader.
**
** Rules:
** 	1. All arguments of type String, Number or Switch are inserted into the table by the installpcidb
** 	   script and should never need to be added or deleted. Only 'array' type arguments can be added
** 	   and deleted.
** 	2. String, Number and Switch type arguments can be enabled or disabled and in the case of String
** 	   and Number arguments, the update command can be used to modify their associated values.
** 	3. Any attempts to insert an additional row for one of these arguments will be automatically
** 	   rolled back by this trigger and stored procedure.
**
**
** By doing this validation in a trigger, the stored procedure sp_jreconfig does not need to worry about invalid inserts that 
** are done outside of the stored procedure (using 'insert into' statements for example), and therefore does not have to do
** this type of validation.
*/
if exists (select 1 from sysobjects where name = 'sp_generic_insert_trigger')
	drop proc sp_generic_insert_trigger
go

create procedure sp_generic_insert_trigger
	@arg_name varchar(255) = NULL,
	@units varchar(255) = NULL
as
begin
	/*
	** If Units is 'string', 'number' or 'switch' we can only have one row
	** per argument name in the table. Only array type arguments can have more than
	** one row.
	*/
	if (@units != 'array') and
		(select count(*)
		from pca_jre_arguments
			where jre_args_name = @arg_name) > 1
	begin
		return (1)
	end

	return (0)
end
go

/*
** Stored Procedure:  sp_ext_loader_update_trigger
** 	This stored procedure is called by a trigger for updates into the pca_jre_arguments table
** 	specifically for arguments that are one of the two Extension Class Loader properties:
** 		pca_jvm_ext_class_loader_global
** 		pca_jvm_ext_class_loader_dbase
**
** Purpose: The purpose of this trigger is to validate updated values that configure the 
** Extension Class Loaders only. No other configuration properties need this type of validation.
** This trigger will catch two types of updates:
** 	- when a row or rows are enabled or disabled
** 	- when the string value has been changed
** 	- or when both have occured
**
** This trigger will be called whether a row is updated with the stored procedure sp_jreconfig or with
** manual 'update table' statements. Either way we need to ensure that a few rules are followed for any
** rows that have been enabled or have had their string value modified.
** However, this trigger is only called when a row is either already enabled or is being enabled by
** the update. If a row is already disabled, or is being disabled by the update, we allow the changes
** to go through without any validation. It is only at such time that the row is enabled that we do the
** proper validation.
**
** Validation Rules:
** 	1. For the Global type of Extension Class Loader only---
** 	   one database can be configured but it can have as many Jars as necessary
** 		We can have all of these:
** 			'db1:jar1'
** 			'db1:jar2'
** 			'db1:jar3'
** 		But we cannot also have this: (because it uses a different database 'db2')
** 			'db2:jar4'
** 	   for the Database type of Extension Class Loaders it is ok to have as many different databases
** 	   as needed and this particular rule does not apply.
**
** 	2. If the supplied string contains whitespace, we need to trim it (tabs and spaces are removed)
** 		'db1   :    jar2' -> 'db1:jar2'
**
** 	3. If the database is configured to include ALL of the Jars (using 'db1:*') then it must be the only row
** 		that is configured for that database. It is not allowed to have both 'db1:*' and 'db1:jar1'. 
** 		Either we configure each Jar separately, or we configure for all of Jars registered to a database.
** 		So, if 'db1:*' is already in the table, we don't allow 'db1:jar1', and conversly, if 'db1:jar1'
** 		is already in the table, we don't allow 'db1:*' to be added either.
**
** 	4. string validation is also done to ensure the following:
** 		- no commas appear in the string - (this is invalid: 'db1:jar1,jar2,jar3')
** 		- only one colon can appear which separates the database and jar ('db1:jar1')
** 		- both sides of the colon have at least 1 char (ie, we have both a database name and a jar name)
**
**
** By doing this validation in a trigger, the stored procedure sp_jreconfig does not need to worry about invalid inserts that 
** are done outside of the stored procedure (using 'insert into' statements for example), and therefore does not have to do
** this type of validation.
*/
if exists (select 1 from sysobjects where name = 'sp_ext_loader_update_trigger')
	drop proc sp_ext_loader_update_trigger
go

create procedure sp_ext_loader_update_trigger
	@arg_name varchar(255) = NULL,
	@inserted_str varchar(255) = NULL,
	@deleted_str  varchar(255) = NULL,
	@enabled_ins int,
	@enabled_del int
as
begin
	declare @notab_str	varchar(255),
		@dbname		varchar(255),
		@jarname	varchar(255),
		@trimmed_val	varchar(255),
		@tempstr	varchar(255),
		@dblen		int,
		@jarlen		int,
		@count		int

	/*
	** Make sure we have at least one ':' character
	*/
	if (charindex(':', @inserted_str) = 0)
	begin
		return (1)
	end
	/*
	** Make sure we do not have any comma ',' characters
	*/
	if (charindex(',', @inserted_str) != 0)
	begin
		return (1)
	end

	/*
	** Make sure we do not have any whitespace characters (tab or space)
	** We should not unless a SQL 'insert into' command was used, it is
	** not possible with the stored procedure since whitespace chars are
	** stripped before the update command is called. If we find whitespace
	** now, we have to rollback since we really shouldn't call update to
	** fix it from within an update trigger as it could cause an infinite loop
	*/
	if (charindex(' ', @inserted_str) != 0) or (charindex(char(9), @inserted_str) != 0)
	begin
		return (1)
	end

	/*
	** remove tabs and then trim spaces and split up the string into dbname and jarname parts
	** and also create @trimmed_val which is a trimmed up version of the original string
	*/
	select @dbname  = rtrim(ltrim(substring(@inserted_str, 1, charindex(':', @inserted_str) - 1)))
	select @jarname = rtrim(ltrim(substring(@inserted_str, charindex(':', @inserted_str) + 1, len(@inserted_str) - charindex(':', @inserted_str))))
	select @dblen = len(@dbname)
	select @jarlen = len(@jarname)
	/*
	** Check for a zero-length database name, like: ':jar1'
	*/
	if (@dblen IS NULL)
	begin
		return (1)
	end
	/*
	** Check for a zero-length Jar name, like: 'db1:'
	*/
	if (@jarlen IS NULL)
	begin
		return (1)
	end

	/*
	** Check for colon chars ':' in the jar name, like 'd1:jar1:jar2'
	*/
	if (charindex(':', @jarname) != 0)
	begin
		return (1)
	end
	/*
	** If the supplied value has a '*' for Jar Name and
	** there are already other rows in the table, we report an error
	** and return (1) to rollback the transaction
	*/
	if (@jarname = '*') and
		(select count(*)
			from pca_jre_arguments
			where jre_args_name = @arg_name and
				jre_args_string_value LIKE @dbname + ':%' and
				jre_args_string_value != @inserted_str) > 0
	begin
		return (1)
	end
	/*
	** If there is already a wildcard entry (db1:*) and we are trying
	** to insert another row, we report that this is not allowed and
	** return (1) to rollback the transaction
	** Exception: if we are updating a row with 'db1:*' to 'db1:jar'
	** then it's ok and we let it through
	*/
	if (@jarname != "*") and (@deleted_str not like "%" + ":*") and
		(select count(*)
		from pca_jre_arguments
			where jre_args_name = @arg_name and
				jre_args_string_value = @dbname + ':*') > 0
	begin
		return (1)
	end

	/*
	** Global Extension Class Loader only -----
	** If the new value uses a different database name than the existing
	** rows, we need to report the mistmatch and return (1) to rollback
	** the transaction
	*/
	if (select count(distinct substring(t1.jre_args_string_value, 1, charindex(':', t1.jre_args_string_value)))
		from pca_jre_arguments t1, pca_jre_arguments t2
			where t1.jre_args_name = 'pca_jvm_ext_class_loader_global' and
				t1.jre_args_enabled = 1 and
				(substring(t1.jre_args_string_value, 1, charindex(':', t1.jre_args_string_value)) !=
				substring(t2.jre_args_string_value, 1, charindex(':', t2.jre_args_string_value)))) > 1
	begin
		return (1)
	end

	if exists (select name from master..sysdatabases where name = @dbname)
	begin
		if (@jarname != '*')
		begin
			select @tempstr = "select @count = count(*) from [" + @dbname + "]..sysjars where jname = '" + @jarname + "'"
			exec (@tempstr)
			if (@count = 0)
			begin
				return (1)
			end
		end
	end
	else /* Error: @dbname does not exist in sysdatabases */
	begin
		return (1)
	end
	return (0)
end
go

/*
** Stored Procedure:  sp_workdir_update_trigger
** 	This stored procedure is called by a trigger for updates into the pca_jre_arguments table
** 	specifically for Working Directories (aka Trusted Directories)
** 		pca_jvm_work_dir
**
** Purpose: The purpose of this trigger is to validate changes to the work_dir entries.
** This trigger will only be called for this type of update:
** 	- when the string value has been changed
** Enable or Disable commands are filtered out before this trigger is called
**	see: pca_jre_argument_update_trigger
**
** Validation Rules:
**	1. duplicates are based on the path and do not involve the mask - a path can only be
**		specified once, with or without a permissions mask
**	2. if a path is added with a trailing separator ( such as '/tmp/' or '/tmp/(u+rw)' ), the 
**		trailing separator is removed. This is done to make paths consistent and simplify 
**		the process of detecting duplicates.
**	3. when an entry is updated, the new value will be checked against all existing values whether
**		they are enabled or not. No duplicates are allowed and any attempt to update an 
**		entry so that it becomes a duplicate will result in the update being rolled back
*/
if exists (select 1 from sysobjects where name = 'sp_workdir_update_trigger')
	drop proc sp_workdir_update_trigger
go

create procedure sp_workdir_update_trigger
	@new_workdir varchar(255) = NULL,
	@previous_str      varchar(255) = NULL
as
begin
	declare @workdir_no_mask varchar(255),
		@the_mask	varchar(255),
		@the_path	varchar(255),
		@unix_sep	varchar(3),
		@win_sep	varchar(3),
		@last_char	varchar(3),
		@count		int,
		@lparen		int,
		@lparen2	int,
		@rparen		int,
		@length		int

	/*
	** Unix and Win path separators
	** char(47) = '/'
	** char(92) = '\'
	*/
	select @unix_sep = char(47)
	select @win_sep = char(92)

	/*
	** First we validate the parens to make sure our new value is at least reasonable
	** 1. either it has no parens at all (no permission mask)
	** 2. or if it has parens, we check the following:
	** 	- the first right paren IS the last character of the string
	** 	- the first left paren IS NOT the first charcter of the string
	** 	- there is not a second left paren
	*/


	select @lparen = charindex(char(40), @new_workdir)
	select @rparen = charindex(char(41), @new_workdir)
	select @length = len(@new_workdir)
	select @lparen2 = charindex(char(40), substring(@new_workdir, @lparen + 1, len(@new_workdir)))

	if (@lparen = 0) and (@rparen != 0)
	begin
		/* Error: only a right paren found*/
		return (1)
	end
	else if (@rparen = 0) and (@lparen != 0)
	begin
		/* Error: only a left paren found*/
		return (1)
	end
	else if (@rparen != 0) and (@rparen != @length)
	begin
		/* Error: the first right paren is not on the end of the string */
		return (1)
	end
	else if (@lparen = 1)
	begin
		/* Error: left paren is the first character of the string! */
		return (1)
	end
	else if (@lparen != 0) and (@lparen2 != 0)
	begin
		/* Error: found 2 left parens */
		return (1)
	end
	else if (@lparen != 0) and (@rparen = @length)
	begin
		/*
		** user value is ok and does have a mask
		** we have to strip the mask off for our duplicate check below
		*/
		select @the_path = substring(@new_workdir, 1, @lparen - 1)
		select @the_mask = substring(@new_workdir, @lparen, len(@new_workdir))
		select @last_char = substring(@the_path, len(@the_path), len(@the_path) + 1)
		if (@last_char = @unix_sep) or (@last_char = @win_sep)
		begin
			/*
			** remove trailing separator to ensure that all paths
			** are equal in this respect (in order to validate against
			** duplicates below
			*/
			select @the_path = substring(@the_path, 1, len(@the_path) - 1)
			/*
			** update the value in the table to strip the trailing separator
			** unless the stripped value is already in the table.
			*/
			if (select count(*) from pca_jre_arguments 
					where jre_args_name = "pca_jvm_work_dir" and
					jre_args_string_value = @the_path + @the_mask) = 0
			begin
				update pca_jre_arguments
					set jre_args_string_value = @the_path + @the_mask
						where jre_args_name = "pca_jvm_work_dir" and
							jre_args_string_value like @new_workdir
			end
			else
			begin
				return (1)
			end
		end
		select @workdir_no_mask = @the_path
	end
	else if (@lparen = 0) and (@rparen = 0)
	begin
		/*
		** The user value is ok - but does NOT have a mask
		*/
		select @the_path = @new_workdir
		select @last_char = substring(@the_path, len(@the_path), len(@the_path) + 1)
		if (@last_char = @unix_sep) or (@last_char = @win_sep)
		begin
			/*
			** remove trailing separator to ensure that all paths
			** are equal in this respect (in order to validate against
			** duplicates below
			*/
			select @the_path = substring(@the_path, 1, len(@the_path) - 1)
			/*
			** update the value in the table to strip the trailing separator
			** unless the stripped value is already in the table.
			*/
			if (select count(*) from pca_jre_arguments 
					where jre_args_name = "pca_jvm_work_dir" and
					jre_args_string_value = @the_path) = 0
			begin
				update pca_jre_arguments
					set jre_args_string_value = @the_path
						where jre_args_name = "pca_jvm_work_dir" and
							jre_args_string_value like @new_workdir
			end
			else
			begin
				return (1)
			end
		end
		select @workdir_no_mask = @the_path
	end
	else
	begin
		/* Error: unknown error - we should never land here but if we do, something is clearly wrong */
		return (1)
	end

	select @count = count(*) 
		from pca_jre_arguments
		where jre_args_name = "pca_jvm_work_dir" and
			(jre_args_string_value = @workdir_no_mask or
			jre_args_string_value like @workdir_no_mask + "(%)")
	if (@count > 1)
	begin
		return (1)
	end
	else if (@count < 1)
	begin
		/* 
		** @count = 0
		** this is an unknown error - we should never reach this case.
		** However, if we do reach this, all we can do is return 1 to 
		** forca a rollback
		*/
		return (1)
	end

	return (0)
end
go

/*
** Trigger:  pca_jre_argument_insert_trigger
**
** Purpose: The purpose of this trigger is to validate inserted values that configure the 
** Extension Class Loaders and/or Work Directories (also known as Trusted Directories)
**
** This trigger will be called for any insert into the pca_jre_arguments table whether it
** is made from the sp_jreconfig stored procedure or a manual SQL insert command.
**
** see also: the stored procedure header comments for these two stored procs:
**	sp_ext_loader_insert_trigger
**	sp_workdir_insert_trigger
*/
begin
	create trigger pca_jre_argument_insert_trigger
	on pca_jre_arguments 
	for insert as 
	declare @tmp1 varchar(255),
		@tmp2 varchar(255),
		@tmp3 varchar(255),
		@ret  int

	select
		@tmp1 = jre_args_name,
		@tmp2 = jre_args_string_value,
		@tmp3 = jre_args_units
			from inserted

	if (@tmp1 like "pca_jvm_ext_class_loader" + "%")
	begin
		exec @ret = sp_ext_loader_insert_trigger @tmp1, @tmp2
		if (@ret > 0)
		begin
			rollback trigger
		end
	end
	else if (@tmp1 = "pca_jvm_work_dir")
	begin
		exec @ret = sp_workdir_insert_trigger @tmp2
		if (@ret > 0)
		begin
			rollback trigger
		end
	end
	else /* all other arguments (non ext class loaders) */
	begin
		exec @ret = sp_generic_insert_trigger @tmp1, @tmp3
		if (@ret > 0)
		begin
			rollback trigger
		end
	end
end
go

/*
** Trigger:  pca_jre_argument_update_trigger
**
** Purpose: The purpose of this trigger is to validate updated values for two specific types of arguments:
** 		1. Extension Class Loaders (arg_name = pca_jvm_ext_class_loader_*)
** 		2. Working Directories (aka Trusted Directories) (arg_name = pca_jvm_work_dir)
** 	This trigger will catch items being enabled as well as items whos string values are being modified.
**
** This trigger will be called whether a row is updated with the stored procedure sp_jreconfig or with
** manual 'update' SQL statements.
**
** see also: the header comments for these stored procedures:
** 	sp_ext_loader_update_trigger
** 	sp_workdir_update_trigger
*/
begin
	create trigger pca_jre_argument_update_trigger
	on pca_jre_arguments 
	for update
	as 
	declare @actualname varchar(255),
		@inserted_str varchar(255),
		@deleted_str  varchar(255),
		@enabled_ins int,
		@enabled_del int,
		@ret        int,
		@rollback   int,
		@count      int

	if exists (select 1 
		from inserted t1, deleted t2
			where 
				t1.jre_args_name like "pca_jvm_ext_class_loader" + "%" and
				(t1.jre_args_enabled != t2.jre_args_enabled or
				t1.jre_args_string_value != t2.jre_args_string_value))
	begin
		select @rollback=0
		declare pci_cursor cursor for
			select t1.jre_args_name, t1.jre_args_string_value, t2.jre_args_string_value, t1.jre_args_enabled, t2.jre_args_enabled
			from inserted t1, deleted t2
			where t1.jre_args_enabled = 1
			for read only
		open pci_cursor
		fetch pci_cursor into @actualname, @inserted_str, @deleted_str, @enabled_ins, @enabled_del
		while (@@sqlstatus = 0) and (@rollback = 0)
		begin
			exec @ret = sp_ext_loader_update_trigger @actualname, @inserted_str, @deleted_str, @enabled_ins, @enabled_del
			if (@ret != 0)
			begin
				select @rollback=1
			end
			fetch pci_cursor into @actualname, @inserted_str, @deleted_str, @enabled_ins, @enabled_del
		end
		close pci_cursor
		deallocate cursor pci_cursor
		if (@rollback = 1)
		begin
			rollback trigger
		end
	end
	if exists (select 1 
		from inserted t1, deleted t2
			where 
				t1.jre_args_name like "pca_jvm_work_dir" and
				(t1.jre_args_enabled != t2.jre_args_enabled or
				t1.jre_args_string_value != t2.jre_args_string_value))
	begin
		select @rollback=0
		declare workdir_cursor cursor for
			select t1.jre_args_string_value, t2.jre_args_string_value, t1.jre_args_enabled, t2.jre_args_enabled
			from inserted t1, deleted t2
			for read only
		open workdir_cursor
		fetch workdir_cursor into @inserted_str, @deleted_str, @enabled_ins, @enabled_del
		while (@@sqlstatus = 0) and (@rollback = 0)
		begin
			/*
			** We only need to call the trigger if we are updating the string
			** value, so we can ignore updates that modify the enable bit
			*/
			if (@enabled_ins = @enabled_del)
			begin
				exec @ret = sp_workdir_update_trigger @inserted_str, @deleted_str
				if (@ret != 0)
				begin
					select @rollback=1
				end
			end
			fetch workdir_cursor into @inserted_str, @deleted_str, @enabled_ins, @enabled_del
		end
		close workdir_cursor
		deallocate cursor workdir_cursor
		if (@rollback = 1)
		begin
			rollback trigger
		end
	end
end
go

/*
** Populate Table: pca_jre_arguments
**
** RESOLVE - resolution 467130-5 has been opened to generate these insert statements
** 	from information in a header file instead of hard coded here
*/
if exists (select 1 from sysobjects where name = "pca_jre_arguments")
begin
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_report',
			'switch',
			0,
			'',
			'Report PCA/VM JNI/JVM invocations',
			0,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_debug',
			'switch',
			0,
			'',
			'Report PCA_DEBUG requests',
			0,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_except',
			'switch',
			0,
			'',
			'Report excepting PCA/VM JNI/JVM invocations',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_strace',
			'switch',
			0,
			'',
			'Produce stack traces on none emulated VM handles',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_abort',
			'switch',
			0,
			'',
			'Abort abort(2) all on any failure (dangerous)',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_jvmti',
			'switch',
			0,
			'',
			'Java VM Tools Interface',
			0,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_heap_ratio',
			'string',
			1,
			'0.3',
			'VM Heap / PCI memory ratio',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_netio',
			'switch',
			0,
			'',
			'Allow VM network support',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_security_manager_enabled',
			'switch',
			0,
			'',
			'Enable the SecurityManager in the PCA/JVM',
			0,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_enabled',
			'switch',
			0,
			'',
			'Enable PCA/VM signature cache',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_washdaemon',
			'switch',
			0,
			'',
			'Enable PCA/VM signature cache wash daemon',
			0,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_washcycle',
			'number',
			1000,
			'',
			'PCA/VM signature cache wash daemon cycle time (ms)',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_size',
			'number',
			512,
			'',
			'PCA/VM signature cache size in KBytes',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_size_type',
			'number',
			1,
			'',
			'PCA/VM signature cache size_type 0:AS_PCT 1:Kbyte 2:Mbyte',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_fixed_ratio',
			'number',
			50,
			'',
			'PCA/VM signature cache size percentage fixed',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_density',
			'number',
			100,
			'',
			'PCA/VM signature cache target density',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_sigcache_freeboard',
			'number',
			30,
			'',
			'PCA/VM signature cache space recovery percentaga on cache sweeps',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_min_port',
			'number',
			1026,
			'',
			'Allow VM network support',
			1,
			0)
	insert into pca_jre_arguments values ( 			 0,
			'pca_jvm_allow_unchecked_sockops',
			'switch',
			0,
			'',
			'Allow unchecked socket operations',
			0,
			0)
	insert into pca_jre_arguments values (			 4,
			'pca_jvm_min_jni_version',
			'string',
			0,
			'JNI_VERSION_1_2',
			'Minimum backward compatible JNI version',
			1,
			0)
	insert into pca_jre_arguments values ( 			 6,
			'pca_jvm_ase_logging',
			'switch',
			0,
			'',
			'Configure ASE logging',
			1,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Djava.awt.headless=true',
			'Run JVM in headless mode',
			1,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Djava.compiler=JIT',
			'Force JIT compilation and optimization',
			1,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-verbose:class',
			'Class loading within the JRE/VM',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-verbose:gc',
			'Garbage Collection statistics',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-verbose:jni',
			'Java Native Interface (JNI) invokations',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xbatch',
			'disabled background compilation',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xcheck:jni',
			'perform additional checks for JNI functions',
			1,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xfuture',
			'perform strict checks, anticipating future default',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xincgc',
			'enable incremental garbage collection',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xint',
			'interpreted mode execution only',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xloggc:./myGClog',
			'log GC status to a file with teim stamps',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xmixed',
			'mixed mode execution (default)',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xms64m',
			'set initial Java heap size',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xmx64m',
			'set maximum Java heap size',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xss64m',
			'set Java thread stack size',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xnoclassgc',
			'disable class garbage collection',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xprof',
			'output cpu profiling data',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xrs',
			'reduce use of OS signals by Java/VM (see documentation)',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-Xshare:auto',
			'Configure shared class data (set to auto, off or on)',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-XX:+UseAltSigs',
			'This option seems to crash the J2SE',
			0,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-XX:+CITime',
			'Time spent in JIT Compiler (1.4 only)',
			0,
			0)
	insert into pca_jre_arguments values ( 			 9,
			'pca_jvm_java_dbg_agent_port',
			'number',
			8000,
			'',
			'Configure the port number and the Java VM Debug Agent',
			0,
			0)
	insert into pca_jre_arguments values ( 			 9,
			'pca_jvm_java_dbg_agent_suspend',
			'switch',
			0,
			'',
			'Java VM Debug Agent starts suspended when enabled',
			0,
			0)
end
go

go

/*
** Populate Table: pci_slot_syscalls
*/

use sybpcidb
go

if exists (select 1 from sysobjects where name = "pci_slot_syscalls")
begin
	insert into pci_slot_syscalls values ( 1, 'access',		'pca_access', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'alarm',		'pca_alarm', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'chdir',		'pca_chdir', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'chown',		'pca_chown', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'chmod',		'pca_chmod', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'chroot',		'pca_chroot', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'close',		'pca_close', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'confstr',		'pca_confstr', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'fchdir',		'pca_fchdir', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'fchown',		'pca_fchown', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'fchmod',		'pca_fchmod', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'fcntl',		'pca_fcntl', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'fdatasync',		'pca_fdatasync', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'fpathconf',		'pca_fpathconf', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'fsync',		'pca_fsync', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'ftruncate',		'pca_ftruncate', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'getcwd',		'pca_getcwd', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getegid',		'pca_getegid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'geteuid',		'pca_geteuid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getgid',		'pca_getgid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getgroups',		'pca_getgroups', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'getpgid',		'pca_getpgid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getpgrp',		'pca_getpgrp', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getpid',		'pca_getpid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getppid',		'pca_getppid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getrlimit',		'pca_getrlimit', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'getsid',		'pca_getsid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getuid',		'pca_getuid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'lchown',		'pca_lchown', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'msgctl',		'pca_msgctl', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'msgget',		'pca_msgget', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'msgrcv',		'pca_msgrcv', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'open64',		'pca_open64', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'pathconf',		'pca_pathconf', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'pause',		'pca_pause', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'pipe',		'pca_pipe', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'pread',		'pca_pread', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'pwrite',		'pca_pwrite', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'rename',		'pca_rename', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'remove',		'pca_remove', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'read',		'pca_read', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'readlink',		'pca_readlink', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'readv',		'pca_readv', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'rmdir',		'pca_rmdir', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'semctl',		'pca_semctl', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'semget',		'pca_semget', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setegid',		'pca_setegid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'seteuid',		'pca_seteuid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setgid',		'pca_setgid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setgroups',		'pca_setgroups', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'setpgid',		'pca_setpgid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setregid',		'pca_setregid', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'setreuid',		'pca_setreuid', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'setrlimit',		'pca_setrlimit', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'setsid',		'pca_setsid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setuid',		'pca_setuid', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'shmat',		'pca_shmat', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'shmctl',		'pca_shmctl', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'shmdt',		'pca_shmdt', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'shmget',		'pca_shmget', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'stime',		'pca_stime', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'symlink',		'pca_symlink', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'sysinfo',		'pca_sysinfo', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'umount2',		'pca_umount2', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'unlink',		'pca_unlink', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'utimes',		'pca_utimes', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'write',		'pca_write', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'writev',		'pca_writev', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'ioctl',		'pca_ioctl', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'stat',		'pca_stat', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'accept',		'pca_accept', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'bind',		'pca_bind', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'connect',		'pca_connect', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'getpeername',	'pca_getpeername', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'getsockname',	'pca_getsockname', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'getsockopt',		'pca_getsockopt', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'listen',		'pca_listen', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'recv',		'pca_recv', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'recvfrom',		'pca_recvfrom', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'recvmsg',		'pca_recvmsg', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'send',		'pca_send', 		0, 0)
	insert into pci_slot_syscalls values ( 1, 'sendmsg',		'pca_sendmsg', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'sendto',		'pca_sendto', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'setsockopt',		'pca_setsockopt', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'shutdown',		'pca_shutdown', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'socket',		'pca_socket', 		1, 0)
	insert into pci_slot_syscalls values ( 1, 'sockatmark',		'pca_sockatmark', 	1, 0)
	insert into pci_slot_syscalls values ( 1, 'socketpair',		'pca_socketpair', 	1, 0)
end
go

go

use sybpcidb
go

/*
** Populate Table: pca_jre_arguments
*/
if exists (select 1 from sysobjects where name = "pca_jre_arguments")
begin
	insert into pca_jre_arguments values ( 			1,
			'pca_jvm_tmp_dir',
			'string',
			0,
			'/tmp',
			'the absolute path to the system temp directory (required for File I/O)',
			1,
			0)
	insert into pca_jre_arguments values ( 			1,
			'pca_jvm_root_dir',
			'string',
			0,
			'/',
			'the absolute path to the system root directory (required for File I/O)',
			1,
			0)
	insert into pca_jre_arguments values ( 			2,
			'pca_jvm_work_dir',
			'array',
			0,
			'/tmp(u+rw)',
			'the absolute path (and optional permission mask) where the VM is allowed to do File I/O (optional for File I/O)',
			0,
			0)
	insert into pca_jre_arguments values ( 			5,
			'pca_jvm_module_path',
			'string',
			0,
			'shared/JRE-6_0_6_64BIT/lib/amd64/server/libjvm.so',
			'the location of the JVM shared library using a relative path located under $SYBASE, or a fully qualified filename anywhere on your system',
			1,
			0)
	insert into pca_jre_arguments values ( 			6,
			'pca_jvm_log_filename',
			'string',
			0,
			'/tmp/Java_vm.log1',
			'a fully qualified filename that the VM will use for logging',
			0,
			0)
	insert into pca_jre_arguments values ( 			8,
			'pca_jvm_java_options',
			'array',
			0,
			'-esa',
			'Enable All System Assertions - only applies to platforms using the Sun HotSpot (TM) JavaVM',
			1,
			0)
	insert into pca_jre_arguments values ( 			8,
			'pca_jvm_java_options',
			'array',
			0,
			'-enablesystemassertions',
			'Enable Java/VM System Assertions - only applies to platforms using the Sun HotSpot (TM) JavaVM',
			1,
			0)
	insert into pca_jre_arguments values ( 			 8,
			'pca_jvm_java_options',
			'array',
			0,
			'-XX:CodeCacheExpansionSize=512000',
			'Code Cache extension size',
			1,
			0)
end
go

if exists (select 1 from sysobjects where name = "pci_slot_syscalls")
begin
	insert into pci_slot_syscalls values ( 1, 'lseek64',		'pca_lseek64', 		1, 0)
end
go

go

/*
** raiserror Messages for jreconfig [Total 26]
**
**	19898: "Syntax Error: missing arguments ( correct syntax: %1! )"
**	19900: "record not found: '%1!'"
**	19902: "Duplicates are not permitted! The value is already in the table: '%1!'"
**	19903: "Internal error: units type is not recognized: '%1!'"
**	19904: "list command option is invalid or ambiguous: '%1!'"
**	19906: "record not found: '%1!', '%2!'"
**	19907: "No records found! There are no records in the database for this argument: '%1!'"
**	19908: "Supplied value matches multiple records: '%1!'"
**	19909: "Supplied values match multiple records: '%1!', '%2!'"
**	19911: "Syntax Error: disable by database only, no support for disabling individual jar files"
**	19912: "Syntax Error: enable by database only, no support for enabling individual jar files"
**	19913: "Syntax Error: the supplied value is not numeric: %1!"
**	19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'"
**	19915: "Syntax Error: invalid paren(s) found"
**	19916: "Syntax Error: unmatched paren found"
**	19917: "Unload command failed - the PCA/JVM is not in the correct state to do an Unload operation - make sure the JVM has not been initialized"
**	19918: "Load command failed - the PCA/JVM is not in the correct state to do a Load operation - make sure the JVM has not been initialized"
**	19921: "Delete Failed - Transaction Rolled Back"
**	19922: "Insert Failed - Transaction Rolled Back"
**	19923: "Update Failed - Transaction Rolled Back"
**	19924: "Supplied value matches multiple argument names: '%1!'"
**	19925: "Supplied value matches multiple directive names: '%1!'"
**	19926: "The '%1!' command cannot be used with Directives"
**	19927: "The '%1!' command does not support arguments of type: '%2!'"
*/



use sybpcidb
go

if exists (select 1 from sysobjects where name = 'sp_jreconfig')
	drop proc sp_jreconfig
go

create proc sp_jreconfig
	@cmd		varchar(16)	= NULL,	/* the command */
	@name		varchar(32)	= NULL,	/* usually arg_name or directive name */
	@opt1		varchar(255)	= NULL,	/* option 1 - use varies by command */
	@opt2		varchar(255)	= NULL	/* option 2 - use varies by command */
as
begin
	/*
	** Declare Variables used in this proc
	*/
	declare 	@units 			varchar(12), 
			@dbname			varchar(255),
			@jarname		varchar(255),
			@full_argname		varchar(64),
			@notab_str		varchar(255),
			@trimmed_val		varchar(255),
			@workdir_nomask		varchar(255),
			@count			int, 
			@lparen			int, 
			@lparen2		int, 
			@rparen			int, 
			@length			int,
			@has_mask		int,
			@result			int,
			@flag			int,
			@directive_index	int

	set nocount on

	if (@cmd IS NULL) and (@name IS NULL) and (@opt1 IS NULL) and (@opt2 IS NULL)
	begin
		/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
		raiserror 19898, "sp_jreconfig 'cmd', 'name' [, 'opt1' [, 'opt2'] ]"

		return (1)
	end
	else if (@cmd IS NOT NULL) and ((('report' like @cmd + "%") and (@cmd like 'rep%')) or (@cmd = "rpt")) /* must be at least rep or rpt */
	begin
		/*
		** REPORT command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'report', 'name' [, 'opt1' [, 'opt2'] ]"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select 1 from pca_jre_directives where jre_directives_name = @name)
			begin
				if (@opt1 = 'args')
				begin
					if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
					begin
						select
							"Directive" = convert(char(27), a.jre_directives_name),
							"Active" = convert(char(4), a.jre_directives_enabled),
							"Argument Name" = convert(char(31), b.jre_args_name),
							"Units" = convert(char(10), b.jre_args_units),
							CASE
								when b.jre_args_units = 'string'	then convert(char(18), '"' + b.jre_args_string_value + '"')
								when b.jre_args_units = 'array' 	then convert(char(18), '"' + b.jre_args_string_value + '"')
								else ''
							END as "String Value",
							CASE
								when b.jre_args_units = 'number'	then convert(char(8), b.jre_args_number_value)
								else ''
							END as "Number Value",
							"Enabled" = convert(char(4), b.jre_args_enabled),
							"Description" = convert(char(21), b.jre_args_description)
								from pca_jre_directives a, pca_jre_arguments b
									where a.jre_directives_name = @name and
										a.jre_directives_index = b.jre_args_directive_index
									order by a.jre_directives_index
					end
					else /* unformatted report */
					begin
						select
							a.jre_directives_name,
							a.jre_directives_enabled,
							b.jre_args_name, 
							b.jre_args_units,
							b.jre_args_string_value,
							b.jre_args_number_value,
							b.jre_args_enabled,
							b.jre_args_description
							from pca_jre_directives a, pca_jre_arguments b
								where a.jre_directives_name = @name and
									a.jre_directives_index = b.jre_args_directive_index
									order by a.jre_directives_index
					end
				end
				else /* @opt1 != 'args' */
				begin
					if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
					begin
						select
							"Directive Name"	= convert(char(27), jre_directives_name),
							"Active"		= convert(char(4),  jre_directives_enabled),
							"Description"		= convert(char(52), jre_directives_description)
								from pca_jre_directives
									where jre_directives_name = @name
									order by jre_directives_index
					end
					else /* unformatted report */
					begin
						select
							jre_directives_name,
							jre_directives_enabled,
							jre_directives_description
								from pca_jre_directives
									where jre_directives_name = @name
									order by jre_directives_index
					end
				end
			end
			else if exists (select 1 from pca_jre_arguments where jre_args_name = @name)
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
					"Argument Name" = convert(char(31), jre_args_name),
					"Units" = convert(char(10), jre_args_units),
					CASE
						when jre_args_units = 'string'		then convert(char(30), '"' + jre_args_string_value + '"')
						when jre_args_units = 'array' 		then convert(char(30), '"' + jre_args_string_value + '"')
						else ''
					END as "String Value",
					CASE
						when jre_args_units = 'number'		then convert(char(8), jre_args_number_value)
						else ''
					END as "Number Value",
					"Enabled" = convert(char(4), jre_args_enabled),
					"Description" = convert(char(40), jre_args_description)
						from pca_jre_arguments 
							where jre_args_name = @name
							order by jre_args_name
				end
				else /* unformatted report */
				begin
					select
						jre_args_name, 
						jre_args_units,
						jre_args_string_value,
						jre_args_number_value,
						jre_args_enabled,
						jre_args_description
							from pca_jre_arguments 
								where jre_args_name = @name
								order by jre_args_name
				end
			end
			else if exists (select 1 from pca_jre_directives where jre_directives_name like "%" + @name + "%")
			begin
				if (@opt1 = 'args')
				begin
					if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
					begin
						select
							"Directive" = convert(char(27), a.jre_directives_name),
							"Active" = convert(char(4), a.jre_directives_enabled),
							"Argument Name" = convert(char(31), b.jre_args_name),
							"Units" = convert(char(10), b.jre_args_units),
							CASE
								when b.jre_args_units = 'string'	then convert(char(18), '"' + b.jre_args_string_value + '"')
								when b.jre_args_units = 'array' 	then convert(char(18), '"' + b.jre_args_string_value + '"')
								else ''
							END as "String Value",
							CASE
								when b.jre_args_units = 'number'	then convert(char(8), b.jre_args_number_value)
								else ''
							END as "Number Value",
							"Enabled" = convert(char(4), b.jre_args_enabled),
							"Description" = convert(char(21), b.jre_args_description)
								from pca_jre_directives a, pca_jre_arguments b
									where a.jre_directives_name like "%" + @name + "%" and
										a.jre_directives_index = b.jre_args_directive_index
									order by a.jre_directives_index
					end
					else /* unformatted report */
					begin
						select
							a.jre_directives_name,
							a.jre_directives_enabled,
							b.jre_args_name, 
							b.jre_args_units,
							b.jre_args_string_value,
							b.jre_args_number_value,
							b.jre_args_enabled,
							b.jre_args_description
							from pca_jre_directives a, pca_jre_arguments b
								where a.jre_directives_name like "%" + @name + "%" and
									a.jre_directives_index = b.jre_args_directive_index
									order by a.jre_directives_index
					end
				end
				else /* @opt1 != 'args' */
				begin
					if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
					begin
						select
							"Directive Name"	= convert(char(27), jre_directives_name),
							"Active"		= convert(char(4),  jre_directives_enabled),
							"Description"		= convert(char(52), jre_directives_description)
								from pca_jre_directives
									where jre_directives_name like "%" + @name + "%"
									order by jre_directives_index
					end
					else /* unformatted report */
					begin
						select
							jre_directives_name,
							jre_directives_enabled,
							jre_directives_description
								from pca_jre_directives
									where jre_directives_name like "%" + @name + "%"
									order by jre_directives_index
					end
				end
			end
			else if exists (select 1 from pca_jre_arguments where jre_args_name like "%" +  @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
					"Argument Name" = convert(char(31), jre_args_name),
					"Units" = convert(char(10), jre_args_units),
					CASE
						when jre_args_units = 'string'		then convert(char(30), '"' + jre_args_string_value + '"')
						when jre_args_units = 'array' 		then convert(char(30), '"' + jre_args_string_value + '"')
						else ''
					END as "String Value",
					CASE
						when jre_args_units = 'number'		then convert(char(8), jre_args_number_value)
						else ''
					END as "Number Value",
					"Enabled" = convert(char(4), jre_args_enabled),
					"Description" = convert(char(40), jre_args_description)
						from pca_jre_arguments 
							where jre_args_name like "%" + @name + "%"
							order by jre_args_name
				end
				else /* unformatted report */
				begin
					select
						jre_args_name, 
						jre_args_units,
						jre_args_string_value,
						jre_args_number_value,
						jre_args_enabled,
						jre_args_description
							from pca_jre_arguments 
								where jre_args_name like "%" + @name + "%"
								order by jre_args_name
				end
			end
			else /* no match */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('list' like @cmd + "%")
	begin
		/*
		** LIST command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'list', 'name' [, 'opt1']"

			return (1)
		end
		else
		begin
			if (@name IS NOT NULL) and ('directives' like @name + "%") and (@name like "dir%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive Name"	= convert(char(27), jre_directives_name),
						"Enabled"		= convert(char(4),  jre_directives_enabled),
						"Description"		= convert(char(72), jre_directives_description)
							from pca_jre_directives
								order by jre_directives_index
				end
				else /* unformatted report */
				begin
					select
						jre_directives_name,
						jre_directives_enabled,
						jre_directives_description
							from pca_jre_directives
								order by jre_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('argnames' like @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select distinct
						"Argument Name" = convert(char(32), b.jre_args_name),
						"Arg Units" = convert (char(10), b.jre_args_units),
						"Directive" = convert(char(27), a.jre_directives_name)
						from pca_jre_directives a, pca_jre_arguments b
							where b.jre_args_directive_index = a.jre_directives_index
							order by b.jre_args_name
				end
				else /* unformatted report */
				begin
					select distinct
						b.jre_args_name,
						b.jre_args_units,
						a.jre_directives_name
						from pca_jre_directives a, pca_jre_arguments b
							where b.jre_args_directive_index = a.jre_directives_index
							order by b.jre_args_name
				end
			end
			else if (@name IS NOT NULL) and ('enabled' like @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive" = convert(char(27), a.jre_directives_name),
						"Active" = convert(char(4), a.jre_directives_enabled),
						"Argument Name" = convert(char(32), b.jre_args_name), 
						"Units" = convert(char(10), b.jre_args_units),
						CASE
							when b.jre_args_units = 'string'	then convert(char(32), '"' + b.jre_args_string_value + '"')
							when b.jre_args_units = 'array' 	then convert(char(32), '"' + b.jre_args_string_value + '"')
							else ''
						END as 'String Value',
						CASE
							when b.jre_args_units = 'number'	then convert(char(8), b.jre_args_number_value)
							else ''
						END as 'Number Value',
						"Enabled" = convert(char(4), b.jre_args_enabled)
							from pca_jre_directives a, pca_jre_arguments b
								where b.jre_args_enabled = 1 and a.jre_directives_enabled = 1 and
									b.jre_args_directive_index = a.jre_directives_index
								order by a.jre_directives_index
				end
				else /* unformatted report */
				begin
					select
						a.jre_directives_name,
						a.jre_directives_enabled,
						b.jre_args_name, 
						b.jre_args_units,
						b.jre_args_string_value,
						b.jre_args_number_value,
						b.jre_args_enabled
							from pca_jre_directives a, pca_jre_arguments b
								where b.jre_args_enabled = 1 and a.jre_directives_enabled = 1 and
									b.jre_args_directive_index = a.jre_directives_index
								order by a.jre_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('disabled' like @name + "%") and (@name like "dis%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive" = convert(char(27), a.jre_directives_name),
						"Active" = convert(char(4), a.jre_directives_enabled),
						"Argument Name" = convert(char(32), b.jre_args_name), 
						"Units" = convert(char(10), b.jre_args_units),
						CASE
							when b.jre_args_units = 'string'	then convert(char(32), '"' + b.jre_args_string_value + '"')
							when b.jre_args_units = 'array' 	then convert(char(32), '"' + b.jre_args_string_value + '"')
							else ''
						END as 'String Value',
						CASE
							when b.jre_args_units = 'number'	then convert(char(8), b.jre_args_number_value)
							else ''
						END as 'Number Value',
						"Enabled" = convert(char(4), b.jre_args_enabled)
						from pca_jre_directives a, pca_jre_arguments b
							where (a.jre_directives_enabled = 0 or
								(a.jre_directives_enabled = 1 and b.jre_args_enabled = 0)) and
								b.jre_args_directive_index = a.jre_directives_index
							order by a.jre_directives_index
				end
				else /* unformatted report */
				begin
					select
						a.jre_directives_name,
						a.jre_directives_enabled,
						b.jre_args_name, 
						b.jre_args_units,
						b.jre_args_string_value,
						b.jre_args_number_value,
						b.jre_args_enabled
						from pca_jre_directives a, pca_jre_arguments b
							where (a.jre_directives_enabled = 0 or
								(a.jre_directives_enabled = 1 and b.jre_args_enabled = 0)) and
								b.jre_args_directive_index = a.jre_directives_index
							order by a.jre_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('units' like @name + "%")
			begin
				if (@opt1 IS NOT NULL)
				begin
					/*
					** count the number of arguments that match the criteria
					*/
					select @count = count(distinct jre_args_units)
						from pca_jre_arguments
							where jre_args_units like "%" + @opt1 + "%"
					/*
					** store the actual argument name in case a partial string was supplied
					** this actual name is used for reporting only
					*/
					select @full_argname = jre_args_units
						from pca_jre_arguments
							where jre_args_units like "%" + @opt1 + "%"
	
					/*
					** We have at least one argument to report
					*/
					if (@count > 0)
					begin
						if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
						begin
							select
								"Argument Name" = convert(char(32), jre_args_name), 
								"Units" = convert(char(10), jre_args_units),
								CASE
									when jre_args_units = 'string'		then convert(char(32), '"' + jre_args_string_value + '"')
									when jre_args_units = 'array' 		then convert(char(32), '"' + jre_args_string_value + '"')
									else ''
								END as 'String Value',
								CASE
									when jre_args_units = 'number'  	then convert(char(8), jre_args_number_value)
									else ''
								END as 'Number Value',
								"Enabled" = convert(char(4), jre_args_enabled)
									from pca_jre_arguments
										where jre_args_units like @opt1 + "%"
						end
						else /* unformatted report */
						begin
							select
								jre_args_name, 
								jre_args_units,
								jre_args_string_value,
								jre_args_number_value,
								jre_args_enabled
									from pca_jre_arguments
										where jre_args_units like @opt1 + "%"
						end
					end
					/*
					** No records in the table have units = @opt1
					*/
					else
					begin
						/* 19906: "record not found: '%1!', '%2!'" */
						raiserror 19906, @name, @opt1

						return (1)
					end
				end
				else if (@opt1 IS NULL)
				begin
					select distinct
						"Units" = convert(char(10), jre_args_units),
						CASE
							when jre_args_units = 'switch'		then convert(char(80), 'can only be enabled or disabled and do not have stored values')
							when jre_args_units = 'string'		then convert(char(80), 'can be enabled or disabled and also have a stored string value')
							when jre_args_units = 'array'		then convert(char(80), 'can be enabled or disabled and can also have multiple stored string values')
							when jre_args_units = 'number'		then convert(char(80), 'can be enabled or disabled and also have a stored number value')
						END as 'Description'
							from pca_jre_arguments
				end
			end
			else
			begin
				/* 19904: "list command option is invalid or ambiguous: '%1!'" */
				raiserror 19904, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('enable' like @cmd + "%")
	begin
		/*
		** ENABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_jreconfig 'enable'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'enable', 'name' [, 'optional_value']"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select * from pca_jre_directives where jre_directives_name = @name)
			begin
				select @count = count(*) 
					from pca_jre_directives
						where jre_directives_name = @name
				if (@count = 1)
				begin
					update pca_jre_directives
						set jre_directives_enabled = 1
						where jre_directives_name = @name and
							jre_directives_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19925: "Supplied value matches multiple directive names: '%1!'" */
					raiserror 19925, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_arguments where jre_args_name = @name)
			begin
				/*
				** We got here with an exact match on args_name, so there is no need
				** to do a distinct count like we do on a fuzzy match below. With an
				** exact match there can only be one arg_name that matches.
				*/

				/*
				** store the units and save the arg name in @full_argname
				*/
				select @units = jre_args_units, @full_argname = jre_args_name
					from pca_jre_arguments
						where jre_args_name = @name

				if (@units = 'switch') or (@units = 'string') or (@units = 'number')
				begin
					/*
					** if the row is not already enabled, we enable it
					*/
					update pca_jre_arguments
						set jre_args_enabled = 1
						where jre_args_name = @name and
							jre_args_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'enable', 'name', 'string_value'"

						return (1)
					end
					/*
					** since we support matching against the path portion only, ignoring the mask
					** we first need to check the value the user gave us (in $opt1). If it contains
					** a mask, we can do only an exact match on the full string (a partial match 
					** that contains a mask doesn't make sense)
					*/
					select @has_mask = 0
					select @lparen = charindex(char(40), @opt1)
					select @rparen = charindex(char(41), @opt1)
					select @length = len(@opt1)
					select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))

					if (@lparen = 0) and (@rparen != 0)
					begin
						/* Error: only a right paren found*/

						/* 19916: "Syntax Error: unmatched paren found" */
						raiserror 19916

						return (1)
					end
					else if (@rparen = 0) and (@lparen != 0)
					begin
						/* Error: only a left paren found*/

						/* 19916: "Syntax Error: unmatched paren found" */
						raiserror 19916

						return (1)
					end
					else if (@rparen != 0) and (@rparen != @length)
					begin
						/* Error: the first right paren is not on the end of the string */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen = 1)
					begin
						/* Error: left paren is the first character of the string! */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen != 0) and (@lparen2 != 0)
					begin
						/* Error: found 2 left parens */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen != 0) and (@rparen = @length)
					begin
						/*
						** value is ok - we do have a mask
						** so we set has_mask to 1 to indicate that the orig value has a supplied mask
						*/
						select @has_mask = 1
					end
					else if (@lparen = 0) and (@rparen = 0)
					begin
						/*
						** Value is ok - does not have a permission mask
						** so we set has_mask to 0 to indicate that the orig value did not have a mask
						*/
						select @has_mask = 0
					end
					else
					begin
						/* Error: unknown error - should never land here - report the "invalid parens" message */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end

					/*
					** If the user specified a mask, then our only choice is to do an exact match
					** On the other hand, if we didn't get a match we try an exact match first, on
					** the path portion only, and if no match there we check for a fuzzy match on path only.
					** In any case, we will only do the enable if we match exactly one item
					*/
					if (@has_mask = 1)
					begin
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = "pca_jvm_work_dir" and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 1
								where jre_args_name = "pca_jvm_work_dir" and
									jre_args_string_value = @opt1 and
									jre_args_enabled != 1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
						else /* @count > 1 */
						begin
							/*
							** We should never get here on an exact match
							*/

							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* @has_mask = 0 */
					begin
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = "pca_jvm_work_dir" and
									(jre_args_string_value = @opt1 or
									jre_args_string_value like @opt1 + "[(]%[)]")
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 1
								where jre_args_name = "pca_jvm_work_dir" and
									(jre_args_string_value = @opt1  or
									jre_args_string_value like @opt1 + "[(]%[)]") and
									jre_args_enabled != 1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** We did not have an exact match - now try a fuzzy match using wildcards
							** we can only continue if we match exactly one row
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
										(jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 1
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
										(jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]")) and
										jre_args_enabled != 1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
				end
				else if (@units = 'array') and (@full_argname like 'pca_jvm_ext_class_loader' + '%')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'enable', 'name', 'db_name'"

						return (1)
					end
					/*
					** This section deals with both types of Extension Class Loaders. In both cases we are
					** going to expect that a database name has been supplied.
					**
					** Syntax:
					** 	sp_jreconfig 'enable', 'global, 'db1'
 					** this example will enable all of the jars for the Global Ext Class Loader that use database db1
					**
					**
					** Validation:
					** The only validation we will do on the supplied database name is to make sure it does not
					** include a colon character ':'. If it does, the user has probably supplied a "database:jar" pair
					** and is confused. In this case we want to give them a useful message so they quickly realize
					** what they did wrong.
					*/
					if (charindex(':', @opt1) != 0)
					begin
						/* 19912: "Syntax Error: enable by database only, no support for enabling individual jar files" */
						raiserror 19912

						return (1)
					end

					/*
					** Check to see if we have any rows in the table that match the argument name
					** and have database set to @opt1 which are disabled
					*/
					if exists (select 1 from pca_jre_arguments
							where jre_args_name = @full_argname and
								jre_args_string_value like @opt1 + ":%")
					begin
						update pca_jre_arguments
							set jre_args_enabled = 1
							where jre_args_name = @full_argname and
								jre_args_string_value like @opt1 + ":%" and
								jre_args_enabled != 1
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* no matching rows */
					begin
						/* 19906: "record not found: '%1!', '%2!'" */
						raiserror 19906, @name, @opt1

						return (1)
					end
				end
				else if (@units = 'array')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'enable', 'name', 'string_value'"

						return (1)
					end
					/*
					** make sure we match only 1 row to continue
					** first using an exact match, and then if no match,
					** then we do a fuzzy match below
					*/
					select @count = count(*) 
						from pca_jre_arguments
							where jre_args_name = @name and
								jre_args_string_value = @opt1
					if (@count = 1)
					begin
						update pca_jre_arguments
							set jre_args_enabled = 1
							where jre_args_name = @name and
								jre_args_string_value = @opt1 and
								jre_args_enabled != 1
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else if (@count = 0)
					begin
						/*
						** We did not have an exact match - now try a fuzzy match using wildcards
						** we can only continue if we match exactly one row
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = @name and
									jre_args_string_value like "%" + @opt1 + "%"
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 1
								where jre_args_name = @name and
									jre_args_string_value like "%" + @opt1 + "%" and
									jre_args_enabled != 1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* @count > 1 */
					begin
						/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
						raiserror 19909, @name, @opt1

						return (1)
					end
				end
				else /* not recognized units type */
				begin
					/* 19903: "Internal error: units type is not recognized: '%1!'" */
					raiserror 19903, @units

					return (1)
				end
			end
			else if exists (select * from pca_jre_directives where jre_directives_name like "%" + @name + "%")
			begin
				select @count = count(*) 
					from pca_jre_directives
						where jre_directives_name like "%" + @name + "%"
				if (@count = 1)
				begin
					update pca_jre_directives
						set jre_directives_enabled = 1
						where jre_directives_name like "%" + @name + "%" and
							jre_directives_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19925: "Supplied value matches multiple directive names: '%1!'" */
					raiserror 19925, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct jre_args_name)
					from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = jre_args_units, @full_argname = jre_args_name
						from pca_jre_arguments
							where jre_args_name like "%" + @name + "%"
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already enabled, we enable it
						*/
						update pca_jre_arguments
							set jre_args_enabled = 1
							where jre_args_name like "%" + @name + "%" and
								jre_args_enabled != 1
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'enable', 'name', 'string_value'"

							return (1)
						end
						/*
						** since we support matching against the path portion only, ignoring the mask
						** we first need to check the value the user gave us (in $opt1). If it contains
						** a mask, we can do only an exact match on the full string (a partial match 
						** that contains a mask doesn't make sense)
						*/
						select @has_mask = 0
						select @lparen = charindex(char(40), @opt1)
						select @rparen = charindex(char(41), @opt1)
						select @length = len(@opt1)
						select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))
						if (@lparen = 0) and (@rparen != 0)
						begin
							/* Error: only a right paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen = 0) and (@lparen != 0)
						begin
							/* Error: only a left paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen != 0) and (@rparen != @length)
						begin
							/* Error: the first right paren is not on the end of the string */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen = 1)
						begin
							/* Error: left paren is the first character of the string! */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@lparen2 != 0)
						begin
							/* Error: found 2 left parens */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@rparen = @length)
						begin
							/*
							** value is ok - we do have a mask
							** so we set has_mask to 1 to indicate that the orig value has a supplied mask
							*/
							select @has_mask = 1
						end
						else if (@lparen = 0) and (@rparen = 0)
						begin
							/*
							** Value is ok - does not have a permission mask
							** so we set has_mask to 0 to indicate that the orig value did not have a mask
							*/
							select @has_mask = 0
						end
						else
						begin
							/* Error: unknown error - should never land here - report the "invalid parens" message */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end

						/*
						** If the user specified a mask, then our only choice is to do an exact match
						** On the other hand, if we didn't get a match we try an exact match first, on
						** the path portion only, and if no match there we check for a fuzzy match on path only.
						** In any case, we will only do the enable if we match exactly one item
						*/
						if (@has_mask = 1)
						begin
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 1
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1 and
										jre_args_enabled != 1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/*
								** We should never get here on an exact match
								*/

								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @has_mask = 0 */
						begin
							/*
							** We first do an exact match on the path portion (ignoring the mask when present)
							** For the value to be a match it must perfectly match the string up to the '(' that
							** starts the mask.
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 1
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]") and
										jre_args_enabled != 1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** We did not have an exact match - now try a fuzzy match on the path only
								** which means we ignore the mask if there is one
								** we can only continue if we match exactly one row
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
								if (@count = 1)
								begin
									update pca_jre_arguments
										set jre_args_enabled = 1
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]")) and
											jre_args_enabled != 1
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19923: "Update Failed - Transaction Rolled Back" */
										raiserror 19923

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array') and (@full_argname like 'pca_jvm_ext_class_loader' + '%')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'enable', 'name', 'db_name'"

							return (1)
						end
						/*
						** This section deals with both types of Extension Class Loaders. In both cases we are
						** going to expect that a database name has been supplied.
						**
						** Syntax:
						** 	sp_jreconfig 'enable', 'global, 'db1'
	 					** this example will enable all of the jars for the Global Ext Class Loader that use database db1
						**
						**
						** Validation:
						** The only validation we will do on the supplied database name is to make sure it does not
						** include a colon character ':'. If it does, the user has probably supplied a "database:jar" pair
						** and is confused. In this case we want to give them a useful message so they quickly realize
						** what they did wrong.
						*/
						if (charindex(':', @opt1) != 0)
						begin
							/* 19912: "Syntax Error: enable by database only, no support for enabling individual jar files" */
							raiserror 19912

							return (1)
						end
	
						/*
						** Check to see if we have any rows in the table that match the argument name
						** and have database set to @opt1 which are disabled
						*/
						if exists (select 1 from pca_jre_arguments
								where jre_args_name = @full_argname and
									jre_args_string_value like @opt1 + ":%")
						begin
							update pca_jre_arguments
								set jre_args_enabled = 1
								where jre_args_name = @full_argname and
									jre_args_string_value like @opt1 + ":%" and
									jre_args_enabled != 1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else /* no matching rows */
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
					end
					else if (@units = 'array')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'enable', 'name', 'string_value'"

							return (1)
						end
						/*
						** make sure we match only 1 row to continue
						**
						** if no match, then we do a fuzzy match next
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 1
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1 and
									jre_args_enabled != 1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** We did not have an exact match - now try a fuzzy match using wildcards
							** we can only continue if we match exactly one row
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%"
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 1
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_enabled != 1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @name does not match any of the jre_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('disable' like @cmd + "%") and (@cmd like "di%") /* must be at least 'di' to be unique */
	begin
		/*
		** DISABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_jreconfig 'disable'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'disable', 'name'"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select * from pca_jre_directives where jre_directives_name = @name)
			begin
				select @count = count(*) 
					from pca_jre_directives
						where jre_directives_name = @name
				if (@count = 1)
				begin
					update pca_jre_directives
						set jre_directives_enabled = 0
						where jre_directives_name = @name and
							jre_directives_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_arguments where jre_args_name = @name)
			begin
				/*
				** We got here with an exact match on args_name, so there is no need
				** to do a distinct count like we do on a fuzzy match below. With an
				** exact match there can only be one arg_name that matches.
				*/

				/*
				** store the units and save the arg name in @full_argname
				*/
				select @units = jre_args_units, @full_argname = jre_args_name
					from pca_jre_arguments
						where jre_args_name = @name

				if (@units = 'switch') or (@units = 'string') or (@units = 'number')
				begin
					/*
					** if the row is not already disabled, we disable it
					*/
					update pca_jre_arguments
						set jre_args_enabled = 0
						where jre_args_name = @name and
							jre_args_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'disable', 'name', 'string_value'"

						return (1)
					end
					/*
					** since we support matching against the path portion only, ignoring the mask
					** we first need to check the value the user gave us (in $opt1). If it contains
					** a mask, we can do only an exact match on the full string (a partial match 
					** that contains a mask doesn't make sense)
					*/
					select @has_mask = 0
					select @lparen = charindex(char(40), @opt1)
					select @rparen = charindex(char(41), @opt1)
					select @length = len(@opt1)
					select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))

					if (@lparen = 0) and (@rparen != 0)
					begin
						/* Error: only a right paren found*/

						/* 19916: "Syntax Error: unmatched paren found" */
						raiserror 19916

						return (1)
					end
					else if (@rparen = 0) and (@lparen != 0)
					begin
						/* Error: only a left paren found*/

						/* 19916: "Syntax Error: unmatched paren found" */
						raiserror 19916

						return (1)
					end
					else if (@rparen != 0) and (@rparen != @length)
					begin
						/* Error: the first right paren is not on the end of the string */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen = 1)
					begin
						/* Error: left paren is the first character of the string! */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen != 0) and (@lparen2 != 0)
					begin
						/* Error: found 2 left parens */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end
					else if (@lparen != 0) and (@rparen = @length)
					begin
						/*
						** value is ok - we do have a mask
						** so we set has_mask to 1 to indicate that the orig value has a supplied mask
						*/
						select @has_mask = 1
					end
					else if (@lparen = 0) and (@rparen = 0)
					begin
						/*
						** Value is ok - does not have a permission mask
						** so we set has_mask to 0 to indicate that the orig value did not have a mask
						*/
						select @has_mask = 0
					end
					else
					begin
						/* Error: unknown error - should never land here - report the "invalid parens" message */

						/* 19915: "Syntax Error: invalid paren(s) found" */
						raiserror 19915

						return (1)
					end

					/*
					** If the user specified a mask, then our only choice is to do an exact match
					** On the other hand, if we didn't get a match we try an exact match first, on
					** the path portion only, and if no match there we check for a fuzzy match on path only.
					** In any case, we will only do the enable if we match exactly one item
					*/
					if (@has_mask = 1)
					begin
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = "pca_jvm_work_dir" and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 0
								where jre_args_name = "pca_jvm_work_dir" and
									jre_args_string_value = @opt1 and
									jre_args_enabled != 0
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
						else /* @count > 1 */
						begin
							/*
							** We should never get here on an exact match
							*/
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* @has_mask = 0 */
					begin
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = "pca_jvm_work_dir" and
									(jre_args_string_value = @opt1 or
									jre_args_string_value like @opt1 + "[(]%[)]")
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 0
								where jre_args_name = "pca_jvm_work_dir" and
									(jre_args_string_value = @opt1  or
									jre_args_string_value like @opt1 + "[(]%[)]") and
									jre_args_enabled != 0
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** We did not have an exact match - now try a fuzzy match using wildcards
							** we can only continue if we match exactly one row
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
										(jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 0
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
										(jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]")) and
										jre_args_enabled != 0
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
				end
				else if (@units = 'array') and (@full_argname like 'pca_jvm_ext_class_loader' + '%')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'disable', 'name', 'db_name'"

						return (1)
					end
					/*
					** This section deals with both types of Extension Class Loaders. In both cases we are
					** going to expect that a database name has been supplied.
					**
					** Syntax:
					** 	sp_jreconfig 'disable', 'global, 'db1'
 					** this example will disable all of the jars for the Global Ext Class Loader that use database db1
					**
					**
					** Validation:
					** The only validation we will do on the supplied database name is to make sure it does not
					** include a colon character ':'. If it does, the user has probably supplied a "database:jar" pair
					** and is confused. In this case we want to give them a useful message so they quickly realize
					** what they did wrong.
					*/
					if (charindex(':', @opt1) != 0)
					begin
						/* 19911: "Syntax Error: disable by database only, no support for disabling individual jar files" */
						raiserror 19911

						return (1)
					end

					/*
					** Check to see if we have any rows in the table that match the argument name
					** and have database set to @opt1 which are disabled
					*/
					if exists (select 1 from pca_jre_arguments
							where jre_args_name = @full_argname and
								jre_args_string_value like @opt1 + ":%")
					begin
						update pca_jre_arguments
							set jre_args_enabled = 0
							where jre_args_name = @full_argname and
								jre_args_string_value like @opt1 + ":%" and
								jre_args_enabled != 0
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* no matching rows */
					begin
						/* 19906: "record not found: '%1!', '%2!'" */
						raiserror 19906, @name, @opt1

						return (1)
					end
				end
				else if (@units = 'array')
				begin
					if (@opt1 IS NULL)
					begin
						/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
						raiserror 19898, "sp_jreconfig 'disable', 'name', 'string_value'"

						return (1)
					end
					/*
					** Validate arguments
					** Arguments for properties of  type 'array' use a slightly different syntax than
					** other types because with arrays we have to identify a single row.
					** 	1. arg1 is the command, in this case 'enable'
					** 	2. arg2 is the arg_name and must uniquely identify a single argument name
					** 	3. arg3 is a string that identifies one of the arguments in the array
					** 	4. arg4 is not used
					** Example:
					** sp_jreconfig 'enable', 'work_dir', '/tmp'
					**
					**
					** count the number of rows we match with @name and @opt1.
					** we must match only 1 row to continue
					**
					** First we check for an exact match, if no match, then we do a fuzzy match
					*/
					select @count = count(*) 
						from pca_jre_arguments
							where jre_args_name = @name and
								jre_args_string_value = @opt1
					if (@count = 1)
					begin
						update pca_jre_arguments
							set jre_args_enabled = 0
							where jre_args_name = @name and
								jre_args_string_value = @opt1 and
								jre_args_enabled != 0
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else if (@count = 0)
					begin
						/*
						** We did not have an exact match - now try a fuzzy match using wildcards
						** we can only continue if we match exactly one row
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = @name and
									jre_args_string_value like "%" + @opt1 + "%"
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 0
								where jre_args_name = @name and
									jre_args_string_value like "%" + @opt1 + "%" and
									jre_args_enabled != 0
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* @count > 1 */
					begin
						/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
						raiserror 19909, @name, @opt1

						return (1)
					end
				end
				else /* not recognized units type */
				begin
					/* 19903: "Internal error: units type is not recognized: '%1!'" */
					raiserror 19903, @units

					return (1)
				end
			end
			else if exists (select * from pca_jre_directives where jre_directives_name like "%" + @name + "%")
			begin
				select @count = count(*) 
					from pca_jre_directives
						where jre_directives_name like "%" + @name + "%"
				if (@count = 1)
				begin
					update pca_jre_directives
						set jre_directives_enabled = 0
						where jre_directives_name like "%" + @name + "%" and
							jre_directives_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct jre_args_name)
					from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = jre_args_units, @full_argname = jre_args_name
						from pca_jre_arguments
							where jre_args_name like "%" + @name + "%"
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already disabled, we disable it
						*/
						update pca_jre_arguments
							set jre_args_enabled = 0
							where jre_args_name like "%" + @name + "%" and
								jre_args_enabled != 0
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'disable', 'name', 'string_value'"

							return (1)
						end
						/*
						** since we support matching against the path portion only, ignoring the mask
						** we first need to check the value the user gave us (in $opt1). If it contains
						** a mask, we can do only an exact match on the full string (a partial match 
						** that contains a mask doesn't make sense)
						*/
						select @has_mask = 0
						select @lparen = charindex(char(40), @opt1)
						select @rparen = charindex(char(41), @opt1)
						select @length = len(@opt1)
						select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))
						if (@lparen = 0) and (@rparen != 0)
						begin
							/* Error: only a right paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen = 0) and (@lparen != 0)
						begin
							/* Error: only a left paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen != 0) and (@rparen != @length)
						begin
							/* Error: the first right paren is not on the end of the string */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen = 1)
						begin
							/* Error: left paren is the first character of the string! */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@lparen2 != 0)
						begin
							/* Error: found 2 left parens */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@rparen = @length)
						begin
							/*
							** value is ok - we do have a mask
							** so we set has_mask to 1 to indicate that the orig value has a supplied mask
							*/
							select @has_mask = 1
						end
						else if (@lparen = 0) and (@rparen = 0)
						begin
							/*
							** Value is ok - does not have a permission mask
							** so we set has_mask to 0 to indicate that the orig value did not have a mask
							*/
							select @has_mask = 0
						end
						else
						begin
							/* Error: unknown error - should never land here - report the "invalid parens" message */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end

						/*
						** If the user specified a mask, then our only choice is to do an exact match
						** On the other hand, if we didn't get a match we try an exact match first, on
						** the path portion only, and if no match there we check for a fuzzy match on path only.
						** In any case, we will only do the enable if we match exactly one item
						*/
						if (@has_mask = 1)
						begin
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 0
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1 and
										jre_args_enabled != 0
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/*
								** We should never get here on an exact match
								*/
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @has_mask = 0 */
						begin
							/*
							** We first do an exact match on the path portion (ignoring the mask when present)
							** For the value to be a match it must perfectly match the string up to the '(' that
							** starts the mask.
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 0
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]") and
										jre_args_enabled != 0
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** We did not have an exact match - now try a fuzzy match on the path only
								** which means we ignore the mask if there is one
								** we can only continue if we match exactly one row
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
								if (@count = 1)
								begin
									update pca_jre_arguments
										set jre_args_enabled = 0
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]")) and
											jre_args_enabled != 0
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19923: "Update Failed - Transaction Rolled Back" */
										raiserror 19923

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array') and (@full_argname like 'pca_jvm_ext_class_loader' + '%')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'disable', 'name', 'db_name'"

							return (1)
						end
						/*
						** This section deals with both types of Extension Class Loaders. In both cases we are
						** going to expect that a database name has been supplied.
						**
						** Syntax:
						** 	sp_jreconfig 'disable', 'global, 'db1'
	 					** this example will disable all of the jars for the Global Ext Class Loader that use database db1
						**
						**
						** Validation:
						** The only validation we will do on the supplied database name is to make sure it does not
						** include a colon character ':'. If it does, the user has probably supplied a "database:jar" pair
						** and is confused. In this case we want to give them a useful message so they quickly realize
						** what they did wrong.
						*/
						if (charindex(':', @opt1) != 0)
						begin
							/* 19911: "Syntax Error: disable by database only, no support for disabling individual jar files" */
							raiserror 19911

							return (1)
						end
	
						/*
						** Check to see if we have any rows in the table that match the argument name
						** and have database set to @opt1 which are disabled
						*/
						if exists (select 1 from pca_jre_arguments
								where jre_args_name = @full_argname and
									jre_args_string_value like @opt1 + ":%")
						begin
							update pca_jre_arguments
								set jre_args_enabled = 0
								where jre_args_name = @full_argname and
									jre_args_string_value like @opt1 + ":%" and
									jre_args_enabled != 0
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else /* no matching rows */
						begin
							/* 19906: "record not found: '%1!', '%2!'" */
							raiserror 19906, @name, @opt1

							return (1)
						end
					end
					else if (@units = 'array')
					begin
						if (@opt1 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'disable', 'name', 'string_value'"

							return (1)
						end
						/*
						** Validate arguments
						** Arguments for properties of  type 'array' use a slightly different syntax than
						** other types because with arrays we have to identify a single row.
						** 	1. arg1 is the command, in this case 'enable'
						** 	2. arg2 is the arg_name and must uniquely identify a single argument name
						** 	3. arg3 is a string that identifies one of the arguments in the array
						** 	4. arg4 is not used
						** Example:
						** sp_jreconfig 'enable', 'work_dir', '/tmp'
						**
						**
						** count the number of rows we match with @name and @opt1.
						** we must match only 1 row to continue
						**
						** First we check for an exact match, if no match, then we do a fuzzy match
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							update pca_jre_arguments
								set jre_args_enabled = 0
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1 and
									jre_args_enabled != 0
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** We did not have an exact match - now try a fuzzy match using wildcards
							** we can only continue if we match exactly one row
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%"
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_enabled = 0
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%" and
										jre_args_enabled != 0
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @name does not match any of the jre_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('update' like @cmd + "%")
	begin
		/*
		** UPDATE command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'update', 'arg_name', 'old_value', 'new_value'  "

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** In the case of the update command, we don't check for directives at all until we have tried both an exact
			** match as well as a fuzzy match on argument name. This is because directives cannot be updated.
			** 1. check for exact match on argument name
			** 2. next, we do a fuzzy match on argument name
			** 3. finally, if we don't have any match on argument name, we can test against directive to give the user a
			** 	meaningful error message if they try to update a directive.
			** 4. if no match on directive either, then we report the "record not found" error message
			*/
			if exists (select * from pca_jre_arguments where jre_args_name = @name)
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct jre_args_name)
					from pca_jre_arguments
						where jre_args_name = @name
				/*
				** The update can only be done if @count = 1 which means that @name is distinct
				** enough to match only one argument name
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name could have been supplied as a partial string)
					*/
					select @units = jre_args_units, @full_argname = jre_args_name
						from pca_jre_arguments
							where jre_args_name = @name
					if (@units = 'switch')
					begin
						/*
						** Using the 'update' command on a switch is not valid, so if we get a switch here
						** we report a usage/syntax error and refer to the usage/help screen
						*/

						/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
						raiserror 19927, 'update', @units

						return (1)
					end
					else if (@units = 'string')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'string_arg', 'old_string', 'new_string'"

							return (1)
						end
						else
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** string_value field and if not we report the error and skip the update
							*/
							if exists (select * from pca_jre_arguments where jre_args_name = @name
									and jre_args_string_value = @opt1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
										where jre_args_name = @name
											and jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing string_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'number')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'number_arg', 'old_value', 'new_value'"

							return (1)
						end
						else if (isnumeric(@opt1) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt1

							return (1)
						end
						else if (isnumeric(@opt2) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt2

							return (1)
						end
						else /* all is ok, go forward with the update */
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** number_value field and if not we report the error and skip the update
							*/
							if exists (select * from pca_jre_arguments where jre_args_name = @name
									and jre_args_number_value = convert(int, @opt1))
							begin
								update pca_jre_arguments
									set jre_args_number_value = convert(int, @opt2)
										where jre_args_name = @name
											and jre_args_number_value = convert(int, @opt1)
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing number_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'array_arg', 'old_string', 'new_string'"

							return (1)
						end
						/*
						** since we support matching against the path portion only, ignoring the mask
						** we first need to check the value the user gave us (in $opt1). If it contains
						** a mask, we can do only an exact match on the full string (a partial match 
						** that contains a mask doesn't make sense)
						*/
						select @has_mask = 0
						select @lparen = charindex(char(40), @opt1)
						select @rparen = charindex(char(41), @opt1)
						select @length = len(@opt1)
						select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))

						if (@lparen = 0) and (@rparen != 0)
						begin
							/* Error: only a right paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen = 0) and (@lparen != 0)
						begin
							/* Error: only a left paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen != 0) and (@rparen != @length)
						begin
							/* Error: the first right paren is not on the end of the string */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen = 1)
						begin
							/* Error: left paren is the first character of the string! */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@lparen2 != 0)
						begin
							/* Error: found 2 left parens */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@rparen = @length)
						begin
							/*
							** value is ok - we do have a mask
							** so we set has_mask to 1 to indicate that the orig value has a supplied mask
							*/
							select @has_mask = 1
						end
						else if (@lparen = 0) and (@rparen = 0)
						begin
							/*
							** Value is ok - does not have a permission mask
							** so we set has_mask to 0 to indicate that the orig value did not have a mask
							*/
							select @has_mask = 0
						end
						else
						begin
							/* Error: unknown error - should never land here - report the "invalid parens" message */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end

						/*
						** If the user specified a mask, then our only choice is to do an exact match
						** On the other hand, if we didn't get a match we try an exact match first, on
						** the path portion only, and if no match there we check for a fuzzy match on path only.
						** In any case, we will only do the enable if we match exactly one item
						*/
						if (@has_mask = 1)
						begin
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/*
								** We should never get here on an exact match
								*/
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @has_mask = 0 */
						begin
							/*
							** We first do an exact match on the path portion (ignoring the mask when present)
							** For the value to be a match it must perfectly match the string up to the '(' that
							** starts the mask.
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** We did not have an exact match - now try a fuzzy match on the path only
								** which means we ignore the mask if there is one
								** we can only continue if we match exactly one row
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
								if (@count = 1)
								begin
									update pca_jre_arguments
										set jre_args_string_value = @opt2
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19923: "Update Failed - Transaction Rolled Back" */
										raiserror 19923

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'array_arg', 'old_string', 'new_string'"

							return (1)
						end
						/*
						** Check for an exact match on the old string value (@opt1)
						** if we don't find it, then we do a fuzzy match down below
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name = @name and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							/*
							** Before we continue with the update we need to see if this
							** is an Extension Class Loader and if it is, we need to trim
							** any whitespace the user may have placed between the database
							** and jar names in the new_string (@opt2)
							** example: 'db1  :  jar' -> 'db1:jar'
							*/
							select @full_argname = jre_args_name
								from pca_jre_arguments
								where jre_args_name = @name and
									jre_args_string_value = @opt1
							if (@full_argname like 'pca_jvm_ext_class_loader' + '%')
							begin
								select @notab_str = str_replace(str_replace(str_replace(@opt2, char(10), ''), char(13), ''), char(9), '')
								select @dbname  = rtrim(ltrim(substring(@notab_str, 1, charindex(':', @notab_str) - 1)))
								select @jarname = rtrim(ltrim(substring(@notab_str, charindex(':', @notab_str) + 1, len(@notab_str) - charindex(':', @notab_str))))
								select @trimmed_val = @dbname + ':' + @jarname
								
							end
							else
							begin
								select @trimmed_val = @opt2
							end
							update pca_jre_arguments
								set jre_args_string_value = @trimmed_val
								where jre_args_name = @name and
									jre_args_string_value = @opt1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** Next we try a fuzzy match
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = @name and
										jre_args_string_value like "%" + @opt1 + "%"
							if (@count = 1)
							begin
								/*
								** Before we continue with the update we need to see if this
								** is an Extension Class Loader and if it is, we need to trim
								** any whitespace the user may have placed between the database
								** and jar names in the new_string (@opt2)
								** example: 'db1  :  jar' -> 'db1:jar'
								** It is important that this string be cleaned up before we call update
								*/
								select @full_argname = jre_args_name
									from pca_jre_arguments
									where jre_args_name = @name and
										jre_args_string_value like "%" + @opt1 + "%"
								if (@full_argname like 'pca_jvm_ext_class_loader' + '%')
								begin
									select @notab_str = str_replace(str_replace(str_replace(@opt2, char(10), ''), char(13), ''), char(9), '')
									select @dbname  = rtrim(ltrim(substring(@notab_str, 1, charindex(':', @notab_str) - 1)))
									select @jarname = rtrim(ltrim(substring(@notab_str, charindex(':', @notab_str) + 1, len(@notab_str) - charindex(':', @notab_str))))
									select @trimmed_val = @dbname + ':' + @jarname
									
								end
								else
								begin
									select @trimmed_val = @opt2
								end
								update pca_jre_arguments
									set jre_args_string_value = @trimmed_val
									where jre_args_name = @name and
										jre_args_string_value like "%" + @opt1 + "%"
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count >1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count >1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/*
					** with an exact match we should never have >1 match
					** but in case that happens, we will catch it here
					*/
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				else /* count = 0 */
				begin
					/*
					** it is not likely that we should ever land here
					*/
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct jre_args_name)
					from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
				/*
				** The update can only be done if @count = 1 which means that @name is distinct
				** enough to match only one argument name
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name could have been supplied as a partial string)
					*/
					select @units = jre_args_units, @full_argname = jre_args_name
						from pca_jre_arguments
							where jre_args_name like "%" + @name + "%"
					if (@units = 'switch')
					begin
						/*
						** Using the 'update' command on a switch is not valid, so if we get a switch here
						** we report a usage/syntax error and refer to the usage/help screen
						*/
						/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
						raiserror 19927, 'update', @units

						return (1)
					end
					else if (@units = 'string')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'string_arg', 'old_string', 'new_string'"

							return (1)
						end
						else
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** string_value field and if not we report the error and skip the update
							*/
							if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%"
									and jre_args_string_value = @opt1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
										where jre_args_name like "%" + @name + "%"
											and jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing string_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'number')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'number_arg', 'old_value', 'new_value'"

							return (1)
						end
						else if (isnumeric(@opt1) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt1

							return (1)
						end
						else if (isnumeric(@opt2) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt2

							return (1)
						end
						else /* all is ok, go forward with the update */
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** number_value field and if not we report the error and skip the update
							*/
							if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%"
									and jre_args_number_value = convert(int, @opt1))
							begin
								update pca_jre_arguments
									set jre_args_number_value = convert(int, @opt2)
										where jre_args_name like "%" + @name + "%"
											and jre_args_number_value = convert(int, @opt1)
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing number_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'array_arg', 'old_string', 'new_string'"

							return (1)
						end
						/*
						** since we support matching against the path portion only, ignoring the mask
						** we first need to check the value the user gave us (in $opt1). If it contains
						** a mask, we can do only an exact match on the full string (a partial match 
						** that contains a mask doesn't make sense)
						*/
						select @has_mask = 0
						select @lparen = charindex(char(40), @opt1)
						select @rparen = charindex(char(41), @opt1)
						select @length = len(@opt1)
						select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))

						if (@lparen = 0) and (@rparen != 0)
						begin
							/* Error: only a right paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen = 0) and (@lparen != 0)
						begin
							/* Error: only a left paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen != 0) and (@rparen != @length)
						begin
							/* Error: the first right paren is not on the end of the string */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen = 1)
						begin
							/* Error: left paren is the first character of the string! */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@lparen2 != 0)
						begin
							/* Error: found 2 left parens */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@rparen = @length)
						begin
							/*
							** value is ok - we do have a mask
							** so we set has_mask to 1 to indicate that the orig value has a supplied mask
							*/
							select @has_mask = 1
						end
						else if (@lparen = 0) and (@rparen = 0)
						begin
							/*
							** Value is ok - does not have a permission mask
							** so we set has_mask to 0 to indicate that the orig value did not have a mask
							*/
							select @has_mask = 0
						end
						else
						begin
							/* Error: unknown error - should never land here - report the "invalid parens" message */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end

						/*
						** If the user specified a mask, then our only choice is to do an exact match
						** On the other hand, if we didn't get a match we try an exact match first, on
						** the path portion only, and if no match there we check for a fuzzy match on path only.
						** In any case, we will only do the enable if we match exactly one item
						*/
						if (@has_mask = 1)
						begin
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/*
								** We should never get here on an exact match
								*/
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @has_mask = 0 */
						begin
							/*
							** We first do an exact match on the path portion (ignoring the mask when present)
							** For the value to be a match it must perfectly match the string up to the '(' that
							** starts the mask.
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
							if (@count = 1)
							begin
								update pca_jre_arguments
									set jre_args_string_value = @opt2
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** We did not have an exact match - now try a fuzzy match on the path only
								** which means we ignore the mask if there is one
								** we can only continue if we match exactly one row
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
								if (@count = 1)
								begin
									update pca_jre_arguments
										set jre_args_string_value = @opt2
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19923: "Update Failed - Transaction Rolled Back" */
										raiserror 19923

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_jreconfig 'update', 'array_arg', 'old_string', 'new_string'"

							return (1)
						end
						/*
						** Check for an exact match on the old string value (@opt1)
						** if we don't find it, then we do a fuzzy match down below
						*/
						select @count = count(*) 
							from pca_jre_arguments
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1
						if (@count = 1)
						begin
							/*
							** Before we continue with the update we need to see if this
							** is an Extension Class Loader and if it is, we need to trim
							** any whitespace the user may have placed between the database
							** and jar names in the new_string (@opt2)
							** example: 'db1  :  jar' -> 'db1:jar'
							*/
							select @full_argname = jre_args_name
								from pca_jre_arguments
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1
							if (@full_argname like 'pca_jvm_ext_class_loader' + '%')
							begin
								select @notab_str = str_replace(str_replace(str_replace(@opt2, char(10), ''), char(13), ''), char(9), '')
								select @dbname  = rtrim(ltrim(substring(@notab_str, 1, charindex(':', @notab_str) - 1)))
								select @jarname = rtrim(ltrim(substring(@notab_str, charindex(':', @notab_str) + 1, len(@notab_str) - charindex(':', @notab_str))))
								select @trimmed_val = @dbname + ':' + @jarname
								
							end
							else
							begin
								select @trimmed_val = @opt2
							end
							update pca_jre_arguments
								set jre_args_string_value = @trimmed_val
								where jre_args_name like "%" + @name + "%" and
									jre_args_string_value = @opt1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19923: "Update Failed - Transaction Rolled Back" */
								raiserror 19923

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** Next we try a fuzzy match
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%"
							if (@count = 1)
							begin
								/*
								** Before we continue with the update we need to see if this
								** is an Extension Class Loader and if it is, we need to trim
								** any whitespace the user may have placed between the database
								** and jar names in the new_string (@opt2)
								** example: 'db1  :  jar' -> 'db1:jar'
								** It is important that this string be cleaned up before we call update
								*/
								select @full_argname = jre_args_name
									from pca_jre_arguments
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%"
								if (@full_argname like 'pca_jvm_ext_class_loader' + '%')
								begin
									select @notab_str = str_replace(str_replace(str_replace(@opt2, char(10), ''), char(13), ''), char(9), '')
									select @dbname  = rtrim(ltrim(substring(@notab_str, 1, charindex(':', @notab_str) - 1)))
									select @jarname = rtrim(ltrim(substring(@notab_str, charindex(':', @notab_str) + 1, len(@notab_str) - charindex(':', @notab_str))))
									select @trimmed_val = @dbname + ':' + @jarname
									
								end
								else
								begin
									select @trimmed_val = @opt2
								end
								update pca_jre_arguments
									set jre_args_string_value = @trimmed_val
									where jre_args_name like "%" + @name + "%" and
										jre_args_string_value like "%" + @opt1 + "%"
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count >1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @count >1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/*
					** our fuzzy match has matched >1 row, so we cannot
					** continue with the update. The supplied value must identify
					** a single row before we can do the update.
					*/
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				else /* count = 0 */
				begin
					/*
					** it is not likely that we should ever land here
					*/
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pca_jre_directives where jre_directives_name like "%" + @name + "%")
			begin
				/* 19926: "The '%1!' command cannot be used with Directives" */
				raiserror 19926, "update"

				return (1)
			end
			else /* @name does not match any of the jre_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('add' like @cmd + "%")
	begin
		/*
		** ADD command
		*/

		if (@name IS NULL) or (@opt1 IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'add', 'arg_name', 'string_value'"

			return (1)
		end
		else
		begin
			/*
			** Check to make sure supplied arg1 is not too general
			** it should not match more than one arg_name (or zero is ok too)
			*/
			select @count = count(distinct jre_args_name)
				from pca_jre_arguments
					where jre_args_name like "%" + @name + "%"
			if (@count > 1)
			begin
				/* 19924: "Supplied value matches multiple argument names: '%1!'" */
				raiserror 19924, @name

				return (1)
			end
	
			/*
			** Each of the array type arguments are handled individually with
			** respect to the 'add' command
			*/
			if (@name IS NOT NULL) and ('pca_jvm_work_dir' like "%" + @name + "%")
			begin
				/*
				** setup some variables used by the code below
				*/
				select @full_argname = 'pca_jvm_work_dir'
				select @units = 'array'
				select @directive_index = jre_directives_index
					from pca_jre_directives
						where jre_directives_name = "PCA_JVM_WORK_DIR"
	
				/*
				** look for duplicates
				*/
				select @count = count(*)
					from pca_jre_arguments
						where jre_args_name = @full_argname and
							jre_args_string_value = @opt1
	
				/*
				** no duplicates found - ok to insert new row
				*/
				if (@count = 0)
				begin
					insert into pca_jre_arguments values ( 
						@directive_index,
						@full_argname,
						@units,
						0,
						@opt1,
						'row added by sp_jreconfig',
						1,
						0)
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19922: "Insert Failed - Transaction Rolled Back" */
						raiserror 19922

						return (1)
					end
				end
				else /* report error - we have a duplicate row */
				begin
					/* 19902: "Duplicates are not permitted! The value is already in the table: '%1!'" */
					raiserror 19902, @opt1

					return (1)
				end
			end
			else if (@name IS NOT NULL) and ('pca_jvm_java_options' like "%" + @name + "%")
			begin
				/*
				** setup some variables used by the code below
				*/
				select @full_argname = 'pca_jvm_java_options'
				select @units = 'array'
				select @directive_index = jre_directives_index
					from pca_jre_directives
						where jre_directives_name = "PCA_JVM_JAVA_OPTIONS"
	
				/*
				** look for duplicates
				*/
				select @count = count(*)
					from pca_jre_arguments
						where jre_args_name = @full_argname and
							jre_args_string_value = @opt1
	
				/*
				** no duplicates found - ok to insert new row
				*/
				if (@count = 0)
				begin
					insert into pca_jre_arguments values ( 
						@directive_index,
						@full_argname,
						@units,
						0,
						@opt1,
						'row added by sp_jreconfig',
						1,
						0)
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19922: "Insert Failed - Transaction Rolled Back" */
						raiserror 19922

						return (1)
					end
				end
				else /* report error - we have a duplicate row */
				begin
					/* 19902: "Duplicates are not permitted! The value is already in the table: '%1!'" */
					raiserror 19902, @opt1

					return (1)
				end
			end
			else if (@name IS NOT NULL) and ('pca_jvm_sys_device_path' like "%" + @name + "%")
			begin
				/*
				** setup some variables used by the code below
				*/
				select @full_argname = 'pca_jvm_sys_device_path'
				select @units = 'array'
				select @directive_index = jre_directives_index
					from pca_jre_directives
						where jre_directives_name = "PCA_JVM_SYS_DEVICE_PATH"
	
				/*
				** look for duplicates
				*/
				select @count = count(*)
					from pca_jre_arguments
						where jre_args_name = @full_argname and
							jre_args_string_value = @opt1
	
				/*
				** no duplicates found - ok to insert new row
				*/
				if (@count = 0)
				begin
					insert into pca_jre_arguments values ( 
						@directive_index,
						@full_argname,
						@units,
						0,
						@opt1,
						'row added by sp_jreconfig',
						1,
						0)
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19922: "Insert Failed - Transaction Rolled Back" */
						raiserror 19922

						return (1)
					end
				end
				else /* report error - we have a duplicate row */
				begin
					/* 19902: "Duplicates are not permitted! The value is already in the table: '%1!'" */
					raiserror 19902, @opt1

					return (1)
				end
			end
			else if (@name IS NOT NULL) and ('pca_jvm_ext_class_loader_global' like "%" + @name + "%")
			begin
				/*
				** setup some variables used by the insert statement below
				*/
				select @full_argname = 'pca_jvm_ext_class_loader_global'
				select @units = 'array'
				select @directive_index = jre_directives_index
					from pca_jre_directives
						where jre_directives_name = "PCA_JVM_EXT_CLASS_LOADER"
	
	
				/*
				** No additional validation needs to be done because it is
				** all handled in the table insert trigger: sp_ext_loader_insert_trigger 
				** in a file called pcitables.
				** Using a trigger to do the validation allows us to do the same validation
				** whether this stored procedure is used, or if a user decides to use manual
				** 'insert into' statements in a script instead. Either way the same rules
				** are enforced.
				*/
				insert into pca_jre_arguments values ( 
					@directive_index,
					@full_argname,
					@units,
					0,
					@opt1,
					'row added by sp_jreconfig',
					1,
					0)
				if (@@error != 0) or (@@transtate > 1)
				begin
					rollback tran
					/* 19922: "Insert Failed - Transaction Rolled Back" */
					raiserror 19922

					return (1)
				end
			end
			else if (@name IS NOT NULL) and ('pca_jvm_ext_class_loader_dbase' like "%" + @name + "%")
			begin
				/*
				** setup some variables used by the insert statement below
				*/
				select @full_argname = 'pca_jvm_ext_class_loader_dbase'
				select @units = 'array'
				select @directive_index = jre_directives_index
					from pca_jre_directives
						where jre_directives_name = "PCA_JVM_EXT_CLASS_LOADER"
	
				/*
				** No additional validation needs to be done because it is
				** all handled in the table insert trigger: sp_ext_loader_insert_trigger 
				** in a file called pcitables.
				** Using a trigger to do the validation allows us to do the same validation
				** whether this stored procedure is used, or if a user decides to use manual
				** 'insert into' statements in a script instead. Either way the same rules
				** are enforced.
				*/
				insert into pca_jre_arguments values ( 
					@directive_index,
					@full_argname,
					@units,
					0,
					@opt1,
					'row added by sp_jreconfig',
					1,
					0)
				if (@@error != 0) or (@@transtate > 1)
				begin
					rollback tran
					/* 19922: "Insert Failed - Transaction Rolled Back" */
					raiserror 19922

					return (1)
				end
			end
			else if exists (select 1 from pca_jre_directives where jre_directives_name like "%" + @name + "%")
			begin
				/* 19926: "The '%1!' command cannot be used with Directives" */
				raiserror 19926, "add"

				return (1)
			end
			else /* arg_name doesn't match any of our valid names */
			begin
				select @units = jre_args_units from pca_jre_arguments
					where jre_args_name like "%" + @name + "%"
				/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
				raiserror 19927, 'add', @units

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('delete' like @cmd + "%") and (@cmd like "de%") /* must be at least 'de' to be unique */
	begin
		/*
		** DELETE command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'delete', 'arg_name', 'string_value'"

			return (1)
		end
		else
		begin
			/*
			** First we need to make sure the arg_name specified has units=array
			** no other type of argument can be deleted
			** anything that doesn't match an array type argument will fall to the else cases
			** below for error handling
			*/
			if exists (select * from pca_jre_arguments
					where jre_args_units = 'array' and
						jre_args_name like "%" + @name + "%")
			begin
				if (@opt1 IS NULL)
				begin
					/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
					raiserror 19898, "sp_jreconfig 'delete', 'arg_name', 'string_value'"

					return (1)
				end
				/* exact match on args_name first */
				else if exists (select * from pca_jre_arguments where jre_args_name = @name)
				begin
					/*
					** store the units and save the arg name in @full_argname
					*/
					select @units = jre_args_units, @full_argname = jre_args_name
						from pca_jre_arguments
							where jre_args_name = @name
	
					if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
					begin
						/*
						** since we support matching against the path portion only, ignoring the mask
						** we first need to check the value the user gave us (in $opt1). If it contains
						** a mask, we can do only an exact match on the full string (a partial match 
						** that contains a mask doesn't make sense)
						*/
						select @has_mask = 0
						select @lparen = charindex(char(40), @opt1)
						select @rparen = charindex(char(41), @opt1)
						select @length = len(@opt1)
						select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))
						if (@lparen = 0) and (@rparen != 0)
						begin
							/* Error: only a right paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen = 0) and (@lparen != 0)
						begin
							/* Error: only a left paren found*/

							/* 19916: "Syntax Error: unmatched paren found" */
							raiserror 19916

							return (1)
						end
						else if (@rparen != 0) and (@rparen != @length)
						begin
							/* Error: the first right paren is not on the end of the string */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen = 1)
						begin
							/* Error: left paren is the first character of the string! */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@lparen2 != 0)
						begin
							/* Error: found 2 left parens */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end
						else if (@lparen != 0) and (@rparen = @length)
						begin
							/*
							** value is ok - we do have a mask
							** so we set has_mask to 1 to indicate that the orig value has a supplied mask
							*/
							select @has_mask = 1
						end
						else if (@lparen = 0) and (@rparen = 0)
						begin
							/*
							** Value is ok - does not have a permission mask
							** so we set has_mask to 0 to indicate that the orig value did not have a mask
							*/
							select @has_mask = 0
						end
						else
						begin
							/* Error: unknown error - should never land here - report the "invalid parens" message */

							/* 19915: "Syntax Error: invalid paren(s) found" */
							raiserror 19915

							return (1)
						end

						/*
						** If the user specified a mask, then our only choice is to do an exact match
						** On the other hand, if we didn't get a match we try an exact match first, on
						** the path portion only, and if no match there we check for a fuzzy match on path only.
						** In any case, we will only do the enable if we match exactly one item
						*/
						if (@has_mask = 1)
						begin
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
							if (@count = 1)
							begin
								delete from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19921: "Delete Failed - Transaction Rolled Back" */
									raiserror 19921

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
							else /* @count > 1 */
							begin
								/*
								** We should never get here on an exact match
								*/
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
						else /* @has_mask = 0 */
						begin
							/*
							** We first do an exact match on the path portion (ignoring the mask when present)
							** For the value to be a match it must perfectly match the string up to the '(' that
							** starts the mask.
							*/
							select @count = count(*) 
								from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
							if (@count = 1)
							begin
								delete from pca_jre_arguments
									where jre_args_name = "pca_jvm_work_dir" and
										(jre_args_string_value = @opt1 or
										jre_args_string_value like @opt1 + "[(]%[)]")
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19921: "Delete Failed - Transaction Rolled Back" */
									raiserror 19921

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** We did not have an exact match - now try a fuzzy match on the path only
								** which means we ignore the mask if there is one
								** we can only continue if we match exactly one row
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
								if (@count = 1)
								begin
									delete from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
											(jre_args_string_value like "%" + @opt1 + "%" and
											jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19921: "Delete Failed - Transaction Rolled Back" */
										raiserror 19921

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'array')
					begin
						/*
						** First do an exact match, if we match, go ahead with the delete.
						** If no exact match, we try a fuzzy match and if we match exactly
						** one record, we go ahead with the delete.
						*/
						select @count = count(*)
							from pca_jre_arguments
								where jre_args_name = @full_argname and
									jre_args_string_value = @opt1
						/*
						** If we match exactly one row, we go ahead with the delete
						*/
						if (@count = 1)
						begin
							delete from pca_jre_arguments
								where jre_args_name = @full_argname and
									jre_args_string_value = @opt1
							if (@@error != 0) or (@@transtate > 1)
							begin
								rollback tran
								/* 19921: "Delete Failed - Transaction Rolled Back" */
								raiserror 19921

								return (1)
							end
						end
						else if (@count = 0)
						begin
							/*
							** If we didn't get an exact match on the string_value, we
							** check again for a fuzzy match
							*/
							select @count = count(*)
								from pca_jre_arguments
									where jre_args_name = @full_argname and
										jre_args_string_value like "%" + @opt1 + "%"
							/*
							** If we match exactly one row, we go ahead with the delete
							*/
							if (@count = 1)
							begin
								delete from pca_jre_arguments
									where jre_args_name = @full_argname and
										jre_args_string_value like "%" + @opt1 + "%"
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19921: "Delete Failed - Transaction Rolled Back" */
									raiserror 19921

									return (1)
								end
							end
							/*
							** If the fuzzy match finds more than one row, we report this and do nothing
							** we can only do the delete if the supplied values are sufficient to identify
							** a single table row!
							*/
							else if (@count > 1)
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
							else /* @count == 0 */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
						/*
						** If we match more than one row, we report this and do nothing
						*/
						else /* @count > 1 */
						begin
							/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
							raiserror 19909, @name, @opt1

							return (1)
						end
					end
				end
				else if exists (select * from pca_jre_arguments where jre_args_name like "%" + @name + "%")
				begin
					/*
					** Count the distinct arg names to distinguish between a match
					** on >1 arg_name vs a match on a single arg name that happens to have 
					** more than one record (java_options or work_dir for example)
					** getting a 1 here means that @name matches a single arg_name
					*/
					select @count = count(distinct jre_args_name)
						from pca_jre_arguments
							where jre_args_name like "%" + @name + "%"
					/*
					** If @count = 1 we have a unique arg_name and can continue
					*/
					if (@count = 1)
					begin
						/*
						** store the units and save the arg name in @full_argname
						*/
						select @units = jre_args_units, @full_argname = jre_args_name
							from pca_jre_arguments
								where jre_args_name like "%" + @name + "%"
		
						if (@units = 'array') and (@full_argname = 'pca_jvm_work_dir')
						begin
							/*
							** since we support matching against the path portion only, ignoring the mask
							** we first need to check the value the user gave us (in $opt1). If it contains
							** a mask, we can do only an exact match on the full string (a partial match 
							** that contains a mask doesn't make sense)
							*/
							select @has_mask = 0
							select @lparen = charindex(char(40), @opt1)
							select @rparen = charindex(char(41), @opt1)
							select @length = len(@opt1)
							select @lparen2 = charindex(char(40), substring(@opt1, @lparen + 1, len(@opt1)))
							if (@lparen = 0) and (@rparen != 0)
							begin
								/* Error: only a right paren found*/

								/* 19916: "Syntax Error: unmatched paren found" */
								raiserror 19916

								return (1)
							end
							else if (@rparen = 0) and (@lparen != 0)
							begin
								/* Error: only a left paren found*/

								/* 19916: "Syntax Error: unmatched paren found" */
								raiserror 19916

								return (1)
							end
							else if (@rparen != 0) and (@rparen != @length)
							begin
								/* Error: the first right paren is not on the end of the string */

								/* 19915: "Syntax Error: invalid paren(s) found" */
								raiserror 19915

								return (1)
							end
							else if (@lparen = 1)
							begin
								/* Error: left paren is the first character of the string! */

								/* 19915: "Syntax Error: invalid paren(s) found" */
								raiserror 19915

								return (1)
							end
							else if (@lparen != 0) and (@lparen2 != 0)
							begin
								/* Error: found 2 left parens */

								/* 19915: "Syntax Error: invalid paren(s) found" */
								raiserror 19915

								return (1)
							end
							else if (@lparen != 0) and (@rparen = @length)
							begin
								/*
								** value is ok - we do have a mask
								** so we set has_mask to 1 to indicate that the orig value has a supplied mask
								*/
								select @has_mask = 1
							end
							else if (@lparen = 0) and (@rparen = 0)
							begin
								/*
								** Value is ok - does not have a permission mask
								** so we set has_mask to 0 to indicate that the orig value did not have a mask
								*/
								select @has_mask = 0
							end
							else
							begin
								/* Error: unknown error - should never land here - report the "invalid parens" message */

								/* 19915: "Syntax Error: invalid paren(s) found" */
								raiserror 19915

								return (1)
							end
		
							/*
							** If the user specified a mask, then our only choice is to do an exact match
							** On the other hand, if we didn't get a match we try an exact match first, on
							** the path portion only, and if no match there we check for a fuzzy match on path only.
							** In any case, we will only do the enable if we match exactly one item
							*/
							if (@has_mask = 1)
							begin
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											jre_args_string_value = @opt1
								if (@count = 1)
								begin
									delete from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											jre_args_string_value = @opt1
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19921: "Delete Failed - Transaction Rolled Back" */
										raiserror 19921

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
								else /* @count > 1 */
								begin
									/*
									** We should never get here on an exact match
									*/
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
							else /* @has_mask = 0 */
							begin
								/*
								** We first do an exact match on the path portion (ignoring the mask when present)
								** For the value to be a match it must perfectly match the string up to the '(' that
								** starts the mask.
								*/
								select @count = count(*) 
									from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value = @opt1 or
											jre_args_string_value like @opt1 + "[(]%[)]")
								if (@count = 1)
								begin
									delete from pca_jre_arguments
										where jre_args_name = "pca_jvm_work_dir" and
											(jre_args_string_value = @opt1 or
											jre_args_string_value like @opt1 + "[(]%[)]")
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19921: "Delete Failed - Transaction Rolled Back" */
										raiserror 19921

										return (1)
									end
								end
								else if (@count = 0)
								begin
									/*
									** We did not have an exact match - now try a fuzzy match on the path only
									** which means we ignore the mask if there is one
									** we can only continue if we match exactly one row
									*/
									select @count = count(*) 
										from pca_jre_arguments
											where jre_args_name = "pca_jvm_work_dir" and
												(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
												(jre_args_string_value like "%" + @opt1 + "%" and
												jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
									if (@count = 1)
									begin
										delete from pca_jre_arguments
											where jre_args_name = "pca_jvm_work_dir" and
												(jre_args_string_value like "%" + @opt1 + "%" + "[(]%[)]" or
												(jre_args_string_value like "%" + @opt1 + "%" and
												jre_args_string_value not like "%" + @opt1 + "%" + "[(]%[)]"))
										if (@@error != 0) or (@@transtate > 1)
										begin
											rollback tran
											/* 19921: "Delete Failed - Transaction Rolled Back" */
											raiserror 19921

											return (1)
										end
									end
									else if (@count = 0)
									begin
										/* 19906: "record not found: '%1!', '%2!'" */
										raiserror 19906, @name, @opt1

										return (1)
									end
									else /* @count > 1 */
									begin
										/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
										raiserror 19909, @name, @opt1

										return (1)
									end
								end
								else /* @count > 1 */
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
							end
						end
						else if (@units = 'array')
						begin
							/*
							** First do an exact match, if we match, go ahead with the delete.
							** If no exact match, we try a fuzzy match and if we match exactly
							** one record, we go ahead with the delete.
							*/
							select @count = count(*)
								from pca_jre_arguments
									where jre_args_name = @full_argname and
										jre_args_string_value = @opt1
							/*
							** If we match exactly one row, we go ahead with the delete
							*/
							if (@count = 1)
							begin
								delete from pca_jre_arguments
									where jre_args_name = @full_argname and
										jre_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19921: "Delete Failed - Transaction Rolled Back" */
									raiserror 19921

									return (1)
								end
							end
							else if (@count = 0)
							begin
								/*
								** If we didn't get an exact match on the string_value, we
								** check again for a fuzzy match
								*/
								select @count = count(*)
									from pca_jre_arguments
										where jre_args_name = @full_argname and
											jre_args_string_value like "%" + @opt1 + "%"
								/*
								** If we match exactly one row, we go ahead with the delete
								*/
								if (@count = 1)
								begin
									delete from pca_jre_arguments
										where jre_args_name = @full_argname and
											jre_args_string_value like "%" + @opt1 + "%"
									if (@@error != 0) or (@@transtate > 1)
									begin
										rollback tran
										/* 19921: "Delete Failed - Transaction Rolled Back" */
										raiserror 19921

										return (1)
									end
								end
								/*
								** If the fuzzy match finds more than one row, we report this and do nothing
								** we can only do the delete if the supplied values are sufficient to identify
								** a single table row!
								*/
								else if (@count > 1)
								begin
									/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
									raiserror 19909, @name, @opt1

									return (1)
								end
								else /* @count == 0 */
								begin
									/* 19906: "record not found: '%1!', '%2!'" */
									raiserror 19906, @name, @opt1

									return (1)
								end
							end
							/*
							** If we match more than one row, we report this and do nothing
							*/
							else /* @count > 1 */
							begin
								/* 19909: "Supplied values match multiple records: '%1!', '%2!'" */
								raiserror 19909, @name, @opt1

								return (1)
							end
						end
					end
					else if (@count > 1)
					begin
						/* 19908: "Supplied value matches multiple records: '%1!'" */
						raiserror 19908, @name

						return (1)
					end
					/*
					** it is not likely that we should ever land here
					*/
					else /* count = 0 */
					begin
						/* 19906: "record not found: '%1!', '%2!'" */
						raiserror 19906, @name, @opt1

						return (1)
					end
				end
				else /* shouldn't ever land here - no match */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			/*
			** Our check above for array type arguments found none, so now the thing
			** we need to figure out is if we matched a directive, an argument that is not an 'array'
			** or if we didn't match anything. We determine that here and report the appropriate message.
			*/
			else if exists (select 1 from pca_jre_directives
				where jre_directives_name like "%" + @name + "%")
			begin
				/* 19926: "The '%1!' command cannot be used with Directives" */
				raiserror 19926, "delete"

				return (1)
			end
			else if exists (select 1 from pca_jre_arguments
				where jre_args_name like "%" + @name + "%")
			begin
				select @units = jre_args_units from pca_jre_arguments
					where jre_args_name like "%" + @name + "%"
				/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
				raiserror 19927, 'delete', @units

				return (1)
			end
			else
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('array_enable' like @cmd + "%") and (@cmd like "array_e%") /* must be at least 'array_e' */
	begin
		/*
		** ARRAY_ENABLE command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'array_enable', 'arg_name'"

			return (1)
		end
		else
		begin
			/*
			** Use @flag to count the argument names that @name matches.
			** if @flag is anything other than 1, it means we have an invalid arg_name in @name
			** When we match we also need to set @full_argname since the user may have only given
			** us a partial name.
			*/
			select @flag = 0
			if ('pca_jvm_work_dir' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_work_dir'
			end
			if ('pca_jvm_java_options' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_java_options'
			end
			if ('pca_jvm_sys_device_path' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_sys_device_path'
			end
			if ('pca_jvm_ext_class_loader_global' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_global'
			end
			if ('pca_jvm_ext_class_loader_dbase' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_dbase'
			end
	
			if (@flag = 1)
			begin
				/*
				** We have a valid argument name but we can't do the update
				** unless we have some rows in the table for this argument
				*/
				if exists (select 1 from pca_jre_arguments
						where jre_args_name = @full_argname)
				begin
					update pca_jre_arguments
						set jre_args_enabled = 1
						where jre_args_name = @full_argname and
							jre_args_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else /* argument not found */
				begin
					/* 19907: "No records found! There are no records in the database for this argument: '%1!'" */
					raiserror 19907, @full_argname

					return (1)
				end
			end
			else if (@flag = 0)
			begin
				if exists (select 1 from pca_jre_directives where jre_directives_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the supplied value matches a Directive Name
					** Since directives are not supported by array_enable, we report the error and exit
					*/
					/* 19926: "The '%1!' command cannot be used with Directives" */
					raiserror 19926, "array_enable"

					return (1)
				end
				else if exists (select 1 from pca_jre_arguments where jre_args_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the argument exists, but it is not one of the arguments
					** that is supported by the array_enable command, so we must report an error message
					*/
					select @units = jre_args_units from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
					/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
					raiserror 19927, 'array_enable', @units

					return (1)
				end
				else /* argument does not exist */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @flag > 1 */
			begin
				/* 19908: "Supplied value matches multiple records: '%1!'" */
				raiserror 19908, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('array_disable' like @cmd + "%") and (@cmd like "array_d%") /* must be at least 'array_d' */
	begin
		/*
		** ARRAY_DISABLE command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'array_disable', 'arg_name'"

			return (1)
		end
		else
		begin
			/*
			** Use @flag to count the argument names that @name matches.
			** if @flag is anything other than 1, it means we have an invalid arg_name in @name
			*/
			select @flag = 0
			if ('pca_jvm_work_dir' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_work_dir'
			end
			if ('pca_jvm_java_options' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_java_options'
			end
			if ('pca_jvm_sys_device_path' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_sys_device_path'
			end
			if ('pca_jvm_ext_class_loader_global' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_global'
			end
			if ('pca_jvm_ext_class_loader_dbase' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_dbase'
			end
	
			if (@flag = 1)
			begin
				/*
				** We have a valid argument name but we can't do the update
				** unless we have some rows in the table for this argument
				*/
				if exists (select 1 from pca_jre_arguments
						where jre_args_name = @full_argname)
				begin
					update pca_jre_arguments
						set jre_args_enabled = 0
						where jre_args_name = @full_argname and
							jre_args_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else
				begin
					/* 19907: "No records found! There are no records in the database for this argument: '%1!'" */
					raiserror 19907, @full_argname

					return (1)
				end
			end
			else if (@flag = 0)
			begin
				if exists (select 1 from pca_jre_directives where jre_directives_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the supplied value matches a Directive Name
					** Since directives are not supported by array_disable, we report the error and exit
					*/
					/* 19926: "The '%1!' command cannot be used with Directives" */
					raiserror 19926, "array_disable"

					return (1)
				end
				else if exists (select 1 from pca_jre_arguments where jre_args_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the argument exists, but it is not one of the arguments
					** that is supported by the array_enable command, so we must report an error message
					*/
					select @units = jre_args_units from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
					/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
					raiserror 19927, 'array_disable', @units

					return (1)
				end
				else /* argument does not exist */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @flag > 1 */
			begin
				/* 19924: "Supplied value matches multiple argument names: '%1!'" */
				raiserror 19924, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('array_clear' like @cmd + "%") and (@cmd like "array_c%") /* must be at least 'array_c' */
	begin
		/*
		** ARRAY_CLEAR command
		**
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_jreconfig 'array_clear', 'arg_name'"

			return (1)
		end
		else
		begin
			/*
			** Use @flag to count the argument names that @name matches.
			** if @flag is anything other than 1, it means we have an invalid arg_name in @name
			*/
			select @flag = 0
			if ('pca_jvm_work_dir' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_work_dir'
			end
			if ('pca_jvm_java_options' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_java_options'
			end
			if ('pca_jvm_sys_device_path' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_sys_device_path'
			end
			if ('pca_jvm_ext_class_loader_global' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_global'
			end
			if ('pca_jvm_ext_class_loader_dbase' like "%" + @name + "%")
			begin
				select @flag = (@flag + 1)
				select @full_argname = 'pca_jvm_ext_class_loader_dbase'
			end
	
			if (@flag = 1)
			begin
				select @count = count(*)
					from pca_jre_arguments
						where jre_args_name = @full_argname
				if (@count > 0)
				begin
					delete from pca_jre_arguments
						where jre_args_name = @full_argname
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19921: "Delete Failed - Transaction Rolled Back" */
						raiserror 19921

						return (1)
					end
				end
				else
				begin
					/* 19907: "No records found! There are no records in the database for this argument: '%1!'" */
					raiserror 19907, @full_argname

					return (1)
				end
			end
			else if (@flag = 0)
			begin
				if exists (select 1 from pca_jre_directives where jre_directives_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the supplied value matches a Directive Name
					** Since directives are not supported by array_enable, we report the error and exit
					*/
					/* 19926: "The '%1!' command cannot be used with Directives" */
					raiserror 19926, "array_clear"

					return (1)
				end
				else if exists (select 1 from pca_jre_arguments where jre_args_name like "%" + @name + "%")
				begin
					/*
					** Getting here means the argument exists, but it is not one of the arguments
					** that is supported by the array_enable command, so we must report an error message
					*/
					select @units = jre_args_units from pca_jre_arguments
						where jre_args_name like "%" + @name + "%"
					/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
					raiserror 19927, 'array_clear', @units

					return (1)
				end
				else /* argument does not exist */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @flag > 1 */
			begin
				/* 19924: "Supplied value matches multiple argument names: '%1!'" */
				raiserror 19924, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('reload_config' like @cmd + "%") and (@cmd like "rel%") /* must be at least 'rel' */
	begin
		/*
		** RELOAD_CONFIG command
		** This command basically unloads the PCA/JVM and then loads it again, using the built-in
		** commands "unload" and "load". The side-effect of doing this is the config values that
		** are stored in the sybpcidb database tables are re-loaded into memory for use when the 
		** PCA/JVM next initializes.
		**
		** The only restriction here is that the PCA/JVM cannot be active when this command executes.
		** If it is active, the "unload" command will fail and an appropriate message will be reported
		** to the user letting them know that the JVM is in an incorrect state for the unload command. 
		**
		** The PCA/JVM is not active if the ASE has been booted but the JVM has not yet been initialized.
		** Also possible, if it has been implemented, you can stop the JVM while leaving the ASE server
		** running. Using this command would also be possible at that time as well.
		**
		** example syntax:
		** sp_jreconfig 'reload_config'
		**
		** this command uses two built-in commands behind the scenes
		** load:    pci_command('pca config', 1)
		** unload:  pci_command('pca config', 2)
		**
		** The "load" command will only be executed if the "unload" command was successful.
		** If the user supplies extra arguments after @cmd, they are ignored.
		*/

		/*
		** unload
		** Note: this will fail if the PCI/JVM has already been initialized
		*/
		select @result = pci_command('pca config', 2)

		/*
		** If the unload command was successful, we continue with the load command.
		** On error, the unload cmd already reported this to the log.
		**
		** Note that if the config is in an unloaded state (PCI_SLOT_INITIALIZED), there
		** is nothing to unload, so the Unload command will succeed. (introduced in CR 537444-1)
		*/
		if (@result < 0)
		begin
			/* 19917: "Unload command failed - the PCA/JVM is not in the correct state to do an Unload operation - make sure the JVM has not been initialized" */
			raiserror 19917

			return (1)
		end
		else /* success */
		begin
			/* 
			** Unload command was successful, the PCA/JVM configuration has been Unloaded
			** We now continue with the Load
			**
			** Load
			*/
			select @result = pci_command('pca config', 1)
			if (@result < 0)
			begin
				/* 19918: "Load command failed - the PCA/JVM is not in the correct state to do a Load operation - make sure the JVM has not been initialized" */
				raiserror 19918

				return (1)
			end
		end
	end
	else /* unknown command: @cmd */
	begin
		/*
		** UNKNOWN command
		*/

		/* 19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'" */
		raiserror 19914, @cmd

		return (1)
	end
end
go

grant execute on sp_jreconfig to sa_role
go

go

/*
** raiserror Messages used pciconfig [Total 11]
**
**	19898: "Syntax Error: missing arguments ( correct syntax: %1! )"
**	19900: "record not found: '%1!'"
**	19903: "Internal error: units type is not recognized: '%1!'"
**	19904: "list command option is invalid or ambiguous: '%1!'"
**	19908: "Supplied value matches multiple records: '%1!'"
**	19910: "Syntax Error: %1! parameter is missing ( correct syntax: %2!)"
**	19913: "Syntax Error: the supplied value is not numeric: %1!"
**	19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'"
**	19923: "Update Failed - Transaction Rolled Back"
**	19926: "The '%1!' command cannot be used with Directives"
**	19927: "The '%1!' command does not support arguments of type: '%2!'"
*/

use sybpcidb
go

if exists (select 1 from sysobjects where name = 'sp_pciconfig')
	drop proc sp_pciconfig
go

create proc sp_pciconfig
	@cmd		varchar(16)	= NULL,	/* the command */
	@name		varchar(32)	= NULL,	/* usually arg_name or directive name/index */
	@opt1		varchar(255)	= NULL,	/* option 1 - use varies by command */
	@opt2		varchar(255)	= NULL	/* option 2 - use varies by command */
as
begin
	/*
	** Declare Variables used in this proc
	*/
	declare 	@units varchar(12), 
			@count int, 
			@actualname varchar(64)

	set nocount on

	if (@cmd IS NULL) and (@name IS NULL) and (@opt1 IS NULL) and (@opt2 IS NULL)
	begin
		/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
		raiserror 19898, "sp_pciconfig 'cmd', 'name' [, 'opt1' [, 'opt2'] ]"

		return (1)
	end
	else if (@cmd IS NOT NULL) and (('report' like @cmd + "%") or (@cmd = 'rpt'))
	begin
		/*
		** REPORT command
		** reports directives or arguments based on the @name argument passed in
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_pciconfig 'report', 'name' [, 'opt1' [, 'opt2'] ]"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select 1 from pci_directives where pci_directives_name = @name)
			begin
				if (@opt1 = 'args')
				begin
					if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
					begin
						select
							"Directive" = convert(char(27), a.pci_directives_name),
							"Active" = convert(char(4), a.pci_directives_enabled),
							"Argument Name" = convert(char(31), b.pci_args_name),
							"Units" = convert(char(10), pci_args_units),
							CASE
								when b.pci_args_units = 'string'	then convert(char(18), '"' + b.pci_args_string_value + '"')
								else ''
							END as "String Value",
							CASE
								when b.pci_args_units = 'number'	then convert(char(8), b.pci_args_number_value)
								else ''
							END as "Number Value",
							"Enabled" = convert(char(4), b.pci_args_enabled),
							"Description" = convert(char(21), b.pci_args_description)
								from pci_directives a, pci_arguments b
									where a.pci_directives_name = @name and
										a.pci_directives_index = b.pci_args_directive_index
									order by a.pci_directives_index
					end
					else /* unformatted report */
					begin
						select
							a.pci_directives_name,
							a.pci_directives_enabled,
							b.pci_args_name, 
							b.pci_args_units,
							b.pci_args_string_value,
							b.pci_args_number_value,
							b.pci_args_enabled,
							b.pci_args_description
							from pci_directives a, pci_arguments b
								where a.pci_directives_name = @name and
									a.pci_directives_index = b.pci_args_directive_index
									order by a.pci_directives_index
					end
				end
				else /* @opt1 != 'args' */
				begin
					if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
					begin
						select
							"Directive Name"	= convert(char(27), pci_directives_name),
							"Active"		= convert(char(4),  pci_directives_enabled),
							"Description"		= convert(char(52), pci_directives_description)
								from pci_directives
									where pci_directives_name = @name
									order by pci_directives_index
					end
					else /* unformatted report */
					begin
						select
							pci_directives_name,
							pci_directives_enabled,
							pci_directives_description
								from pci_directives
									where pci_directives_name = @name
									order by pci_directives_index
					end
				end
			end
			else if exists (select 1 from pci_arguments where pci_args_name = @name)
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
					"Argument Name" = convert(char(31), pci_args_name),
					"Units" = convert(char(10), pci_args_units),
					CASE
						when pci_args_units = 'string'		then convert(char(30), '"' + pci_args_string_value + '"')
						else ''
					END as "String Value",
					CASE
						when pci_args_units = 'number'		then convert(char(8), pci_args_number_value)
						else ''
					END as "Number Value",
					"Enabled" = convert(char(4), pci_args_enabled),
					"Description" = convert(char(40), pci_args_description)
						from pci_arguments 
							where pci_args_name = @name
							order by pci_args_name
				end
				else /* unformatted report */
				begin
					select
						pci_args_name, 
						pci_args_units,
						pci_args_string_value,
						pci_args_number_value,
						pci_args_enabled,
						pci_args_description
							from pci_arguments 
								where pci_args_name = @name
								order by pci_args_name
				end
			end
			else if exists (select 1 from pci_directives where pci_directives_name like "%" + @name + "%")
			begin
				if (@opt1 = 'args')
				begin
					if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
					begin
						select
							"Directive" = convert(char(27), a.pci_directives_name),
							"Active" = convert(char(4), a.pci_directives_enabled),
							"Argument Name" = convert(char(31), b.pci_args_name),
							"Units" = convert(char(10), pci_args_units),
							CASE
								when b.pci_args_units = 'string'	then convert(char(18), '"' + b.pci_args_string_value + '"')
								else ''
							END as "String Value",
							CASE
								when b.pci_args_units = 'number'	then convert(char(8), b.pci_args_number_value)
								else ''
							END as "Number Value",
							"Enabled" = convert(char(4), b.pci_args_enabled),
							"Description" = convert(char(21), b.pci_args_description)
								from pci_directives a, pci_arguments b
									where a.pci_directives_name like "%" + @name + "%" and
										a.pci_directives_index = b.pci_args_directive_index
									order by a.pci_directives_index
					end
					else /* unformatted report */
					begin
						select
							a.pci_directives_name,
							a.pci_directives_enabled,
							b.pci_args_name, 
							b.pci_args_units,
							b.pci_args_string_value,
							b.pci_args_number_value,
							b.pci_args_enabled,
							b.pci_args_description
							from pci_directives a, pci_arguments b
								where a.pci_directives_name like "%" + @name + "%" and
									a.pci_directives_index = b.pci_args_directive_index
									order by a.pci_directives_index
					end
				end
				else /* @opt1 != 'args' */
				begin
					if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
					begin
						select
							"Index"			= convert(char(4),  pci_directives_index),
							"Directive Name"	= convert(char(27), pci_directives_name),
							"Active"		= convert(char(4),  pci_directives_enabled),
							"Description"		= convert(char(52), pci_directives_description)
								from pci_directives
									where pci_directives_name like "%" + @name + "%"
									order by pci_directives_index
					end
					else /* unformatted report */
					begin
						select
							pci_directives_index,
							pci_directives_name,
							pci_directives_enabled,
							pci_directives_description
								from pci_directives
									where pci_directives_name like "%" + @name + "%"
									order by pci_directives_index
					end
				end
			end
			else if exists (select 1 from pci_arguments where pci_args_name like "%" +  @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
					"Argument Name" = convert(char(31), pci_args_name),
					"Units" = convert(char(10), pci_args_units),
					CASE
						when pci_args_units = 'string'		then convert(char(30), '"' + pci_args_string_value + '"')
						else ''
					END as "String Value",
					CASE
						when pci_args_units = 'number'		then convert(char(8), pci_args_number_value)
						else ''
					END as "Number Value",
					"Enabled" = convert(char(4), pci_args_enabled),
					"Description" = convert(char(40), pci_args_description)
						from pci_arguments 
							where pci_args_name like "%" + @name + "%"
							order by pci_args_name
				end
				else /* unformatted report */
				begin
					select
						pci_args_name, 
						pci_args_units,
						pci_args_string_value,
						pci_args_number_value,
						pci_args_enabled,
						pci_args_description
							from pci_arguments 
								where pci_args_name like "%" + @name + "%"
								order by pci_args_name
				end
			end
			else /* no match */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('list' like @cmd + "%")
	begin
		/*
		** LIST command
		*/

		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_pciconfig 'list', 'name' [, 'opt1']"

			return (1)
		end
		else
		begin
			if (@name IS NOT NULL) and ('directives' like @name + "%") and (@name like "dir%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive Name"	= convert(char(27), pci_directives_name),
						"Enabled"		= convert(char(4),  pci_directives_enabled),
						"Description"		= convert(char(72), pci_directives_description)
							from pci_directives
								order by pci_directives_index
				end
				else /* unformatted report */
				begin
					select
						pci_directives_name,
						pci_directives_enabled,
						pci_directives_description
							from pci_directives
								order by pci_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('argnames' like @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select distinct
						"Argument Name" = convert(char(32), b.pci_args_name),
						"Arg Units" = convert (char(10), b.pci_args_units),
						"Directive" = convert(char(27), a.pci_directives_name)
						from pci_directives a, pci_arguments b
							where b.pci_args_directive_index = a.pci_directives_index
							order by b.pci_args_name
				end
				else /* unformatted report */
				begin
					select distinct
						b.pci_args_name,
						b.pci_args_units,
						a.pci_directives_name
						from pci_directives a, pci_arguments b
							where b.pci_args_directive_index = a.pci_directives_index
							order by b.pci_args_name
				end
			end
			else if (@name IS NOT NULL) and ('enabled' like @name + "%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive" = convert(char(27), a.pci_directives_name),
						"Active" = convert(char(4), a.pci_directives_enabled),
						"Argument Name" = convert(char(32), b.pci_args_name), 
						"Units" = convert(char(10), b.pci_args_units),
						CASE
							when b.pci_args_units = 'string'	then convert(char(32), '"' + b.pci_args_string_value + '"')
							else ''
						END as 'String Value',
						CASE
							when b.pci_args_units = 'number'	then convert(char(8), b.pci_args_number_value)
							else ''
						END as 'Number Value',
						"Enabled" = convert(char(4), b.pci_args_enabled)
						from pci_directives a, pci_arguments b
							where b.pci_args_enabled = 1 and a.pci_directives_enabled = 1 and
								b.pci_args_directive_index = a.pci_directives_index
							order by a.pci_directives_index
				end
				else /* unformatted report */
				begin
					select
						a.pci_directives_name,
						a.pci_directives_enabled,
						b.pci_args_name, 
						b.pci_args_units,
						b.pci_args_string_value,
						b.pci_args_number_value,
						b.pci_args_enabled
						from pci_directives a, pci_arguments b
							where b.pci_args_enabled = 1 and a.pci_directives_enabled = 1 and
								b.pci_args_directive_index = a.pci_directives_index
							order by a.pci_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('disabled' like @name + "%") and (@name like "dis%")
			begin
				if (@opt1 IS NOT NULL) and ('formatted' like @opt1 + "%")
				begin
					select
						"Directive" = convert(char(27), a.pci_directives_name),
						"Active" = convert(char(4), a.pci_directives_enabled),
						"Argument Name" = convert(char(32), b.pci_args_name), 
						"Units" = convert(char(10), b.pci_args_units),
						CASE
							when b.pci_args_units = 'string'	then convert(char(32), '"' + b.pci_args_string_value + '"')
							else ''
						END as 'String Value',
						CASE
							when b.pci_args_units = 'number'	then convert(char(8), b.pci_args_number_value)
							else ''
						END as 'Number Value',
						"Enabled" = convert(char(4), b.pci_args_enabled)
						from pci_directives a, pci_arguments b
							where (a.pci_directives_enabled = 0 or
								(a.pci_directives_enabled = 1 and b.pci_args_enabled = 0)) and
								b.pci_args_directive_index = a.pci_directives_index
							order by a.pci_directives_index
				end
				else /* unformatted report */
				begin
					select
						a.pci_directives_name,
						a.pci_directives_enabled,
						b.pci_args_name, 
						b.pci_args_units,
						b.pci_args_string_value,
						b.pci_args_number_value,
						b.pci_args_enabled
						from pci_directives a, pci_arguments b
							where (a.pci_directives_enabled = 0 or
								(a.pci_directives_enabled = 1 and b.pci_args_enabled = 0)) and
								b.pci_args_directive_index = a.pci_directives_index
							order by a.pci_directives_index
				end
			end
			else if (@name IS NOT NULL) and ('units' like @name + "%")
			begin
				if (@opt1 IS NOT NULL)
				begin
					/*
					** count the number of arguments that match the criteria
					*/
					select @count = count(distinct pci_args_units)
						from pci_arguments
							where pci_args_units like "%" + @opt1 + "%"
					/*
					** store the actual argument name in case a partial string was supplied
					** this actual name is used for reporting only
					*/
					select @actualname = pci_args_units
						from pci_arguments
							where pci_args_units like "%" + @opt1 + "%"
	
					/*
					** We have at least one argument to report
					*/
					if (@count > 0)
					begin
						if (@opt2 IS NOT NULL) and ('formatted' like @opt2 + "%")
						begin
							select
								"Argument Name" = convert(char(32), pci_args_name), 
								"Units" = convert(char(10), pci_args_units),
								CASE
									when pci_args_units = 'string'		then convert(char(32), '"' + pci_args_string_value + '"')
									else ''
								END as 'String Value',
								CASE
									when pci_args_units = 'number'  	then convert(char(8), pci_args_number_value)
									else ''
								END as 'Number Value',
								"Enabled" = convert(char(4), pci_args_enabled)
									from pci_arguments
										where pci_args_units like @opt1 + "%"
						end
						else /* unformatted report */
						begin
							select
								pci_args_name, 
								pci_args_units,
								pci_args_string_value,
								pci_args_number_value,
								pci_args_enabled
									from pci_arguments
										where pci_args_units like @opt1 + "%"
						end
					end
					/*
					** No records in the table have units = @opt1
					*/
					else
					begin
						/* 19900: "record not found: '%1!'" */
						raiserror 19900, @opt1

						return (1)
					end
				end
				else if (@opt1 IS NULL)
				begin
					select distinct
						"Units" = convert(char(10), pci_args_units),
						CASE
							when pci_args_units = 'switch'		then convert(char(80), 'can only be enabled or disabled and do not have stored values')
							when pci_args_units = 'string'		then convert(char(80), 'can be enabled or disabled and also have a stored string value')
							when pci_args_units = 'number'		then convert(char(80), 'can be enabled or disabled and also have a stored number value')
						END as 'Description'
							from pci_arguments
				end
			end
			else
			begin
				/* 19904: "list command option is invalid or ambiguous: '%1!'" */
				raiserror 19904, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('enable' like @cmd + "%")
	begin
		/*
		** ENABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_pciconfig 'enable'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_pciconfig 'enable', 'name'"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select * from pci_directives where pci_directives_name = @name)
			begin
				select @count = count(*) 
					from pci_directives
						where pci_directives_name = @name
				if (@count = 1)
				begin
					update pci_directives
						set pci_directives_enabled = 1
							where pci_directives_name = @name and
								pci_directives_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pci_arguments where pci_args_name = @name)
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name = @name
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name = @name
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already enabled, we enable it
						*/
						update pci_arguments
							set pci_args_enabled = 1
								where pci_args_name = @name and
									pci_args_enabled != 1
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran

							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pci_directives where pci_directives_name like "%" + @name + "%")
			begin
				select @count = count(*) 
					from pci_directives
						where pci_directives_name like "%" + @name + "%"
				if (@count = 1)
				begin
					update pci_directives
						set pci_directives_enabled = 1
							where pci_directives_name like "%" + @name + "%" and
								pci_directives_enabled != 1
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pci_arguments where pci_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name like "%" + @name + "%"
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name like "%" + @name + "%"
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already enabled, we enable it
						*/
						update pci_arguments
							set pci_args_enabled = 1
								where pci_args_name like "%" + @name + "%" and
									pci_args_enabled != 1
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @name does not match any of the pci_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('disable' like @cmd + "%")
	begin
		/*
		** DISABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_pciconfig 'disable'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_pciconfig 'disable', 'name'"

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on directive name
			** 2. next, check for exact match on argument name
			** 3. if those fail, we come back and check for fuzzy (wildcard) matches on directive
			**	first, and then argument second
			** 4. finally, if @name doesn't match any of the above, we report the "record not found" message
			*/
			if exists (select * from pci_directives where pci_directives_name = @name)
			begin
				select @count = count(*) 
					from pci_directives
						where pci_directives_name = @name
				if (@count = 1)
				begin
					update pci_directives
						set pci_directives_enabled = 0
							where pci_directives_name = @name and
								pci_directives_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pci_arguments where pci_args_name = @name)
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name = @name
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name = @name
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already disabled, we disable it
						*/
						update pci_arguments
							set pci_args_enabled = 0
								where pci_args_name = @name and
									pci_args_enabled != 0
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pci_directives where pci_directives_name like "%" + @name + "%")
			begin
				select @count = count(*) 
					from pci_directives
						where pci_directives_name like "%" + @name + "%"
				if (@count = 1)
				begin
					update pci_directives
						set pci_directives_enabled = 0
							where pci_directives_name like "%" + @name + "%" and
								pci_directives_enabled != 0
					if (@@error != 0) or (@@transtate > 1)
					begin
						rollback tran
						/* 19923: "Update Failed - Transaction Rolled Back" */
						raiserror 19923

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
			end
			else if exists (select * from pci_arguments where pci_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name like "%" + @name + "%"
				/*
				** If @count = 1 we have a unique arg_name and can continue
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name can be supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name like "%" + @name + "%"
	
					if (@units = 'switch') or (@units = 'string') or (@units = 'number')
					begin
						/*
						** if the row is not already disabled, we disable it
						*/
						update pci_arguments
							set pci_args_enabled = 0
								where pci_args_name like "%" + @name + "%" and
									pci_args_enabled != 0
						if (@@error != 0) or (@@transtate > 1)
						begin
							rollback tran
							/* 19923: "Update Failed - Transaction Rolled Back" */
							raiserror 19923

							return (1)
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else /* @name does not match any of the pci_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('update' like @cmd + "%")
	begin
		/*
		** UPDATE command
		*/

		if (@name IS NULL)
		begin
			/* 19910: "Syntax Error: %1! parameter is missing ( correct syntax: %2!)" */
			raiserror 19910, "arg_name", "sp_pciconfig 'update', 'arg_name' ..."

			return (1)
		end
		else
		begin
			/*
			** Checking the @name argument is done in this order:
			** In the case of the update command, we don't check for directives at all until we have tried both an exact
			** match as well as a fuzzy match on argument name. This is because directives cannot be updated.
			** 1. check for exact match on argument name
			** 2. next, we do a fuzzy match on argument name
			** 3. finally, if we don't have any match on argument name, we can test against directive to give the user a
			** 	meaningful error message if they try to update a directive.
			** 4. if no match on directive either, then we report the "record not found" error message
			*/
			if exists (select * from pci_arguments where pci_args_name = @name)
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name = @name
				/*
				** The update can only be done if @count = 1 which means that @name is distinct
				** enough to match only one argument name
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name could have been supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name = @name
					if (@units = 'switch')
					begin
						/*
						** Using the 'update' command on a switch is not valid, so if we get a switch here
						** we report a usage/syntax error and refer to the usage/help screen
						*/
						/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
						raiserror 19927, 'update', @units

						return (1)
					end
					else if (@units = 'string')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_pciconfig 'update', 'string_arg', 'old_string', 'new_string'"

							return (1)
						end
						else
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** string_value field and if not we report the error and skip the update
							*/
							if exists (select * from pci_arguments     where pci_args_name = @name
									and pci_args_string_value = @opt1)
							begin
								update pci_arguments
									set pci_args_string_value = @opt2
										where pci_args_name = @name
											and pci_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923
		
									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing string_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'number')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_pciconfig 'update', 'number_arg', 'old_value', 'new_value'"

							return (1)
						end
						else if (isnumeric(@opt1) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt1

							return (1)
						end
						else if (isnumeric(@opt2) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt2

							return (1)
						end
						else /* all is ok, go forward with the update */
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** number_value field and if not we report the error and skip the update
							*/
							if exists (select * from pci_arguments where pci_args_name = @name
									and pci_args_number_value = convert(int, @opt1))
							begin
								update pci_arguments
									set pci_args_number_value = convert(int, @opt2)
										where pci_args_name = @name
											and pci_args_number_value = convert(int, @opt1)
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923
	
									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing number_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pci_arguments     where pci_args_name like "%" + @name + "%")
			begin
				/*
				** Count the distinct arg names to distinguish between a match
				** on >1 arg_name vs a match on a single arg name that happens to have 
				** more than one record (java_options or work_dir for example)
				** getting a 1 here means that @name matches a single arg_name
				*/
				select @count = count(distinct pci_args_name)
					from pci_arguments
						where pci_args_name like "%" + @name + "%"
				/*
				** The update can only be done if @count = 1 which means that @name is distinct
				** enough to match only one argument name
				*/
				if (@count = 1)
				begin
					/*
					** store the units and the full arg name (@name could have been supplied as a partial string)
					*/
					select @units = pci_args_units, @actualname = pci_args_name
						from pci_arguments
							where pci_args_name like "%" + @name + "%"
					if (@units = 'switch')
					begin
						/*
						** Using the 'update' command on a switch is not valid, so if we get a switch here
						** we report a usage/syntax error and refer to the usage/help screen
						*/
						/* 19927: "The '%1!' command does not support arguments of type: '%2!'" */
						raiserror 19927, 'update', @units

						return (1)
					end
					else if (@units = 'string')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_pciconfig 'update', 'string_arg', 'old_string', 'new_string'"

							return (1)
						end
						else
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** string_value field and if not we report the error and skip the update
							*/
							if exists (select * from pci_arguments     where pci_args_name like "%" + @name + "%"
									and pci_args_string_value = @opt1)
							begin
								update pci_arguments
									set pci_args_string_value = @opt2
										where pci_args_name like "%" + @name + "%"
											and pci_args_string_value = @opt1
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing string_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else if (@units = 'number')
					begin
						if (@opt1 IS NULL) or (@opt2 IS NULL)
						begin
							/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
							raiserror 19898, "sp_pciconfig 'update', 'number_arg', 'old_value', 'new_value'"

							return (1)
						end
						else if (isnumeric(@opt1) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt1

							return (1)
						end
						else if (isnumeric(@opt2) = 0)
						begin
							/* 19913: "Syntax Error: the supplied value is not numeric: %1!" */
							raiserror 19913, @opt2

							return (1)
						end
						else /* all is ok, go forward with the update */
						begin
							/*
							** Before doing the update we need to check to make sure that opt1 matches our 
							** number_value field and if not we report the error and skip the update
							*/
							if exists (select * from pci_arguments     where pci_args_name like "%" + @name + "%"
									and pci_args_number_value = convert(int, @opt1))
							begin
								update pci_arguments
									set pci_args_number_value = convert(int, @opt2)
										where pci_args_name like "%" + @name + "%"
											and pci_args_number_value = convert(int, @opt1)
								if (@@error != 0) or (@@transtate > 1)
								begin
									rollback tran
									/* 19923: "Update Failed - Transaction Rolled Back" */
									raiserror 19923

									return (1)
								end
							end
							else /* opt1 (old value) does not match the existing number_value field */
							begin
								/* 19906: "record not found: '%1!', '%2!'" */
								raiserror 19906, @name, @opt1

								return (1)
							end
						end
					end
					else /* not recognized units type */
					begin
						/* 19903: "Internal error: units type is not recognized: '%1!'" */
						raiserror 19903, @units

						return (1)
					end
				end
				else if (@count > 1)
				begin
					/* 19908: "Supplied value matches multiple records: '%1!'" */
					raiserror 19908, @name

					return (1)
				end
				/*
				** it is not likely that we should ever land here
				*/
				else /* count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
			else if exists (select * from pci_directives where pci_directives_name like "%" + @name + "%")
			begin
				/* 19926: "The '%1!' command cannot be used with Directives" */
				raiserror 19926, "update"

				return (1)
			end
			else /* @name does not match any of the pci_args_name values in the table */
			begin
				/* 19900: "record not found: '%1!'" */
				raiserror 19900, @name

				return (1)
			end
		end
	end
	else /* unknown command: @cmd */
	begin
		/*
		** UNKNOWN command
		*/

		/* 19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'" */
		raiserror 19914, @cmd

		return (1)
	end
end
go

grant execute on sp_pciconfig to sa_role
go

go

/*
** raiserror Messages used slotsyscalls [Total 5]
**
**	19898: "Syntax Error: missing arguments ( correct syntax: %1! )"
**	19900: "record not found: '%1!'"
**	19904: "list command option is invalid or ambiguous: '%1!'"
**	19899: "Supplied value matches multiple syscall names: '%1!'"
**	19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'"
*/

use sybpcidb
go

if exists (select 1 from sysobjects where name = 'sp_slotsyscalls')
	drop proc sp_slotsyscalls
go

create proc sp_slotsyscalls
	@cmd		varchar(255)	= NULL,		-- the Command (report, enable, disable, etc)
	@slotName	varchar(255)	= NULL,		-- Slot Name
	@name		varchar(255)	= NULL		-- the syscall name
as
begin
	/*
	** Declare Variables used in this proc
	*/
	declare 	@count int,
			@errstr1 varchar(255),
			@errstr2 varchar(255)

	set nocount on
	/*
	** if no args are given, report an error (at minimum we need at least a 'cmd' and a 'slotName')
	** one exception is the 'list, slots' command, which does not take a slotName because it returns
	** a list of all installed slots (by slot name). 
	** All notion of slot number has been removed so the only way to access a slot's properties is by
	** the slot's name. So, if the slot number ever changes in the future, it will not affect the external
	** interface.
	*/
	if (@cmd IS NULL) or (@slotName IS NULL)
	begin
		/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
		raiserror 19898, "sp_slotsyscalls 'cmd', 'slotName' [, 'name']"

		return (1)
	end
	else if (@cmd IS NOT NULL) and (('report' like @cmd + "%") or (@cmd = 'rpt'))
	begin
		/*
		** REPORT command
		*/

		if (@name IS NULL) or (@name = 'all')
		begin
			/*
			** No name means we are going to report everything
			** alternatively, the user could give 'all' for the name
			*/
			select
				b.slot_name,
				a.syscall_system_call,
				a.syscall_dispatch_name,
				a.syscall_enabled
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number
		end
		else /* syscall name was supplied */
		begin
			/*
			** A (system call) name was specified, so we report all matching records
			** an exact match is checked first, then if no match we do a fuzzy match
			** looking for exactly one match and finally if there are no matches
			** we return a "row not found" type error.
			** An error is also returned if >1 record is matched as well
			*/
			select @count = count(*)
				from pci_slot_syscalls a, pci_slotinfo b
					where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number and
						a.syscall_system_call = @name
			if (@count >= 1)
			begin
				/*
				** An exact match should never match more than one row
				*/
				select
					b.slot_name,
					a.syscall_system_call,
					a.syscall_dispatch_name,
					a.syscall_enabled
						from pci_slot_syscalls a, pci_slotinfo b
							where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call = @name
			end
			else /* @count = 0 */
			begin
				/*
				** Try a fuzzy match since an exact match produced zero matching rows
				*/
				select @count = count(*)
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call LIKE "%" + @name + "%"
				if (@count >= 1)
				begin
					/*
					** We matched one or more rows with the fuzzy match, so we report the information
					*/
					select
						b.slot_name,
						a.syscall_system_call,
						a.syscall_dispatch_name,
						a.syscall_enabled
							from pci_slot_syscalls a, pci_slotinfo b
								where b.slot_name = @slotName and
								b.slot_number = a.syscall_slot_number and
								a.syscall_system_call like "%" + @name + "%"
				end
				else /* @count = 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('list' like @cmd + "%")
	begin
		/*
		** LIST command
		*/

		/*
		** the 'list slots' command is the only one that doesn't take a 'slotName'
		*/
		if (@name IS NULL) and (@slotName = 'slots')
		begin
			select distinct slot_name from pci_slotinfo
		end
		else if (@name IS NOT NULL) and ('enabled' like @name + "%")
		begin
			select
				b.slot_name,
				a.syscall_system_call,
				a.syscall_dispatch_name,
				a.syscall_enabled
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number and
						a.syscall_enabled = 1
		end
		else if (@name IS NOT NULL) and ('disabled' like @name + "%")
		begin
			select
				b.slot_name,
				a.syscall_system_call,
				a.syscall_dispatch_name,
				a.syscall_enabled
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number and
						a.syscall_enabled = 0
		end
		else if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_slotsyscalls 'list', 'slotName' , 'name'"

			return (1)
		end
		else /* unknown list cmd */
		begin
			/* 19904: "list command option is invalid or ambiguous: '%1!'" */
			raiserror 19904, @name

			return (1)
		end
	end
	else if (@cmd IS NOT NULL) and ('enable' like @cmd + "%")
	begin
		/*
		** ENABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_slotsyscalls 'enable', 'JVM'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_slotsyscalls 'enable', 'slotName', 'name'"

			return (1)
		end
		else /* required arguments were supplied */
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on syscall_system_call
			** 2. if this fails, we try a fuzzy (wildcard) match
			** 3. if we didn't get a match, we report error "record not found"
			** 4. an error is also returned if we match more than one record as well
			*/
			select @count=count(*) 
				from pci_slot_syscalls a, pci_slotinfo b
					where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number and
						a.syscall_system_call = @name
			if (@count = 1)
			begin
				update pci_slot_syscalls
					set a.syscall_enabled = 1
						from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call = @name and
							a.syscall_enabled != 1
			end
			else if (@count > 1)
			begin
				/* 19899: "Supplied value matches multiple syscall names: '%1!'" */
				raiserror 19899, @name

				return (1)
			end
			else /* @count == 0 */
			begin
				select @count=count(*) 
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call like "%" + @name + "%"
				if (@count = 1)
				begin
					update pci_slot_syscalls
						set a.syscall_enabled = 1
							from pci_slot_syscalls a, pci_slotinfo b
							where b.slot_name = @slotName and
								b.slot_number = a.syscall_slot_number and
								a.syscall_system_call like "%" + @name + "%" and
								a.syscall_enabled != 1
				end
				else if (@count > 1)
				begin
					/* 19899: "Supplied value matches multiple syscall names: '%1!'" */
					raiserror 19899, @name

					return (1)
				end
				else /* @count == 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
		end
	end
	else if (@cmd IS NOT NULL) and ('disable' like @cmd + "%")
	begin
		/*
		** DISABLE command
		*/

		/*
		** error returned to the user when no 'name' is supplied, like this:
		**    sp_slotsyscalls 'disable', 'JVM'
		*/
		if (@name IS NULL)
		begin
			/* 19898: "Syntax Error: missing arguments ( correct syntax: %1! )" */
			raiserror 19898, "sp_slotsyscalls 'disable', 'slotName' , 'name'"

			return (1)
		end
		else /* required arguments were supplied */
		begin
			/*
			** Checking the @name argument is done in this order:
			** 1. check for exact match on syscall_system_call
			** 2. if this fails, we try a fuzzy (wildcard) match
			** 3. if we didn't get a match, we report error "record not found"
			** 4. an error is also returned if we match more than one record as well
			*/
			select @count=count(*) 
				from pci_slot_syscalls a, pci_slotinfo b
					where b.slot_name = @slotName and
						b.slot_number = a.syscall_slot_number and
						a.syscall_system_call = @name
			if (@count = 1)
			begin
				update pci_slot_syscalls
					set a.syscall_enabled = 0
						from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call = @name and
							a.syscall_enabled != 0
			end
			else if (@count > 1)
			begin
				/* 19899: "Supplied value matches multiple syscall names: '%1!'" */
				raiserror 19899, @name

				return (1)
			end
			else /* @count == 0 */
			begin
				select @count=count(*) 
					from pci_slot_syscalls a, pci_slotinfo b
						where b.slot_name = @slotName and
							b.slot_number = a.syscall_slot_number and
							a.syscall_system_call like "%" + @name + "%"
				if (@count = 1)
				begin
					update pci_slot_syscalls
						set a.syscall_enabled = 0
							from pci_slot_syscalls a, pci_slotinfo b
							where b.slot_name = @slotName and
								b.slot_number = a.syscall_slot_number and
								a.syscall_system_call like "%" + @name + "%" and
								a.syscall_enabled != 0
				end
				else if (@count > 1)
				begin
					/* 19899: "Supplied value matches multiple syscall names: '%1!'" */
					raiserror 19899, @name

					return (1)
				end
				else /* @count == 0 */
				begin
					/* 19900: "record not found: '%1!'" */
					raiserror 19900, @name

					return (1)
				end
			end
		end
	end

	else /* unknown command: @cmd */
	begin
		/* 19914: "Syntax Error: the supplied command is either invalid or ambiguous: '%1!'" */
		raiserror 19914, @cmd

		return (1)
	end
end
go

grant execute on sp_slotsyscalls to sa_role
go

go

use sybpcidb
go

if exists (select 1 from sysobjects where name = 'sp_pci_engine_slot_bind')
	drop proc sp_pci_engine_slot_bind
go

create proc sp_pci_engine_slot_bind
	@slot		int = NULL
as
begin
	declare 	@return_stat		int

	set nocount on

	if ( @slot IS NULL ) or ( @slot <= 0 )
	begin
		/* 19901: "Syntax Error: slot number value must be greater than 0" */
		raiserror 19901

		return (1)
	end
	else
	begin
		select @return_stat = pci_command("engine bind", @slot)
	end

	return @return_stat
end
go

grant execute on sp_pci_engine_slot_bind to sa_role
go

go

use sybpcidb
go

if exists (select 1 from sysobjects where name = 'sp_pci_engine_slot_unbind')
	drop proc sp_pci_engine_slot_unbind
go

create proc sp_pci_engine_slot_unbind
	@slot		int		= NULL
as
begin
	declare 	@return_stat		int

	set nocount on

	if ( @slot IS NULL ) or ( @slot <= 0 )
	begin
		/* 19901: "Syntax Error: slot number value must be greater than 0" */
		raiserror 19901

		return (1)
	end
	else
	begin
		select @return_stat = pci_command("engine unbind", @slot)
	end

	return @return_stat
end
go

grant execute on sp_pci_engine_slot_unbind to sa_role
go

go
-- Trailer for PCI_DB
use master
go
sp_dboption sybpcidb, 'trunc log', 'false'
go
-- END PCI_DB Trailer


declare @retval int
exec @retval = sp_version 'installpcidb', 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

