ASE114: Web Services Techwave 2004

1
ASE114 Integrating Adaptive Server Enterprise
and Web Services
Kannan Ananthanarayanan Senior Manager,
RD kannan_at_sybase.com Rajesh Chawla Staff
Software Engineer -- XML rchawla_at_sybase.com Augus
t 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
Outline

• Web Services Primer
• Web Services support in ASE - Overview
• Integrating ASE and Web Services

5
Web Services What are they?

• Web Services are self-contained, modular
  applications
• Software components developed by vendors to solve
  some business problems
• Services can reside on different systems and can
  be implemented by vastly different technologies
• Services must be packaged using standard
  protocols, making them easily accessible
• Web Services Technology has become a key to the
  Service Oriented Internet World
• It promises the interaction of unknown and
  unrelated applications on the Internet in a more
  standardized and simpler way than any other
  approach ever claimed
• INTEROPERABILITY is a key reason for Web
  Services
• Platform independent
• Language independent
• Transport independent
• Examples
• http//webservices.try.sybase.com/web/services/Sto
  ckQuotes?wsdl
• http//www.xmethods.net/sd/2001/TemperatureService
  .wsdl

6
Web Services Building Blocks

• Web Services can be
• Described
• Published
• located, and invoked over a network, generally
  the Web.
• Common protocols associated with Web Services are
  
• XML (language and format for information
  exchange)
• WSDL (to describe)
• SOAP (invoke) protocol specifications typically
  over HTTP
• UDDI (publish and locate)

7
Web Services Standards
From Web Services Architecture Scenarios
(http//www.w3.org/TR/ws-arch-scenarios)
8
SOAP (Simple Object Access Protocol)

• Standard defined by W3C (http//www.w3.org/TR/SOA
  P/)
• XML-based protocol that consists of three parts
• Defines what is in a message and how to process
  it
• A set of rules for expressing data types
• Convention for representing remote procedure
  calls (RPCs) and responses
• RPC/Encoding and Document/Literals are two
  popular methods of defining the rules
• Transport independent, although most common
  transport is HTTP

9
SOAP Message - Example

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltsoapenvEnvelope xmlnssoapenv"http//schemas.xm
  lsoap.org/soap/envelope/" xmlnsxsd"http//www.w3
  .org/2001/XMLSchema" xmlnsxsi"http//www.w3.org/
  2001/XMLSchema-instance"gt
• ltsoapenvBodygt
• ltns1execute soapenvencodingStyle"http//schem
  as.xmlsoap.org/soap/encoding/" xmlnsns1"urngenw
  sdl.ws.ase.sybase.com"gt
• ltaseServerName xsitype"xsdstring"gtsuperiorlt/
  aseServerNamegt
• ltasePortNumber xsitype"xsdint"gt9765lt/asePort
  Numbergt
• ltuserName xsitype"xsdstring"gtbharatlt/userNam
  egt
• ltpassword xsitype"xsdstring"gtsybaselt/passwor
  dgt
• ltsqlxOptions xsitype"xsdstring"gttablenamews
  lt/sqlxOptionsgt
• ltsql xsitype"xsdstring"gtselect from
  discountslt/sqlgt
• lt/ns1executegt
• lt/soapenvBodygt

10
WSDL (Web Services Description Language)

• Standard defined by W3C (http//www.w3.org/TR/wsdl
  )
• Describes services as a set of endpoints and how
  to invoke them
• Based on XML
• Supports HTTP, Mime, and SOAP bindings
• Usually generated and consumed transparently by
  web services toolkits

11
WSDL for Document/Literal Web Service

• http//www.xignite.com/xquotes.asmx?WSDL
• ltbinding name"XigniteQuotesSoap"
  type"s0XigniteQuotesSoap"gt 
• ltoperation name"GetQuotes"gt 
• ltsoapoperation soapAction"http//www.xignite.c
  om/services/GetQuotes" style"document" /gt
• ltinputgt 
• ltsoapbody use"literal" /gt
• ltsoapheader message"s0GetQuotesHeader"
  part"Header" use"literal" /gt  
• lt/inputgt
• ltoutputgt
• ltsoapbody use"literal" /gt
• lt/outputgt
• lt/operationgt
• lt/bindinggt
• ltoperation name"GetQuotesgt
• ltdocumentationgt
• This operation returns a 20 minutes delayed
  quote for a list of US Domestic equities.
• lt/documentationgt
• ltinput message"s0GetQuotesSoapIn" /gt
• ltoutput message"s0GetQuotesSoapOut" /gt

12
WSDL for Document/Literal Web Service

• ltmessage name"GetQuotesSoapIn"gt
• ltpart name"parameters" element"s0GetQuotes"
  /gt
• lt/messagegt
• ltmessage name"GetQuotesSoapOut"gt
• ltpart name"parameters" element"s0GetQuotesResp
  onse /gt
• lt/messagegt
• lttypesgt
• ltsschema elementFormDefault"qualified"
  targetNamespace"http//www.xignite.com/services/"
  gt
• ltselement name"GetQuotes"gt
• ltscomplexTypegt
• ltssequencegt
• ltselement minOccurs"0" maxOccurs"1"
  name"Symbol" type"sstring/gt
• lt/ssequencegt
• lt/scomplexTypegt
• lt/selementgt
• lt/typesgt

13
RPC/encoded Web Service Sample WSDL

• http//webservices.try.sybase.com/web/services/Sto
  ckQuotes?wsdl
• ltwsdlportType name"StockQuoteService"gt
• ltwsdloperation name"getQuote"
  parameterOrder"symbol"gt 
• ltwsdlinput message"implgetQuoteRequest"
  name"getQuoteRequest" /gt  
• ltwsdloutput message"implgetQuoteResponse"
  name"getQuoteResponse" /gt  
• lt/wsdloperationgt 
• ltwsdlmessage name"getQuoteRequest"gt 
• ltwsdlpart name"symbol" type"xsdstring" /gt  
  
• ltwsdlmessage name"getQuoteResponse"gt 
• ltwsdlpart name"getQuoteReturn"
  type"xsdfloat" /gt  
• lt/wsdlportTypegt

14
UDDI

• Provides a mechanism for publishing and locating
  web services (WSDL)
• All access to a UDDI registry is by web services
• UDDI registries support
• White pages provide a search capability based
  on a unique identifier (such as DUNS, Thomas
  Registry or other).
• Yellow pages provide a search capability based
  on categories. Categories may include industry,
  product/service offering, and location.
• Green pages provide a search capability based
  on service type or capabilities.
• Standard defined by Oasis (http//www.oasis-open.o
  rg/committees/tc_home.php?wg_abbrevuddi-spec)

15
Other Web Services Standards

• Standards and concepts surrounding Web Services
  is evolving
• Security WS-Security, SAML
• Transaction/Business Process WS-Transaction,
  BPEL
• Management WSM
• Messaging/Events WS-Eventing, WS-Reliability,
  WS-Reliable Messaging
• ASE does not have to implement all of these
• As a Web Services practitioner/solution provider
  we need to be aware of these

16
Security Standards

• Basic SSL/TLS, LDAP based security are very
  relevant and fundamental building blocks
• WS-Security and SAML are popular Web Services
  specific ones
• WS-Security
• Provides security (message integrity and
  confidentiality) specifically for web services
• Security information is communicated by passing
  information in the headers of messages based on
  Simple Object Access Protocol (SOAP)
• Works end-to-end taking into account different
  SOAP transports and SOAP routing
• SAML
• Provides ability for single sign on
• Advantage Consistent/centralized authorization
  mechanism
• Disadvantage Not widely deployed (yet)

17
Putting it together (Standards)
From Web Services Architecture (http//www.w3.org/
TR/2002/WD-ws-arch-20021114/)
18
Putting it altogether (Sybase products)
19
ASE Web Services Producer

• Use Case
• Customer needs secure access to ASE through a
  firewall
• Specific Customer Need
• Programmatically access ASE through a firewall
• ASE Functionality
• Access ASE as Web Service (e.g., can execute
  T-SQL, stored procedures, functions)
• Availability of WSDL file access a webservices
• Leverage and support ASE security features (SSL,
  LDAP look-up and LDAP Authentication)
• Interoperability with Apache (Java) and .NET (C)
  Samples provided

20
ASE Web Services Producer - Example

• WSDL File This file provides the specification
  for the SOAP web methods. After the Producer has
  started, it can be viewed in a browser, by going
  to the URL
• http//ltProducerhostgt8181/services/ase?WSDL
• https//ltProducerhostgt8182/services/ase?WSDL
• Three SOAP web methods available
• login (String serviceName, String username,
  String password)
• execute (String serviceName, String username,
  String password, String sqlxOptions)
• logout ()
• Sample
• execute "http//localhost8181/services/ase"
  rchawla mylogin mypassword "tablenamews" all 1
  "select _at__at_version
• ltws xmlnsxsi"http//www.w3.org/2001/XMLSchema-in
  stance"gt
• ltrowgt
• ltC1gtAdaptive Server Enterprise/12.5.1/EBF
  11420/P/Sun_svr4/OS 5.8/ase1251/1821/32-bit/FBO/We
  d Sep 3 034956 2003
• lt/C1gt
• lt/rowgt
• Output consists of DTD, XML Schema and Data

21
ASE Web Services Producer API

• General Flow/Logic
• Create a service
• Find the port
• Execute
• Java Example Using Apache Client
• ExecuteStoredProcServiceLocator service new
  ExecuteStoredProcServiceLocator ()
• ExecuteStoredProc port service.getase(endURL)
• DataReturn data port.execute (serverName,
  userName, password, sqlxOptions, sql)
• C example Using .NET Client
• WebReference1.ExecuteStoredProcService ase new
  WebReference1.ExecuteStoredProcService()
• ase.Url soapurl
• data ase.execute (aseService, aseUserName,
  asePassword, sqlxOptions, sqlQuery)

22
ASE Web Services Consumer

• Use Case
• Integrate data from Web Service and Relational
  data in ASE
• Specific Customer Need
• In a stored procedure, trigger, or view retrieve
  data from one or more web services and be able to
  use T-SQL to manipulate the output of a web
  service
• ASE Functionality
• Dynamic way to create a mapping between a proxy
  table and a web service using sp_webservices
• The result of a web service call is mapped into a
  resultset
• GUI administration via Sybase Installer and
  SybaseCentral
• Access to any web service (document/literal or
  RPC/encoded)
• Stored procedure (sp_webservices) is provided for
  configuration/setup purposes

23
Web Services Consumer - Usage

• Notify ASE of existence of ASE Web Services A
  specialty service
• sp_addserver webservices, sds, ws
• Executed once per installation, typically at
  install time
• Map external web services into ASE
• sp_webservices 'add','http//www.xmethods.net/sd/
  2001/TemperatureService.wsdl'
• Maps the web services defined in the WSDL file as
  Proxy Tables
• Every (different) external web services must be
  mapped for ASE to access it
• Execute Web Service
• select RRETURN from GETTEMP where _ZIPCODE
  80303

24
RPC/encoded Web Service Sample WSDL

• http//webservices.try.sybase.com/web/services/Sto
  ckQuotes?wsdl
• ltwsdlportType name"StockQuoteService"gt
• ltwsdloperation name"getQuote"
  parameterOrder"symbol"gt 
• ltwsdlinput message"implgetQuoteRequest"
  name"getQuoteRequest" /gt  
• ltwsdloutput message"implgetQuoteResponse"
  name"getQuoteResponse" /gt  
• lt/wsdloperationgt 
• ltwsdlmessage name"getQuoteRequest"gt 
• ltwsdlpart name"symbol" type"xsdstring" /gt  
  
• ltwsdlmessage name"getQuoteResponse"gt 
• ltwsdlpart name"getQuoteReturn"
  type"xsdfloat" /gt  
• lt/wsdlportTypegt

25
Mapping and Invoking RPC/encoded Web Service

• Map External Web Services
• 1gtsp_webservices "add" ,"http//webservices.try.s
  ybase.com/web/services/StockQuotes?wsdl" , ws
• Result
• Proxy table mapped to the external web services
  is created with two columns
• getQuoteReturn and _symbol (_ prefix represents
  an input argument)
• In general number of columns depends on the of
  input and output arguments
• Invoke External Web Services
• 1gt SELECT FROM getQuote WHERE _symbol 'SY'
• Result
• getQuoteReturn     _symbol--------------      --
  -----22.480000           SY(1 row affected)

26
Document/Literal Web Service Sample WSDL

• http//www.xignite.com/xquotes.asmx?WSDL
• ltbinding name"XigniteQuotesSoap"
  type"s0XigniteQuotesSoap"gt 
• ltoperation name"GetQuotes"gt 
• ltsoapoperation soapAction"http//www.xignite.c
  om/services/GetQuotes" style"document" /gt
• ltinputgt 
• ltsoapbody use"literal" /gt
• ltsoapheader message"s0GetQuotesHeader"
  part"Header" use"literal" /gt  
• lt/inputgt
• ltoutputgt
• ltsoapbody use"literal" /gt
• lt/outputgt
• lt/operationgt
• lt/bindinggt
• ltoperation name"GetQuotesgt
• ltdocumentationgt
• This operation returns a 20 minutes delayed
  quote for a list of US Domestic equities.
• lt/documentationgt
• ltinput message"s0GetQuotesSoapIn" /gt
• ltoutput message"s0GetQuotesSoapOut" /gt

27
Document/Literal Web Service Sample WSDL

• ltmessage name"GetQuotesSoapIn"gt
• ltpart name"parameters" element"s0GetQuotes"
  /gt
• lt/messagegt
• ltmessage name"GetQuotesSoapOut"gt
• ltpart name"parameters" element"s0GetQuotesResp
  onse /gt
• lt/messagegt
• lttypesgt
• ltsschema elementFormDefault"qualified"
  targetNamespace"http//www.xignite.com/services/"
  gt
• ltselement name"GetQuotes"gt
• ltscomplexTypegt
• ltssequencegt
• ltselement minOccurs"0" maxOccurs"1"
  name"Symbol" type"sstring/gt
• lt/ssequencegt
• lt/scomplexTypegt
• lt/selementgt
• lt/typesgt

28
Mapping and Invoking Document/literal Web Service

• Map External Web Services
• 1gtsp_webservices "add","http//www.xignite.com/xqu
  otes.asmx?WSDL", ws
• Result
• Proxy table mapped to the external web services
  is created with two columns
• _inxml and outxml (_ prefix represents an input
  argument)
• Proxy tables are always created with 2 columns
  for document/literal web method
• Invoke External Web Services
• 1gt SELECT  outxml FROM GetQuotes WHERE  _inxml
  
• 'ltGetQuotes xmlns"http//www.xignite.com/servic
  es/"gt ltSymbolgtSYlt/Symbolgtlt/GetQuotesgt
• Result
• outxml
• ----------
• lt?xml version"1.0" encoding"UTF-8" 
  ?gtltGetQuotesResponse xmlns ....gt...lt/GetQuotes
  Responsegt
• (1 row affected)

29
Join ASE table with web method - Example

• Map
• 1gt create table stocksymbol(symbol varchar(100))
• 1gt insert stocksymbol values("SY")
• 2gt insert stocksymbol values("PSFT")
• 3gt insert stocksymbol values("ORCL")
• 4gt insert stocksymbol values("MSFT")
• 5gt insert stocksymbol values("SUNW")
• 1gtsp_webservices "add" , "http//webservices.try.s
  ybase.com/web/services/StockQuotes?wsdl", ws
• Invoke
• 1gt SELECT symbol,getQuoteReturn
  FROM getQuote,stocksymbol
• WHERE getQuote._symbol stocksymbol.symbol
• symbol        getQuoteReturn------        ------
  --------SY            22.469999
  PSFT          21.040001 ORCL          13.730000
  MSFT          27.070000 SUNW           5.700000
  (5 rows affected)

30
View using ASE table and web service - Example

• Map
• 1gt create table stocksymbol(symbol varchar(100))
• 1gt sp_webservices "add" , "http//www.xignite.com/
  xquotes.asmx?WSDL", ws
• 1gt CREATE VIEW getstockvw as
• SELECT
• Symbolxmlextract('//Quote/Symbol/text()',outxml
  returns varchar(10)),
• Name xmlextract('//Quote/Name/text()',outxml
  returns varchar(100)),
• Date xmlextract('//Quote/Date/text()',outxml
  returns date),
• Time xmlextract('//Quote/Time/text()',outxml
  returns varchar(25)),
• Openval xmlextract('//Quote/Open/text()',outxml
  returns decimal(15,3)),
• High xmlextract('//Quote/High/text()',outxml
  returns decimal(15,3)),
• Low xmlextract('//Quote/Low/text()',outxml
  returns decimal(15,3)),
• Last xmlextract('//Quote/Last/text()',outxml
  returns decimal(15,3)),
• Volume xmlextract('//Quote/Volume/text()',outxml
  returns decimal(15,3)),
• PercentChange xmlextract('//Quote/PercentChange/
  text()',outxml returns decimal(15,3))
• FROM GetQuotes ,stocksymbol
• WHERE _inxml 'ltGetQuotes xmlns"http//www.xign
  ite.com/services/"gtltSymbolgt'symbol'lt/Symbol
  lt/GetQuotesgt

31
View using ASE table and web service - Example

• Invoke
• 1gt select from getstockvw where Symbol in
  ('SY','ORCL')
• Symbol  Name           Date                   Time
     Openval  High  Low  Last  Volume  PercentChange
  ------  -----          ----                   ---
  -   -------  ----  ---  ----  -------  -----------
  SY      SYBASE INC     Feb 11 2004 255PM
  ET  22.22  22.53  22.03  22.53  344600.00   1.40O
  RCL    ORACLE CORP     Feb 11 2004 300PM
  ET 13.53  13.79  13.33  13.73  29931784.00  2.54
  (2 rows affected)

32
Administration

• UI interface for producer and consumer via ASE
  Plug-in
• Create Web Services Consumer
• Delete Web Services Consumer
• Modify/View Property of Web Services Consumer
• Execute Web Service Consumer
• Web Browser interface supplied
• Requires producer to be running, as it shares the
  HTTP server with it
• Command line interface using sp_webservices
  stored procedure is provided
• Command line scripts to start and stop web
  services is provided

33
Security

• Logins to Producer can be authenticated via LDAP
  or ASE
• HTTPS support for Producer
• Secure exchange between client and the web
  services engine
• Password authentication is required for Web
  Services administrative activities
• Any use or storage of password internally is
  encrypted using strong encryption techniques that
  are available in ASE

34
Licensing

• This feature is a licensed option
• Can be licensed independently of other ASE
  features (it is an option, not a package)
• License name ASE_WEBSERVICES
• In addition including license entry into the
  license.dat file, must enable it using
• sp_configure enable webservices, 1
• Having XML Management package along with
  ASE_WEBSERVICES is recommended for typical usage
• A single Web Services Engine can serve multiple
  ASE. Each ASE must have its own ASE_WEBSERVICES
  license

35
ASE Web Services Summary

• ASE Web Services functionality is foundation for
  Service Oriented Integration
• ASE can host Web Services and Integrate external
  Web Services
• ASE Web Services producer was introduced in
  12.5.1 and Consumer (for integrating external web
  services) was introduced in 12.5.2
• Producer provides access to ASE through a
  firewall
• Consumer provides the ability to access a web
  service from T-SQL
• ASE Web Services engine is light weight and runs
  external to ASE Typically on the same machine
  where ASE runs
• ASE Web Services functionality is a licensed
  option and is available on all major platforms