Perl Cd Bookshelf [Electronic resources] نسخه متنی

Building and Publishing the First Database Web Service

In this section, we discuss building a web service for a fictitious bookstore, Big Barrel Books (BBB), that needs to enable its partners to promote and sell its books. Through web services, BBB’s partners will be able to query and update its catalog of books in the Oracle database.

Setting Up the Database Schema

In real applications, the database schema that is set up to manage a bookstore may be very complicated. It may require more than one table, with many columns in each table, such as a separate table to store data for the authors of the book. However, in this example, to help you focus on the techniques of building web services, we have greatly simplified the definition of these tables. Therefore, the following table, called book, is created to store the records of books:

CREATE TABLE book(
ISBN VARCHAR2(200) PRIMARY KEY,
author varchar2(200),
title VARCHAR2(200),
price FLOAT,
instock_num NUMBER(20)
);

In addition to the book table, a partner table is created to store information about the bookstore’s partners and a book_sale table is created to store the partners’ transactions that are executed within the database:

CREATE TABLE partner(
partner_id NUMBER PRIMARY KEY,
partner_name VARCHAR2(200),
discount_rate NUMBER);
CREATE TABLE book_sale(
ISBN VARCHAR2(12),
op_time DATE,
partner_id NUMBER,
checkout_num NUMBER);

In this example, a discount rate is assigned for each partner that is stored in the partner table. The book_sale table keeps the details of each transaction on the book table, including the ISBN of the book, the time of the transaction, the ID of the partner that performs the transaction, and the number of items checked out of the inventory. You can insert the sample data into the book table as follows:

INSERT INTO book VALUES('0072225211','Kevin Loney,
George Koch, Tusc','Oracle9i: The Complete Reference',74.99,1000);
INSERT INTO book VALUES('0072229527',
'Mark Scardina, Ben Chang, Jinyu Wang',
'Oracle Database 10g XML and SQL',49.99,1000);

You can insert partners into the database as follows:

INSERT INTO partner VALUES(1,'book wholesale',0.6);

This example inserts a sample partner called Book Wholesale and assigns it a 60 percent price discount from this bookstore.

In the example, two web services will be provided. The first one allows the partners to query the database and list all the information about the books they can sell, including the title, author, and price of each book. The query shows both the original price and the price the partner can get after applying the discount rate. The second web service is a transactional operation that allows the partners to check out books from the bookstore’s inventory. After each transaction, the transaction information is stored in the book_sale table for later reference. A PL/SQL package called booksale_pkg is created to allow transaction operations as follows:

CREATE OR REPLACE PACKAGE booksale_pkg AS
FUNCTION query(p_partner_id IN NUMBER) RETURN XMLType;
FUNCTION checkout(p_partner_id IN NUMBER, p_isbn in VARCHAR2,
p_book_num IN NUMBER) RETURN VARCHAR2;
END booksale_pkg;
/

The two functions in the PL/SQL package can be used to provide the services, and the PL/SQL package body for booksale_pkg is created as follows:

CREATE OR REPLACE PACKAGE BODY booksale_pkg AS
FUNCTION query(p_partner_id IN NUMBER) RETURN XMLType AS
out XMLType;
qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN
qryCtx := DBMS_XMLGEN.NEWCONTEXT(
'SELECT b.partner_id AS "@partner_id", a.isbn, a.author,
a.title, a.price, a.price*discount_rate AS sale_price
FROM book a, partner b
WHERE b.partner_id=:PARTNER_ID');
DBMS_XMLGEN.setBindValue(qryCtx, 'PARTNER_ID', p_partner_id);
out := DBMS_XMLGEN.GETXMLTYPE(qryCtx);
DBMS_XMLGEN.closeContext(qryCtx);

RETURN out;
END query;
FUNCTION checkout(p_partner_id IN NUMBER,p_isbn IN VARCHAR2,
p_book_num IN NUMBER) RETURN VARCHAR2 AS
out XMLType;
current_num NUMBER;
BEGIN
INSERT INTO book_sale VALUES(p_isbn,sysdate,p_partner_id,
p_book_num);
UPDATE book
SET instock_num=instock_num-p_book_num
WHERE isbn=p_isbn;
COMMIT;
RETURN 'OK';
EXCEPTION
WHEN OTHERS THEN
RETURN 'FAILED';
RAISE;
END checkout;
END booksale_pkg;
/
show errors;

In the query() function, DBMS_XMLGEN is used to generate the result in XML and return it as an XMLType. To get the XMLType, you need to use the DBMS_XMLGEN.GETXMLTYPE() function. In the checkout() procedure, the book table is updated by subtracting the requested number of books from the inventory and returning a string in VARCHAR2, which returns OK if enough books exist in inventory to fill the order, and otherwise returns FAILED.

Before you deploy the PL/SQL package to web services, you need to test it within the Oracle database, because debugging a web service is difficult. For example, the query to test Book Wholesale can be as follows:

LONG 10000
SET AUTOPRINT ON
VAR out CLOB
BEGIN
:out := booksale_pkg.query(1).getCLOBVal();
END;
/

The output is the listing of the books stored in the book table, including the actual price and the discount price for the Book Wholesale partner:

<ROWSET>
<ROW partner_id = "1">
<ISBN>0072225211</ISBN>
<AUTHOR>Kevin Loney, George Koch, Tusc</AUTHOR>
<TITLE>Oracle9i: The Complete Reference</TITLE>
<PRICE>74.99</PRICE>
<SALE_PRICE>44.994</SALE_PRICE>
</ROW>
<ROW partner_id = "1">
<ISBN>0072229527</ISBN>
<AUTHOR>Mark Scardina, Ben Chang, Jinyu Wang</AUTHOR>
<TITLE>Oracle Database 10g XML and SQL</TITLE>
<PRICE>49.99</PRICE>
<SALE_PRICE>29.994</SALE_PRICE>
</ROW>
</ROWSET>

A similar test applies for the checkout() function, which is provided along with the samples for this chapter.

So far, we have built the database schema for a simplified book management system. In the following section, we publish the PL/SQL package as a web service and show you how to call the web service from its clients.

Publishing the PL/SQL Web Service Using Oracle JDeveloper 10g

The bookstore has provided the PL/SQL functions to operate on the book table and track the transactions. In this section, we discuss how to publish PL/SQL functions as web services. There are different ways to provide web services, as we will discuss in depth later. However, to create a web service, you need provide at least the following:

A proxy program, residing within the server, that processes the incoming SOAP messages, calls the PL/SQL procedure, and then returns the result to the client via SOAP messages

A dedicated server running the proxy program to receive SOAP messages

A WSDL file that describes the web service, so that clients can easily develop client applications to call the service

If you build everything from scratch, a lot of work is required. To help you simplify the process, Oracle JDeveloper 10g provides a wizard that can automatically generate the proxy program, the WSDL file, and the stub program for the web service client, as well as the deployment profile, and the required features to debug them. All you need to do is provide the name of the PL/SQL package. Therefore, before we present the alternate approach that requires more work, we will discuss the procedures for creating PL/SQL web services via the wizard.

First, open the Oracle JDeveloper 10g project provided along with the book and set up a database connection, as discussed in Chapter 13. For example, create a database connection called demo, which connects to the database using the user for whom you just set up the book management schemas.

After the connection is set up, right-click the project name and select New. In the New Gallery dialog box, select Business Tier | Web Services | PL/SQL Web Service. This launches the Oracle JDeveloper 10g wizard that enables you to create PL/SQL web services through which you can access the PL/SQL procedures.

After you click Next on the Welcome screen, you should see the dialog box shown in Figure 26-1, which requires you to specify the database connection, the PL/SQL package, the name of the web service, and the name of the Java package that is used for the generated proxy program.

Figure 26-1: Step 1 of the Create PL/SQL Web Service wizard

After you select the database connection, select the BOOKSALE_PKG from the available PL/SQL packages in the drop-down list. In this step, you can specify any name for the web service and the Java package. In the example, BookSaleService is the name of the web service and demo is the name of the Java package of the proxy program. Click Next after you have completed the page.

Step 2 of the wizard requires you to select the PL/SQL procedures from the PL/SQL package to publish as part of the web service, as shown in Figure 26-2. In this example, both the checkout() and query() procedures are selected. After selecting the PL/SQL procedures, you can continue by clicking the Next button.

Figure 26-2: Select the PL/SQL procedures.

Before you complete the PL/SQL web service creation step, you are requested to specify the endpoint URL for the web service and its target namespace. The endpoint URL needs to reflect the J2EE server you want to deploy the web service to. For example, in the provided sample project, the J2EE application name is set to be Chapter26, and you will deploy the web service to the embedded Oracle J2EE Container (OC4J) of Oracle JDeveloper 10g, which runs on port 8988 by default. Therefore, the endpoint URL for BookSaleService is http://127.0.0.1:8988/Chapter26/ BookSaleService. You can specify any target namespace here. In this example, we use http://demo/BookSaleService.wsdl.

After you complete the wizard, a set of files are created, as shown in Figure 26-3.

Figure 26-3: Generated web service for the PL/SQL package

You now have all the prerequisites to build a web service:

The proxy program You can download and review the source code via the web service user interface, as discussed later in this section.

The server running the deployed proxy program This is the embedded OC4J in Oracle JDeveloper 10g. However, a deployment file, called WebServices.deploy, has been created to allow you to deploy the proxy program to other OC4J containers. In the example, this server is the OC4J web server.

The WSDL file This file summarizes the signatures of the methods exposed by the web service. To review this file, right-click the BookSaleService description file in the Applications Navigator and choose Go to WSDL.

You are ready to run the web service. Right-click the BookSaleService description file and select Run. In the message window of Oracle JDeveloper 10g, you will see the following messages:

04/02/10 11:52:26 Oracle Application Server Containers for J2EE 10g
(9.0.4.0.0) initialized
04/02/10 11:52:26 Web service BookSaleService has been started on
the embedded server
The application can be accessed at location:
http://169.254.74.222:8988/Chapter26/BookSaleService

Pay no attention to the IP address that is displayed, because it is the IP address automatically detected by Oracle JDeveloper 10g when you run the samples. The IP address will be different when you run it. Now you can launch your browser and enter the URL, http://127.0.0.1:8988/Chapter26/BookSaleService, to access your up-and-running web service, as shown in Figure 26-4.

Figure 26-4: Published web service user interface

Through this web interface, you can download the Java package and source code for the proxy program, test each operation, and review the WSDL file.

Calling the Web Service from Clients

Because web services are open and standards-based, they have little restriction on the clients that can invoke them. As long as the client follows the standards and sends the SOAP message in the correct format, the web service will respond whether it is written in Java, JavaScript, ASP, JSP, or Perl. However, in Oracle JDeveloper 10g, you can easily generate Java client programs that call web services. You just need to right-click the BookSaleService description file and choose Generate Web Service Stub. You can also choose Generate Simple Java Client to get a simplified version of the client program.

Figure 26-5 shows the dialog box in which you set up the wizard to generate the client stub program by right-clicking the BookSaleService and choosing Generate Web Service Stub.

Figure 26-5: Step 1 of the Generate Web Service Stub/Skeleton wizard

As shown in Figure 26-5, from a given WSDL, you can generate not only client-side stubs but also a server-side skeleton. However, in this example, we need only a simple client stub. After you choose the options shown in Figure 26-5 and click Next, you then need to choose the necessary functions for the client and the package name for the Java package that will be generated. In the example, a BookSaleServiceStub.java file will be created in the oracle.xml .sample.ws package.

After the wizard generates the code, you need to update the code so that it can be used within your application.

In this example, you first need to modify the generated code so that it can properly handle the XMLType, which will be returned by the book query service (all the updated content is bold):

import oracle.xdb.XMLType;
public XMLType query(BigDecimal pPartnerId) throws Exception
{
XMLType returnVal = null;
URL endpointURL = new URL(_endpoint);
Call call = new Call();
call.setSOAPTransport(m_httpConnection);
call.setTargetObjectURI("BookSaleService");
call.setMethodName("query");
call.setEncodingStyleURI(Constants.NS_URI_SOAP_ENC);
Vector params = new Vector();
params.addElement(new Parameter("pPartnerId",
java.math.BigDecimal.class, pPartnerId, null));
call.setParams(params);
call.setSOAPMappingRegistry(m_smr);
Response response = call.invoke(endpointURL, ");
if (!response.generatedFault()) {
Parameter result = response.getReturnValue();
returnVal = (XMLType)result.getValue();
} else {
Fault fault = response.getFault();
throw new SOAPException(fault.getFaultCode(),
fault.getFaultString());
}
return returnVal;
}

This proper datatype casting allows better use of XMLType functionality within Oracle XML DB.

Then, for the main() function, you need to add your code to specify how you call the web service. In the example, we simply dump the result to the screen:

public static void main(String[] args)
{
try {
BookSaleServiceStub stub = new BookSaleServiceStub();
// Add your own code here.
System.out.println(
stub.checkout(new BigDecimal(1),"0072225211",new BigDecimal(10)));
System.out.println(stub.query(new BigDecimal(1)).getStringVal());
}catch(Exception ex){
ex.printStackTrace();
}
}

When calling the stub.checkout(), you need to provide the partner_id, ISBN, and number of the book to be checked out. However, the call to stub.query() needs some explanation. The stub.query() function returns the XMLType that contains the book list in XML for the specified partner. Because the returned content is in the XMLType, which provides the XMLType.getStringVal() procedure, the call finally returns the content in String so that it can be printed.

Debugging the Web Service

If you follow the instructions, everything should work fine when you generate and call the example web service. However, in the real world, something usually happens that requires you to debug the problem. A useful debug technique is to peek at the SOAP messages that are actually sent and received. In Oracle JDeveloper 10g, you can do this by using TCP Packet Monitor (select View | TCP Packet Monitor). After you open TCP Package Monitor, click the green Play button to start the monitor. Each SOAP message is then recorded in the History window, and the Data window shows both the incoming and outgoing SOAP message content.

For the checkout service call, the outgoing SOAP message is shown as follows:

<?xml version = '1.0' encoding = 'UTF-8'?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns1:checkout xmlns:ns1="BookSaleService"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<pPartnerId xsi:type="xsd:decimal">1</pPartnerId>
      <pIsbn xsi:type="xsd:string">0072225211</pIsbn>
      <pBookNum xsi:type="xsd:decimal">10</pBookNum>
</ns1:checkout>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

It returns a SOAP message as follows:

<?xml version = '1.0' encoding = 'UTF-8'?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns1:checkoutResponse xmlns:ns1="BookSaleService"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<return xsi:type="xsd:string">OK</return>
</ns1:checkoutResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

The SOAP messages contain a lot of information, including namespaces. However, you do not have to create SOAP messages yourself. The SOAP messages can be simply generated using the supplied tools in Oracle JDeveloper 10g.

So far, the web service communication protocol and the interoperation between the bookstore owner and its partners have been set up. From the example, you can see that publishing a PL/SQL web service for Oracle Database 10g can be a very simple process.