Invoking a Web service

To invoke a Web service using the ASE Web Services Engine, use the following procedure:

1. Start the ASE Web Services Engine.

2. Use the add option of sp_webservices to map the Web service to a proxy table in Adaptive Server Enterprise.

3. Use sp_help to determine the input and output parameters needed to invoke the Web method.

4. Invoke the Web method with a select statement on the proxy table.

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 Web method getRate is mapped to a proxy table of the same name.

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)

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 Web method GetQuote1 is mapped to a proxy table of the same name.

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

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

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

 outxml

<?xml version="1.0" encoding="UTF-8" ?><GetQuote1Response
xmlns="http://www.webserviceX.NET/"><GetQuoteResult><StockQuotes><Stock><Symb
ol>SY</Symbol><Last>21.48</Last><Date>7/21/2005</Date><Time>4:01pm</Time><Cha
nge>+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></GetQuote1Response>

(1 row affected)

To use this Web service, you must create a table to hold symbols representing stocks:

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

Now create a view that invokes the GetQuote1 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 GetQuote1 ,stocksymbol
9> WHERE _inxml = '<GetQuote1 xmlns="http://www.webserviceX.NET/"><symbol>'+symbol+'</symbol></GetQuote1>'
10> go

Select from the getstockvw view to view output from the GetQuote1 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)