sp_webservices

Creates and manages the proxy tables used in the SAP ASE Web Services Engine.

Syntax

To create a proxy table:

sp_webservices 'add', 'wsdl_uri' [, sds_name] 
	[, 'method_name=proxy_table
	 [,method_name=proxy_table ]* ' ]

To display usage information for sp_webservices:

sp_webservices help [, ’option’]

To list the proxy tables mapped to a WSDL file:

sp_webservices 'list' [, 'wsdl_uri'] [, sds_name]

To modify timeout setting:

sp_webservices 'modify', 'wsdl_uri', 'timeout=time'

To remove proxy tables mapped to a WSDL file:

sp_webservices 'remove', 'wsdl_uri' [, sds_name]

Options for User-Defined Web Services:

To create a database alias for user-defined Web services:
```
sp_webservices 'addalias' alias_name , database_name
```
To deploy a user-defined Web service:
```
sp_webservices 'deploy', ['all' | 'service_name']
```
To drop a database alias in user-defined Web services:
```
sp_webservices 'dropalias' alias_name
```
To list the proxy tables mapped to a WSDL file in user-defined Web services:
```
sp_webservices 'listudws' [, 'service_name']
```
To list a database alias or aliases for a user-defined Web service.
```
sp_webservices 'listalias'
```
To undeploy a user-defined Web service:
```
sp_webservices 'undeploy', ['all' | 'service_name']
```

Parameters

'add', 'wsdl_uri' [, sds_name] [, 'method_name=proxy_table[, method_name=proxy_table ]* ' ] – is used to create a proxy table for a Web method specified by a WSDL file. When the add option is used successfully, the list option is invoked automatically to describe the schema of the new proxy table:
- wsdl_uri – is the location for the WSDL file to be mapped to the new proxy table. If this parameter is specified, Web Services ensures that the URI exists in the syswsdl table.
- sds_name – is the name specified for the ASE Web Services Engine in the interfaces or sql.ini file. The default value is ws. If no entry exists in the sysattributes table, an error results.
- method_name – is the name of the Web method to be mapped to a proxy table. The method_name specified must be the name of a Web method specified in the associated WSDL file.
- proxy_table – is the name of proxy table to which the Web method specified in method_name is mapped.
'addalias' alias_name , database_name – is used to create an alias representing a database name in user-defined Web services, where:
- alias_name – (required) is the alias for the specified database.
- database_name – (required) is the name of the database for which the alias is specified.
An alias provides greater control in specifying the portion of the URL representing the database name. Used with the userpath option of the create service command, an alias provides complete control over the URL used to access a user-defined Web service.
'deploy', ['all' | 'service_name'] – is used to deploy a user-defined Web service, making it accessible to the ASE Web Services Engine through HTTP or HTTPS, where:
- all – specifies that all user-defined Web services are to be deployed for the current database.
- service_name – is the name of the user-defined Web service to be deployed.
The deploy and undeploy options are used to control when user-defined Web services are available. The system role webservices_role privilege is required for this option.

If the all parameter is specified, the ASE Web Services Engine deletes its internal cache of user-defined Web services and rereads all metadata about user-defined Web services from SAP ASE.

You cannot drop or rename a user-defined Web service that is currently deployed.
'dropalias' alias_name – is used to drop an alias representing a database name, where alias_name is the alias to be dropped.
You cannot drop an alias if it is being referenced by a deployed user-defined Web service. To drop the alias, undeploy the user-defined Web service that references the alias first.
help[, 'option'] – provides instructions and examples illustrating how to use the sp_webservices stored procedure. The valid values for 'option' are add, list, remove, and modify.
If you do not specify a value for option, the help option prints a brief syntax description for the add, addalias, deploy, dropalias, list, listalias, listudws, modify, remove, and undeploy options.
'list' [, 'wsdl_uri'] [, sds_name] – lists Web methods described in a WSDL file, where:
- wsdl_uri – is the URI for the mapped WSDL file. If you do not specify a value for wsdl_uri, the list option displays information about all Web methods that have been mapped to proxy tables.
- sds_name – is the name of the SDS server specified for the ASE Web Services Engine in the interfaces or sql.ini file. The default value is ws. If no entry exists in the sysattributes table, an error results.
If you specify neither the wsdl_uri nor the sds_name parameter, all entries in the sysattributes table are listed, ordered by wsdlid.
If the Web methods described in the WSDL file:
- Have already been mapped to proxy tables – the list option prints information about each proxy table.
- Have not already been mapped to proxy tables – the list option prints SQL that can be used to create proxy tables.
'listalias' – is used to list all aliases in user-defined Web services.
'listudws' [, 'service_name'] – is used to list user-defined Web services for the current database, where service_name is the name of the user-defined Web service to be listed.
If you do not specify the service_name parameter, all user-defined Web services are listed.
'modify', 'wsdl_uri', 'timeout=time' – is used to modify the attribute information for a WSDL file, where:
- wsdl_uri – is the URI of the WSDL file for which attribute information is to be changed.
- time – is the interval in seconds during which a Web method must respond before the operation is aborted.
'remove', 'wsdl_uri' [, sds_name] – is used to remove a proxy table mapping for a Web method, where:
- wsdl_uri – is the URI of the WSDL file for which the proxy table is to be removed.
- sds_name – is the name of the SDS server specified for the ASE Web Services Engine in the interfaces or sql.ini file. The default value is ws.
  
  Note: An error results if no entry exists in the sysattributes table.
'undeploy', ['all' | 'service_name'] – is used to make a user-defined Web service inaccessible to the SAP ASE Web Services Engine through HTTP or HTTPS, where:
- all – specifies that all user-defined Web services are to be undeployed for the current database.
- service_name – is the name of the user-defined Web service to be undeployed.
Use the deploy and undeploy options to control when user-defined Web services are available. The system role webservices_role privilege is required for this option.

Examples

Example 1 – Invokes an RPC/encoded Web method to display the exchange rate between two currencies.
1. Use the add option of sp_webservices to map Web methods to proxy tables:
```
1> sp_webservices 'add', 'http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl'
2> go
```
  The getRate Web method is mapped to a proxy table of the same name.
2. Invoke the Web method by selecting from the proxy table:
```
1> select * from getRate where _country1 ='usa' and _country2 = 'india'
2> go
```
  The results returned for the previous select show the exchange rate for the specified parameters:
```
Result          _country1       _country2
43.000000       usa             india
(1 row affected)
```

Example 2 – Invokes a Web method to display stock information within an XML document.

Use the add option of sp_webservices to map Web methods to proxy tables:
```
1> sp_webservices "add" , "http://www.webservicex.net/stockquote.asmx?WSDL"
2> go
```
The GetQuote Web method is mapped to a proxy table of the same name.

Invoke the Web method by selecting the outxml column of the GetQuote proxy table:

1> select outxml from GetQuote where _inxml = '<?xml version="1.0"
encoding="utf-8"?>
2>     <GetQuote xmlns="http://www.webserviceX.NET/">
3>       <symbol>SY</symbol>
4>     </GetQuote>'
5> go

The results for the previous select display quote information within an XML document:

 outxml

<?xml version="1.0" encoding="UTF-8" ?><GetQuoteResponse 
xmlns="http://www.webserviceX.NET/"><GetQuoteResult><StockQuotes><Stock>
<Symbol>SY</Symbol><Last>21.48</Last><Date>7/21/2005</Date><Time>4:01pm
</Time><Change>+1.72</Change><Open>20.00</Open><High>21.60</High>
<Low>19.91</Low><Volume>2420100</Volume><MktCap>1.927B</MktCap>
<PreviousClose>19.76</PreviousClose><PercentageChange>+8.70%
</PercentageChange><AnnRange>12.75 - 20.44</AnnRange><Earns>0.706</Earns>
<P-E>27.99</P-E><Name>SYBASE INC</Name></Stock></StockQuotes>
</GetQuoteResult></GetQuoteResponse>

(1 row affected)

Example 3 – Invokes the GetQuote Web method, mapped to a proxy table in the previous example, through a view to display stock information.

Create a table to hold symbols representing stocks to use this Web service:
```
1> create table stocksymbol(symbol varchar(100))
2> go
```

Insert data into the stocksymbol table:

1> insert stocksymbol values("SY")
2> insert stocksymbol values("ORCL")
3> go

Create a view that invokes the GetQuote Web method:

1> CREATE VIEW getstockvw as
2> select Symbol = xmlextract('//Stock/Symbol/text()',outxml returns varchar(5)),
3>   Name = xmlextract('//Stock/Name/text()',outxml returns varchar(20)),
4>   Time = xmlextract('//Stock/Time/text()',outxml returns varchar(10)),
5>   Date = xmlextract('//Stock/Date/text()',outxml returns date),
6>   High = xmlextract('//Stock/High/text()',outxml returns decimal(15,2)),
7>   Low = xmlextract('//Stock/Low/text()',outxml returns decimal(15,2))
8> FROM GetQuote ,stocksymbol
9> WHERE _inxml = '<GetQuote xmlns="http://www.webserviceX.NET/"><symbol>'+symbol+'</symbol></GetQuote>'
10> go

Select from the getstockvw view to view output from the GetQuotes method:

1> select * from getstockvw
2> go

The results for the previous select display quote information for the parameters specified by the view definition:

Symbol   Name            Time      Date           High     Low
-------  -------------   -------   ------------   ------   ------
SY       SYBASE INC      4:01pm    Jul 21 2005    21.60    19.91
ORCL     ORACLE CORP     4:00pm    Jul 21 2005    14.05    13.54
MSFT     MICROSOFT CP    4:00pm    Jul 21 2005    26.48    26.19

(3 rows affected)

Example 4 – Shows an audit table entry for the following command entered in the pubs2 database by the user “bob”:
```
sp_webservices 'deploy', 'all'
```
The corresponding audit table entry lists 110, bob, and pubs2 as values in the event, loginname, and dbname columns, respectively. The extrainfo column contains the following:
```
webservices_role; deploy_all; ; ; ; ; bob/ase;
```
Example 5 – Shows an audit table entry for the following command entered in the pubs2 database by the user “bob”:
```
sp_webservices 'deploy', 'rawservice'
```
The corresponding audit table entry lists 110, bob, and pubs2 as values in the event, loginname, and dbname columns, respectively. The extrainfo column contains the following:
```
webservices_role; deploy; ; ; ; ; bob/ase;
```
Example 6 – Shows an audit table entry for the following command entered in the pubs2 database by the user “bob”:
```
sp_webservices 'undeploy', 'all'
```
The corresponding audit table entry lists 111, bob, and pubs2 as values in the event, loginname, and dbname columns, respectively. The extrainfo column contains the following:
```
webservices_role; undeploy_all; ; ; ; ; bob/ase;
```
Example 7 – Shows an audit table entry for the following command entered in the pubs2 database by the user “bob”:
```
sp_webservices 'undeploy', 'rawservice'
```
The corresponding audit table entry lists 111, bob, and pubs2 as values in the event, loginname, and dbname columns, respectively. The extrainfo column contains the following:
```
webservices_role; deploy; ; ; ; ; bob/ase;
```
For a full description of sysaudits table columns, see the System Administration Guide.

Usage

If you not specify method_name and proxy_table values for a Web method, the proxy table generated for the Web method is, by default, the name of the Web method specified in the WSDL file. If there is already a proxy table with the name of this Web method, a new proxy table is generated with a name like:

method_nameN

Where:

method_name – is the default proxy table name
N – is a digit from 1 to 9 denoting each successive mapping of the Web method. There can be as many as 99 duplicate proxy tables.

If you do specify method_name and proxy_table values for a Web method, the name of the proxy table must be new. If there is already a proxy table with the name specified in proxy_table, an error results, and none of the Web methods specified in the add option are mapped to proxy tables.

The output from the add option lists the methods that have been successfully mapped to proxy tables as well as those that have not been mapped. The name of a proxy table for an unmapped Web method is indicated as NULL in the output from the add option.

Note: The columns used for input and output vary for proxy tables generated for RPC/encoded Web methods and document/literal Web methods. A proxy table representing an RPC/encoded Web method contains a column for each input and output parameter. A proxy table representing a document/literal Web method contains two columns, _inxml and outxml.

Permissions

You must be a user with webservices_role (for deploy and undeploy) to execute sp_webservices. Permission checks do not differ based on the granular permissions settings.

The system role webservices_role is required to use the deploy and undeploy options for sp_webservices. To execute a user-defined Web service, a valid login and permissions to execute the corresponding stored procedure are required.

To create, drop, and execute user-defined Web services, you need the same privileges as are necessary to create, drop, and execute stored procedures in SAP ASE. See the System Administration Guide for details on how to set the proper privileges using the grant and revoke commands.

Auditing

Audit event number 110 corresponds to the deploy option of sp_webservices.
Audit event number 111 corresponds to the undeploy option of sp_webservices.

User-defined Web services are modeled as stored procedures within SAP ASE. In manipulating user-defined Web services, SAP ASE generates the following events using the existing auditing coverage for stored procedures:

The creation of a user-defined Web service – Event 11 named "Create Procedure" is generated
The dropping of a user-defined Web service – Event 28 named "Drop Procedure" is generated
The execution of a user-defined Web service – Event 38 named "Execution of Stored Procedure" is generated

For detailed information on existing auditing functionality, see the System Administration Guide.

In addition to existing auditing functionality, SAP ASE provides two audit events for the deploy and undeploy options of sp_webservices.

Audit records are stored in the sysaudits system table. You can enable auditing for Web services with the following command:

sp_audit "security", "all", "all", "on"