Web Services Enable Your Database

Mar. 27, 2003 12:00 AM

"Si tu ne vas pas à Lagardère, Lagardère ira à toi!"
"Le Bossu", Alexandre Dumas

On guard! What do Web services have to do with a swordsman? Well, paraphrasing Alexandre Dumas's character, "if you don't go to Web services, Web services will come to you." Web services are pervading every layer of enterprise computing, from packaged e-business applications (e.g., ERP, CRM) to middle-tier (e.g., J2EE, .NET) and database infrastructure.

Web services promise easy access to remote content and application functionality using industry-standard mechanisms, without any dependency on the provider's platform, the location, the service implementation, or the data format.

The proliferation of structured and unstructured data and data logic in databases, the increasing momentum of XML as a data exchange format, the de facto acceptance of HTTP as a ubiquitous transport mechanism -in the context of heterogeneous environments - has aroused interest in database Web services. For example, many applications have significant amounts of business logic (such as PL/SQL packages) that run in the database. Database Web services allow organizations to leverage this existing investment.

Database Web services work in two directions: database as service provider, i.e. calling from the outside in, which lets client applications access the database via Web services mechanisms; and database as service consumer, i.e. calling from the inside out, which lets a SQL query or an application module in a database session consume an external Web Service. This article looks at the benefits of turning your database into a Web services player.

I'll explore how the database can be enabled both as a Web service provider and a consumer of external Web services. I'll also discuss future database support for Web services.

Database as Service Provider
Motivation
Why would you consider exposing database operations as Web services? What if you could expose existing stored procedures as Web services? When should you consider database Web services and when should you consider middle-tier (J2EE, .NET) Web services?

Many organizations are considering exposing their existing data and data logic that runs in the database as Web services (to both internal and external audiences). Consider Amazon Web services ( www.amazon.com/webservices) or Google Web services (www.google.com/apis/index.html). These let client applications discover and interact with their catalogs or search engines using standard Web services protocols (WSDL, SOAP). Generally, when considering the circumstances in which database Web services should be deployed, the rules of application partitioning across the middle tier and database should be observed. The business logic associated with Web services fits most naturally in the middle tier, and data logic in the database. This separation of concerns reduces application complexity; increases security, reuse, and data access efficiency; and localizes the impact of schema change.

Consider stored procedures as an example. Stored procedures are an essential database-programming model that allows a clean separation of data-centric logic that runs in the database from business logic that runs in the middle tier. Exposing a stored procedure as a Web service is a more cost-effective, more secure, cleaner, and more efficient choice than exposing a middle-tier component.

Architecture: The Service Provider Framework and the Service Invocation Mechanism
As part of their end-to-end Web services offerings, all major database vendors, including Oracle, IBM, and Microsoft, now provide access to their databases through Web services mechanisms (SOAP, WSDL, UDDI). Currently, they are all leveraging their existing middle-tier Web services framework. An example is shown in Figure 1.

IBM's DB2 also leverages the middle tier to handle SOAP encoding/decoding but uses a file-based approach for designating the database operations that are to be exposed as Web services. Microsoft's SQL Server utilizes the SQL Server 2000 Web Service Toolkit. A multistep process consisting of defining the database operations to be exposed as Web services, configuring a SOAP virtual name, and defining an IIS folder associating a SQLXML virtual name to the IIS subfolder is required.

Database APIs for Implementing Database Web Services
Due to the rapid evolution and maturing of Web services standards and technology, an exhaustive list of APIs and features provided by each database vendor is beyond the scope of this paper. However, I can give you a general idea of what database Web services you can expect to see:

SQL queries and DML statements: The sky's the limit here. SQL offers amazing possibilities for retrieving, updating, and storing any data that can be held in your database, including relational, text, spatial, multimedia, and XML data. But let's not miss the point: Web services are not the new or fashionable way of submitting SQL statements to your database, nor a replacement for gateways. You should consider implementing coarse-grain services using SQL statements only when the benefits outweigh the SOAP overhead. SQL database Web services will allow client applications and lightweight SOAP enabled appliances to, for example, query a catalog or retrieve the map of a location based on the ZIP code.

XML APIs: The integration of XML with relational databases enables storing, indexing, and retrieving semistructured and nonstructured data. Depending on the level of integration, XML documents can be stored either natively or through XML views over relational structure. XML database Web services will let client applications (i.e., desktop tools) retrieve XML documents, make changes locally, and synchronize back to a back-end database. This approach also hides the complexity of XML APIs, as well as concerns over storage and indexing, from the service consumer.

Stored procedures and user-defined functions: Stored procedures are an essential database-programming model that allows a clean separation of data-centric logic that runs in the database from business logic that runs in the middle tier. Stored procedures can be programmed in a database-specific language, e.g. PL/ SQL, or in a portable and database-independent language such as Java. The ANSI SQLJ Part 1 specifies the ability to invoke static Java methods from SQL as procedures or functions. The beauty of Web services is that you can leverage your investment in stored procedures and expose these as Web services without having to worry about the language in which the stored procedures are implemented.

Database messaging and queuing APIs: A Web services API can be used to expose AQ operations: message enqueuing/ dequeuing and notification registration; capturing and propagating database changes operations; and providing access to queue administration operations.

Facing Web Services Issues
How do database Web services address common Web services issues such as data type mapping, security, transactions, and interoperability?

Data type mapping: Producing XML documents from database types requires some metadata. Simple SQL types are taken care of through generic or predefined XML Schema. Complex database types such as Resultset, JDBC WebRowset, ADO Datasets, and other proprietary hierarchical data shapes will be mapped through specific XML Schema. The introduction of Pull parser and XML writer in JDBC and .NET will improve and simplify XML documents creation and parsing.

Security: Since business data is the most valuable corporate asset, security management is a critical requirement. For database Web services, this requirement is addressed by leveraging the Web services security framework as well as database security mechanisms such as controlling the effective database user or schema at runtime and restricting the allowed operations through the service implementation.

Transactions: Since standards for conducting Web services transactions across database, middle-tier, and packaged applications are still evolving, DML statements (update, insert, and delete) exposed as Web services will be automatically committed without any transaction context propagation across calls and updates will use optimistic locking.

Interoperability: Database Web services can leverage middle-tier interoperability features. For example, Web services running in the Oracle9i Database leverage Oracle9i Application Server to benefit from built-in interoperability based on Web Services Interoperability (WS-I) and SOAPBuilder efforts.

Database as Service Consumer
Motivation - Scenario
Relational databases are extending their storage, indexing, and searching capabilities to semistructured and nonstructured data, in addition to enabling federated data, e.g. from heterogeneous and external sources including Web services. The ability to call out Web services enables databases to track, aggregate, refresh, and query dynamic data, as well as data produced on demand such as stock prices, currency exchange rate, interest rate, IRS tax tables, scientific data, and weather information. For example, a database job can be scheduled to run at a predefined frequency to invoke external Web services that return inventory information from multiple suppliers and update a local inventory database. Another example is that of a Web Crawler: a database job can be scheduled to collate product and price information from a number of sources.

Architecture: The Service Consumer Framework
Consuming data from external Web services requires the following steps: building the SOAP message based on the Web service endpoint; sending the SOAP message over HTTP to the Web service endpoint; receiving the SOAP response; extracting resulting data from the SOAP message body, and dealing with faults and exceptions. Architectural issues you need to be aware of include:

Non-SOAP-aware infrastructure: This basic approach, taken by some database vendors, requires user-defined functions to programmatically build a SOAP-like XML text (the SOAP envelope is mostly static), and send it over HTTP, parse, and decompose the SOAP response. This approach may be acceptable if you know the Web service and its format ahead of time, and if you are willing to read and interpret the service's WSDL specification yourself. It requires some programming skills, but can rapidly become time consuming and cumbersome.

SOAP-aware infrastructure: A more flexible alternative is to use an existing SOAP client stack that understands WSDL. For example, the Oracle database allows loading Java-based libraries such as the Apache SOAP client or Sun's JAX-RPC, permitting you to dynamically interact with a Web service or utilize pregenerated client-proxy code. The SOAP stack can be refreshed simply by loading a newer version.

Static Invocation: A pregenerated client proxy can be obtained and used by the database for invoking the Web services. A client proxy simplifies Web service invocations as it knows exactly where the service is located without looking it up in the UDDI registry and does all the work to construct the SOAP request, and marshall and unmarshall parameters. Once you have created a proxy instance, you can call the desired Web service operations on it.

Dynamic Invocation: The static invocation is the most common invocation style. However, it requires downloading the pregenerated proxy. Dynamic invocation provides the ability to dynamically construct the SOAP request and access the service without the client proxy.

Integration with SQL Engine:
SQL over SOAP
After dealing with the invocation style, you want to integrate the Web service information at the heart of the database - the SQL engine. I'll describe two mechanisms that allow you to accomplish this.

Consuming External Web Service - Function Call: The database session invokes the Web service through a user-defined function call either directly within a SQL statement or view, or via a variable (see Figure 2).

X := getTemp(zipcode) SELECT city_name, getTemp(zipcode) FROM ... WHERE ...
CREATE VIEW city_temp AS SELECT city_name, getTemp(zipcode) FROM ...

Virtual Tables - Mining Dynamic Data: What if multiple values are returned from the Web services? And what if you want to track and materialize the range of values returned from a series of a Web service function calls as a SQL table or view structure? How can virtual tables be used in real life? Consider the two following scenarios:
- Scenario 1: Calculating Aggregate Values: An application is implemented to aggregate some temperature values for a set of cities. The application can be implemented as a store procedure that requests the current temperatures of selected cities by invoking a public Temperature Web service with the zip codes of the selected cities. Aggregate functions, such as max, min, and avg, can be applied to the resulting data set.
- Scenario 2: Collating Data: An application is designed that collates, at predefined time intervals, the temperature for a set of cities and stores this information in a local database. This application can be implemented by a database batch job that calls the Temperature Web service with the Zip codes of all the cities and stores the result data in a local database table.

An architecture supporting both of these scenarios is shown in Figure 3.

What's Next for Database Web Services
Web services have received significant attention and there is a great deal of industry excitement around the opportunities afforded by them. While most of this attention has focused on middle-tier Web services, an increasing interest in database Web services has recently emerged as organizations reflect on their existing investments in data and stored procedures. So what can you expect in the future? We're likely to see increased integration with tools and frameworks, support for more complex data types, and convergence with Grid technologies. Tools will begin to support database Web services coupled with emerging Web services standards around choreography and orchestration, and we will also start to see SOAP bindings for protocols other than HTTP (e.g., FTP, vendor-specific messaging protocols).

As complex and hierarchical data types are widely used, you can also expect cross-vendor interoperability for hierarchical data shapes such as JDBC WebRowset and ADO Datasets, so as to allow data to be exchanged between Web services consumers and producers without requiring XML Schema Definition information. You can also expect a convergence between database Web services and (data-centric) Grid data services for service description and publication, service invocation, event subscription, and notification.

Conclusion
Database Web services leverage your existing server-side infrastructure, allowing you to use your database as both service provider and service consumer. I showed you how we at Oracle allow you to turn your database into a Web service provider, thus enabling you to share data and metadata across corporate intranets and access the database operations, e.g. triggers, through SOAP requests. I also explained how you can turn your database into a Web services consumer to access dynamic data. Finally, I gave you an overview of future database Web services capabilities.

Related Links

▪ Figure 1

▪ Figure 2

▪ Figure 3

About Kuassi Mensah