/*
 * This script installs a procedure (sp_webservices) to manage web services.
 */

use sybsystemprocs
go

if exists (select * from master..sysobjects where name = 'sp_configure')
begin
    /* Required to update system tables*/
    execute sp_configure "allow updates", 1
end

/*
 * The procedure sp_get_unique_table creates a tablename that
 * is unique by appending a number to the tablename.
 * The number is incremented until the table does not exist
 * in sysobjects.
 *
 * Input: table_in varchar(255) -- Base tablename
 * Output: table_out varchar(255) -- Name that is unique.
 *
 * Messages: None
 */

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

create procedure sp_get_unique_table
     @table_in varchar(30),
     @db_name varchar(30),
     @in_userid int,
     @table_out varchar(30) output
as
    declare @mycount int
    declare @mycountstr varchar(2)
    declare @fullName varchar(256)
    declare @userid int
    declare @userid2 int
    declare @objidfullname int

    declare @uname varchar(30)

    select @table_out = @table_in
    select @mycount = 1
    select @mycountstr = convert(varchar(2),@mycount)
    select @uname = user_name(@in_userid)
    select @fullName = @db_name + '.' + @uname + '.' + @table_in
    select @objidfullname = object_id(@fullName)

    while (@objidfullname > 0)
    begin
        select @table_out = @table_in + @mycountstr
        select @fullName = @db_name + "." + @uname + "." + @table_out
        select @objidfullname = object_id(@fullName)
        select @mycount = @mycount + 1
        select @mycountstr = convert(varchar(2),@mycount)
    end
    return
go
/*
 * The procedure sp_webservices is the main interface for the
 * user to manage web services in the system.
 *
 * Input: action  varchar(255) -- The action to take
 *        The actions supported are add, list, modify, remove, and help
 *
 * Input: wsdluri varchar(2048)-- The URI to operate on
 * Input: arg1    varchar(30)  -- An argument used by different
 *                                actions
 * Input: arg2    varchar(2048) -- An argument used by different actions.
 * Output: status 0 -- succeeded
 *                1 -- failure
 * Messages: Currently all messages are from print statements.
 *           This will be changed to use msgdb.
 */

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

create procedure sp_webservices
    @action  varchar(30) = 'help',
    @wsdluri varchar(2048) = NULL,
    @arg1 varchar(30) = 'ws',
    @arg2 varchar(2048) = NULL
as
    declare @status      int
    declare @strtoexec   varchar(2048)
    declare @err int
    declare @wsdltabname varchar(20)
    declare @notifytabname varchar(20)
    declare @proxytabname varchar(30)
    declare @parenLoc int
    declare @sdsname varchar(30)
    declare @wsdlid char(30)
    declare @sysattr_wsdluri varchar(2048)
    declare @moretodo int
    declare @sysattr_wsdlid  varchar(255)
    declare @currdbname      varchar(30)
    declare @proxy_overrides varchar(2048)
    declare @mydbname varchar(255)
    declare @methodid     int
    declare @userid int        
    declare @eqlindex int
    declare @set_cis_rpc_handling int
    declare @dbName varchar(255)
    declare @serviceItem   varchar(2048)
    declare @aliasName varchar(255)
    declare @configsql varchar(255)
    declare @configValue int


    select @userid = user_id() 

    -- Raise exception if webservices option not enabled
    if ( exists ( select c.name from master.dbo.syscolumns c, master.dbo.sysobjects o, master.dbo.sysusers u where c.id=o.id and o.uid = u.uid and o.name='syscurconfigs' and u.name='dbo' and c.name='instanceid' ) )
    begin
	select @configsql = 'select @configValue = value from master.dbo.syscurconfigs where config=420 and instanceid=@@instanceid'
    end
    else
    begin
	select @configsql = "select @configValue = value from master.dbo.syscurconfigs where config=420"
    end

    exec (@configsql)
    if ( 1!= @configValue )
    begin
	raiserror 19331
	return 1
    end

    /* Set the variable to a known state. */
    select @set_cis_rpc_handling = 0

    select @wsdluri = ltrim(rtrim(@wsdluri))
    select @mydbname = db_name()
        
    if (@action = "add")
    begin
        /* Verify arguments are valid */
        select @sdsname = @arg1
        select @proxy_overrides = @arg2

        if (not exists (select srvname from master..sysservers where srvname = @sdsname))
        begin
	    raiserror 19319, @sdsname
            return 1
        end

	if (@proxy_overrides is not NULL)
	begin
		select @eqlindex=charindex('=', @proxy_overrides)
		if (@eqlindex = 0)
		begin
			print "Invalid parameter value [%1!]. Must be of the form name=value.", @proxy_overrides
		return 1
		end
	end
   
        if (@wsdluri = NULL)
        begin
	    raiserror 19321
            return 1
        end
        else
        /* Does the WSDL URI already exist?    */
        /* If so, don't try and load it again. */        

        exec @moretodo = sp_webservices isvalid, @wsdluri
        if (@moretodo = 1)
        begin
	    raiserror 19322, @wsdluri
            return 1
        end

        /* Let the user know what's going on. This will likely */
        /* take a while                                        */

	print "Generating proxy tables using sds [%1!] for WSDL URI: [%2!]", @sdsname, @wsdluri

        /* Generate a temporary table name that is to be used as a */
        /* rpc proxy table. The reason for the rpc proxy table is  */
        /* to capture multiple rows returned from the stored       */
        /* procedure get_mappings */

	/* create an unique table name */
        select @wsdltabname = 'ws_' + newid()

        /* All columns being returned from the Consumer may be null. */

        select @strtoexec = 'create existing table tempdb..' + @wsdltabname + ' (cetsql varchar(255) null, operation varchar(255) null, tablename varchar(255) null, skipped varchar(255) null, _wsdl varchar(255) null, _overrides varchar(2048) null) external procedure at "' + @sdsname + '...get_mappings"'
        exec(@strtoexec)
        select @err = @@error

        /* This occurs when CIS cannot contact the remote server, */
        /* in this case, the consumer                             */

        if (@err = 11206)
        begin
	    raiserror 19320
            return 1
        end

	/* create an unique table name */
        select @notifytabname = 'ws_' + newid()

        /* Create a proxy table that maps to the notify stored proc in the */
        /* Consumer. */
        
        select @strtoexec = 'create existing table tempdb..' + @notifytabname + ' (_type varchar(30) null, _wsdl varchar(255) null, _operation varchar(255) null, _argValue varchar(255) null) external procedure at "' + @sdsname + '...notify"'
        exec(@strtoexec)
        select @err = @@error

        /* This occurs when CIS cannot contact the remote server, */
        /* in this case, the consumer                             */

        if (@err = 11206)
        begin
	    raiserror 19320
            return 1
        end
        
        /* Create a temporary table to hold the result set */
        /* from the proxy table, so that we can step through */
        /* the result set using a cursor                     */

        /* As we may have null data coming in the columns, make */
        /* sure to account for it.                              */
        
        create table #wsvalues (cetsql varchar(255) null, operation varchar(255) null, tablename varchar(255) null, skipped varchar(255) null)

        /* Move the results of the proxy table into the      */
        /* temporary table. Make sure to take the skipped column */
        /* information as well. */

        select @strtoexec = "insert into #wsvalues select cetsql, operation, tablename, skipped from tempdb.." + @wsdltabname + " where _wsdl='" + @wsdluri + "'" + " and _overrides='" + @proxy_overrides + "'"
        exec(@strtoexec)

        /* Create a unique id for the WSDL URI. */
        select @wsdlid = newid()

        /* Store the meta data info for the WSDL URI */
        /* in sysattributes                          */

        insert into sysattributes (class, attribute, object_type, object_cinfo, object_info1, text_value) 
            values (18, 5, 'WF', @wsdlid, @userid, @wsdluri)

        select @methodid = 1

        /* Iterate through the result returned from get_mappings and */
        /* execute each row. Each row represents a the creation of a */
        /* proxy table that maps to a web service */

        create table #tblName (cetsql varchar(255) null, tablename varchar(255) null, operation varchar(255))

        /* It is possible that the tablename is set to null to indicate that we could */
        /* not map this operation to a proxy table. In our processing, however, we    */
        /* ignore these rows. */
        
        declare cs1 cursor for select cetsql, operation, tablename from #wsvalues where tablename != null
        declare @cetsql   varchar(255)
        declare @opName   varchar(255)
        declare @tableName varchar(255)
        declare @curruser  varchar(255)
        declare @numOps   int
        declare @found int
        declare @mycount int
        declare @mycountstr varchar(2)
        declare @toignore int
        declare @oppattern varchar(255)
        
        select @numOps = 0
        select @curruser=user_name()
        open cs1

        fetch cs1 into @cetsql, @opName, @tableName
        while (@@sqlstatus = 0)
        begin        
            /* We are guaranteed to be returned a max of 28 characters in */
            /* tableName from the Consumer. */
            
            select @proxytabname = @tableName
            select @mycount = 0
            
            /* In the case the user has specified a specific proxy table name */
            /* we do not try to make the name unique. Instead, we allow the   */
            /* failure to create the proxy table to be passed back.           */

            select @oppattern = "%" + @opName + "=" + "%"
            
            if (patindex (@oppattern, @proxy_overrides) = 0)
            begin
                /* The scheme in creating a unique table name is to           */
                /* follow the algorithm in sp_get_unique_table, but if there  */
                /* is another process that is running in tandem with this one */
                /* it is still possible to get error #2714 indicating that    */
                /* the table already exists. In this case we retry, getting   */
                /* a unique name using sp_get_unique_name.                    */
                /* This mechanism is used to eliminate timing windows for     */
                /* creating unique table names while reducing the number of   */
                /* superflous error messages sent to the user.                */         
                
                select @err = 2714

                while (@err = 2714)
                begin
                    /* Does this table already exist?            */
                    /* If so, append a number to make it unique. */

                    exec sybsystemprocs..sp_get_unique_table @tableName, @mydbname, @userid, @proxytabname out
          
                    /* Modify the sql to execute if necessary */
            
                    if (@tableName != @proxytabname)
                    begin              
                        select @cetsql=str_replace (@cetsql, @tableName, @proxytabname)
                    end
 
                    /* Populate sysattributes for each web method, before the create call  */
                    /* so that the proxy table can be identified as a web service proxy    */
                    /* table during the create sequence. We use methodid so that           */
                    /* the uniqueness of the row is ensured. The object column  is updated */
                    /* to the objectid of the proxy table as soon as it is available.      */

                    insert into sysattributes (class, attribute, object_type, object, object_cinfo, object_info1, comments, char_value) 
                        values (18, 5, 'P', @methodid, @wsdlid, 60, @proxytabname, @opName)

                    select @methodid = @methodid + 1 
  
                    exec (@cetsql)
                    select @err = @@error
                        
                    /* If we get any error here, we remove the sysattributes entry. */
                    /* If the error is 2714, the while loop we are in continues      */
                    /* to search for a valid name for the proxy table to invoke the */
                    /* web method.                                                  */

                    if (@err != 0)
                    begin
                        /* Remove the sysattributes entry. */
                        delete from sysattributes where 
                              class=18 and 
                              attribute=5 and 
                              object_type='P' and 
                              object_cinfo=@wsdlid and
                              comments= @proxytabname and
                              char_value = @opName
                              
                       /* On any error, we stop processing and go immediately to */
                       /* cleanup code. */
                        
                       goto errorCleanup                              

                    end
                    else
                    begin
                        /* Now that we have an object id, stash it away... */

                        update sysattributes set object = object_id (@proxytabname)
                            where class = 18
                            and   object_cinfo = @wsdlid
                            and   comments = @proxytabname                    
                    end
                end
  
                /* Notify consumer of change, if we need to... */
  
                if (@tableName != @proxytabname)
                begin                           

                    select @strtoexec = "declare @ignore int select @ignore=1 from tempdb.." + @notifytabname + " where _type='proxytablename' and _wsdl='" + @wsdluri + "' and _operation='" + @opName + "' and _argValue ='" + @proxytabname + "'"
                    exec(@strtoexec)
                    select @tableName = @proxytabname   

                end
                
                select @strtoexec = "insert into #tblName values (""" + @cetsql + """, """ + @tableName + """, """ + @opName + """)"
                exec (@strtoexec)
            end
            else
            begin
                /* Populate sysattributes for each web method, before the create call */
                /* so that the proxy table can be identified as a web service proxy   */
                /* table during the create sequence. */

                insert into sysattributes (class, attribute, object_type, object, object_cinfo, object_info1, comments, char_value) 
                    values (18, 5, 'P', @methodid, @wsdlid, 60, @tableName, @opName)

                select @methodid = @methodid + 1

                exec (@cetsql)
                select @err = @@error
                    
                if (@err != 0)
                begin
                    /* On any error, we stop processing and go immediately to */
                    /* cleanup code. */
                    
                    delete from sysattributes where 
                              class=18 and 
                              attribute=5 and 
                              object_type='P' and 
                              object_cinfo=@wsdlid and
                              comments = @tableName and
                              char_value = @opName 
                              
                    goto errorCleanup
                end
                else
                begin
                    /* Now that we have an object id, stash it away... */
                        
                    update sysattributes set object = object_id (@tableName)
                            where class = 18
                            and   object_cinfo = @wsdlid
                            and   comments = @tableName     
                
                    select @strtoexec = "insert into #tblName values (""" + @cetsql + """, """ + @tableName + """, """ + @opName + """)"
                    exec (@strtoexec)
                end
            end      

            /* Fetch next row of web methods mapped to proxy tables .... */
            
            fetch cs1 into @cetsql, @opName, @tableName
        end
        
errorCleanup:
        close cs1    
        deallocate cursor cs1

        /* If an error has occurred, we remove any proxy tables that */
        /* may have been created.  This is needed because we cannot  */
        /* use transactions.                                         */
        
        if (@err != 0)
        begin
            exec sp_webservices 'remove', @wsdluri, @arg1, @arg2
        end
        
        /* Otherwise, the WSDL file has been fully processed. */
        else
        begin
            declare @numProxy int
            select @numProxy=count(tablename) from #tblName

            /* If no proxy tables were created, make sure we clean up  */
            /* the sysattributes table                                 */
        
            if (@numProxy = 0)
            begin
		delete from sysattributes where 
			class=18 and attribute = 5 and object_cinfo = @wsdlid
		print "No proxy tables were created for the WSDL URL: [%1!]", @wsdluri
            end
            else
            begin
                /* Add the skipped operations into #tblName so that we can tell */
                /* the user of the action taken for all the web methods in the  */
                /* WSDL file. Note that the skipped column may contain null     */
                /* so we only want the non-null values here.                    */
                
                insert into #tblName (operation) select skipped from #wsvalues where skipped != null

                /* Notify the user of items created. */
                select operation, tablename from #tblName 
            end
        end

        /* More cleanup..... */
        if exists(select 1 from tempdb..sysobjects where name = @wsdltabname)
        begin
            select @strtoexec = "drop table tempdb.." + @wsdltabname
            exec(@strtoexec)
        end
        
        if exists(select 1 from tempdb..sysobjects where name = @notifytabname)
        begin
            select @strtoexec = "drop table tempdb.." + @notifytabname
            exec(@strtoexec)
        end        
    end
    else if (@action = "list")
    begin
        if (@wsdluri = null)
        begin
            /* List all the WSDL URI's in the system accompanied by */
            /* all information about proxy tables and mappings to   */
            /* web methods.    */
            select 'Proxy Table Name'=substring (object_name(sa1.object),1,30),
                   'WebMethod' = substring (sa1.char_value,1,40), 
		   'Owner' = convert(varchar(30), user_name(sa2.object_info1)),
                   'Timeout' =  sa1.object_info1, 
                   'WSDL URI'=  convert (varchar(120),sa2.text_value)
            from sysattributes sa1, sysattributes sa2
            where sa1.class=18 and sa1.char_value!= null
                and sa1.object_cinfo = sa2.object_cinfo
                and sa1.object_type='P'
                and sa2.object_type='WF'
            order by sa1.object_cinfo, (object_name(sa1.object)), Owner desc
        end
        else
        begin
            /* The request is to display information for a specific        */
            /* WSDL URI, so first we have to see if sysattributes contains */
            /* the requested URI. */

            declare csList cursor for 
                select object_cinfo, convert(varchar(2048), text_value) 
			from sysattributes 
			where class=18 and object_type = 'WF'
			and object_info1=user_id()
            open csList

            select @found = 0
            select @wsdlid = ''

            fetch csList into @sysattr_wsdlid, @sysattr_wsdluri
            while (@@sqlstatus = 0)
            begin
                if (patindex (@wsdluri, @sysattr_wsdluri) = 1)
                begin
		    print "Found WSDL Match for [%1!]", @wsdluri
                    select @found = 1
                    select @wsdlid = @sysattr_wsdlid
                    break
                end
                fetch csList into @sysattr_wsdlid, @sysattr_wsdluri
            end
            if (@found = 1)
            begin

                /* We found the WSDL file in sysattributes, so print out */
                /* the information we know about the proxy tables and    */
                /* and web method mappings. */
                select 'Proxy Table Name'=substring (object_name(sa1.object),1,30),
                       'WebMethod' = substring (sa1.char_value,1,40), 
                       'Timeout' =  sa1.object_info1,
                       'WSDL URI'=convert (varchar(120),sa2.text_value)
                from sysattributes sa1, sysattributes sa2
                where sa1.class=18 and sa1.char_value!= null and sa1.object_cinfo = @wsdlid
                    and sa1.object_cinfo = sa2.object_cinfo
                    and sa1.object_type='P'
                    and sa2.object_type='WF'
                order by sa1.object_cinfo, (object_name(sa1.object)) desc
            end
            else
            begin
		print "The WSDL URI [%1!] is not found in the system. ", @wsdluri
		select @sdsname = @arg1
                 
		if (not exists (select srvname from master..sysservers where srvname = @sdsname))
		begin
		    raiserror 19323, @sdsname
		    return 1
		end
        
                /* Generate a temporary table name that is to be used as a */
                /* rpc proxy table. The reason for the rpc proxy table is  */
                /* to capture multiple rows returned from the stored       */
                /* procedure get_mappings */

		/* create an unique table name */
		select @wsdltabname = 'ws_' + newid()
        
                select @strtoexec = 'create existing table tempdb..' + @wsdltabname + ' (cetsql varchar(255), operation varchar(255), tablename varchar(255), _wsdl varchar(255) null) external procedure at "' + @sdsname + '...get_mappings"'
                exec(@strtoexec)
                select @err = @@error

                /* This occurs when CIS cannot contact the remote server, */
                /* in this case, the consumer                             */

                if (@err = 11206)
                begin
		    raiserror 19320
                    return 1
                end
        
               /* Show the results */

                select @strtoexec = "select 'Proxy Table Name'=tablename, 'Web Method'=operation, 'Create SQL'=cetsql from tempdb.." + @wsdltabname + " where _wsdl='" + @wsdluri + "'"
                exec(@strtoexec)

            end
        end
    end
    else if (@action = "listudws")
    begin
        if (@wsdluri = null)
        begin
                select 
		'User Defined Web Service'=substring(object_name(object),1,30), 
		'Alias'=comments,
                'Owner'=user_name(uid),
		'Type'= 
	            case
		        when object_info3 & 16 = 16 then 'SOAP'
	                when object_info3 & 32 = 32 then 'XML'
        	        when object_info3 & 64 = 64 then 'RAW'
	            end,
	       'Transport'= 
                    case
                        when object_info3 & 1 = 1 then 'http'
                        when object_info3 & 2 = 2 then 'https'
                    end,
                'Deployed'= 
                    case
                        when object_info3 & 128 = 128 then 'Yes'
                        when object_info3 & 128 = 0 then 'No'
                    end,
       		'Userpath'=substring(object_cinfo,1,30)
		from sysattributes, sysobjects
		where class=18 and attribute = 6
                      and object=id
		order by object_name(object), user_name(uid)

        end
        else
        begin

	select 'User Defined Web Service'=substring(object_name(object),1,30), 
		       'Alias'=object_info1,
                       'Owner'=user_name(uid),
		       'Type'= 
		           case
		              when object_info3 & 16 = 16 then 'SOAP'
		              when object_info3 & 32 = 32 then 'XML'
		              when object_info3 & 64 = 64 then 'RAW'
		           end,
		       'Transport'= 
		          case
		              when object_info3 & 1 = 1 then 'http'
		              when object_info3 & 2 = 2 then 'https'
		          end,
		      'Deployed'= 
		          case
		              when object_info3 & 128 = 128 then 'Yes'
		              when object_info3 & 128 = 0 then 'No'
		          end,
		       'Userpath'=substring(object_cinfo,1,30)
			from sysattributes, sysobjects
			where class=18 and attribute=6 and 
                              object = id and
                              object_name(object)=@wsdluri
                        order by user_name(uid)
	end
    end

    else if (@action = 'modify')
    begin
        declare @optstring varchar(1024)
        declare @eqindex int
        declare @realoption varchar(512)
        declare @realvalue  varchar(512)

        select @optstring = @arg1

        /* Verify that the user specified a WSDL URI. */
        if (@wsdluri = NULL)
        begin
	    raiserror 19324
            return 1
        end

        /* Ensure that the user has specified an option string */
        if (@optstring = 'ws')
        begin
            raiserror 19325
            return 1
        end

        select @eqindex=charindex('=', @optstring)
        
        /* Ensure the user has specfied a reasonable syntax in the option string */
        if (@eqindex != 0)
        begin

            /* Figure out what the user wanted to modify. */
            select @realoption=substring (@optstring, 1, @eqindex - 1)
            select @realvalue=substring (@optstring, @eqindex + 1, 512)

            declare mod1 cursor for 
                select object_cinfo, convert(varchar(2048), text_value) 
                    from sysattributes where class=18 and object_type = 'WF'
			and object_info1=user_id()

            open mod1

            select @found = 0
            select @wsdlid = ''

            fetch mod1 into @sysattr_wsdlid, @sysattr_wsdluri
            while (@@sqlstatus = 0)
            begin
                if (patindex (@wsdluri, @sysattr_wsdluri) = 1)
                begin
		    print "Found WSDL Match for [%1!]", @wsdluri
                    select @found = 1
                    select @wsdlid = @sysattr_wsdlid
                    break
                end
                fetch mod1 into @sysattr_wsdlid, @sysattr_wsdluri
            end
            if (@found = 0)
            begin
		raiserror 19332, @wsdluri
		return 1
            end
            else
            begin
                if (@realoption = 'timeout')
                begin
		    print "Updating timeout entries for WSDL URI [%1!] with [%2!]", @wsdluri, @realvalue                                                        
		    update sysattributes 
			set object_info1=convert (int, @realvalue)
			where object_cinfo=@wsdlid and class=18 and object_type='P'
                end
                else
                begin
		    raiserror 19326, @realoption
                end
            end
        end
        else
        begin
	    raiserror 19327
        end
        
    end
    else if (@action = "remove")
    begin
        declare @delProxyTable   varchar(255)

        /* Ok, this is probably not necessary, but I wanted */
        /* to be clear that arg1 is the name of the Consumer */

        select @sdsname = @arg1

        if (not exists (select srvname from master..sysservers where srvname = @sdsname))
        begin
	    raiserror 19313, @sdsname 
            return 1
        end
        
        /* If there is no WSDL URI, then remove all meta data */
        /* about web services in sysattributes.               */
        if (@wsdluri = "all")
        begin
	    raiserror 19311
            
            declare csDeleteProxy cursor 
                for select object_name(object) 
                    from sysattributes where class=18 and object_type='P'

            open csDeleteProxy
            fetch csDeleteProxy into @delProxyTable
            while (@@sqlstatus = 0)
            begin

                /* Only execute the drop statement if the table */
                /* actually exists                              */

                if exists (select 1 from sysobjects where name = @delProxyTable)
                begin
                    select @strtoexec = "drop table " + @delProxyTable
                    exec (@strtoexec)
                end

                fetch csDeleteProxy into @delProxyTable
            end

            delete from sysattributes where class=18        
            
            /* Enable cis_rpc_handling to make remote call, only if needed.        */
            /* We also keep track of the fact that the value has been turned on,   */
            /* so that at the end of the script, we can restore the settint to off */

            if (@@cis_rpc_handling = 0)
            begin
                set cis_rpc_handling on
                select @set_cis_rpc_handling = 1
            end

            /* Now notify the Consumer to delete the data. */
            select @strtoexec = @sdsname + "...removews 'all'"
            exec (@strtoexec)

            if (@set_cis_rpc_handling = 1)
            begin
                set cis_rpc_handling off
                select @set_cis_rpc_handling = 0
            end
        end
        else
        begin
        
            if (@wsdluri = NULL)
            begin
		raiserror 19333
                return 1
            end
                
            /* Now we have a specific WSDL URI to remove, so */
            /* first we have to find it.                     */

            declare cs2 cursor for 
                select object_cinfo, convert(varchar(2048), text_value) from sysattributes 
                    where class=18 and object_type = 'WF'
			and object_info1=user_id()
            open cs2
  
            select @found = 0
            select @wsdlid = ''

            /* If the WSDL URI is in sysattributes, find it. */

            fetch cs2 into @sysattr_wsdlid, @sysattr_wsdluri
            while (@@sqlstatus = 0)
            begin
                if (patindex (@wsdluri, @sysattr_wsdluri) = 1)
                begin
		    print "Found WSDL Match for [%1!]", @wsdluri
                    select @found = 1
                    select @wsdlid = @sysattr_wsdlid
                    break
                end
                fetch cs2 into @sysattr_wsdlid, @sysattr_wsdluri
            end

            if (@found = 0)
            begin
                raiserror 19332, @wsdluri
		return 1
            end
            else
            begin
                print "Deleting entries for WSDL URI  [%1!]", @wsdluri
                declare csDeleteProxy1 cursor for select object_name(object) 
                    from sysattributes 
                        where class=18 and object_type='P' 
			and object_cinfo=@wsdlid

                open csDeleteProxy1
                fetch csDeleteProxy1 into @delProxyTable
                while (@@sqlstatus = 0)
                begin

                    /* Only execute the drop statement if the table     */
                    /* actually exists. It may have been deleted by the */
                    /* user. */

                    if exists (select 1 from sysobjects where name = @delProxyTable)
                    begin
                        select @strtoexec = "drop table " + @delProxyTable
                        exec (@strtoexec)
                    end

                    fetch csDeleteProxy1 into @delProxyTable

                end

                /* Remove the row that contains the WSDL URI. */
                delete from sysattributes 	
			where class=18 
			and object_cinfo=@wsdlid
			and object_info1=user_id()
                

                /* Enable cis_rpc_handling to make remote call, only if needed.        */
                /* We also keep track of the fact that the value has been turned on,   */
                /* so that at the end of the script, we can restore the settint to off */

                if (@@cis_rpc_handling = 0)
                begin
                    set cis_rpc_handling on
                    select @set_cis_rpc_handling = 1
                end

                /* Now notify the Consumer to clean it's cache of WSDL URI's. */
                /* Note that the Consumer may or may not have the WSDL cached. */
                select @strtoexec = @sdsname + "...removews '" + @wsdluri + "'"
                exec (@strtoexec)

                /* In case we had to turn on cis_rpc_handling, turn it off */

                if (@set_cis_rpc_handling = 1)
                begin
                    set cis_rpc_handling off
                    select @set_cis_rpc_handling = 0
                end
            end
        end
    end
    else if (@action = "addalias")
    begin
        /* before wsdluri is assigned to aliasName, */
	/* the length of wsdluri should be checked.*/
	
        if (len(@wsdluri) > 255)
        begin	
               raiserror 19420
               return 1
        end

	select @aliasName=@wsdluri
	select @dbName=@arg1

	if (@aliasName = NULL)
	begin
		raiserror 19409
		return 1
	end

	if (@dbName = NULL)
	begin
		raiserror 19410
		return 1
	end

	begin tran

	insert into master..sysattributes (class, attribute, object_type, object_cinfo, char_value)
	        values (18, 7, 'AL', @aliasName, @dbName)

	commit 
    end
    else if (@action = "dropalias")
    begin
	select @aliasName=@wsdluri

	/* Does the alias exist to drop? */
	if (exists (select 1 from master..sysattributes 
                             where class=18 and attribute = 7 and object_cinfo=@aliasName))
        begin
		if (ws_admin("dropalias", @aliasName) = 0)
		begin
			return(1)
		end
        end
	else
	begin
		raiserror 19408
	 	return 1
	end
    end
    else if (@action = "listalias")
    begin
	select @dbName=@wsdluri

	if (@dbName = NULL)
        begin
        	select 'Alias Name'=object_cinfo,
			'Database'=substring (char_value,1,30)
		from master..sysattributes 
		where class = 18 and attribute = 7 and object_type='AL'
		order by object_cinfo
	end
	else
	begin
        	select 'Alias Name'=object_cinfo,
			'Database'=substring (char_value,1,30)
		from master..sysattributes 
		where class = 18 and attribute = 7 and 
	              object_type='AL' and
	              char_value = @dbName
		order by object_cinfo
	end
    end
    else if (@action = "deploy")
    begin

        /* arg1 is the name of the Consumer */

        select @sdsname = @arg1

        if (not exists (select srvname from master..sysservers where srvname = @sdsname))
        begin
	        raiserror 19313, @sdsname 
                return 1
        end
        
        /* We do not have a wsdl uri for this command, so pull the value into */
        /* a more appropriate variable name */

        select @serviceItem = @wsdluri
	
	if (ws_admin("deploy", @serviceItem) = 0)
        begin
	        return (1)
        end

        /* Enable cis_rpc_handling to make remote call, only if needed.        */
        /* We also keep track of the fact that the value has been turned on,   */
        /* so that at the end of the script, we can restore the settint to off */

        if (@@cis_rpc_handling = 0)
        begin
                set cis_rpc_handling on
                select @set_cis_rpc_handling = 1
        end

        /* Now notify the Consumer to deploy the list of user defined web service */
        select @strtoexec = @sdsname + "...deploy '" + @serviceItem + "', '" + db_name() + "'"

        exec (@strtoexec)
		    
        if (@set_cis_rpc_handling = 1)
        begin
                 set cis_rpc_handling off
                 select @set_cis_rpc_handling = 0
        end
        
    end
    else if (@action = "undeploy")
    begin
        /* arg1 is the name of the Consumer */

        select @sdsname = @arg1

        if (not exists (select srvname from master..sysservers where srvname = @sdsname))
        begin
	        raiserror 19313, @sdsname 
                return 1
        end
	
        /* We do not have a wsdl uri for this command, so pull the value into */
        /* a more appropriate variable name */

        select @serviceItem = @wsdluri
	
	if (ws_admin("undeploy", @serviceItem) = 0)
        begin
	        return (1)
        end
        
        /* Enable cis_rpc_handling to make remote call, only if needed.        */
        /* We also keep track of the fact that the value has been turned on,   */
        /* so that at the end of the script, we can restore the settint to off */

        if (@@cis_rpc_handling = 0)
        begin
                set cis_rpc_handling on
                select @set_cis_rpc_handling = 1
        end

        /* Now notify the Consumer to deploy the list of user defined web service */
        select @strtoexec = @sdsname + "...undeploy '" + @serviceItem + "', '" + db_name() + "'"

        exec (@strtoexec)
		    
        if (@set_cis_rpc_handling = 1)
        begin
                 Set cis_rpc_handling off
                 select @set_cis_rpc_handling = 0
        end
    end
    else if (@action = "configure")
    begin
    
    	declare @optionName varchar(2048)
    	declare @optionValue varchar(30)
    	
        /* arg2 is the name of the Consumer */
        
        select @sdsname = @arg2
        
	/* the default name of the Consumer is 'ws' */
        if ( @sdsname = null )
        begin
        	select @sdsname = 'ws'
        end
        
        /* We do not have a wsdl uri for this command, so pull the value into */
        /* a more appropriate variable name */

        select @optionName = @wsdluri
        select @optionValue = @arg1
        
        /* check optionName and optionValue */
	/* because the value of arg1 is 'ws',  */
	/* if the optionValue is not specified, */
	/* it should be equal with 'ws' */
        if (@optionName = NULL or @optionValue = 'ws')
        begin
        	/* 19422 "Specify an 'optionName' and an 'optionValue." */
        	raiserror 19422
        	exec sp_webservices help, configure
        	return 1
        end
	
        if (not exists (select srvname from master..sysservers where srvname = @sdsname))
        begin
	        raiserror 19313, @sdsname 
                return 1
        end

        /* Enable cis_rpc_handling to make remote call, only if needed.        */
        /* We also keep track of the fact that the value has been turned on,   */
        /* so that at the end of the script, we can restore the settint to off */

        if (@@cis_rpc_handling = 0)
        begin
                set cis_rpc_handling on
                select @set_cis_rpc_handling = 1
        end

        /* Now notify the Consumer to deploy the list of user defined web service */
        select @strtoexec = @sdsname + "...configure '" + @optionName + ', ' + @optionValue + "'"
        
        exec (@strtoexec)
		    
        if (@set_cis_rpc_handling = 1)
        begin
                 Set cis_rpc_handling off
                 select @set_cis_rpc_handling = 0
        end
    end
    else if (@action = 'isvalid')
    begin

        /* Is the WSDL URI specified in sysattributes already? */
        
        /* The assumption is made that the max URI is 2048 characters */
        /* in length.  */

        declare cs3 cursor for 
            select convert(varchar(2048), text_value) from sysattributes 
                where class=18 and object_type = 'WF' 
		and object_info1 = user_id()

        open cs3
        select @found = 0

        fetch cs3 into @sysattr_wsdluri
        while (@@sqlstatus = 0)
        begin
            if (patindex (@wsdluri, @sysattr_wsdluri) = 1)
            begin
                select @found = 1
            end
            fetch cs3 into @sysattr_wsdluri
        end
        return (@found)
    end
    else /* if (@action = 'help') */
    begin
        declare @subhelp varchar(2048)
        select @subhelp = @wsdluri

        if (@subhelp = 'add')
        begin
            print "Given a WSDL URI, parse the WSDL and add proxy tables to represent the web methods."
            print ""
            print "sp_webservices 'add', 'wsdl uri' [, sds name] [, 'webMethod=proxyTable [, webMethod=proxyTable ]*' ]"
            print "     wsdl uri -- Location of the WSDL."
            print "     sds name -- Name specified for the Consumer in interfaces file."
            print "                 Default value: ws"
            print "     webMethod -- Name of web method to override the proxy table name for."
            print "     proxyTable -- Proxy table name to be used for this web method. "
        end
        else if (@subhelp = 'list')
	begin
            print "Prints information for the WSDL URI. If the WSDL has already been loaded"
            print "all attributes are printed. If the WSDL has not been loaded, the SQL statement"
            print "to create the proxy tables and thier mappings to web methods are printed."
            print ""
            print "sp_webservices 'list' [, 'wsdl uri'] [, sds name] "
            print "     wsdl uri -- Location of the WSDL."
	    print "                 If no wsdl uri is specified, all information about web methods"
            print "                 is listed. If the wsdl uri is not known to the system, "
            print "                 information about how the proxy table will be created is "
            print "                 displayed. "
            print "     sds name -- Name specified for the Consumer in interfaces file."
            print "                 Default value: ws"
        end
        else if (@subhelp = 'remove')
	begin
            print "Removes a WSDL URI from the system. Note that this command"
            print "also deletes the proxy tables that represent the web method as"
            print "well as removing meta data from the system tables."
            print ""
            print "sp_webservices 'remove', 'wsdl uri' [, sds name]"
            print "     wsdl uri -- Location of the WSDL URI to remove."
            print "     sds name -- Name specified for the Consumer in interfaces file."
            print "                 Default value: ws"
        end
        else if (@subhelp = 'modify')
	begin
            print "Modify attribute information for a WSDL URI."
            print ""
            print "sp_webservices 'modify', 'wsdl uri', 'timeout=value'"
            print "    wsdl uri -- Location of the WSDL URI to modify."
	    print "    timeout -- Length of time in seconds to wait for a web method"
        end
        else if (@subhelp = 'deploy')
	begin
            print "Make a user defined web service available though http/https."
            print ""
            print "sp_webservices 'deploy', [ 'all' | 'udws name' ]"
            print "    all -- Deploy all user defined web services in the current database."
	    print "    udws name -- Name of user defined web service to deploy."
        end
        else if (@subhelp = 'undeploy')
	begin
            print "Make a user defined web service unavailable though http/https."
            print ""
            print "sp_webservices 'undeploy', [ 'all' | 'udws name' ]"
            print "    all -- Undeploy all user defined web services in the current database."
	    print "    udws name -- Name of user defined web service to undeploy."
        end
        else if (@subhelp = 'addalias')
	begin
            print "Add an alias to represent a database name."
            print ""
            print "sp_webservices 'addalias', aliasName, databaseName"
            print "    aliasName    -- Alias name to be created."
	    print "    databaseName -- Database the alias is to represent."
        end
        else if (@subhelp = 'dropalias')
	begin
            print "Drop an alias."
            print ""
            print "sp_webservices 'dropalias', aliasName "
            print "    aliasName    -- Alias name to be dropped."
        end
        else if (@subhelp = 'listalias')
	begin
            print "List all aliases."
            print ""
            print "sp_webservices 'listalias' "
        end
        else if (@subhelp = 'listudws')
	begin
            print "List all user defined webservices in current database."
            print ""
            print "sp_webservices 'listudws' [, 'udws name' ]"
	    print "    udws name -- Name of user defined web service to list."
        end
        else if (@subhelp = 'configure')
	begin
            print "Configure the WS server."
            print ""
            print "sp_webservices 'configure', 'optionName', 'optionValue' [, 'sds name' ]"
            print "    optionName  -- Name of the configuration option," 
	    print "                   It is [loglevel]"
            print "    optionValue -- Value of the configuration option,"
	    print "                   It is ['DEBUG'|'INFO'|'ERROR'] "
	    print "    sds name   --  Name specified for the Consumer in interfaces file."
	    print "                   Default value: ws " 
	    end
        else
        begin
            print "Help information for sp_webservices "
            print "sp_webservices 'add', 'wsdl uri' [, sds name]  [, 'webMethod=proxyTable [, webMethod=proxyTable ]*' ]"
            print "sp_webservices 'list', ['wsdl uri'] [, sds name]"
            print "sp_webservices 'remove', 'wsdl uri' [, sds name]"
            print "sp_webservices 'modify', 'wsdl uri', 'timeout=value'"
            print "sp_webservices 'deploy', [ 'all' | 'udws name' ]"
            print "sp_webservices 'undeploy', [ 'all' | 'udws name' ]"
	    print "sp_webservices 'addalias', aliasName, databaseName"
            print "sp_webservices 'dropalias', aliasName "
            print "sp_webservices 'listalias' "
	    print "sp_webservices 'listudws' [, udws_name ] "
            print "sp_webservices 'configure', 'optionName', 'optionValue' [, 'sds name' ]"
            print "sp_webservices help"
            print ""
        end     
    end

    /* If we have sp_configure, then use it to allow updates to */
    /* sysattributes and to enable webservices                  */

    return (0)
go


if exists (select * from master..sysobjects where name = 'sp_configure')
begin
    execute sp_configure "allow updates", 0
end
go

exec sp_procxmode 'sp_webservices', 'anymode'
go

grant execute on sp_webservices to public
go

