sp_webservices

Description

Creates and manages the proxy tables used in the Adaptive Server 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]

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

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.

1. 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.

2. 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.

1. Create a table to hold symbols representing stocks to use this Web service:

   1> create table stocksymbol(symbol varchar(100))
   2> go
   

2. Insert data into the stocksymbol table:

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

3. 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
   

4. 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;

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 Adaptive Server Enterprise 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 the following:

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.

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 Adaptive Server Enterprise. See the Adaptive Server Enterprise 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 Adaptive Server Enterprise. In manipulating user-defined Web services, Adaptive Server Enterprise 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, Adaptive Server Enterprise 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"

See also