Oracle9i Case Studies - XML Applications
Release 1 (9.0.1)
Part Number A88895-01
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to next page

8
Online B2B XML Application: Step by Step

This chapter contains the following topics:

Introduction to the Online B2B XML Application

This chapter describes all the steps and scripts you need to build an online B2B XML application.

A modified version of this application is available on Oracle Technology Network (OTN) site: http://otn.oracle.com/tech/xml, under "WebStore B2B Demo". This modifed version adds support for multiple Suppliers and Retailers, and can be expanded for a larger B2B online data exchange than is described in this chaper. You can also download the scripts from this OTN site.

Requirements for Running the Online B2B XML Application

The following lists requirements to build and run the online B2B XML application:

Building the Online B2B XML Application: Overview

This XML application and demo illustrate a content management and B2B Messaging implementation. The main transactions in this application are as follows:

Problem

Retailers (R) need to automate the ordering of goods from several suppliers (Supplier (S)) and be able to place the order view the order status from any device.

Solution

This solution implements the following:

Tasks Identified

The main tasks are shown in Figure 8-2.

  1. The Retailer enters an order from their Browser, Personal Digital Assistant (PDA), or cell phone.

  2. When the Retailer validates his order, the order is transformed into XML using the XSQL Servlet.

  3. The Retailer application sends the XML order to the AQ Broker.

  4. AQ messaging is used to send the XML order data. The retailer views their order status as "Pending". AQ Broker reformats the XML order into a format understood by the Supplier.

  5. The Supplier application inserts the order into the Supplier database.

  6. The Supplier application parses the order and sends an alert to the Supplier that an order has been received and is waiting processing.

  7. Once the Supplier hits "Shipped" on his screen, AQ messaging is used to return the XML order status data to the AQ Broker. The AQ Broker transforms the returned XML Order status into a format recognized by the Retailer.

  8. The Supplier receives the reformatted XML order status message. The Retailer application updates the Retailer database with the new order status. The Retailer views the order status, which is now "Shipped".

The detailed tasks involved, screens viewed, and scripts used, are described in "Running the B2B XML Application: Detailed Procedure". and illustrated in Figure 8-2, "Online B2B XML Application: Main Components"

XML and Oracle Components Used

Tools Used

JDeveloper

Note:

No pre-authored (static or composed) XML documents are used in this B2B XML application. All XML documents in this application are dynamically generated (decomposed) from the database. 

Why Transform Data to XML?

Retailers and Suppliers use many different formats.

Because Retailers use different order form formats, the Retailer's order data is transformed into XML so that any Supplier can recognize and process their orders.

Suppliers use different formats for their order status and acknowledgement data. This data is converted to XML so that any Retailer can recognize the order status and acknowledgement.

Note:

This solution uses a finite set of two predetermined customer order document formats. 

Figure 8-1 illustrates the overall flow of the Retailer-Supplier transaction. The Retailer enters the order.

Why Use Advanced Queueing (AQ)?

Using AQ in this application has the following advantages:

Figure 8-1 Why Transform Data to XML?: Retailer's Order Data Can be recognized by Any Supplier - Supplier's Order Status and Acknowledgement Can be Recognized by any Retailer

Text description of adxml031.gif follows
Text description of the illustration adxml031.gif

Online B2B XML Application: Main Components

Figure 8-2 shows the main components used in this online B2B XML application. The Retailer orders good from a Supplier and receives a confirmation from the Supplier that the goods have been shipped. The detailed transaction diagram of the process is illustrated in Figure 8-5.

Figure 8-2 Online B2B XML Application: Main Components


Text description of adxml069.gif follows
Text description of the illustration adxml069.gif

Overview of Tasks to Run the Online B2B XML Application

The schemas used in the B2B XML application you are about to build, are illustrated in Figure 8-3.

To run the B2B XML application carry out the following tasks as described:

The details for running the B2B XML application including what you will see on your browser, are provided in "Running the B2B XML Application: Detailed Procedure" . You will also see typical screenshots of what the Retailer and Supplier see.

Figure 8-3 B2B XML Retailer (Customers) and Supplier Schema


Text description of adxml060.gif follows
Text description of the illustration adxml060.gif

Figure 8-4 B2B XML AQ Broker Schema: Stylesheets


Text description of adxml061.gif follows
Text description of the illustration adxml061.gif

Task 1. Set Up Your Environment to Run the Online B2B XML Application

  1. Start your Apache or other Web Server.

  2. Start your Browser, such as IE5

  3. Log on

  4. To set up all the schemas you will need to run the B2B XML application, follow these steps:

    Create the Retailer and Supplier schemas. See "Online B2B XML Application: Main Components"

    • Connect to the database however you like.

    • Run buildAll.sql. The script will ask you for your system password to create the requested users.

  5. Create the AQ Schema

    • On a convenient machine, run the SQL script, mkAQUser.sql.

    • Connected as aqMessBrok/aqMessBrok, run the script, mkQ.sql

  6. Create the XSL Tables

    • Still connected, run the script, mkSSTables.sql

    • Run setup.sql to install the XSL Stylesheets in the database.

    • Test it by running the GUIStylesheet java class, after changing the connections as described in the next step.

  7. Modify the connections

    • Modify the JDBC Connection parameters in the following files:

      • AppCste.java

      • retail.bat

      • supplier.bat

      • PlaceOrder.xsql

    • Finally, modify XSQLConfig.xml to create a connection named retail on retailer/retailer.

    • Recompile all the files before going on.

  8. Before running the B2B XML application, run the script named reset.sql to reset the AQ environment.

  9. Modify and run the three bat files for the Broker, Suppler, and Retailer

    • Modify the .bat files: There are three mains used and these are launched from the following .bat files:

      • Broker.bat for the message broker

      • Supplier.bat for the supplier

      • Retail.bat for the retailer

      First modify the .bat files for your environment as follows:

      • verbose: If set to y or true, gives a lot of detail about the received messages.

      • step: If set to y or true, asks the user to hit return after each processing step. If step has a numeric value, it'll be considered, in milliseconds, as the time to wait between each step before going on

      Retail.bat and Supplier.bat also accept a -dbURL parameter, describing the URL used to get you connected to the database in question. The default URL is : jdbc:oracle:thin:@localhost:1521:ORCL.

Task 2. Run the B2B Application

  1. Run broker.bat, supplier.bat, and retailer.bat

  2. Check the StyleSheet utility by running GUIStylesheet.class

These stylesheets are used by the Broker to process the documents it receives.

Details for running the B2B XML application including what you will see on your browser, are provided in "Running the B2B XML Application: Detailed Procedure".

Task 3. End the B2B Application Session

  1. To finish the B2B XML application

    Run the Java class, stopAllQueues, or the script named stopQ.bat

  2. Stop Apache or your Web Server.

Online B2B XML Application: Setting Up the Database Schema

The following schema scripts are provided here in the order in which they should be executed:

SQL Code Calling Sequence

The following list provides the SQL example code calling sequence. The .sql extension for each file has been omitted. The notation "<---" implies "calls", for example, BuildAllsql <----- BuildSchema implies that BuildAllsql calls BuildSchema.

Create and Build the Retailer and Supplier Schemas

These schema scripts set up the Retailer and Supplier environment, users, tablespaces, quota, and so on. They also create and then populate the schemas.

SQL Example 1: Set up the Retailer and Supplier Environment -- BuildAll.sql

BuildAll.sql sets up the environment for the Retailer and Supplier schema. It calls BuildSchema.sql which creates the Retailer and Supplier schemas and then populates them with data. 

--
-- buildall.sql builds all the schemas
-- 
accept sysPswd prompt 'Enter the system password                                       
> ' hide
accept cStr    prompt 'Enter the connect string if any, including ''@'' sign (ie 
@atp-1) > '
connect system/&sysPswd&cStr
drop user retailer cascade
/
drop user supplier cascade
/
col tablespace_name head "Available Tablespaces"
select tablespace_name from dba_tablespaces
/
prompt
accept userTbsp prompt 'What is the DEFAULT Tablespace name ?   > '
accept tempTbsp prompt 'What is the TEMPORARY Tablespace name ? > '
prompt

create user retailer identified by retailer
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, create any directory to retailer
/
create user supplier identified by supplier
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, create any directory to supplier
/
prompt Now populating Supplier, hit [Return]
pause
connect supplier/supplier&cStr
@buildSchema
prompt Now populating Retailer, hit [Return]
pause
connect retailer/retailer&cStr
@buildSchema
prompt done !

SQL Example 2: Create and Populate the Retailer-Supplier Schema -- BuildSchema.sql

BuildSchema.sql is called from BuildAll.sql. It creates, populates, and builds the Retailer and Supplier schema.

This script creates and populates the following five tables:

See Figure 8-3 for an illustration of this schema. 

--
-- buildSchema.sql drops then creates all the tables for the B2B XML Application
--
drop trigger line_item_insert_trigger;
drop table line_item;
drop table ord;
drop table customer;
drop table inventory_item;
drop table supplier;
drop sequence ord_seq;
drop sequence customer_seq;
drop sequence line_item_seq;
drop sequence supplier_seq;
drop sequence inventory_item_seq;

prompt
prompt Creating sequences...
prompt
prompt
prompt Creating sequence ORD_SEQ
create sequence ord_seq start with 101;

prompt Creating sequence CUSTOMER_SEQ
create sequence customer_seq start with 201;

prompt Creating sequence LINE_ITEM_SEQ
create sequence line_item_seq start with 1001;

prompt Creating sequence SUPPLIER_SEQ
create sequence supplier_seq start with 301;

prompt Creating sequence INVENTORY_ITEM_SEQ
create sequence inventory_item_seq start with 401;

prompt
prompt
prompt Creating tables...
prompt
prompt
--
-- ***** Create table CUSTOMERS ******
--
prompt Creating table CUSTOMER
create table customer(
  id         number,
  name       varchar2(30),
  status     varchar2(8),
  web_site   varchar2(40),
  constraint 
     customer_pk 
     primary key (id)
);
--
-- ***** Create table SUPPLIERS ******
--
prompt Creating table SUPPLIER
create table supplier(
  id         number,
  name       varchar2(30),
  web_site   varchar2(40),
  constraint 
     supplier_pk 
     primary key (id)
);
--
-- ***** Create table INVENTORY_ITEM ******
--
prompt Creating table INVENTORY_ITEM
create table inventory_item(
  id             number,
  description    varchar2(30),
  price          number(8,2),
  onhand         number,
  supplier_id    number,
  constraint
     inventory_item_pk 
     primary key (id),
  constraint 
     supplied_by
     foreign key (supplier_id) references supplier
);
--
-- ***** Create table ORD ******
--
prompt Creating table ORD
create table ord (
  id             number,
  orderDate      date,
  contactName    varchar2(30),
  trackingNo     varchar2(20),
  status         varchar2(10),
  customer_id    number,
  constraint 
     ord_pk 
     primary key (id),
  constraint
     order_placed_by 
     foreign key (customer_id) references customer
);

prompt Creating table LINE_ITEM
create table line_item(
  id             number,
  quantity       number,
  item_id        number,
  ord_id         number,
  discount       number,
  constraint
     line_item_pk 
     primary key (id),
  constraint 
     item_ordered_on 
     foreign key (ord_id) references ord,
  constraint 
     order_for_item
     foreign key (item_id) references inventory_item
);

prompt
prompt
prompt Inserting data...
prompt
prompt
prompt Inserting values into SUPPLIER and INVENTORY_ITEM
prompt

insert into supplier values( supplier_seq.nextval,'DELL','http://dell.com');
insert into inventory_item values( inventory_item_seq.nextval,'Optiplex GXPro', 
1500, 27, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 7000', 
2500, 49, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'PowerEdge 6300', 
7500, 16, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 3000', 
2500, 0, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 2000', 
2500, 0, supplier_seq.currval );

insert into supplier values( supplier_seq.nextval, 'HP', 'http://hp.com');
insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 6MP', 
899, 123, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'Jornada 2000', 
450, 1198, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'HP 12C', 69, 
801, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 2', 69, 
3, supplier_seq.currval );


insert into inventory_item values( inventory_item_seq.nextval,'Jaz PCMCIA 
adapter', 125, 54, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'8860 Digital 
phone', 499, 12, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Jaz carrying 
bag', 20, 66, supplier_seq.currval );

insert into supplier values(supplier_seq.nextval,'Intel', 
'http://www.intel.com');
prompt Inserting values into CUSTOMER
prompt

insert into ord values(ord_seq.nextval,sysdate,'George','AX'||ord_seq.currval, 
'Pending', 201);
insert into line_item values (line_item_seq.nextval, 2, 410,ord_seq.currval, 0);
insert into line_item values (line_item_seq.nextval, 1, 402,ord_seq.currval, 0);
insert into line_item values (line_item_seq.nextval, 1, 406,ord_seq.currval, 0);

insert into ord values(ord_seq.nextval,sysdate,'Elaine','AX'||ord_seq.currval,
 'BackOrdered', 0);
create trigger line_item_insert_trigger
  before insert on line_item for each row
  begin
     select line_item_seq.nextval into :new.id from dual ;
  end;
/

commit;

Create the AQ Environment and Queue Tables

Run the AQ schema scripts as follows:

SQL Example 3: Set Up the Environment for AQ -- mkAQUser.sql

The following SQL script sets up the environment for using AQ, creates user aqMessBrok, creates default and temporary tablespace, grants execute privileges on the AQ PL/SQL packages dbms_aqadm and dbms_aq to aqMessBrok. 

set ver off
set scan on
prompt Creating environment for Advanced Queuing
accept mgrPsw prompt 'Please enter the SYSTEM password                                 
> ' hide
accept cStr   prompt 'Please enter the the DB Alias if any, WITH the @ sign (ie 
@Ora8i)> '
connect system/&mgrPsw&cStr

col tablespace_name head "Available Tablespaces"
select tablespace_name from dba_tablespaces
/
Prompt
accept userTbsp prompt 'What is the DEFAULT Tablespace name ?   > '
accept tempTbsp prompt 'What is the TEMPORARY Tablespace name ? > '
prompt

prompt Creating aqMessBrok
create user aqMessBrok identified by aqMessBrok
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, aq_administrator_role, create any directory to 
aqMessBrok
/
grant execute on dbms_aqadm to aqMessBrok
/
grant execute on dbms_aq to aqMessBrok
/

SQL Example 4: Call the AQ Queue Creation Scripts -- mkQ.sql

This script calls four scripts to create the AQ queue tables. 

@mkQueueTableApp1
@mkQueueTableApp2
@mkQueueTableApp3
@mkQueueTableApp4

SQL (PL/SQL) Example 5: Create Table, AppOne_QTab -- mkQueueTableApp1.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 1, AppOne_QTab. 

execute dbms_aqadm.create_queue_table (queue_table => 'AppOne_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 6: Create Table, AppTwo_QTab -- mkQueueTableApp2.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 2, AppTwo_QTab. 

execute dbms_aqadm.create_queue_table (queue_table => 'AppTwo_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 7: Create Table, AppThree_QTab -- mkQueueTableApp3.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 3, AppThree_QTab. 

execute dbms_aqadm.create_queue_table (queue_table => 'AppThree_QTab', queue_
payload_type => 'RAW');

SQL (PL/SQL) Example 8: Create Table, AppFour_QTab -- mkQueueTableApp4.sql

This script is called from mkQ.sql. It calls the dbms_aqadm.create_queue_table procedure to create queue table 4, AppFour_QTab. 

execute dbms_aqadm.create_queue_table (queue_table => 'AppFour_QTab', queue_
payload_type => 'RAW');

Create the Broker Schema Including XSL Stylesheet Table

Run the following scripts to create and populate the stylesheets, tasks, and applications tables:

SQL Example 9: Create Broker Schema -- mkSSTables.sql

Run mkSSTables.sql to create the Broker schema. It creates and populates the following three tables:

This schema is illustrated in Figure 8-4. This script then calls setup.sql. 

prompt Building Stylesheets management tables.
prompt Must be connected as aqMessBrok (like the borker)
accept cStr prompt 'ConnectString (WITH @ sign, like @Ora8i) > '
connect aqMessBrok/aqMessBrok&cStr

drop table styleSheets
/
drop table tasks

/ 

drop table applications
/
create table applications
(
  code  varchar2(16) not null,
  descr varchar2(256)
)
/
alter table applications
  add constraint PK_APP
  primary key (code)
/
create table tasks
(
  code_app varchar2(16) not null,
  code     varchar2(16) not null,
  descr    varchar2(256)
)
/
alter table tasks
  add constraint PK_TASKS
  primary key (code_app,code)
/
alter table tasks
  add constraint TASK_FK_APP
  foreign key (code_app)
  references applications(code) on delete cascade
/
create table styleSheets
(
  appFrom varchar2(16) not null,
  appTo   varchar2(16) not null,
  op      varchar2(16) not null,
  xsl     clob
)
/
alter table styleSheets
  add constraint PK_SS
  primary key (appFrom,appTo,op)
/
alter table styleSheets
  add constraint SS_FK_FROM
  foreign key (appFrom)
  references applications(code)
/
alter table styleSheets
  add constraints SS_FK_TASK
  foreign key (appTo,op)
  references tasks(code_app,code)
/
@setup

SQL (PL/SQL) Example 10: Input XSL data into CLOB. Populate the Broker Schema -- setup.sql

setup.sql installs stylesheet data into the XSL column (CLOB) of the stylesheets table. This script creates a procedure, loadlob. The script also uses PL/SQL packages dbms_lob and dbms_output. 

prompt Installing the stylesheets
-- accept cStr prompt 'ConnectString (WITH @ sign, like @Ora8i) > '
-- connect aqMessBrok/aqMessBrok&cStr
prompt Creating LoadLob procedure
create or replace procedure loadLob (imgDir in varchar2, 
                                     fname in varchar2,
                                     app_From in varchar2,
                                     app_To in varchar2,
                                     oper in varchar2) as
  tempClob  CLOB;
  fileOnOS  BFILE := bfilename(imgDir, fname);
  ignore    INTEGER;
begin
  dbms_lob.fileopen(fileOnOS, dbms_lob.file_readonly);
  select xsl
  into tempClob
  from StyleSheets S
  where s.APPFROM = app_From and
        s.APPTO = app_To and
        s.OP = oper
  for UPDATE;
dbms_output.put_line('External file size is: ' || dbms_lob.getlength(fileOnOS));
dbms_lob.loadfromfile(tempClob, fileOnOS, dbms_lob.getlength(fileOnOS));
dbms_lob.fileclose(fileOnOS);
dbms_output.put_line('Internal CLOB size is: '|| dbms_lob.getlength(tempClob));
exception
  When Others then
    dbms_output.put_line('Oooops : ' || SQLERRM);  
end LoadLob;
/
show errors
set scan off

create or replace directory "LOB_DIR" as 'D:\xml817\references\olivier_new'
/
insert into applications values ('RETAIL', 'Origin')
/
insert into applications values ('SUPPLY', 'Destination')
/
insert into tasks values ('SUPPLY', 'NEW ORDER', 'Insert a new Order')
/
insert into tasks values ('RETAIL', 'UPDATE ORDER', 'Update an Order Status')
/

set serveroutput on
begin
 insert into StyleSheets values ('RETAIL','SUPPLY','NEW ORDER',EMPTY_CLOB());
 loadLob('LOB_DIR', 'one.xsl', 'RETAIL','SUPPLY','NEW ORDER');
 insert into StyleSheets values ('SUPPLY','RETAIL','UPDATE ORDER',EMPTY_CLOB());
 loadLob('LOB_DIR', 'two.xsl', 'SUPPLY','RETAIL','UPDATE ORDER');
exception
  when others then
  dbms_output.put_line('Error Occurred : ' || chr(10) || SQLERRM);
end;
/
commit
/

Cleaning Up Your Environment and Preparing to Rerun Application

Run reset.sql to clean up your environment and rerun this application.

SQL Example 11: Stops and Drops Queue Applications. Starts Queue Applications -- reset.sql

reset.sql script first stops all four queue applications by calling the stopQueueApp1 through 4, then drops them by calling dropQueueApp1 through 4, and restarts them by calling startQueueApp1 through 4.

The script also prompts you to Hit Return to Exit. 

connect aqMessBrok/aqMessBrok
start stopQueueApp1
start stopQueueApp2
start stopQueueApp3
start stopQueueApp4
start dropQueueApp1
start dropQueueApp2
start dropQueueApp3
start dropQueueApp4
start createQueueApp1
start createQueueApp2
start createQueueApp3
start createQueueApp4
start startQueueApp1
start startQueueApp2
start startQueueApp3
start startQueueApp4
prompt Press [Return] to exit !
pause
exit

Stop Queue SQL Scripts

These four scripts are called from reset.sql.They use PL/SQL procedure dbms_aqadm.stop_queue to stop the queues.

stopQueueApp1.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppOneMsgQueue');

stopQueueApp2.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppTwoMsgQueue');

stopQueueApp3.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppThreeMsgQueue');

stopQueueApp4.sql

execute dbms_aqadm.stop_queue(queue_name=>'AppFourMsgQueue');

Drop Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure dbms_aqadm.drop_queue to drop the queues.

dropQueueApp1.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppOneMsgQueue');

dropQueueApp2.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppTwoMsgQueue');

dropQueueApp3.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppThreeMsgQueue');

dropQueueApp4.sql

execute dbms_aqadm.drop_queue (queue_name=>'AppFourMsgQueue');

Create Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure, dbms_aqadm.create_queue to create the queues.

createQueueApp1.sql

execute dbms_aqadm.create_queue (queue_name=>'AppOneMsgQueue', queue_
table=>'AppOne_QTab');

createQueueApp2.sql

execute dbms_aqadm.create_queue (queue_name=>'AppTwoMsgQueue', queue_
table=>'AppTwo_QTab');

createQueueApp3.sql

execute dbms_aqadm.create_queue (queue_name=>'AppThreeMsgQueue', queue_
table=>'AppThree_QTab');

createQueueApp4.sql

execute dbms_aqadm.create_queue (queue_name=>'AppFourMsgQueue', queue_
table=>'AppFour_QTab');

Start Queue SQL Scripts

These four scripts are called from reset.sql. They use PL/SQL procedure, dbms_aqadm.start_queue to start the queues.

startQueueApp1.sql

execute dbms_aqadm.start_queue(queue_name=>'AppOneMsgQueue');

startQueueApp2.sql

execute dbms_aqadm.start_queue (queue_name=>'AppTwoMsgQueue');

startQueueApp3.sql

execute dbms_aqadm.start_queue (queue_name=>'AppThreeMsgQueue');

startQueueApp4.sql

execute dbms_aqadm.start_queue (queue_name=>'AppFourMsgQueue');

dropOrder.sql

This SQL script deletes orders from the Retailer-Supplier database Customers table according to the customer's ID. 

set ver off
accept CustName prompt 'Drop all for customer named > ' 

Delete LINE_ITEM I
Where I.ORD_ID in
(Select O.ID
 From ORD O
 Where O.CUSTOMER_ID in
 (Select C.ID
  From CUSTOMER C
  Where Upper(C.NAME) = Upper('&CustName')))
/
Delete ORD O
Where O.CUSTOMER_ID in
(Select C.ID
 From CUSTOMER C
 Where Upper(C.NAME) = Upper('&CustName'))
/

Online B2B XML Application: Data Exchange Flow

Figure 8-5 shows the detailed transaction diagram of the process when the Retailer orders good from a Supplier and receives a confirmation from the Supplier that the goods have been shipped.

Figure 8-5 Inter-Business Data Exchange: Using XML and AQ to send Retailer's Order to a Supplier and Receive Order Status and Acknowledgement from the Supplier


Text description of adxml063.gif follows
Text description of the illustration adxml063.gif

Retailer-Supplier Transactions

Figure 8-5 shows the business flow of the Retailer - Supplier transactions. These transactions are summarized here.

The detailed transactions and how to run the B2B XML application is provided in "Running the B2B XML Application: Detailed Procedure" .

Step 1. Retailer Browses the Supplier's OnLine "Hi-Tech Mall" Catalog

The following Retailer transactions occur:

  1. The Retailer logs in from their web site using XSQL.

  2. Retailer browses the Supplier's on-line catalog. Retailer selects a product and quantity.

Step 2. Retailer Places Order

When the Retailer places the order, the Retailer then needs to either confirm the order and cost, by clicking on "Place Order", or cancel "Give Up" the order.

Step 3. Retailer Confirms and Commits to Sending the Order

If Retailer confirms the order by clicking on, "Place Order", this triggers the generation of an XML document containing the order data. The Retailer application sends this XML order document to the Supplier by way of the AQ Broker-Transformer application.

The Action Handler "XSQL Script Example 5: Starts B2B Process -- placeorder.xsql" of the XSQL Servlet is the key component in the whole process. It ensure that this transaction is inserted into the retailer database table, Ord.

The Action Handler also sends the XML order on to the AQ Broker-Transformer.

Step 4. AQ Broker-Transformer Transforms the XML Document According to the Supplier's Format

When the AQ Broker-Transformer receives the XML document the following actions transpire:

  1. The AQ Broker-Transfomer waits for the queue [READS] from the Retailer that they have sent an order. See Figure 8-6.

    Figure 8-6 Online B2B XML Application: AQ Messaging Flow


    Text description of adxml054.gif follows
    Text description of the illustration adxml054.gif
  2. The AQ Broker receives the XML document order message, and determines the following information from the message:

    • FROM: From where the message is coming (from which Retailer)

    • TO: To where the message is going (to which Supplier)

    • OPERATION or TASK: What operation is needed to process this message

  3. The AQ Broker-Transformer refers to the Stylesheets table and according to the From, To, and Task criteria, selects the appropriate XSL stylesheet. The stylesheets are stored in CLOBs in the Stylesheets table in the XSL column. AQ Broker-Transformer accesses the database and stylesheets by means of JDBC.

  4. XSLT Processor is informed by AQ Broker-Transformer application to apply the selected and retrieved XSL stylesheet to the XML document containing the order data. The XSLT Processor outputs the reformatted XML order.

  5. AQ Broker-Transformer uses AQ to send [WRITE] the transformed XML document to the "TO" Supplier destination.

    Note:

    If a DTD (XML Schema) is used, it would be applied before processing in the AQ Broker phase. In this example, for simplicity, we assume that the document is always sent in the same format. 

The schema used by the AQ Broker-Transformer is shown inFigure 8-4.

Step 5. Supplier Application Parses Incoming Reformatted XML Order Document. Inserts Order into the Supplier Database

When the Supplier receives the reformatted XML order document from the AQ Broker-Transformer, the following protocols transpire:

  1. The Supplier waits for the queue from the AQ Broker-Transformer that a order is pending from a Retailer. The Supplier dequeues the AQ message.

  2. The Supplier parses the XML document and INSERTs the order into the Supplier database by means of JDBC.

Step 6. Supplier Application Alerts Supplier of Pending Order

When the Supplier application has inserted the XML document into the Supplier database the following actions transpire:

  1. Supplier Application Alerts the Supplier of the Order. The order status is kept at "pending".

  2. The Supplier, after checking if the product(s) ordered are available, and the Retailer's credit, decides to ship the product(s). Supplier clicks on "Ship".

  3. The Supplier application updates the Supplier database Ord table's status column to "shipped".

Step 7. AQ Broker-Transformer Transforms the XML Order According to Retailer's Format

  1. AQ Broker-Transformer waits [READS] for a queue from the Supplier.

  2. When the XML Order Shipped document is received, the AQ Broker-Transformer refers to the Stylesheets table in the Transformer database, and according to the From, To, and Task criteria, selects the appropriate XSL stylesheet. The stylesheets are stored in CLOBs in the Stylesheets table in the XSL column. AQ Broker-Transformer accesses the database and stylesheets by means of JDBC.

  3. AQ Broker-Transformer uses AQ to send [WRITE] the reformatted XML order update document to the "TO" Retailer destination.

Step 8. Retailer Application Updates the Ord and Line_Item Tables

  1. Retailer application updates the Retailer database with new "shipped" order status information. The Ord table is updated.

  2. This information is viewed by the Retailer from any device. The status is seen as "Shipped".

Running the B2B XML Application: Detailed Procedure

Figure 8-5 shows the detailed transaction and flow of the B2B XML application. The XML order document is sent from the Retailer through the AQ Broker-Transformer, to the Supplier and back to the Retailer.

Before running the B2B XML application, ensure that you have run the schema creation scripts described in "Overview of Tasks to Run the Online B2B XML Application".

The following steps explain the process and how to run this application.

Step 1. Retailer Browses the Supplier's OnLine "Hi-Tech Mall" Catalog

See Figure 8-5 for the detailed procedural flow of the B2B XML application.

Note:

We assume here that a copy of the Supplier's catalog is in the Retailer's database. 

  1. Check the StyleSheet utility to ensure it works by invoking SS.bat.

    Stylesheet Batch File: SS.bat 

    @echo off
@echo Stylesheet Util
D:\jdev31\java\bin\java -mx50m -classpath "D:\xml817\references\olivier_new;
D:\jdev31\lib\jdev-rt.zip;
D:\jdev31\jdbc\lib\oracle8.1.6\classes111.zip;
D:\jdev31\lib\connectionmanager.zip;
D:\jdev31\lib;
D:\jdev31\lib\oraclexsql.jar;
D:\jdev31\lib\oraclexmlsql.jar;
D:\jdev31\lib\xmlparserv2_2027.jar;
D:\jdev31\jfc\lib\swingall.jar;
D:\jdev31\jswdk-1.0.1\lib\servlet.jar;
D:\Ora8i\rdbms\jlib\aqapi11.jar;
D:\Ora8i\rdbms\jlib\aqapi.jar;
D:\XMLWorkshop\xmlcomp.jar;
D:\jdev31\java\lib\classes.zip"  B2BDemo.StyleSheetUtil.GUIStylesheet

    Using this utility you can browse the actual table, Stylesheets, in which the stylesheets are stored. These stylesheets are used by the AQ Broker-Transformer to process the documents it received. See Figure 8-7.

    Figure 8-7 Checking the StyleSheet Utility


    Text description of ss.jpg follows.
    Text description of the illustration ss.jpg

  2. Start the Retailer application by running retailer.bat. See Figure 8-8.

    Figure 8-8 Starting the Retailer Application


    Text description of retail.jpg follows.
    Text description of the illustration retail.jpg
  3. Start the AQ Broker-Transformer application by running broker.bat. See Figure 8-9.

    Figure 8-9 Starting the AQ Broker-Transformer Application


    Text description of broker.jpg follows.
    Text description of the illustration broker.jpg
  4. Start the Supplier application by running supplier.bat. See Figure 8-10.

    Figure 8-10 Starting the Supplier Application: "Supplier Watcher"


    Text description of supplier.jpg follows.
    Text description of the illustration supplier.jpg

    The three batch files for the Retailer, AQ Broker-Transformer (Broker), and Supplier applications are listed here:

    retailer.bat 

    @echo off
@echo Retail Side
D:\jdev31\java\bin\java -mx50m -classpath 
"D:\xml817\references\Ora817DevGuide;
D:\jdev31\lib\jdev-rt.zip;
D:\jdev31\jdbc\lib\oracle8.1.6\classes111.zip;
D:\jdev31\lib\connectionmanager.zip;
D:\jdev31\lib;
D:\jdev31\lib\oraclexsql.jar;
D:\jdev31\lib\oraclexmlsql.jar;
D:\jdev31\lib\xmlparserv2_2027.jar;
D:\jdev31\jfc\lib\swingall.jar;
D:\jdev31\jswdk-1.0.1\lib\servlet.jar;
D:\Ora8i\rdbms\jlib\aqapi11.jar;
D:\Ora8i\rdbms\jlib\aqapi.jar;
D:\XMLWorkshop\xmlcomp.jar;
D:\jdev31\java\lib\classes.zip"  B2BDemo.Retailer.UpdateMaster -step=1000 
-verbose=y -dbURL=jdbc:oracle:thin:@atp-1.us.oracle.com:1521:ORCL

    broker.bat 

    @echo off
@echo Broker
D:\jdev31\java\bin\java -mx50m -classpath 
"D:\xml817\references\Ora817DevGuide;
D:\jdev31\lib\jdev-rt.zip;
D:\jdev31\jdbc\lib\oracle8.1.6\classes111.zip;
D:\jdev31\lib\connectionmanager.zip;
D:\jdev31\lib;D:\jdev31\lib\oraclexsql.jar;
D:\jdev31\lib\oraclexmlsql.jar;
D:\jdev31\lib\xmlparserv2_2027.jar;
D:\jdev31\jfc\lib\swingall.jar;
D:\jdev31\jswdk-1.0.1\lib\servlet.jar;
D:\Ora8i\rdbms\jlib\aqapi11.jar;
D:\Ora8i\rdbms\jlib\aqapi.jar;
D:\XMLWorkshop\xmlcomp.jar;
D:\jdev31\java\lib\classes.zip"  B2BDemo.Broker.MessageBroker -step=1000 
-verbose=y

    supplier.bat 

    @echo off
@echo Supplier
D:\jdev31\java\bin\java -mx50m -classpath 
"D:\xml817\references\Ora817DevGuide;
D:\jdev31\lib\jdev-rt.zip;
D:\jdev31\jdbc\lib\oracle8.1.6\classes111.zip;
D:\jdev31\lib\connectionmanager.zip;
D:\jdev31\lib;D:\jdev31\lib\oraclexsql.jar;
D:\jdev31\lib\oraclexmlsql.jar;
D:\jdev31\lib\xmlparserv2_2027.jar;
D:\jdev31\jfc\lib\swingall.jar;
D:\jdev31\jswdk-1.0.1\lib\servlet.jar;
D:\Ora8i\rdbms\jlib\aqapi11.jar;
D:\Ora8i\rdbms\jlib\aqapi.jar;
D:\XMLWorkshop\xmlcomp.jar;
D:\jdev31\java\lib\classes.zip"  B2BDemo.Supplier.SupplierWatcher -step=1000 
-verbose=y -dbURL=jdbc:oracle:thin:@atp-1.us.oracle.com:1521:ORCL
  5. Finally, start the Client, from a browser, a PDA such as Palm Pilot, cell phone, or other device.

  6. [Retailer] Log in. You will see a Welcome! screen. See Figure 8-11.

    XSQL Script Example 2: Checking the ID of Users Logging In: getlogged.xsql

    <?xml version="1.0"?>

<!--
 | Second script to be called.
 | Check if the user in known in the database.
 | $Author: olediour@us $
 | $Revision: 1.1 $
 +-->
<?xml-stylesheet type="text/xsl" media="HandHTTP"     href="PP.xsl"?>
<?xml-stylesheet type="text/xsl" media="Mozilla"      href="HTML.xsl"?>

<loginResult xmlns:xsql="urn:oracle-xsql"
             connection="retail"
             custName="XXX">
  <pageTitle>Hi-Tech Mall</pageTitle>
  <xsql:query tag-case="upper">
    <![CDATA[
    select C.ID, C.NAME
    from CUSTOMER C
    where Upper(C.NAME) = Upper('{@custName}')
    ]]>
    <xsql:no-rows-query>
      Select '{@custName}' as "unknown" from dual
    </xsql:no-rows-query>
  </xsql:query>
  <nextStep>inventory.xsql</nextStep>
  <returnHome>index.xsql</returnHome>

</loginResult>

    This XSQL script calls the following XSL scripts:

  7. [Retailer]: Click on 'Please Enter the Mall'.

    XSQL Script Example 1: Displays First Hi-Tech Mall Screen -- index.xsql

    <?xml version="1.0"?>

<!--
 | This is the entry point in the application.
 | Notice that this script does not access the database.
 | $Author: olediour@us $
 | $Revision: 1.1 $
 +-->
<?xml-stylesheet type="text/xsl" media="HandHTTP"     href="PP.xsl"?>
<?xml-stylesheet type="text/xsl" media="Mozilla"      href="HTML.xsl"?>

<index xmlns:xsql="urn:oracle-xsql">
  <pageTitle>Hi-Tech Mall</pageTitle>
  <form action="getLogged.xsql" method="post">
    <field type="text" name="custName" prompt="Your ID"/>
    <button type="submit" label="Log In"/>
  </form>
</index>
  8. [Retailer]: The resulting screen displays the Hi-Tech Mall Catalog product listing. Click on the product you are interested in. See Figure 8-12.

    XSQL Script Example 3: Lists Catalog Products -- inventory.xsql

    <?xml version="1.0"?>

<!--
 | This is the third script called.
 | It produces the catalog from the Retailer's database.
 |
 | $Author: olediour@us $
 | $Revision: 1.1 $
 +-->
<?xml-stylesheet type="text/xsl" media="HandHTTP"     href="PP.xsl"?>
<?xml-stylesheet type="text/xsl" media="Mozilla"      href="HTML.xsl"?>

<inventory xmlns:xsql="urn:oracle-xsql"
           connection="retail"
           custId="000">
  <pageTitle>Hi-Tech Mall</pageTitle>
  <form action="order.xsql" method="post">
    <hiddenFields>
      <xsql:include-param name="custId"/>
    </hiddenFields>
    <theMart>
      <xsql:query tag-case="upper">
        <![CDATA[
        select I.ID,
               I.DESCRIPTION,
               I.PRICE,
               S.NAME
        from INVENTORY_ITEM I,
             SUPPLIER S
        where I.SUPPLIER_ID = S.ID
        ]]>
        <xsql:no-rows-query>
          Select 'No items !' as "Wow" from dual
        </xsql:no-rows-query>
      </xsql:query>
    </theMart>
  </form>
  <returnHome>index.xsql</returnHome>

</inventory>

    Figure 8-12 [Retailer] Enter the Hi-Tech Mall (Mart) Catalog


    Text description of catalog.jpg follows.
    Text description of the illustration catalog.jpg
  9. [Retailer]: Enter the quantity you need and click the "Place Order" button. See Figure 8-13.

    Figure 8-13 [Retailer]: Enter the Quantity and Click on "Place Order"


    Text description of placeordr.jpg follows.
    Text description of the illustration placeordr.jpg

    1. [Retailer] Click "Go On", or "Give Up". See Figure 8-14.

    XSQL Script Example 4: Enter a Quantity -- order.xsql

    <?xml version="1.0"?>
<!--
 | This is the fourth script called.
 | It prompts you to enter a quantity.
 |
 | $Author: olediour@us $
 | $Revision: 1.1 $
 +-->
<?xml-stylesheet type="text/xsl" media="HandHTTP"     href="PP.xsl"?>
<?xml-stylesheet type="text/xsl" media="Mozilla"      href="HTML.xsl"?>

<order xmlns:xsql="urn:oracle-xsql"
       connection="retail"
       custId="000"
       prodId="000">
  <pageTitle>Hi-Tech Mall</pageTitle>
  <xsql:query tag-case      = "upper"
              rowset-element= ""
              row-element   = "cust">
    <![CDATA[
    select C.ID,
           C.NAME
    from CUSTOMER C
    where C.ID = '{@custId}'
    ]]>
    <xsql:no-rows-query>
      Select '{@custId}' as "unknown" from dual
    </xsql:no-rows-query>
  </xsql:query>

  <xsql:query tag-case="upper"
        rowset-element=""
        row-element="prod">
    <![CDATA[
        select I.ID,
               I.DESCRIPTION,
               I.PRICE,
               S.NAME
        from INVENTORY_ITEM I,
             SUPPLIER S
        where I.SUPPLIER_ID = S.ID and
              I.ID = '{@prodId}'
    ]]>
    <xsql:no-rows-query>
      Select '{@prodId}' as "unknown" from dual
    </xsql:no-rows-query>
  </xsql:query>

  <returnHome>index.xsql</returnHome>
</order>

    Figure 8-14 [Retailer}: Click "Go On"


    Text description of goon.jpg follows.
    Text description of the illustration goon.jpg

Step 2. Retailer Places Order

The Retailer selects "Go On", then the application checks the order, perhaps the retailer's credit history, and then validates the order by selecting "Validate". See Figure 8-15 and Figure 8-16.

Figure 8-15 [Retailer]: Click "Validate"


Text description of validate.jpg follows.
Text description of the illustration validate.jpg

Figure 8-16 [Retailer]: Commit Successful. Table Ord has Been Updated


Text description of done.jpg follows.
Text description of the illustration done.jpg

Step 3. "Validate" Commits the Transaction. Retailer Application Produces the XML Order

  1. Once "Validate" is clicked, this triggers the main B2B process by means of the XSQL Servlet Action Handler. This is the end of client's interaction.

    The following scripts are executed by the B2B application (demo):

XSQL Script Example 5: Starts B2B Process -- placeorder.xsql

<?xml version="1.0"?>
<!--
 | This is the fifth and last, but not least, script called.
 | This script actually fires the whole B2B process.
 | It uses the Action Handler facility of XSQL Servlet.
 |
 | $Author: olediour@us $
 | $Revision: 1.1 $
 +-->
<?xml-stylesheet type="text/xsl" media="HandHTTP"     href="PP.xsl"?>
<?xml-stylesheet type="text/xsl" media="Mozilla"      href="HTML.xsl"?>

<placeOrder xmlns:xsql="urn:oracle-xsql"
            connection="retail"
            dbUrl     ="jdbc:oracle:thin:@atp-1.us.oracle.com:1521:ORCL"
            username  ="retailer"
            password  ="retailer"
            entity    ="Ord"
            operation ="insert"
            custId    =""
            ordId     =""
            prodId    =""
            qty       ="">
  <xsql:include-request-params/>
  <pageTitle>Hi-Tech Mall</pageTitle>
  <pageSeparator/>

  <xsql:action handler    ="B2BDemo.XSQLActionHandler.RetailActionHandler"
               dbUrl      ="{@dbUrl}"
               username   ="{@username}"
               password   ="{@password}"
               entity     ="{@entity}"
               operation  ="{@operation}"
               custId     ="{@custId}"
               ordId      ="{@ordId}"
               prodId     ="{@prodId}"
               qty        ="{@qty}"/>
  <pageSeparator/>
  <bottomLinks>
    <aLink href="placeOrder.xsql?operation=rollback">Rollback</aLink>
  </bottomLinks>
  <returnHome>index.xsql</returnHome>
</placeOrder>

Java Example 1: Action Handler Called by placeOrder.xsql -- RetailActionHandler.java

Note:

This example traverses almost 20 pages.  

package B2BDemo.XSQLActionHandler;
/**
 * Action Handler called by the placeOrder.xsql script.
 * Actually fires the B2B process itself.
 * Uses SessionHolder to maintain transaction state.
 *
 * @see SessionHolder
 * @see placeOrder.xsql
 * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Corp.
 */
import oracle.xml.xsql.*;
import oracle.xml.xsql.actions.XSQLIncludeXSQLHandler;
import javax.servlet.http.*;
import javax.servlet.*;
import org.w3c.dom.*;

import java.sql.*;
import java.io.*;

import oracle.xml.parser.v2.*;

import B2BDemo.AQUtil.*;
import B2BDemo.*;
import B2BDemo.XMLUtil.*;

public class RetailActionHandler extends XSQLActionHandlerImpl
{
  private static final boolean verbose   = false;
  private static final boolean debugFile = false;

  private Connection actionConnection = null;

  private String appUrl      = "";
  private String appUser     = "";
  private String appPassword = "";

  public static final String DBURL       = "dbUrl";
  public static final String USERNAME    = "username";
  public static final String PASSWORD    = "password";

  public static final String OPERATION   = "operation";

  public static final String ENTITY      = "entity";

  public static final String ORDID       = "ordId";
  public static final String ORDERDATE   = "orderDate";
  public static final String CONTACTNAME = "contactName";
  public static final String TRACKINGNO  = "trackingNo";
  public static final String STATUS      = "status";
  public static final String CUSTID      = "custId";

  public static final String QTY         = "qty";
  public static final String PRODID      = "prodId";

  public static final String SELECT      = "select";
  public static final String INSERT      = "insert";
  public static final String BEGIN       = "begin";
  public static final String COMMIT      = "commit";
  public static final String ROLLBACK    = "rollback";

  XSQLActionHandler nestedHandler = null;
  String operation = null;

  String entity       = null;
  String ordId        = null;
  String orderDate    = null;
  String contactName  = null;
  String trackingNo   = null;
  String status       = null;
  String custId       = null;
  String qty          = null;
  String prodId       = null;

  HttpServletRequest     request  = null;
  HttpServletResponse    response = null;
  HttpSession            session  = null;

  public void init(XSQLPageRequest xspRequest, Element action)
  {
    super.init(xspRequest, action);
    // Retrieve the parameters

    if (verbose)
      System.out.println("init Action Handler...................");

    appUrl      = getAttributeAllowingParam(DBURL,    action);
    appUser     = getAttributeAllowingParam(USERNAME, action);
    appPassword = getAttributeAllowingParam(PASSWORD, action);

    operation = getAttributeAllowingParam(OPERATION, action);
    entity    = getAttributeAllowingParam(ENTITY, action);

    ordId       = getAttributeAllowingParam(ORDID,       action);
    orderDate   = getAttributeAllowingParam(ORDERDATE,   action);
    contactName = getAttributeAllowingParam(CONTACTNAME, action);
    trackingNo  = getAttributeAllowingParam(TRACKINGNO,  action);
    status      = getAttributeAllowingParam(STATUS,      action);
    custId      = getAttributeAllowingParam(CUSTID,      action);
    prodId      = getAttributeAllowingParam(PRODID,      action);
    qty         = getAttributeAllowingParam(QTY,         action);
    //
    if (verbose)
    {
      System.out.println("OrdID  > " + ordId);
      System.out.println("CustID > " + custId);
      System.out.println("ProdID > " + prodId);
    }

    final String HOLDER_NAME = "XSQLActionHandler.connection";
    try
    {
      if (xspRequest.getRequestType().equals("Servlet"))
      {
        XSQLServletPageRequest xspr = (XSQLServletPageRequest)xspRequest;
        HttpServletRequest req      = xspr.getHttpServletRequest();
        session = req.getSession(true); // true : Create if missing !!!
        if (verbose)
          System.out.println("Session Id = " + session.getId() + " - new : " +
                                                              session.isNew());
        SessionHolder sh = (SessionHolder) session.getValue(HOLDER_NAME);
        if (sh == null)
        {
          if (verbose)
            System.out.println("New SessionHandler > Getting connected at " +
                                                       (new java.util.Date()));
          actionConnection = getConnected(appUrl, appUser, appPassword);
          sh = new SessionHolder(actionConnection);
          session.putValue(HOLDER_NAME, sh);
        }
        actionConnection = sh.getConnection();
        if (verbose)
        {
          System.out.println("Reusing Connection at " + (new java.util.Date()) +
                                 " - Opened at " + sh.getOpenDate().toString());
          System.out.println("Driver     : " +
                                actionConnection.getMetaData().getDriverName());
          System.out.println("SessionId  : " + session.getId());
          System.out.println("AutoCommit : " +
                                           actionConnection.getAutoCommit());
        }
      }
    }
    catch (Exception e)
    {
      System.err.println("Error in retrieving session context \n" + e);
      e.printStackTrace();
    }
  }

  // The result is the out parameter
  public void handleAction(Node result) throws SQLException
  {
    XSQLPageRequest xpr = getPageRequest();
    if (xpr.getRequestType().equals("Servlet"))
    {
      // Get the servlet context and components
      XSQLServletPageRequest xspr = (XSQLServletPageRequest)xpr;
      request  = xspr.getHttpServletRequest();
      response = xspr.getHttpServletResponse();

      Document doc = null;

      // Display CLASSPATH
      XMLDocument myDoc = new XMLDocument();
      try
      {
        Element root = myDoc.createElement("root");
        myDoc.appendChild(root);

        Element cp = myDoc.createElement("ClassPath");
        root.appendChild(cp);

        // The text is a descendant of its node
        Node cpTxt = myDoc.createTextNode("text#");
        cpTxt.setNodeValue(System.getProperty("java.class.path"));
        cp.appendChild(cpTxt);

        Element e = myDoc.getDocumentElement();
        e.getParentNode().removeChild(e);
        result.appendChild(e);  // Append child to result before returning it.
      }
      catch (Exception e)
      {
        System.err.println("Building XMLDoc");
        e.printStackTrace();
      }
      try
      {
        // Add a node to hold operation value
        XMLDocument xmlDoc = new XMLDocument();
        Element elmt = xmlDoc.createElement("requiredOperation");
        xmlDoc.appendChild(elmt);
        Node theText = xmlDoc.createTextNode("text#");
        theText.setNodeValue(operation);
        elmt.appendChild(theText);
        // Append to result
        Element e = xmlDoc.getDocumentElement();
        e.getParentNode().removeChild(e);
        result.appendChild(e);  // Append child to result before returning it.
      }
      catch (Exception e)
      {
        System.err.println("Building XMLDoc (2)");
        e.printStackTrace();
      }

      try
      {
        // Dispatch
        if (operation.equals(SELECT))
       /* doc = manageSelect() */;
        else if (operation.equals(INSERT))
          doc = manageInsert();
        else if (operation.equals(BEGIN))
          doc = doBegin();
        else if (operation.equals(COMMIT))
          doc = doCommit();
        else if (operation.equals(ROLLBACK))
          doc = doRollback();
        else // Wrong operation
        {
          XMLDocument xmlDoc = new XMLDocument();
          Element elmt = xmlDoc.createElement("unknownOperation");
          xmlDoc.appendChild(elmt);
          Node theText = xmlDoc.createTextNode("text#");
          theText.setNodeValue(operation);
          elmt.appendChild(theText);
          // Append to result
          Element e = xmlDoc.getDocumentElement();
          e.getParentNode().removeChild(e);
          result.appendChild(e);  // Append child to result before returning it.
        }
      }
      catch (Exception ex)
      {
      // file://this.reportError(e);
        XMLDocument xmlDoc = new XMLDocument();
        Element elmt = xmlDoc.createElement("operationProblem");
        xmlDoc.appendChild(elmt);
        Node theText = xmlDoc.createTextNode("text#");
        theText.setNodeValue(ex.toString());
        elmt.appendChild(theText);
        // Append to result
        Element e = xmlDoc.getDocumentElement();
        e.getParentNode().removeChild(e);
        result.appendChild(e);  // Append child to result before returning it.
      }

      try
      {
        if (doc != null)
        {
          Element e = doc.getDocumentElement();
          e.getParentNode().removeChild(e);
          result.appendChild(e);  // Append child to result before returning it.
        }
      }
      catch (Exception e)
      {
        try
        {
          ServletOutputStream out = response.getOutputStream();
          out.println(e.toString());
        }
        catch (Exception ex) {}
      }
    }
    else  // Command line ?
    {
      System.out.println("Request type is [" + xpr.getRequestType() + "]");
    }
  }

/**
 * Removed because uselezss in this demo.
 *
  private Document manageSelect() throws Exception
  {
    Document doc = null;
    String cStmt = "";

    if (custId != null && custId.length() > 0)
      vo.setWhereClause("Customer_Id = '" + custId + "'");
    else
      vo.setWhereClause(null);
    vo.executeQuery();
    doc = data.getXMLDocument(); // Query implicitly executed !
    return doc;
  }
*/
  private Document manageInsert() throws Exception
  {
    Document doc = null;

    if (entity.equals("Ord"))
      doc = insertInOrd();
    else if (entity.equals("LineItem"))
      doc = insertInLine();
    else
    {
      doc = new XMLDocument();
      Element elmt = doc.createElement("operationQuestion");
      Attr attr = doc.createAttribute("opType");
      attr.setValue("insert");
      elmt.setAttributeNode(attr);
      doc.appendChild(elmt);
      Node txt = doc.createTextNode("text#");
      elmt.appendChild(txt);
      txt.setNodeValue("Don't know what to do with " + entity);
    }
    return doc;
  }

  private Document insertInOrd()
  {
    Document doc = null;
    if (custId == null || custId.length() == 0)
    {
      doc = new XMLDocument();
      Element elmt = doc.createElement("operationProblem");
      Attr attr = doc.createAttribute("opType");
      attr.setValue("OrdInsert");
      elmt.setAttributeNode(attr);
      doc.appendChild(elmt);
      Node txt = doc.createTextNode("text#");
      elmt.appendChild(txt);
      txt.setNodeValue("Some element(s) missing for ord insert (custId)");
    }
    else
    {
      String seqStmt = "select Ord_Seq.nextVal from dual";
      String seqVal = "";
      try
      {
        Statement stmt = actionConnection.createStatement();
        ResultSet rSet = stmt.executeQuery(seqStmt);
        while (rSet.next())
          seqVal = rSet.getString(1);
        rSet.close();
        stmt.close();
      }
      catch (SQLException e)
      {
        System.err.println("Error reading ORD_SEQ Sequence : " + e.toString());
      }
      //                                  1           2          3             4
      String cStmt = "insert into ORD values (?, sysdate, ?, 'AX' || ?,
                                                               'Pending', ?)";
      try
      {
        if (verbose)
          System.out.println("Inserting Order # " + seqVal);
        PreparedStatement pStmt = actionConnection.prepareStatement(cStmt);
        pStmt.setString(1, seqVal);
        pStmt.setString(2, "Ora817");  // Default value !
        pStmt.setString(3, seqVal);
        pStmt.setString(4, custId);
        pStmt.execute();
        pStmt.close();
      /**
        try
        {
          Statement stmt = actionConnection.createStatement();
          ResultSet rSet = stmt.executeQuery("SELECT * FROM ORD WHERE ID = " +
                                                                      seqVal);
          int i = 0;
          while (rSet.next())
            i++;
          if (verbose)
            System.out.println(i + " record found for " + seqVal);
          rSet.close();
          stmt.close();
        }
        catch (SQLException e)
        {
          System.err.println("Error : " + e.toString());
        }
       */
        doc = new XMLDocument();
        Element elmt = doc.createElement("operationResult");
        Attr attr = doc.createAttribute("opType");
        attr.setValue("insert");
        elmt.setAttributeNode(attr);

        attr = doc.createAttribute("Step");
        attr.setValue(entity);
        elmt.setAttributeNode(attr);

        doc.appendChild(elmt);
        Node txt = doc.createTextNode("text#");
        elmt.appendChild(txt);
        txt.setNodeValue("About to insert your Order for " + qty + " item(s)");

        Element nextElmt = doc.createElement("nextStep");
        elmt.appendChild(nextElmt);
        attr = doc.createAttribute("Label");
        attr.setValue("Go on");
        nextElmt.setAttributeNode(attr);

        attr = doc.createAttribute("Action");
        nextElmt.setAttributeNode(attr);
        attr.setValue("placeOrder.xsql");
        Element pList = doc.createElement("prmList");
        nextElmt.appendChild(pList);
        // viewobject
        Element prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("entity");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue("LineItem");
        prm.setAttributeNode(attr);
        // custId
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("custId");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue(custId);
        prm.setAttributeNode(attr);
        // prodId
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("prodId");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue(prodId);
        prm.setAttributeNode(attr);
        // qty
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("qty");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue(qty);
        prm.setAttributeNode(attr);
        // ordId
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("ordId");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue(seqVal);
        prm.setAttributeNode(attr);

        nextElmt = doc.createElement("nextStep");
        elmt.appendChild(nextElmt);
        attr = doc.createAttribute("Label");
        attr.setValue("Give up");
        nextElmt.setAttributeNode(attr);

        attr = doc.createAttribute("Action");
        nextElmt.setAttributeNode(attr);
        attr.setValue("placeOrder.xsql");
        pList = doc.createElement("prmList");
        nextElmt.appendChild(pList);
        // viewobject
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("operation");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue("rollback");
        prm.setAttributeNode(attr);
      }
      catch (Exception e)
      {
        doc = new XMLDocument();
        Element elmt = doc.createElement("operationProblem");
        Attr attr = doc.createAttribute("opType");
        attr.setValue("insert");
        elmt.setAttributeNode(attr);

        attr = doc.createAttribute("Step");
        attr.setValue(entity);
        elmt.setAttributeNode(attr);

        doc.appendChild(elmt);
        Node txt = doc.createTextNode("text#");
        elmt.appendChild(txt);
        txt.setNodeValue(e.toString());
        if (verbose)
          System.out.println("Error : " + e.toString());
        Element prm = doc.createElement("parameters");
        elmt.appendChild(prm);
        // ID
        Element prmVal = doc.createElement("ID");
        prm.appendChild(prmVal);
        txt = doc.createTextNode("text#");
        prmVal.appendChild(txt);
        txt.setNodeValue(ordId);
        // CUSTOMER_ID
        prmVal = doc.createElement("CUSTOMER_ID");
        prm.appendChild(prmVal);
        txt = doc.createTextNode("text#");
        prmVal.appendChild(txt);
        txt.setNodeValue(custId);
      }
    }
    return doc;
  }

  private Document insertInLine()
  {
    Document doc = null;
    if (custId == null || custId.length() == 0 ||
        qty == null || qty.length() == 0 ||
        prodId == null || prodId.length() == 0 ||
        ordId == null || ordId.length() == 0)
    {
      doc = new XMLDocument();
      Element elmt = doc.createElement("operationProblem");
      Attr attr = doc.createAttribute("opType");
      attr.setValue("lineInsert");
      elmt.setAttributeNode(attr);
      doc.appendChild(elmt);
      Node txt = doc.createTextNode("text#");
      elmt.appendChild(txt);
      txt.setNodeValue("Some element(s) missing for line insert (" +
                       ((custId == null || custId.length() == 0)?"custId ":"") +
                       ((qty == null || qty.length() == 0)?"qty ":"") +
                       ((prodId == null || prodId.length() == 0)?"prodId ":"") +
                       ((ordId == null || ordId.length() == 0)?"ordId ":"") +")"
                       );

      Element subElmt = doc.createElement("custId");
      elmt.appendChild(subElmt);
      txt = doc.createTextNode("text#");
      subElmt.appendChild(txt);
      txt.setNodeValue(custId);

      subElmt = doc.createElement("qty");
      elmt.appendChild(subElmt);
      txt = doc.createTextNode("text#");
      subElmt.appendChild(txt);
      txt.setNodeValue(qty);

      subElmt = doc.createElement("prodId");
      elmt.appendChild(subElmt);
      txt = doc.createTextNode("text#");
      subElmt.appendChild(txt);
      txt.setNodeValue(prodId);

      subElmt = doc.createElement("ordId");
      elmt.appendChild(subElmt);
      txt = doc.createTextNode("text#");
      subElmt.appendChild(txt);
      txt.setNodeValue(ordId);
    }
    else
    {
      if (verbose)
        System.out.println("Inserting line : Ord>" + ordId + ", Prod>" + prodId
                                                             + ", Qty>" + qty);
    /**
      try
      {
        Statement stmt = actionConnection.createStatement();
        ResultSet rSet = stmt.executeQuery("SELECT * FROM ORD WHERE ID = " +
                                                                      ordId);
        int i = 0;
        while (rSet.next())
          i++;
        System.out.println(i + " record found for " + ordId);
        rSet.close();
        stmt.close();
      }
      catch (SQLException e)
      {
        System.err.println("Error : " + e.toString());
      }
     */
      String cStmt = "insert into line_item values (Line_item_seq.nextVal, ?, ?,
                                                                         ?, 0)";
      try
      {
        PreparedStatement pStmt = actionConnection.prepareStatement(cStmt);
        pStmt.setString(1, qty);
        pStmt.setString(2, prodId);
        pStmt.setString(3, ordId);
        pStmt.execute();
        pStmt.close();

        doc = new XMLDocument();
        Element elmt = doc.createElement("operationResult");
        Attr attr = doc.createAttribute("opType");
        attr.setValue("insert");
        elmt.setAttributeNode(attr);

        attr = doc.createAttribute("Step");
        attr.setValue(entity);
        elmt.setAttributeNode(attr);

        doc.appendChild(elmt);
        Node txt = doc.createTextNode("text#");
        elmt.appendChild(txt);
        txt.setNodeValue("Insert Successful");

        Element nextElmt = doc.createElement("nextStep");
        elmt.appendChild(nextElmt);
        attr = doc.createAttribute("Label");
        attr.setValue("Validate");
        nextElmt.setAttributeNode(attr);

        attr = doc.createAttribute("Action");
        nextElmt.setAttributeNode(attr);
        attr.setValue("placeOrder.xsql");
        Element pList = doc.createElement("prmList");
        nextElmt.appendChild(pList);
        // operation
        Element prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("operation");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue("commit");
        prm.setAttributeNode(attr);
        // ordId
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("ordId");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue(ordId);
        prm.setAttributeNode(attr);

        nextElmt = doc.createElement("nextStep");
        elmt.appendChild(nextElmt);
        attr = doc.createAttribute("Label");
        attr.setValue("Cancel");
        nextElmt.setAttributeNode(attr);

        attr = doc.createAttribute("Action");
        nextElmt.setAttributeNode(attr);
        attr.setValue("placeOrder.xsql");
        pList = doc.createElement("prmList");
        nextElmt.appendChild(pList);
        // operation
        prm = doc.createElement("prm");
        pList.appendChild(prm);
        attr = doc.createAttribute("name");
        attr.setValue("operation");
        prm.setAttributeNode(attr);
        attr = doc.createAttribute("value");
        attr.setValue("rollback");
        prm.setAttributeNode(attr);
      }
      catch (Exception e)
      {
        if (verbose)
          System.out.println("Error when inserting " + e.toString());

        doc = new XMLDocument();
        Element elmt = doc.createElement("operationProblem");
        Attr attr = doc.createAttribute("opType");
        attr.setValue("insert");
        elmt.setAttributeNode(attr);

        attr = doc.createAttribute("Step");
        attr.setValue(entity);
        elmt.setAttributeNode(attr);
        doc.appendChild(elmt);

        Node txt = doc.createTextNode("text#");
        elmt.appendChild(txt);
        txt.setNodeValue(e.toString());

        Element prm = doc.createElement("parameters");
        elmt.appendChild(prm);
        // ID
        Element prmVal = doc.createElement("ORD_ID");
        prm.appendChild(prmVal);
        txt = doc.createTextNode("text#");
        prmVal.appendChild(txt);
        txt.setNodeValue(ordId);
        // QTY
        prmVal = doc.createElement("QTY");
        prm.appendChild(prmVal);
        txt = doc.createTextNode("text#");
        prmVal.appendChild(txt);
        txt.setNodeValue(qty);
        // ITEM_ID
        prmVal = doc.createElement("ITEM_ID");
        prm.appendChild(prmVal);
        txt = doc.createTextNode("text#");
        prmVal.appendChild(txt);
        txt.setNodeValue(prodId);
      }
    }
    return doc;
  }

  private Document doCommit() throws Exception
  {
    Document doc = null;
    actionConnection.commit();

    doc = new XMLDocument();
    Element elmt = doc.createElement("operationResult");
    Attr attr = doc.createAttribute("opType");
    attr.setValue("commit");
    elmt.setAttributeNode(attr);
    doc.appendChild(elmt);
    Node txt = doc.createTextNode("dummy");
    elmt.appendChild(txt);
    txt.setNodeValue("Commit successfull for order #" + ordId + " from " + 
entity);

    if (ordId != null && ordId.length() > 0)
    {
      // Generate XML Document to send to AQ
      // Start from Ord with OrdId value -

      AQWriter aqw = null;

      aqw = new AQWriter(AppCste.AQuser,
                         AppCste.AQpswd,
                         AppCste.AQDBUrl,
                         "AppOne_QTab",
                         "AppOneMsgQueue");

      String doc2send = XMLGen.returnDocument(actionConnection, ordId);
      // sending XMLDoc in the Queue
      try
      {
        if (verbose)
          System.out.println("Doc : " + doc2send);
        if (debugFile)
        {
          BufferedWriter bw = new BufferedWriter(new FileWriter("debug.txt"));
          bw.write("Rows in " + entity);
          bw.write(doc2send);
          bw.flush();
          bw.close();
        }
      }
      catch (Exception ex) {}

      aqw.writeQ(new B2BMessage(MessageHeaders.APP_A,
                                MessageHeaders.APP_B,
                                MessageHeaders.NEW_ORDER,
                                doc2send));
      aqw.flushQ();  // Commit !
    }

    return doc;
  }

  private Document doRollback() throws Exception
  {
    Document doc = null;
    actionConnection.rollback();

    doc = new XMLDocument();
    Element elmt = doc.createElement("operationResult");
    Attr attr = doc.createAttribute("opType");
    attr.setValue("rollback");
    elmt.setAttributeNode(attr);
    doc.appendChild(elmt);
    Node txt = doc.createTextNode("dummy");
    elmt.appendChild(txt);
    txt.setNodeValue("Rollback successfull");

    return doc;
  }

  private Document doBegin() throws Exception
  {
    Document doc = null;
    actionConnection.setAutoCommit(false);

    doc = new XMLDocument();
    Element elmt = doc.createElement("operationResult");
    Attr attr = doc.createAttribute("opType");
    attr.setValue("begin");
    elmt.setAttributeNode(attr);
    doc.appendChild(elmt);
    Node txt = doc.createTextNode("dummy");
    elmt.appendChild(txt);
    txt.setNodeValue("Begin successfull");

    return doc;
  }
  
  private static Connection getConnected(String connURL,
                                         String userName,
                                         String password)
  {
    Connection conn = null;
    try
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(connURL, userName, password);
      conn.setAutoCommit(false);
    }
    catch (Exception e)
    {
      System.err.println(e);
      System.exit(1);
    }
    return conn;
  }
}

Java Example 2: Maintains Session Context for RetailActionHandler.java -- SessionHolder.java

// Copyright (c) 2000 Oracle Corporation
package B2BDemo.XSQLActionHandler;
/**
 * Used to maintain the connection context from the XSQL Action Handler.
 * Also closes the connection when servlet expires.
 *
 * @see RetailActionHandler
 */
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class SessionHolder implements HttpSessionBindingListener
{
  private Connection c;
  private java.util.Date d = null;

  public SessionHolder(Connection conn)
  {
    System.out.println("New SessionHandler");
    this.c = conn;
    this.d = new java.util.Date();
  }

  public Connection getConnection()
  {
    return this.c;
  }

  public java.util.Date getOpenDate()
  {
    return this.d;
  }

  public void valueBound(HttpSessionBindingEvent event)
  {
    System.out.println("\nvalueBound ! " + event.getName() + "\nat " + (new
          java.util.Date()) + "\nfor " + event.getSession().getId());
  }

  public void valueUnbound(HttpSessionBindingEvent event)
  {
    System.out.println("\nvalueUnbound ! " + event.getName() + "\nat " + (new
                   java.util.Date()) + "\nfor " + event.getSession().getId());
    event.getSession().removeValue("XSQLActionHandler.connection");
    if (this.c != null)
    {
      try { this.c.close(); }
      catch (Exception e)
      {
        System.out.println("Problem when closing the connection from " +
                           event.getName() +
                           " for " +
                           event.getSession().getId() +
                           " :\n" +
                           e);
      }
    }
  }
}

Step 4. AQ Broker-Transformer Transforms XML Document According to Supplier's Format

  1. AQ Broker-Transformer application is alerted that an XML order is pending.

  2. An XML document containing the details of your order has been produced using the XML-SQL Utility. This document has been sent to the AQ Broker-Transformer for propagation, using Advanced Queuing.

    The AQ Broker application knows the following, based on its Stylesheet table:

    • Who it comes from: Retailer

    • Who it goes to: Supplier

    • What its for: NEW ORDER

    These elements are used to select the correct stylesheet from Stylesheet table. XSLT Processor processes the transformation. See Figure 8-17.

Scripts:

Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback