Oracle9i Case Studies - XML Applications Release 1 (9.0.1) Part Number A88895-01 |
|
This chapter contains the following topics:
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.
The following lists requirements to build and run the online B2B XML application:
This XML application and demo illustrate a content management and B2B Messaging implementation. The main transactions in this application are as follows:
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.
This solution implements the following:
The main tasks are shown in Figure 8-2.
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"
JDeveloper
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.
Figure 8-1 illustrates the overall flow of the Retailer-Supplier transaction. The Retailer enters the order.
Using AQ in this application has the following advantages:
Text description of the illustration adxml031.gif
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.
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.
Create the Retailer and Supplier schemas. See "Online B2B XML Application: Main Components"
First modify the .bat files for your environment as follows:
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.
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".
Run the Java class, stopAllQueues, or the script named stopQ.bat
The following schema scripts are provided here in the order in which they should be executed:
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.
These schema scripts set up the Retailer and Supplier environment, users, tablespaces, quota, and so on. They also create and then populate the schemas.
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 !
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;
Run the AQ schema scripts as follows:
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 /
This script calls four scripts to create the AQ queue tables.
@mkQueueTableApp1 @mkQueueTableApp2 @mkQueueTableApp3 @mkQueueTableApp4
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');
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');
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');
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');
Run the following scripts to create and populate the stylesheets, tasks, and applications tables:
This calls:
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
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 /
Run reset.sql
to clean up your environment and rerun this application.
This calls the following 16 PL/SQL scripts:
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
These four scripts are called from reset.sql.
They use PL/SQL procedure dbms_aqadm.stop_queue
to stop the queues.
execute dbms_aqadm.stop_queue(queue_name=>'AppOneMsgQueue');
execute dbms_aqadm.stop_queue(queue_name=>'AppTwoMsgQueue');
execute dbms_aqadm.stop_queue(queue_name=>'AppThreeMsgQueue');
execute dbms_aqadm.stop_queue(queue_name=>'AppFourMsgQueue');
These four scripts are called from reset.sql
. They use PL/SQL procedure dbms_aqadm.drop_queue
to drop the queues.
execute dbms_aqadm.drop_queue (queue_name=>'AppOneMsgQueue');
execute dbms_aqadm.drop_queue (queue_name=>'AppTwoMsgQueue');
execute dbms_aqadm.drop_queue (queue_name=>'AppThreeMsgQueue');
execute dbms_aqadm.drop_queue (queue_name=>'AppFourMsgQueue');
These four scripts are called from reset.sql
. They use PL/SQL procedure, dbms_aqadm.create_queue
to create the queues.
execute dbms_aqadm.create_queue (queue_name=>'AppOneMsgQueue', queue_ table=>'AppOne_QTab');
execute dbms_aqadm.create_queue (queue_name=>'AppTwoMsgQueue', queue_ table=>'AppTwo_QTab');
execute dbms_aqadm.create_queue (queue_name=>'AppThreeMsgQueue', queue_ table=>'AppThree_QTab');
execute dbms_aqadm.create_queue (queue_name=>'AppFourMsgQueue', queue_ table=>'AppFour_QTab');
These four scripts are called from reset.sql
. They use PL/SQL procedure, dbms_aqadm.start_queue
to start the queues.
execute dbms_aqadm.start_queue(queue_name=>'AppOneMsgQueue');
execute dbms_aqadm.start_queue (queue_name=>'AppTwoMsgQueue');
execute dbms_aqadm.start_queue (queue_name=>'AppThreeMsgQueue');
execute dbms_aqadm.start_queue (queue_name=>'AppFourMsgQueue');
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')) /
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 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" .
The following Retailer transactions occur:
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.
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.
When the AQ Broker-Transformer receives the XML document the following actions transpire:
The schema used by the AQ Broker-Transformer is shown inFigure 8-4.
When the Supplier receives the reformatted XML order document from the AQ Broker-Transformer, the following protocols transpire:
When the Supplier application has inserted the XML document into the Supplier database the following actions transpire:
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.
See Figure 8-5 for the detailed procedural flow of the B2B XML application.
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.
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
<?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:
<?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>
<?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>
<?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>
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.
The following scripts are executed by the B2B application (demo):
<?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>
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; } }
// 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); } } } }
The AQ Broker application knows the following, based on its Stylesheet table:
These elements are used to select the correct stylesheet from Stylesheet table. XSLT Processor processes the transformation. See Figure 8-17.
MessageBroker. java
calls BrokerThread.java
which calls
BrokerThread.java
calls AQReader.java
and AQWriter.java
AQReader.java
and AQWriter.java
both use B2BMessages.java
for their message structure.
See the "AQ Broker-Transformer and Advanced Queuing Scripts" section for code listings that run the AQ Broker-Transformer and the Advanced Queuing process.
The newly reformatted XML document is sent to the Supplier by means of Advanced Queuing [WRITE].
Scripts: Still using SupplierWatcher.java
UpdateMaster.java
. This receives the message and parses it.
That's it!
To stop the B2B XML application (demo), run Java Example 3: stopQ.bat.
@echo off @echo stopping all Qs 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.AQUtil.StopAllQueues
To view your order status directly from the database run this SQL script.
set ver off select O.ID as "Order#", O.OrderDate as "Order Date", O.Status as "Status" From ORD O, CUSTOMER C Where O.CUSTOMER_ID = C.ID and Upper(C.NAME) = Upper('&CustName');
The following list provides the Java examples' calling sequence. The .java extension for each file has been omitted. The notation "<---" implies "calls", for example, AQReader <----- B2BMessage implies that AQReader calls B2BMessage.
To prevent over complicating the listing of examples in the section, "Running the B2B XML Application: Detailed Procedure", the XSL examples are listed separately.
<?xml version="1.0"?> <!-- | $Author: olediour@us $ | $Date: 04-May-2000 | xsl for html | $Revision: 1.1 $ +--> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output media-type="text/html" method="html" encoding="ISO-8859-1"/> <xsl:template match="/"> <html> <head> <title>Retail Application</title> </head> <body> <xsl:if test="//pageTitle"> <h2><xsl:value-of select="//pageTitle"/></h2> </xsl:if> <xsl:choose> <xsl:when test="loginResult"> <xsl:apply-templates select="loginResult"/> </xsl:when> <xsl:when test="index"> <xsl:apply-templates select="index"/> </xsl:when> <xsl:when test="inventory"> <xsl:apply-templates select="inventory"/> </xsl:when> <xsl:when test="order"> <xsl:apply-templates select="order"/> </xsl:when> <xsl:when test="placeOrder"> <xsl:apply-templates select="placeOrder"/> </xsl:when> <xsl:otherwise> <p align="center"> <h3>This kind of XML Document cannot be processed...</h3> </p> </xsl:otherwise> </xsl:choose> </body> </html> </xsl:template> <xsl:template match="loginResult"> <xsl:if test="ROWSET/ROW/unknown"> <table width="98%"> <tr> <td bgcolor="yellow" align="center"> <xsl:value-of select="ROWSET/ROW/unknown"/> is not allowed to log in !</td> </tr> </table> </xsl:if> <xsl:if test="ROWSET/ROW/NAME"> <p align="center"> <h2>Welcome <xsl:value-of select="ROWSET/ROW/NAME"/> !</h2> </p> <p align="center"> <a> <xsl:attribute name="href"> <xsl:value-of select="nextStep"/>?custId=<xsl:value-of select="ROWSET/ROW/ID"/> </xsl:attribute> Please enter the Mall ! </a> </p> </xsl:if> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="index"> <xsl:for-each select="form"> <center> <form> <xsl:attribute name="action"><xsl:value-of select="./@action"/></xsl:attribute> <xsl:attribute name="method"><xsl:value-of select="./@method"/></xsl:attribute> <xsl:if test="./field"> <table width="98%" border="1"> <xsl:for-each select="./field"> <tr> <td align="right"><xsl:value-of select="./@prompt"/></td> <td> <input> <xsl:choose> <xsl:when test="./@type = 'text'"> <xsl:attribute name="type">text</xsl:attribute> </xsl:when> </xsl:choose> <xsl:attribute name="name"> <xsl:value-of select="./@name"/></xsl:attribute> </input> </td> </tr> </xsl:for-each> </table> </xsl:if> <xsl:if test="./button"> <p> <xsl:for-each select="./button"> <input> <xsl:choose> <xsl:when test="./@type = 'submit'"> <xsl:attribute name="type">submit</xsl:attribute> </xsl:when> </xsl:choose> <xsl:attribute name="value"> <xsl:value-of select="./@label"/> </xsl:attribute> </input> </xsl:for-each> </p> </xsl:if> </form> </center> </xsl:for-each> </xsl:template> <xsl:template match="inventory"> <h2>This is the Mart content</h2> <table> <tr> <th>Prod #</th> <th>Product</th> <th>Price</th> <th>Supplied by</th> </tr> <xsl:for-each select="form/theMart/ROWSET/ROW"> <tr> <td><xsl:value-of select="ID"/></td> <td> <a> <xsl:attribute name="href"> <xsl:value-of select="../../../../form/@action"/>?custId=<xsl:value-of select="../../../../form/hiddenFields/custId"/>&prodId=<xsl:value-of select="ID"/> </xsl:attribute> <xsl:value-of select="DESCRIPTION"/> </a> </td> <td><xsl:value-of select="PRICE"/></td> <td><xsl:value-of select="NAME"/></td> </tr> </xsl:for-each> </table> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="order"> <center> <h2>Thank you <xsl:value-of select="CUST/NAME"/> for shopping with us !</h2> <hr/> <h2>Please enter the quantity</h2> <form action="placeOrder.xsql" method="post"> <input type="hidden" name="prodId"> <xsl:attribute name="value"> <xsl:value-of select="PROD/ID"/> </xsl:attribute> </input> <input type="hidden" name="custId"> <xsl:attribute name="value"> <xsl:value-of select="CUST/ID"/></xsl:attribute> </input> <table border="1"> <tr> <td colspan="2"><xsl:value-of select="PROD/DESCRIPTION"/> at $<xsl:value-of select="PROD/PRICE"/> each supplied by <xsl:value-of select="PROD/NAME"/></td> </tr> <tr> <td align="right">Quantity</td> <td><input type="text" name="qty"/></td> </tr> </table> <p><input type="submit" value="Place Order"/></p> </form> </center> <p> <a><xsl:attribute name="href"> <xsl:value-of select="returnHome"/> </xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="placeOrder"> <xsl:if test="operationResult"> <table width="98%"> <tr><td align="center"> <font color="navy"> <xsl:value-of select="operationResult/text()"/> </font></td></tr> <tr> <td align="center"> <xsl:for-each select="operationResult/nextStep"> <form method="post"> <xsl:attribute name="action"><xsl:value-of select="./@Action"/></xsl:attribute> <xsl:if test="prmList"> <xsl:for-each select="prmList/prm"> <input type="hidden"> <xsl:attribute name="name"><xsl:value-of select="./@name"/></xsl:attribute> <xsl:attribute name="value"><xsl:value-of select="./@value"/></xsl:attribute> </input> </xsl:for-each> </xsl:if> <input type="submit"> <xsl:attribute name="value"><xsl:value-of select="./@Label"/></xsl:attribute> </input> </form> </xsl:for-each> </td> </tr> </table> </xsl:if> <xsl:if test="xsql-error"> <table width="98%"> <tr><td><xsl:value-of select="xsql-error/@action"/></td></tr> <tr><td><xsl:value-of select="xsql-error/statement"/></td></tr> <tr><td><xsl:value-of select="xsql-error/message"/></td></tr> </table> </xsl:if> <xsl:if test="operationProblem"> <table width="98%"> <tr> <td colspan="2" align="center"> <font color="red"><b><xsl:value-of select="operationProblem/text()"/></b></font> </td> </tr> <xsl:for-each select="operationProblem/parameters/*"> <tr> <td align="right"><xsl:value-of select="name()"/></td> <td align="left"><xsl:value-of select="."/></td> </tr> </xsl:for-each> </table> </xsl:if> <xsl:if test="bottomLinks"> <xsl:choose> <xsl:when test="operationResult"> </xsl:when> <xsl:otherwise> <p align="center"> <xsl:for-each select="bottomLinks/aLink"> [<a><xsl:attribute name="href"><xsl:value-of select="./@href"/></xsl:attribute><xsl:value-of select="."/></a>] </xsl:for-each> </p> </xsl:otherwise> </xsl:choose> </xsl:if> <xsl:choose> <xsl:when test="operationResult/nextStep"> </xsl:when> <xsl:otherwise> <xsl:if test="returnHome"> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:if> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>
<?xml version="1.0"?> <!-- | $Author: olediour@us $ | $Date: 04-May-2000 | xsl for html (Palm Pilot, HandWeb browser) | $Revision: 1.1 $ +--> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output media-type="text/html" method="html" encoding="ISO-8859-1"/> <xsl:template match="/"> <html> <head> <title>Retail Application</title> </head> <body> <xsl:if test="//pageTitle"> <h2><xsl:value-of select="//pageTitle"/></h2> </xsl:if> <xsl:choose> <xsl:when test="loginResult"> <xsl:apply-templates select="loginResult"/> </xsl:when> <xsl:when test="index"> <xsl:apply-templates select="index"/> </xsl:when> <xsl:when test="inventory"> <xsl:apply-templates select="inventory"/> </xsl:when> <xsl:when test="order"> <xsl:apply-templates select="order"/> </xsl:when> <xsl:when test="placeOrder"> <xsl:apply-templates select="placeOrder"/> </xsl:when> <xsl:otherwise> <p align="center"> <h3>This kind of XML Document cannot be processed...</h3> </p> </xsl:otherwise> </xsl:choose> </body> </html> </xsl:template> <xsl:template match="loginResult"> <xsl:if test="ROWSET/ROW/unknown"> <table width="98%"> <tr><td bgcolor="yellow" align="center"><xsl:value-of select="ROWSET/ROW/unknown"/> is not allowed to log in !</td></tr> </table> </xsl:if> <xsl:if test="ROWSET/ROW/NAME"> <p align="center"> <h2>Welcome <xsl:value-of select="ROWSET/ROW/NAME"/> !</h2> </p> <p align="center"> <a> <xsl:attribute name="href"><xsl:value-of select="nextStep"/>?custId=<xsl:value-of select="ROWSET/ROW/ID"/></xsl:attribute> Please enter the Mall ! </a> </p> </xsl:if> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="index"> <xsl:for-each select="form"> <center> <form> <xsl:attribute name="action"><xsl:value-of select="./@action"/></xsl:attribute> <xsl:attribute name="method"><xsl:value-of select="./@method"/></xsl:attribute> <xsl:if test="./field"> <table width="98%" border="1"> <xsl:for-each select="./field"> <tr> <td align="right"><xsl:value-of select="./@prompt"/></td> <td> <input> <xsl:choose> <xsl:when test="./@type = 'text'"> <xsl:attribute name="type">text</xsl:attribute> </xsl:when> </xsl:choose> <xsl:attribute name="name"><xsl:value-of select="./@name"/></xsl:attribute> </input> </td> </tr> </xsl:for-each> </table> </xsl:if> <xsl:if test="./button"> <p> <xsl:for-each select="./button"> <input> <xsl:choose> <xsl:when test="./@type = 'submit'"> <xsl:attribute name="type">submit</xsl:attribute> </xsl:when> </xsl:choose> <xsl:attribute name="value"><xsl:value-of select="./@label"/></xsl:attribute> </input> </xsl:for-each> </p> </xsl:if> </form> </center> </xsl:for-each> </xsl:template> <xsl:template match="inventory"> <h2>This is the Mart content</h2> <xsl:for-each select="form/theMart/ROWSET/ROW"> <xsl:value-of select="ID"/> <xsl:text> </xsl:text> <form method="post"> <xsl:attribute name="action"> <xsl:value-of select="../../../../form/@action"/> </xsl:attribute> <input type="hidden" name="custId"> <xsl:attribute name="value"><xsl:value-of select="../../../../form/hiddenFields/custId"/></xsl:attribute> </input> <input type="hidden" name="prodId"> <xsl:attribute name="value"><xsl:value-of select="ID"/></xsl:attribute> </input> <input type="submit"> <xsl:attribute name="value"><xsl:value-of select="DESCRIPTION"/></xsl:attribute> </input> </form> <xsl:text> @ $</xsl:text><xsl:value-of select="PRICE"/><xsl:text> each</xsl:text> <xsl:text> Supplied by </xsl:text><xsl:value-of select="NAME"/> <br/> </xsl:for-each> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="order"> <center> <h2>Thank you <xsl:value-of select="CUST/NAME"/> for shopping with us !</h2> <hr/> <h2>Please enter the quantity</h2> <form action="placeOrder.xsql" method="post"> <input type="hidden" name="prodId"> <xsl:attribute name="value"><xsl:value-of select="PROD/ID"/></xsl:attribute> </input> <input type="hidden" name="custId"> <xsl:attribute name="value"><xsl:value-of select="CUST/ID"/></xsl:attribute> </input> <p> <xsl:value-of select="PROD/DESCRIPTION"/> at $<xsl:value-of select="PROD/PRICE"/> each supplied by <xsl:value-of select="PROD/NAME"/> <br/> Quantity : <br/> <input type="text" name="qty"/> </p> <p><input type="submit" value="Place Order"/></p> </form> </center> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:template> <xsl:template match="placeOrder"> <xsl:if test="operationResult"> <center> <xsl:value-of select="operationResult/text()"/> <br/> <xsl:for-each select="operationResult/nextStep"> <form method="post"> <xsl:attribute name="action"><xsl:value-of select="./@Action"/></xsl:attribute> <xsl:if test="prmList"> <xsl:for-each select="prmList/prm"> <input type="hidden"> <xsl:attribute name="name"><xsl:value-of select="./@name"/></xsl:attribute> <xsl:attribute name="value"><xsl:value-of select="./@value"/></xsl:attribute> </input> </xsl:for-each> </xsl:if> <input type="submit"> <xsl:attribute name="value"><xsl:value-of select="./@Label"/></xsl:attribute> </input> </form> </xsl:for-each> </center> </xsl:if> <xsl:if test="operationProblem"> <table width="98%"> <tr><td align="center"><font color="red"><xsl:value-of select="operationProblem"/></font></td></tr> </table> </xsl:if> <xsl:if test="bottomLinks"> <xsl:choose> <xsl:when test="operationResult"> </xsl:when> <xsl:otherwise> <p align="center"> <xsl:for-each select="bottomLinks/aLink"> [<a><xsl:attribute name="href"><xsl:value-of select="./@href"/></xsl:attribute><xsl:value-of select="."/></a>] </xsl:for-each> </p> </xsl:otherwise> </xsl:choose> </xsl:if> <xsl:choose> <xsl:when test="operationResult/nextStep"> </xsl:when> <xsl:otherwise> <xsl:if test="returnHome"> <p> <a><xsl:attribute name="href"><xsl:value-of select="returnHome"/></xsl:attribute>Back to Login</a> </p> </xsl:if> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>
This script creates and manages the GUI and stylesheets used in the B2B XML application.
package B2BDemo.StyleSheetUtil; /** * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.sql.*; import java.util.*; // needed for new CLOB and BLOB classes import oracle.sql.*; import oracle.jdbc.driver.*; import java.beans.*; import javax.swing.event.*; import B2BDemo.*; import B2BDemo.XMLUtil.*; public class GUInterface extends JFrame { private boolean lite = false; // Use O8iLite private boolean inserting = false; private final static int UPDATE = 1; private final static int INSERT = 2; private final static int ENTER_QUERY = 1; private final static int EXEC_QUERY = 2; int queryState = ENTER_QUERY; String sqlStmt = "Select APPFROM, " + " APPTO, " + " OP, " + " XSL " + "From styleSheets"; private static String connURL = AppCste.AQDBUrl; private static String userName = AppCste.AQuser; private static String password = AppCste.AQpswd; private Connection conn = null; private Vector recVect = null; int currRec = 0; XslRecord thisRecord = null; BorderLayout borderLayout1 = new BorderLayout(); JPanel jPanel1 = new JPanel(); JMenuBar menuBar1 = new JMenuBar(); JMenu menuFile = new JMenu(); JMenuItem menuFileExit = new JMenuItem(); JMenu menuHelp = new JMenu(); JMenuItem menuHelpAbout = new JMenuItem(); JLabel statusBar = new JLabel(); JToolBar toolBar = new JToolBar(); JButton buttonOpen = new JButton(); JButton buttonClose = new JButton(); JButton buttonHelp = new JButton(); ImageIcon imageOpen; ImageIcon imageClose; ImageIcon imageHelp; JPanel jPanel2 = new JPanel(); BorderLayout borderLayout2 = new BorderLayout(); JButton firstButton = new JButton(); JPanel jPanel3 = new JPanel(); JPanel jPanel4 = new JPanel(); BorderLayout borderLayout3 = new BorderLayout(); BorderLayout borderLayout4 = new BorderLayout(); JPanel jPanel5 = new JPanel(); JTextField fromAppValue = new JTextField(); JLabel fromApp = new JLabel(); JPanel jPanel6 = new JPanel(); BorderLayout borderLayout5 = new BorderLayout(); JLabel jLabel2 = new JLabel(); JScrollPane jScrollPane1 = new JScrollPane(); JTextArea XSLStyleSheet = new JTextArea(); JButton previousButton = new JButton(); JButton nextButton = new JButton(); JButton lastButton = new JButton(); JButton validateButton = new JButton(); GridLayout gridLayout1 = new GridLayout(); JLabel toApp = new JLabel(); JTextField toAppValue = new JTextField(); JLabel operationLabel = new JLabel(); JTextField opValue = new JTextField(); JButton newButton = new JButton(); JButton deleteButton = new JButton(); JButton queryButton = new JButton(); public GUInterface() { super(); try { jbInit(); buttonOpen.setIcon(imageOpen); buttonClose.setIcon(imageClose); buttonHelp.setIcon(imageHelp); } catch (Exception e) { e.printStackTrace(); } } private void getConnected() throws Exception { try { if (lite) { Class.forName("oracle.lite.poljdbc.POLJDBCDriver"); conn = DriverManager.getConnection("jdbc:Polite:POLite", "system", "manager"); } else { Class.forName ("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection (connURL, userName, password); } } catch (Exception e) { System.err.println("Get connected failed : " + e); throw e; } } private void jbInit() throws Exception { if (conn == null) { try { getConnected(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e.toString(), "Connection", JOptionPane.ERROR_MESSAGE); System.exit(1); } } imageOpen = new ImageIcon(GUInterface.class.getResource("openfile.gif")); imageClose = new ImageIcon(GUInterface.class.getResource("closefile.gif")); imageHelp = new ImageIcon(GUInterface.class.getResource("help.gif")); this.setTitle("Style Sheets Management"); this.getContentPane().setLayout(borderLayout1); this.setSize(new Dimension(511, 526)); jPanel1.setLayout(borderLayout2); menuFile.setText("File"); menuFileExit.setText("Exit"); menuFileExit.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { fileExit_ActionPerformed(e); } }); menuHelp.setText("Help"); menuHelpAbout.setText("About"); menuHelpAbout.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { helpAbout_ActionPerformed(e); } }); statusBar.setText("Initializing..."); buttonOpen.setToolTipText("Open File"); buttonClose.setToolTipText("Validate modifications"); buttonHelp.setToolTipText("About Style Sheet Manager"); firstButton.setText("<<"); jPanel5.setLayout(gridLayout1); fromApp.setText("From Application :"); fromApp.setHorizontalAlignment(SwingConstants.RIGHT); jLabel2.setText("XSL Style Sheet"); previousButton.setText("<"); nextButton.setText(">"); lastButton.setText(">>"); validateButton.setText("Validate"); gridLayout1.setRows(4); toApp.setText("To Application : "); toApp.setHorizontalAlignment(SwingConstants.RIGHT); operationLabel.setText("Operation : "); operationLabel.setHorizontalAlignment(SwingConstants.RIGHT); jPanel6.setLayout(borderLayout5); jPanel4.setLayout(borderLayout4); jPanel3.setLayout(borderLayout3); menuFile.add(menuFileExit); menuBar1.add(menuFile); menuHelp.add(menuHelpAbout); menuBar1.add(menuHelp); this.setJMenuBar(menuBar1); this.getContentPane().add(statusBar, BorderLayout.SOUTH); toolBar.add(buttonOpen); toolBar.add(buttonClose); toolBar.add(buttonHelp); this.getContentPane().add(toolBar, BorderLayout.NORTH); this.getContentPane().add(jPanel1, BorderLayout.CENTER); jPanel1.add(jPanel2, BorderLayout.SOUTH); jPanel2.add(queryButton, null); jPanel2.add(newButton, null); jPanel2.add(firstButton, null); jPanel2.add(previousButton, null); jPanel2.add(nextButton, null); jPanel2.add(lastButton, null); jPanel2.add(validateButton, null); jPanel2.add(deleteButton, null); jPanel1.add(jPanel3, BorderLayout.CENTER); jPanel3.add(jPanel4, BorderLayout.NORTH); jPanel3.add(jPanel5, BorderLayout.SOUTH); jPanel5.add(fromApp, null); jPanel5.add(fromAppValue, null); jPanel5.add(toApp, null); jPanel5.add(toAppValue, null); jPanel5.add(operationLabel, null); jPanel5.add(opValue, null); jPanel3.add(jPanel6, BorderLayout.CENTER); jPanel6.add(jLabel2, BorderLayout.NORTH); jPanel6.add(jScrollPane1, BorderLayout.CENTER); jScrollPane1.getViewport().add(XSLStyleSheet, null); // statusBar.setText("Connected..."); // Building Vector of record. queryButton.setText("Enter Query"); queryButton.setActionCommand("query"); queryButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { queryButton_actionPerformed(e); } }); buttonClose.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { buttonClose_actionPerformed(e); } }); deleteButton.setText("Delete"); deleteButton.setToolTipText("Delete the current record"); deleteButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { deleteButton_actionPerformed(e); } }); newButton.setText("New"); newButton.setToolTipText("Create a new record"); newButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { newButton_actionPerformed(e); } }); validateButton.setToolTipText("Validate your modifications"); opValue.setEditable(false); toAppValue.setEditable(false); fromAppValue.setEditable(false); validateButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { validateButton_actionPerformed(e); } }); lastButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { lastButton_actionPerformed(e); } }); firstButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { firstButton_actionPerformed(e); } }); previousButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { previousButton_actionPerformed(e); } }); nextButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { nextButton_actionPerformed(e); } }); lastButton.setActionCommand("last"); lastButton.setToolTipText("Last record"); nextButton.setActionCommand("next"); nextButton.setToolTipText("Next record"); previousButton.setActionCommand("previous"); previousButton.setToolTipText("Previous record"); firstButton.setActionCommand("first"); firstButton.setToolTipText("First record"); // Execute query and build vector executeQuery(sqlStmt); updateStatusBar(); } void executeQuery(String theSqlStmt) { recVect = new Vector(); try { Statement stmt = conn.createStatement(); ResultSet rSet = stmt.executeQuery(theSqlStmt); CLOB clob = null; while (rSet.next()) { clob = ((OracleResultSet)rSet).getCLOB(4); String strLob = dumpClob(conn, clob); XslRecord xslRecord = new XslRecord(rSet.getString(1), rSet.getString(2), rSet.getString(3), strLob); recVect.addElement(xslRecord); } rSet.close(); stmt.close(); // Populate form with first record firstButton.setEnabled(false); previousButton.setEnabled(false); nextButton.setEnabled(false); lastButton.setEnabled(false); if (recVect.size() > 0) { currRec = 1; displayRecord(currRec); } if (recVect.size() > 1) { nextButton.setEnabled(true); lastButton.setEnabled(true); } } catch (Exception e) { JOptionPane.showMessageDialog(null, e.toString(), "Executing request", JOptionPane.ERROR_MESSAGE); System.exit(1); } } void displayRecord(int rnk) { XslRecord xslRecord = (XslRecord)recVect.elementAt(rnk-1); thisRecord = new XslRecord(xslRecord.FROM, xslRecord.TO, xslRecord.TASK, xslRecord.XSL); XSLStyleSheet.setText(xslRecord.XSL); fromAppValue.setText(xslRecord.FROM); toAppValue.setText(xslRecord.TO); opValue.setText(xslRecord.TASK); XSLStyleSheet.requestFocus(); XSLStyleSheet.setCaretPosition(0); // Buttons firstButton.setEnabled(false); previousButton.setEnabled(false); nextButton.setEnabled(false); lastButton.setEnabled(false); if (rnk > 1) { firstButton.setEnabled(true); previousButton.setEnabled(true); } if (rnk < recVect.size()) { nextButton.setEnabled(true); lastButton.setEnabled(true); } } void updateStatusBar() { statusBar.setText("Ready for " + recVect.size() + " records"); } private static String dumpClob(Connection conn, CLOB clob) throws Exception { String returnString = ""; OracleCallableStatement cStmt1 = (OracleCallableStatement) conn.prepareCall ("begin ? := dbms_lob.getLength (?); end;"); OracleCallableStatement cStmt2 = (OracleCallableStatement) conn.prepareCall ("begin dbms_lob.read (?, ?, ?, ?); end;"); cStmt1.registerOutParameter (1, Types.NUMERIC); cStmt1.setCLOB (2, clob); cStmt1.execute (); long length = cStmt1.getLong (1); long i = 0; int chunk = 80; while (i < length) { cStmt2.setCLOB (1, clob); cStmt2.setLong (2, chunk); cStmt2.registerOutParameter (2, Types.NUMERIC); cStmt2.setLong (3, i + 1); cStmt2.registerOutParameter (4, Types.VARCHAR); cStmt2.execute (); long read_this_time = cStmt2.getLong (2); returnString += cStmt2.getString (4); // System.out.print ("Read " + read_this_time + " chars: "); // System.out.println (string_this_time); i += read_this_time; } cStmt1.close (); cStmt2.close (); return returnString; } static void fillClob (Connection conn, CLOB clob, String str) throws SQLException { OracleCallableStatement cStmt = (OracleCallableStatement) conn.prepareCall ("begin dbms_lob.write (?, ?, ?, ?); end;"); int i = 0; int chunk = 80; int length = str.length(); long c, ii; System.out.println("Length: " + length + "\n" + str); while (i < length) { cStmt.setClob (1, clob); c = chunk; cStmt.setLong (2, c); ii = i + 1; cStmt.setLong (3, ii); cStmt.setString (4, str.substring(i, i + chunk)); cStmt.execute (); i += chunk; if (length - i < chunk) chunk = length - i; } cStmt.close (); } void fileExit_ActionPerformed(ActionEvent e) { if (conn != null) { try { conn.close(); } catch (Exception ex) {} } System.exit(0); } void helpAbout_ActionPerformed(ActionEvent e) { JOptionPane.showMessageDialog(this, new GUInterface_AboutBoxPanel1(), "About", JOptionPane.PLAIN_MESSAGE); } void nextButton_actionPerformed(ActionEvent e) { checkRecordChange(); currRec++; displayRecord(currRec); } void previousButton_actionPerformed(ActionEvent e) { checkRecordChange(); currRec--; displayRecord(currRec); } void firstButton_actionPerformed(ActionEvent e) { checkRecordChange(); currRec = 1; displayRecord(currRec); } void lastButton_actionPerformed(ActionEvent e) { checkRecordChange(); currRec = recVect.size(); displayRecord(currRec); } void validateButton_actionPerformed(ActionEvent e) { validateRec(); } void validateRec() { thisRecord = new XslRecord(fromAppValue.getText(), toAppValue.getText(), opValue.getText(), XSLStyleSheet.getText()); if (saveChanges(thisRecord, (inserting?INSERT:UPDATE))) JOptionPane.showMessageDialog(null, "All right!"); } void deleteRec() { thisRecord = new XslRecord(fromAppValue.getText(), toAppValue.getText(), opValue.getText(), XSLStyleSheet.getText()); String sqlStmt = "delete styleSheets where fromApp = ? and " + " toApp = ? and " + " op = ?"; try { PreparedStatement pStmt = conn.prepareStatement(sqlStmt); pStmt.setString(1, thisRecord.FROM); pStmt.setString(2, thisRecord.TO); pStmt.setString(3, thisRecord.TASK); pStmt.execute(); conn.commit(); System.out.println("Deleted !"); pStmt.close(); // Delete from vector... recVect.removeElementAt(currRec - 1); updateStatusBar(); if (currRec >= recVect.size()) currRec--; displayRecord(currRec); JOptionPane.showMessageDialog(null, "All right!"); } catch (SQLException sqlE) { JOptionPane.showMessageDialog(null, sqlE.toString(), "Deleting record", JOptionPane.ERROR_MESSAGE); } catch (Exception e) { JOptionPane.showMessageDialog(null, e.toString(), "Deleting record", JOptionPane.ERROR_MESSAGE); } } void checkRecordChange() { thisRecord = new XslRecord(fromAppValue.getText(), toAppValue.getText(), opValue.getText(), XSLStyleSheet.getText()); if (!thisRecord.equals((XslRecord)recVect.elementAt(currRec-1))) { int result = JOptionPane.showConfirmDialog(null, "Record has changed\nDo you want to save the modifications ?"); if (result == JOptionPane.YES_OPTION) { saveChanges(thisRecord, UPDATE); JOptionPane.showMessageDialog(null, "All right!"); } } } boolean saveChanges(XslRecord rec, int operation) { boolean ret = true; if (operation == this.UPDATE) { String theSqlStmt = "update styleSheets set xsl = ? where appFrom = ? and appTo = ? and op = ?"; try { PreparedStatement pStmt = conn.prepareStatement(theSqlStmt); pStmt.setString(1, rec.XSL); pStmt.setString(2, rec.FROM); pStmt.setString(3, rec.TO); pStmt.setString(4, rec.TASK); pStmt.execute(); conn.commit(); System.out.println("Updated !"); pStmt.close(); // Reinsert in vector... recVect.setElementAt(rec, currRec - 1); } catch (SQLException sqlE) { JOptionPane.showMessageDialog(null, sqlE.toString(), "Saving record", JOptionPane.ERROR_MESSAGE); ret = false; } } else { System.out.println("Inserting new record"); String sqlStmt = "insert into styleSheets " + " ( appFrom, " + " appTo, " + " op, " + " xsl " + " ) values " + " (?, ?, ?, ?)"; String sqlGetLob = "select xsl from styleSheets " + "where appFrom = ? and " + " appTo = ? and " + " op = ?"; try { PreparedStatement pStmt = conn.prepareStatement(sqlStmt); pStmt.setString(1, rec.FROM); pStmt.setString(2, rec.TO); pStmt.setString(3, rec.TASK); pStmt.setString(4, ""); // Null in the LOB, will be filled later pStmt.execute(); System.out.println("Inserted !"); pStmt.close(); PreparedStatement fillLOBStmt = conn.prepareStatement(sqlGetLob); fillLOBStmt.setString(1, rec.FROM); fillLOBStmt.setString(2, rec.TO); fillLOBStmt.setString(3, rec.TASK); ResultSet lobRSet = fillLOBStmt.executeQuery(); while (lobRSet.next()) { CLOB clob = ((OracleResultSet)lobRSet).getCLOB(1); fillClob(conn, clob, rec.XSL); } conn.commit(); // Add in vector... recVect.addElement(rec); currRec = recVect.size(); displayRecord(currRec); } catch (SQLException sqlE) { JOptionPane.showMessageDialog(null, sqlE.toString(), "Inserting record", JOptionPane.ERROR_MESSAGE); ret = false; } inserting = false; fromAppValue.setEditable(false); toAppValue.setEditable(false); opValue.setEditable(false); } updateStatusBar(); return ret; } void buttonClose_actionPerformed(ActionEvent e) { validateRec(); } void newButton_actionPerformed(ActionEvent e) { fromAppValue.setEditable(true); toAppValue.setEditable(true); opValue.setEditable(true); inserting = true; XSLStyleSheet.setText(""); fromAppValue.setText(""); toAppValue.setText(""); opValue.setText(""); } void deleteButton_actionPerformed(ActionEvent e) { deleteRec(); } void queryButton_actionPerformed(ActionEvent e) { if (queryState == ENTER_QUERY) { queryState = EXEC_QUERY; queryButton.setText("Execute Query"); fromAppValue.setEditable(true); toAppValue.setEditable(true); opValue.setEditable(true); XSLStyleSheet.setEditable(false); statusBar.setText("Entering query"); XSLStyleSheet.setText(""); fromAppValue.setText(""); toAppValue.setText(""); opValue.setText(""); newButton.setEnabled(false); firstButton.setEnabled(false); previousButton.setEnabled(false); nextButton.setEnabled(false); lastButton.setEnabled(false); validateButton.setEnabled(false); deleteButton.setEnabled(false); } else { queryState = ENTER_QUERY; queryButton.setText("Enter Query"); statusBar.setText("Executing query"); fromAppValue.setEditable(false); toAppValue.setEditable(false); opValue.setEditable(false); XSLStyleSheet.setEditable(true); newButton.setEnabled(true); firstButton.setEnabled(true); previousButton.setEnabled(true); nextButton.setEnabled(true); lastButton.setEnabled(true); validateButton.setEnabled(true); deleteButton.setEnabled(true); // Execute query String stmt = sqlStmt; boolean firstCondition = true; if (fromAppValue.getText().length() > 0) { stmt += ((firstCondition?" where ":" and ") + "fromApp like '" + fromAppValue.getText() + "' "); firstCondition = false; } if (toAppValue.getText().length() > 0) { stmt += ((firstCondition?" where ":" and ") + "toApp like '" + toAppValue.getText() + "' "); firstCondition = false; } if (opValue.getText().length() > 0) { stmt += ((firstCondition?" where ":" and ") + "op like '" + opValue.getText() + "' "); firstCondition = false; } executeQuery(stmt); updateStatusBar(); displayRecord(currRec); } } }
package B2BDemo.StyleSheetUtil; /** * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.awt.*; import javax.swing.*; import javax.swing.border.*; import oracle.jdeveloper.layout.*; public class GUInterface_AboutBoxPanel1 extends JPanel { JLabel jLabel1 = new JLabel(); JLabel jLabel2 = new JLabel(); JLabel jLabel3 = new JLabel(); JLabel jLabel4 = new JLabel(); GridBagLayout gridBagLayout1 = new GridBagLayout(); Border border1 = new EtchedBorder(); public GUInterface_AboutBoxPanel1() { try { jbInit(); } catch (Exception e) { e.printStackTrace(); } } private void jbInit() throws Exception { jLabel1.setText("Stored Style Sheets management."); jLabel2.setText("Olivier LE DIOURIS"); jLabel3.setText("Copyright (c) 1999"); jLabel4.setText("Oracle Corp."); this.setLayout(gridBagLayout1); this.setBorder(border1); this.add(jLabel1, new GridBagConstraints2(0, 0, 1, 1, 0.0, 0.0, GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5,5,0,5),0,0)); this.add(jLabel2, new GridBagConstraints2(0, 1, 1, 1, 0.0, 0.0, GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(0,5,0,5),0,0)); this.add(jLabel3, new GridBagConstraints2(0, 2, 1, 1, 0.0, 0.0, GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(0,5,0,5),0,0)); this.add(jLabel4, new GridBagConstraints2(0, 3, 1, 1, 0.0, 0.0, GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(0,5,5,5),0,0)); } }
package B2BDemo.StyleSheetUtil; /** * A grapical utility to manipulate the stylesheets stored in the database, * in the AQ Schema. The stylsheets will be used to transform the incoming * document into the outgoing one. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.awt.*; import java.awt.event.*; import javax.swing.*; //import oracle.bali.ewt.border.UIBorderFactory; //import oracle.bali.ewt.olaf.OracleLookAndFeel; public class GUIStylesheet { private static final boolean useBali = false; public GUIStylesheet() { Frame frame = new GUInterface(); //Center the window Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); Dimension frameSize = frame.getSize(); if (frameSize.height > screenSize.height) { frameSize.height = screenSize.height; } if (frameSize.width > screenSize.width) { frameSize.width = screenSize.width; } frame.setLocation((screenSize.width - frameSize.width)/2, (screenSize.height - frameSize.height)/2); frame.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); frame.setVisible(true); } public static void main(String[] args) { new GUIStylesheet(); } }
The XML process and management scripts used in the B2B XML application are as follows:
package B2BDemo.XMLUtil; /** * A main for tests * The XMLtoDMLv2 utility takes an XML document that can contain * data to be inserted in several tables. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.io.*; import java.net.*; public class Main4XMLtoDMLv2 extends Object { // Manage user input... private static BufferedReader _stdin = new BufferedReader(new InputStreamReader(System.in)); private static String _buf = ""; private static String _userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = _stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } // for tests public static void main(String args[]) { XMLtoDMLv2 x2d = new XMLtoDMLv2("scott", "tiger", "jdbc:oracle:thin:@olediour-lap.us.oracle.com:1521:Ora8i"); String xmldocname = ""; try { xmldocname = userInput("XML file name > "); } catch (Exception e) {} String xmldoc = readURL(createURL(xmldocname)); TableInDocument d[] = new TableInDocument[2]; d[0] = new TableInDocument("ROWSET", "ROW", "DEPT"); d[1] = new TableInDocument("EMP", "EMP_ROW", "EMP"); try { x2d.insertFromXML(d, xmldoc); System.out.println(xmldocname + " processed."); } catch (Exception e) { System.err.println("Ooops:\n" + e); } try { _buf = _userInput("End of task..."); } catch (Exception ioe) {} } public static URL createURL(String fileName) { URL url = null; try { url = new URL(fileName); } catch (MalformedURLException ex) { File f = new File(fileName); try { String path = f.getAbsolutePath(); // This is a bunch of weird code that is required to // make a valid URL on the Windows platform, due // to inconsistencies in what getAbsolutePath returns. String fs = System.getProperty("file.separator"); if (fs.length() == 1) { char sep = fs.charAt(0); if (sep != '/') path = path.replace(sep, '/'); if (path.charAt(0) != '/') path = '/' + path; } path = "file://" + path; url = new URL(path); } catch (MalformedURLException e) { System.err.println("Cannot create url for: " + fileName); System.exit(0); } } return url; } public static String readURL(URL url) { URLConnection newURLConn; BufferedInputStream newBuff; int nBytes; byte aByte[]; String resultBuff = ""; aByte = new byte[2]; try { // System.out.println("Calling " + url.toString()); try { newURLConn = url.openConnection(); newBuff = new BufferedInputStream(newURLConn.getInputStream()); resultBuff = ""; while (( nBytes = newBuff.read(aByte, 0, 1)) != -1) resultBuff = resultBuff + (char)aByte[0]; } catch (IOException e) { System.err.println(url.toString() + "\n : newURLConn failed :\n" + e); } } catch (Exception e) {} return resultBuff; } private static String userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = _stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } }
package B2BDemo.XMLUtil; import org.xml.sax.*; import java.io.*; import java.util.*; import java.net.*; import java.sql.*; import oracle.xml.sql.query.*; import oracle.xml.sql.dml.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; /** * Just a main for tests. * Show how to retrieve the ID and CUSTOMER_ID fro an XML document * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class ParserTest extends Object { static DOMParser parser = new DOMParser(); static String XMLDoc = "<ROWSET>" + " <ROW NUM=\"1\">" + " <ID>23321</ID>" + " <ORDERDATE>2000-05-03 00:00:00.0</ORDERDATE>" + " <CONTACTNAME>JDevBC4J</CONTACTNAME>" + " <TRACKINGNO>AX23321</TRACKINGNO>" + " <STATUS>Pending</STATUS>" + " <ITEMS>" + " <ITEM_ROW NUM=\"1\">" + " <ID>1242</ID>" + " <QUANTITY>2</QUANTITY>" + " <ITEM_ID>403</ITEM_ID>" + " <ORD_ID>23321</ORD_ID>" + " <DISCOUNT>0</DISCOUNT>" + " </ITEM_ROW>" + " </ITEMS>" + " </ROW>" + "</ROWSET>"; /** * Constructor */ public ParserTest() { } public static void main(String[] args) { parser.setValidationMode(false); try { parser.parse(new InputSource(new ByteArrayInputStream(XMLDoc.getBytes()))); XMLDocument xml = parser.getDocument(); XMLElement elmt = (XMLElement)xml.getDocumentElement(); NodeList nl = elmt.getElementsByTagName("ID"); // ORD ID for (int i=0; i<nl.getLength(); i++) { XMLElement ordId = (XMLElement)nl.item(i); XMLNode theText = (XMLNode)ordId.getFirstChild(); String ordIdValue = theText.getNodeValue(); System.out.println(ordIdValue); break; } nl = elmt.getElementsByTagName("CUSTOMER_ID"); // CUSTOMER ID for (int i=0; i<nl.getLength(); i++) { XMLElement ordId = (XMLElement)nl.item(i); XMLNode theText = (XMLNode)ordId.getFirstChild(); String custIdValue = theText.getNodeValue(); System.out.println(custIdValue); } } catch (SAXParseException e) { System.out.println(e.getMessage()); } catch (SAXException e) { System.out.println(e.getMessage()); } catch (Exception e) { System.out.println(e.getMessage()); } } }
package B2BDemo.XMLUtil; /** * This class is used by the XMLtoDMLv2.java class * It describes the matching between an XML document and a SQL table. * Created to managed multi-level XML documents (Master-Details) * * @see XMLtoDMLv2 * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class TableInDocument extends Object { public String rowSet = "ROWSET"; public String row = "ROW"; public String table = ""; public TableInDocument (String rset, String r, String t) { this.rowSet = rset; this.row = r; this.table = t; } }
// Copyright (c) 2000 Oracle Corporation package B2BDemo.XMLUtil; import javax.swing.*; import java.awt.*; import oracle.xml.srcviewer.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; /** * A Swing-based top level window class. * Implements the Code View of the Transviewer Bean. * Used in the demo to enhance the XML code propagated from one * component to another. * * @author Olivier LE DIOURIS */ public class XMLFrame extends JFrame { BorderLayout borderLayout1 = new BorderLayout(); JPanel jPanel1 = new JPanel(); BorderLayout borderLayout2 = new BorderLayout(); XMLSourceView xmlSourceViewPanel = new XMLSourceView(); private String frameTitle = ""; private XSLTWrapper xsltw = new XSLTWrapper(); /** * Constructs a new instance. */ public XMLFrame(String fTitle) { super(); this.frameTitle = fTitle; try { jbInit(); } catch (Exception e) { e.printStackTrace(); } } /** * Initializes the state of this instance. */ private void jbInit() throws Exception { this.getContentPane().setLayout(borderLayout1); this.setSize(new Dimension(400, 300)); jPanel1.setLayout(borderLayout2); this.setTitle(this.frameTitle); this.getContentPane().add(jPanel1, BorderLayout.CENTER); jPanel1.add(xmlSourceViewPanel, BorderLayout.CENTER); } public void setXMLDocument(String xmlContent) throws Exception { xmlSourceViewPanel.setXMLDocument(xsltw.parseDocument(xmlContent)); } }
package B2BDemo.XMLUtil; /** * A Wrapper around the XML SQL Utility * Could be called from any java object * to produce an XML document after a SQL query, * not only from a servlet. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ /** */ import java.sql.*; import oracle.xml.sql.query.*; public class XMLProducer { Connection conn = null; String rowset = null; String row = null; public XMLProducer(Connection conn) { this.conn = conn; } public String getXMLString(ResultSet rSet) { return getXMLString(rSet, "N"); } public String getXMLString(ResultSet rSet, String DTD) { String finalDoc = ""; try { boolean dtdRequired = false; if (DTD != null && DTD.length() > 0 && DTD.toUpperCase().equals("Y")) dtdRequired = true; // The Skill ! /////////////////////////////////////////////// OracleXMLQuery oXmlq = new OracleXMLQuery(conn, rSet); // // oXmlq.useUpperCaseTagNames(); // if (this.rowset != null) oXmlq.setRowsetTag(this.rowset); if (this.row != null) oXmlq.setRowTag(this.row); finalDoc = oXmlq.getXMLString(dtdRequired); // // That's it ! /////////////////////////////////////////////// } catch (Exception e) { System.err.println(e); } return finalDoc; } public void setRowset(String rSet) { this.rowset = rSet; } public void setRow(String row) { this.row = row; } }
package B2BDemo.XMLUtil; /** * This class takes an XML document as input to execute * an insert into the database. * Multi level XML documents are supported, but not if * one element has several sons as * <elem1> * <elem11/> * <elem12/> * </elem1> * * @see TableInDocument * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import org.xml.sax.*; import java.io.*; import java.util.*; import java.net.*; import java.sql.*; import oracle.xml.sql.query.*; import oracle.xml.sql.dml.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; public class XMLtoDMLv2 extends Object { static DOMParser parser = new DOMParser(); Connection conn = null; String username = ""; String password = ""; String connURL = ""; public XMLtoDMLv2(String username, String password, String connURL) { this.username = username; this.password = password; this.connURL = connURL; } public void insertFromXML(TableInDocument tInDoc[], String document) throws Exception { if (conn == null) getConnected(); String xmlString = ""; try { xmlString = readURL(createURL(document)); } catch (Exception e) { xmlString = document; } // System.out.println("xml2Insert = \n" + xmlString); // The returned String is turned into an XML Document XMLDocument xmlDoc = parseDocument(xmlString); // And we take a reference on the root of this document XMLElement e = (XMLElement) xmlDoc.getDocumentElement(); // Let's walk thru the ROW nodes NodeList nl = e.getChildrenByTagName(tInDoc[0].row); // "ROW" // System.out.println("This document has " + nl.getLength() + " ROW(s)"); Vector sqlStmt = new Vector(); scanLevel(0, tInDoc, nl, sqlStmt); // Now execute all the statements in the Vector, in reverse order (FK...) int i = sqlStmt.size(); Enumeration enum = sqlStmt.elements(); while (i > 0) { i--; String s = (String)sqlStmt.elementAt(i); // System.out.println("Executing " + s); executeStatement(s); } } // This one is recursive private int scanLevel(int level, TableInDocument tInDoc[], NodeList nl, Vector sqlStmt) throws Exception { int nbRowProcessed = 0; Vector columnNames = new Vector(); Vector columnValues = null; String[] colTypes = null; String columns = "", values = ""; // Loop in tree... boolean firstLoop = true; for (int i=0; i<nl.getLength(); i++) // Loop on all rows of XML doc { columnValues = new Vector(); XMLElement aRow = (XMLElement) nl.item(i); // String numVal = aRow.getAttribute("num"); // System.out.println("NUM = " + numVal); NodeList nlRow = aRow.getChildNodes(); // System.out.println("a Row has " + nlRow.getLength() + " children"); for (int j=0; j<nlRow.getLength(); j++) { XMLElement anXMLElement = (XMLElement)nlRow.item(j); if (anXMLElement.getChildNodes().getLength() == 1 && (level == (tInDoc.length - 1) || (level < (tInDoc.length - 1) && !(anXMLElement.getNodeName().equals(tInDoc[level+1].rowSet)))) ) { // System.out.println("Element " + (j+1) + "=" + anXMLElement.getNodeName()); // System.out.print(anXMLElement.getNodeName()); if (firstLoop) columnNames.addElement(anXMLElement.getNodeName()); // Value XMLNode nodeValue = (XMLNode) anXMLElement.getFirstChild(); // System.out.println("\t" + nodeValue.getNodeValue()); columnValues.addElement(nodeValue.getNodeValue()); } else { // System.out.println(anXMLElement.getNodeName() + " has " + anXMLElement.getChildNodes().getLength() + " children"); // System.out.println("Comparing " + anXMLElement.getNodeName() + " and " + tInDoc[level+1].rowSet); if (level < (tInDoc.length - 1) && anXMLElement.getNodeName().equals(tInDoc[level+1].rowSet)) { // System.out.println("Searching for " + tInDoc[level+1].row); NodeList nl2 = anXMLElement.getChildrenByTagName(tInDoc[level+1].row); // "ROW" if (nl2 == null) System.out.println("Nl2 is null for " + tInDoc[level+1].row); scanLevel(level + 1, tInDoc, nl2, sqlStmt); } } } // System.out.println("INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")"); try { if (firstLoop) { firstLoop = false; String selectStmt = "SELECT "; boolean comma = false; Enumeration cNames = columnNames.elements(); while (cNames.hasMoreElements()) { columns += ((comma?", ":"") + (String)cNames.nextElement()); if (!comma) comma = true; } selectStmt += columns; selectStmt += (" FROM " + tInDoc[level].table + " WHERE 1 = 2"); // No row retrieved Statement stmt = conn.createStatement(); // System.out.println("Executing: " + selectStmt); ResultSet rSet = stmt.executeQuery(selectStmt); ResultSetMetaData rsmd = rSet.getMetaData(); colTypes = new String[rsmd.getColumnCount()]; for (int ci=0; ci<(rsmd.getColumnCount()); ci++) { // System.out.println("Col " + (ci+1) + ":" + rsmd.getColumnName(ci+1) + ", " + rsmd.getColumnTypeName(ci+1)); colTypes[ci] = rsmd.getColumnTypeName(ci+1); } rSet.close(); stmt.close(); } // Build Value Part int vi = 0; Enumeration cVal = columnValues.elements(); boolean comma = false; while (cVal.hasMoreElements()) { if (comma) values += ", "; comma = true; if (colTypes[vi].equals("DATE")) values += ("TO_DATE(SUBSTR("); values += ("'" + cVal.nextElement() + "'"); if (colTypes[vi].equals("DATE")) values += (", 1, 19), 'YYYY-MM-DD HH24:MI:SS')"); vi++; } // Go ! // System.out.println("Stmt:" + "INSERT INTO " + tInDoc[level].table + " (" + columns + ") VALUES (" + values + ")"); sqlStmt.addElement("INSERT INTO " + tInDoc[level].table + " (" + columns + ") VALUES (" + values + ")"); nbRowProcessed++; } catch (Exception execE) { // System.err.println("Executing " + execE); throw execE; } values = ""; } // System.out.println("End of Loop"); return nbRowProcessed; } public static XMLDocument parseDocument(String documentStream) throws Exception { XMLDocument returnXML = null; try { parser.parse(new InputSource(new ByteArrayInputStream(documentStream.getBytes()))); returnXML = parser.getDocument(); } catch (SAXException saxE) { // System.err.println("Parsing XML\n" + "SAX Exception:\n" + saxE.toString()); // System.err.println("For:\n" + documentStream + "\nParse failed SAX : " + saxE); throw saxE; } catch (IOException e) { // System.err.println("Parsing XML\n" + "Exception:\n" + e.toString()); // System.err.println("Parse failed : " + e); throw e; } return returnXML; } // Create a URL from a file name private static URL createURL(String fileName) throws Exception { URL url = null; try { url = new URL(fileName); } catch (MalformedURLException ex) // It is not a valid URL, maybe a file... { File f = new File(fileName); try { String path = f.getAbsolutePath(); // This is a bunch of weird code that is required to // make a valid URL on the Windows platform, due // to inconsistencies in what getAbsolutePath returns. String fs = System.getProperty("file.separator"); if (fs.length() == 1) { char sep = fs.charAt(0); if (sep != '/') path = path.replace(sep, '/'); if (path.charAt(0) != '/') path = '/' + path; } path = "file://" + path; url = new URL(path); } catch (MalformedURLException e) { // System.err.println("Cannot create url for: " + fileName); throw e; // It's not a file either... } } return url; } private static String readURL(URL url) throws Exception { URLConnection newURLConn; BufferedInputStream newBuff; int nBytes; byte aByte[]; String resultBuff = ""; aByte = new byte[2]; try { // System.out.println("Calling " + url.toString()); try { newURLConn = url.openConnection(); newBuff = new BufferedInputStream(newURLConn.getInputStream()); resultBuff = ""; while (( nBytes = newBuff.read(aByte, 0, 1)) != -1) resultBuff = resultBuff + (char)aByte[0]; } catch (IOException e) { // System.err.println("Opening locator\n" + e.toString()); // System.err.println(url.toString() + "\n : newURLConn failed :\n" + e); throw e; } } catch (Exception e) { // System.err.println("Read URL\n" + e.toString()); throw e; } return resultBuff; } private void executeStatement(String strStmt) throws SQLException, Exception { if (conn == null) throw new Exception("Connection is null"); try { Statement stmt = conn.createStatement(); stmt.execute(strStmt); stmt.close(); } catch (SQLException e) { System.err.println("Failed to execute statement\n" + strStmt); throw e; } } private void getConnected() throws Exception { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(connURL, username, password); } catch (Exception e) { // System.err.println(e); throw e; } } public Connection getConnection() { return conn; } }
package B2BDemo.XMLUtil; import java.sql.*; /** * This class is used by the Action Handler called by the XSQL Servlet * in placeOrder.xsql. It generates the original XML Document to be * sent to the broker * * @see B2BMessage * @see XMLProducer * @see RetailActionHandler * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class XMLGen extends Object { static Connection conn = null; // Default connection parameters static String appURL = "jdbc:oracle:thin:@localhost:1521:ORCL"; static String appUser = "retailer"; static String appPassword = "retailer"; static String XMLStmt = "SELECT O.ID as \"Id\"," + " O.ORDERDATE as \"Orderdate\", " + " O.CONTACTNAME as \"Contactname\"," + " O.TRACKINGNO as \"Trackingno\"," + " O.STATUS as \"Status\"," + " O.CUSTOMER_ID as \"CustomerId\"," + " CURSOR (SELECT L.ID as \"Id\"," + " L.QUANTITY as \"Quantity\", " + " L.ITEM_ID as \"ItemId\"," + " L.ORD_ID as \"OrdId\"," + " L.DISCOUNT as \"Discount\" " + " FROM LINE_ITEM L " + " WHERE L.ORD_ID = O.ID) as \"LineItemView\" " + "FROM ORD O " + "WHERE O.ID = ?"; public static String returnDocument (Connection c, String ordId) { String XMLDoc = ""; try { if (c != null) conn = c; if (conn == null) _getConnected(appURL, appUser, appPassword); XMLProducer xmlp = null; xmlp = new XMLProducer(conn); // The XML Producer xmlp.setRowset("Results"); xmlp.setRow("OrdView"); PreparedStatement stmt = conn.prepareStatement(XMLStmt); stmt.setString(1, ordId); ResultSet rSet = stmt.executeQuery(); XMLDoc = xmlp.getXMLString(rSet, "Y"); rSet.close(); stmt.close(); if (c == null) { conn.close(); conn = null; } } catch (SQLException e) {} return XMLDoc; } private static void _getConnected(String connURL, String userName, String password) { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(connURL, userName, password); } catch (Exception e) { System.err.println(e); System.exit(1); } } public static void main (String[] args) // Just for test !! { System.out.println(returnDocument(null, "28004")); } }
package B2BDemo.XMLUtil; /** * Matches a record of the Stylesheet table in the AQ Schema. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class XslRecord { public String FROM; public String TO; public String TASK; public String XSL; public XslRecord(String FROM, String TO, String TASK, String XSL) { this.FROM = FROM; this.TO = TO; this.TASK = TASK; this.XSL = XSL; } public boolean equals(XslRecord x) { if (this.FROM.equals(x.FROM) && this.XSL.equals(x.XSL) && this.TASK.equals(x.TASK) && this.TO.equals(x.TO)) return true; else return false; } }
package B2BDemo.XMLUtil; /** * Wraps some parser features. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.net.*; import java.io.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; /** * This class is a wrapper for the XSL Transformer provided with the * Oracle XML Parser for Java V2. * * It processes XSL Transformations from Strings, files or URL as well. * * @author Olivier Le Diouris. Partner Services. Oracle Corp. * @version 1.0 * */ public class XSLTWrapper { DOMParser parser; String xml = ""; String xsl = ""; String result = ""; private static boolean _debug = false; public XSLTWrapper() { parser = new DOMParser(); } public void process() throws Exception { if (xml.length() == 0) throw new Exception("XML Document is empty"); if (xsl.length() == 0) throw new Exception("XSL Document is empty"); result = processTransformation(xml, xsl); } public void putXml(String xml) throws Exception { if (_debug) System.out.println("Recieved XML : \n" + xml); this.xml = xml; } public void putXsl(String xsl) throws Exception { this.xsl = xsl; if (_debug) System.out.println("Recieved XSL: \n" + xsl); } public String getXml() throws Exception { return xml; } public String getXsl() throws Exception { return xsl; } public String getProcessResult() throws Exception { return result; } // Turns a String into an XMLDocument public XMLDocument parseDocument(String documentStream) throws Exception { XMLDocument returnXML = null; try { parser.parse(new InputSource(new ByteArrayInputStream(documentStream.getBytes()))); returnXML = parser.getDocument(); } catch (SAXException saxE) { if (_debug) System.err.println("For:\n" + documentStream + "\nParse failed SAX : " + saxE); throw new Exception("Parsing XML\n" + "SAX Exception:\n" + saxE.toString()); } catch (IOException e) { if (_debug) System.err.println("Parse failed : " + e); throw new Exception("Parsing XML\n" + "IOException:\n" + e.toString()); } return returnXML; } private XMLDocument processXML(XMLDocument xml, XMLDocument xslDoc) throws Exception { XMLDocument out = null; URL xslURL = null; try { parser.setPreserveWhitespace(true); parser.setValidationMode(false); // Validation. Should be an option. // instantiate a stylesheet XSLStylesheet xsl = new XSLStylesheet(xslDoc, xslURL); XSLProcessor processor = new XSLProcessor(); // display any warnings that may occur processor.showWarnings(true); processor.setErrorStream(System.err); // Process XSL DocumentFragment result = processor.processXSL(xsl, xml); // create an output document to hold the result out = new XMLDocument(); /* // create a dummy document element for the output document Element root = out.createElement("root"); out.appendChild(root); // append the transformed tree to the dummy document element root.appendChild(result); */ out.appendChild(result); // print the transformed document // out.print(System.out); } catch (Exception e) { ByteArrayOutputStream baos = new ByteArrayOutputStream(); PrintWriter pw = new PrintWriter(baos); e.printStackTrace(pw); e.printStackTrace(); throw new Exception("ProcessXML\n" + baos.toString()); } return(out); } /** * XML String and XSL String as input * Input Strings may content : * the name of a URL * the name of a file (on the local file system) * the document itself * XML String as output. */ public String processTransformation(String xmlStream, String xslStream) throws Exception { String xmlContent = ""; String xslContent = ""; try { xmlContent = readURL(createURL(xmlStream)); } catch (Exception e) { xmlContent = xmlStream; } try { xslContent = readURL(createURL(xslStream)); } catch (Exception e) { xslContent = xslStream; } if (_debug) System.out.println("xmlStream = " + xmlContent); if (_debug) System.out.println("xslStream = " + xslContent); XMLDocument xml = parseDocument(xmlContent); XMLDocument xsl = parseDocument(xslContent); XMLDocument out = processXML(xml, xsl); ByteArrayOutputStream baos = new ByteArrayOutputStream(); try { out.print(baos); } catch (IOException ioE) { if (_debug) System.err.println("Exception:" + ioE); throw new Exception("XML Processing throws IOException\n" + ioE.toString()); } return (baos.toString()); } // Create a URL from a file name private static URL createURL(String fileName) throws Exception { URL url = null; try { url = new URL(fileName); } catch (MalformedURLException ex) // It is not a valid URL, maybe a file... { File f = new File(fileName); try { String path = f.getAbsolutePath(); // This is a bunch of weird code that is required to // make a valid URL on the Windows platform, due // to inconsistencies in what getAbsolutePath returns. String fs = System.getProperty("file.separator"); if (fs.length() == 1) { char sep = fs.charAt(0); if (sep != '/') path = path.replace(sep, '/'); if (path.charAt(0) != '/') path = '/' + path; } path = "file://" + path; url = new URL(path); } catch (MalformedURLException e) { if (_debug) System.err.println("Cannot create url for: " + fileName); throw e; // It's not a file either... } } return url; } private static String readURL(URL url) throws Exception { URLConnection newURLConn; BufferedInputStream newBuff; int nBytes; byte aByte[]; String resultBuff = ""; aByte = new byte[2]; try { // System.out.println("Calling " + url.toString()); try { newURLConn = url.openConnection(); newBuff = new BufferedInputStream(newURLConn.getInputStream()); resultBuff = ""; while (( nBytes = newBuff.read(aByte, 0, 1)) != -1) resultBuff = resultBuff + (char)aByte[0]; } catch (IOException e) { // System.err.println("Opening locator\n" + e.toString()); // System.err.println(url.toString() + "\n : newURLConn failed :\n" + e); throw e; } } catch (Exception e) { // System.err.println("Read URL\n" + e.toString()); throw e; } return resultBuff; } }
<?xml version="1.0" ?> <!-- | $Author: smuench $ | $Date: 2000/03/14 10:36:42 $ | $Source: C:\\cvsroot/xsql/src/XSQLConfig.xml,v $ | $Revision: 1.11 $ +--> <XSQLConfig> <!-- | | This section defines configuration settings | specific to the XSQL Servlet | +--> <servlet> <!-- | | Sets the size (in bytes) of the buffered output stream. | If your servlet engine already buffers I/O to the | Servlet Output Stream, then you can set to 0 | to avoid additional buffering. | | <output-buffer-size>10000</output-buffer-size> | +--> <output-buffer-size>0</output-buffer-size> <!-- | | Add <media-type> elements as shown below to cause | the XSQL Servlet to *suppress* sending the "charset=XXX" | portion of the Media/Content-type. | | For example, sending a character set for "image/svg" | documents seems to confuse current SVG plugins. | | <suppress-mime-charset> | <media-type>image/svg</media-type> | </suppress-mime-charset> | +--> <suppress-mime-charset> <media-type>image/svg</media-type> </suppress-mime-charset> </servlet> <!-- | | This section defines XSQL Page Processor configuration settings. | +--> <processor> <!-- | | Connection definitions (see <connectiondefs> below) | are cached when the XSQL Page Processor is initialized. | | Set to "yes" to cause the processor to | reread the XSQLConfig.xml file to reload | connection definitions if an attempt is made | to request a connection name that's not in the | cached connection list. The "yes" setting is useful | during development when you might be adding new | <connection> definitions to the file while the | servlet is running. Set to "no" to avoid reloading | the connection definition file when a connection name | is not found in the in-memory cache. | +--> <reload-connections-on-error>yes</reload-connections-on-error> <!-- | | Set the default value of the Row Fetch Size | for retrieving information from SQL queries | from the database. Only takes effect if you | are using the Oracle JDBC Driver, otherwise | the setting is ignored. Useful for reducing | network roundtrips to the database from | the servlet engine running in a different tier. | | <default-fetch-size>50</default-fetch-size> | +--> <default-fetch-size>50</default-fetch-size> <!-- | | Set the value of the XSQL LRU Cache for XSQL Pages | This determines the maximum number of stylesheets | that will be cached. Least recently used sheets get | "bumped" out of the cache if you go beyond this number. | | <page-cache-size>25</page-cache-size> | +--> <page-cache-size>25</page-cache-size> <!-- | | Set the value of the XSQL LRU Cache for XSL Stylesheets. | This determines the maximum number of stylesheets | that will be cached. Least recently used sheets get | "bumped" out of the cache if you go beyond this number. | | <stylesheet-cache-size>25</stylesheet-cache-size> | +--> <stylesheet-cache-size>25</stylesheet-cache-size> <!-- | | Set the parameters controlling stylesheet pools. | | Each cached stylesheet is actually a cached pool | of stylesheet instances. These values control | The initial number of stylesheet instances in the | pool, the number that will be added/incremented | when under-load the pool must be grown, and | the number of seconds that must transpire without | activity before a stylesheet instance will be | dropped out of the pool to shrink it back towards | its initial number. | | <stylesheet-pool> | <initial>1</initial> | <increment>1</increment> | <timeout-seconds>60</timeout-seconds> | </stylesheet-pool> | +--> <stylesheet-pool> <initial>1</initial> <increment>1</increment> <timeout-seconds>60</timeout-seconds> </stylesheet-pool> <!-- | | Set the parameters controlling database connection pools. | | When used, each named connection defined can have a pool of | connection instances to share among requests. These values | control The initial number of stylesheet instances in the pool, | the number that will be added/incremented when under-load the | pool must be grown, and the number of seconds that must | transpire without activity before a stylesheet instance will be | dropped out of the pool to shrink it back towards its initial | number. | | If the "dump-allowed" element has the value "yes" | then a browser-based status report that dumps the | current state of the connection pools is enabled. | | <connection-pool> | <initial>2</initial> | <increment>1</increment> | <timeout-seconds>60</timeout-seconds> | <dump-allowed>no</dump-allowed> | </connection-pool> | +--> <connection-pool> <initial>2</initial> <increment>1</increment> <timeout-seconds>60</timeout-seconds> <dump-allowed>no</dump-allowed> </connection-pool> <!-- | | Include timing information (in Milliseconds) | | <timing-info> | <page>yes</page> | <action>yes</action> | </timing-info> | +--> <timing-info> <page>no</page> <action>no</action> </timing-info> </processor> <!-- | | This section defines HTTP Proxy Server name | and port for use by the <xsql:include-xml> | action. If you intend to use <xsql:include-xml> | to include XML from URL's outside a firewall, | uncomment the: | | <http> | <proxyhost>your-proxy-server.yourcompany.com</proxyhost> | <proxyport>80</proxyport> | </http> | | section below and change the proxyhost and proxyport | as appropriate. If left commented out, then the XSQL | Page processor does not use a proxy server. | +--> <!-- <http> <proxyhost>your-proxy-server.yourcompany.com</proxyhost> <proxyport>80</proxyport> </http> --> <!-- | | This section defines convenient "nicknames" for | one or more database connections. You can include | any number of <connection> elements inside of | the <connectiondefs> element. XSQL Pages refer to | these connections by their name in the "connection" | attribute on the document element of the page. | +--> <connectiondefs> <connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> <connection name="xmlbook"> <username>xmlbook</username> <password>xmlbook</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> <connection name="lite"> <username>system</username> <password>manager</password> <dburl>jdbc:Polite:POlite</dburl> <driver>oracle.lite.poljdbc.POLJDBCDriver</driver> </connection> <connection name="retail"> <username>retailer</username> <password>retailer</password> <dburl>jdbc:oracle:thin:@atp-1.us.oracle.com:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> </connectiondefs> <!-- | | This section registers pre-defined element names and | handler classes for user-defined XSQL page actions | | The section looks like: | | <actiondefs> | <action> | <elementname>myAction</elementname> | <handlerclass>mypackage.MyActionHandler</handlerclass> | </action> | : | <actiondefs> | | Action Handler classes must implement the interface | oracle.xml.xsql.XSQLActionHandler. | | Once registered here, user-defined actions can be | used in the same way as built-in XSQL actions, for example | including the <xsql:myAction> element in your page. | +--> <actiondefs> <action> <elementname>param</elementname> <handlerclass>oracle.xml.xsql.actions.ExampleGetParameterHandler</handlerclass> </action> <action> <elementname>current-date</elementname> <handlerclass>oracle.xml.xsql.actions.ExampleCurrentDBDateHandler</handlerclass> </action> </actiondefs> </XSQLConfig>
The message header script is given here:
package B2BDemo; /** * Describes the headers used in the messages * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class MessageHeaders extends Object { public static String APP_A = "RETAIL"; public static String APP_B = "SUPPLY"; public static String BROKER = "BROKER"; public static String EXIT = "EXIT"; public static String NEW_ORDER = "NEW ORDER"; public static String UPDATE_ORDER = "UPDATE ORDER"; }
package B2BDemo; /** * Holds the constants to be used by the Message Broker * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class AppCste extends Object { public final static String AQDBUrl = "jdbc:oracle:thin:@atp-1.us.oracle.com:1521:ORCL"; public final static String AQuser = "aqMessBrok"; public final static String AQpswd = "aqMessBrok"; }
The Retailer uses the following scripts:
package B2BDemo.Retailer; /** * * This class implements the component waiting on the retailer side for * the status update made by the Supplier after shipping. * The recieved document is parsed and its content is used to make * the convenient update in the database. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. * */ import java.io.*; import java.util.*; import java.net.*; import java.sql.*; import oracle.xml.sql.query.*; import oracle.xml.sql.dml.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; import B2BDemo.AQUtil.*; import B2BDemo.*; import B2BDemo.XMLUtil.*; import java.awt.*; import java.awt.event.*; import javax.swing.*; //import oracle.bali.ewt.border.UIBorderFactory; //import oracle.bali.ewt.olaf.OracleLookAndFeel; public class UpdateMaster extends Object { private BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in)); private static boolean stepByStep = false; private static boolean verbose = false; private static Integer pauseTime = null; AQReader aqr; private static final String userName = "retailer"; private static final String password = "retailer"; private static String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; // This is the default value ! private static Connection conn = null; String currOrdId = ""; DOMParser parser = new DOMParser(); /** * Constructor */ public UpdateMaster() { XMLFrame frame = new XMLFrame("Retailer"); /** try { OracleLookAndFeel.setColorScheme(Color.cyan); // OracleLookAndFeel.setColorScheme("Titanium"); UIManager.setLookAndFeel(new OracleLookAndFeel()); SwingUtilities.updateComponentTreeUI(frame); frame.setBackground(UIManager.getColor("darkIntensity")); } catch (Exception e) { System.err.println("Exception for Oracle Look and Feel:" + e ); } */ //Center the window Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); Dimension frameSize = frame.getSize(); if (frameSize.height > screenSize.height) { frameSize.height = screenSize.height; } /** if (frameSize.width > screenSize.width) { frameSize.width = screenSize.width; } */ frameSize.width = screenSize.width / 3; // frame.setLocation((screenSize.width - frameSize.width)/2, (screenSize.height - frameSize.height)/2); frame.setLocation(0, (screenSize.height - frameSize.height)/2); // frame.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); frame.setVisible(true); // Initialize AQ reader aqr = new AQReader(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppFour_QTab", "AppFourMsgQueue"); boolean go = true; while (go) { String ordIdValue = ""; B2BMessage sm = aqr.readQ(); if (verbose) System.out.println("Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType() + "\nContent >\n" + sm.getContent()); else System.out.println("Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType()); String xmlDoc = sm.getContent(); if (xmlDoc != null && xmlDoc.length() > 0) { try { frame.setXMLDocument(sm.getContent()); } catch (Exception e) { e.printStackTrace(); } } if (stepByStep) { if (pauseTime != null) { System.out.println("Waiting for " + pauseTime.longValue() + " milliseconds"); try { Thread.sleep(pauseTime.longValue()); } catch (InterruptedException e) {} } else try { String s = _userInput("[Hit return to continue]"); } catch (Exception e) {} } if (sm.getType().equals(MessageHeaders.EXIT)) go = false; else { System.out.println("Updating"); try { parser.parse(new InputSource(new ByteArrayInputStream(sm.getContent().getBytes()))); XMLDocument xml = parser.getDocument(); XMLElement elmt = (XMLElement)xml.getDocumentElement(); NodeList nl = elmt.getElementsByTagName("SHIP"); // ORD ID for (int i=0; i<nl.getLength(); i++) { XMLElement ordId = (XMLElement)nl.item(i); XMLNode theText = (XMLNode)ordId.getFirstChild(); currOrdId = theText.getNodeValue(); System.out.println("Gonna update " + currOrdId); try { if (conn == null) getConnected(url, userName, password); String strStmt = "update ORD set STATUS = 'Shipped' where ID = ?"; PreparedStatement pStmt = conn.prepareStatement(strStmt); pStmt.setString(1, currOrdId); pStmt.execute(); conn.commit(); pStmt.close(); System.out.println("Done !"); } catch (SQLException e) { System.out.println("Pb updating the ORD\n" + e.toString()); } } } catch (SAXParseException e) { System.out.println(e.getMessage()); } catch (SAXException e) { System.out.println(e.getMessage()); } catch (Exception e) { System.out.println(e.getMessage()); } } } frame.setVisible(false); System.exit(0); } private static void getConnected(String connURL, String userName, String password) { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(connURL, userName, password); } catch (Exception e) { System.err.println(e); System.exit(1); } } private String _userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } private static void setRunPrm(String[] prm) { for (int i=0; i<prm.length; i++) { if (prm[i].toLowerCase().startsWith("-verbose")) verbose = isolatePrmValue(prm[i], "-verbose"); else if (prm[i].toLowerCase().startsWith("-help")) { System.out.println("Usage is:"); System.out.println("\tjava B2BDemo.Retailer.MessageBroker"); System.out.println("\tparameters can be -dbURL -verbose, -step, -help"); System.out.println("\tdbURL contains a string like jdbc:oracle:thin:@localhost:1521:ORCL"); System.out.println("\tparameters values can be (except for -help):"); System.out.println("\t\tnone - equivalent to 'y'"); System.out.println("\t\ty"); System.out.println("\t\ttrue - equivalent to 'y'"); System.out.println("\t\tn"); System.out.println("\t\tfalse - equivalent to 'n'"); System.out.println("\t\t-step can take a value in milliseconds"); System.exit(0); } else if (prm[i].toLowerCase().startsWith("-step")) { String s = getPrmValue(prm[i], "-step"); try { pauseTime = new Integer(s); System.out.println("Timeout " + pauseTime); stepByStep = true; } catch (NumberFormatException nfe) { pauseTime = null; if (s.toUpperCase().equals("Y") || s.toUpperCase().equals("TRUE")) stepByStep = true; else stepByStep = false; } } else if (prm[i].toLowerCase().startsWith("-dburl")) { url = getPrmValue(prm[i], "-dbURL"); } else System.err.println("Unknown parameter [" + prm[i] + "], ignored."); } } private static boolean isolatePrmValue(String s, String p) { boolean ret = true; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { String val = s.substring(s.indexOf("=") + 1); if (val.toUpperCase().equals("Y") || val.toUpperCase().equals("TRUE")) ret = true; else if (val.toUpperCase().equals("N") || val.toUpperCase().equals("FALSE")) ret = false; else { System.err.println("Unrecognized value for " + p + ", set to y"); ret = true; } } } return ret; } private static String getPrmValue(String s, String p) { String ret = ""; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { ret = s.substring(s.indexOf("=") + 1); } } return ret; } /** * main * @param args */ public static void main(String[] args) { if (args.length > 0) setRunPrm(args); UpdateMaster updateMaster = new UpdateMaster(); } }
The AQ-Broker-Transformer uses the following scripts:
package B2BDemo.Broker; import java.sql.*; import oracle.AQ.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.driver.*; import B2BDemo.AQUtil.*; import B2BDemo.XMLUtil.*; import B2BDemo.*; /** * This class implements a thread listening on one AQ. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class BrokerThread extends Thread { private BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in)); private static boolean stepByStep = false; private static boolean verbose = false; AQReader aqReader; AQWriter aqWriter; String threadName; XSLTWrapper wrapper; Connection conn; Integer pause; XMLFrame frame; /** * Constructor */ public BrokerThread(String name, AQReader aqr, AQWriter aqw, XSLTWrapper wrap, Connection c, boolean v, boolean s, Integer p, XMLFrame f) { this.aqReader = aqr; this.aqWriter = aqw; this.threadName = name; this.wrapper = wrap; this.conn = c; this.verbose = v; this.stepByStep = s; this.pause = p; this.frame = f; } public void run() { boolean go = true; while (go) { B2BMessage sm = this.aqReader.readQ(); if (verbose) System.out.println(this.threadName + " Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType() + "\nContent >\n" + sm.getContent()); else System.out.println(this.threadName + " Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType()); String xmlDoc = sm.getContent(); if (xmlDoc != null && xmlDoc.length() > 0) { try { this.frame.setXMLDocument(sm.getContent()); } catch (Exception e) { e.printStackTrace(); } } if (stepByStep) { if (pause != null) { System.out.println("Waiting for " + pause.longValue() + " milliseconds"); try { Thread.sleep(pause.longValue()); } catch (InterruptedException e) {} } else try { String s = _userInput("[Hit return to continue]"); } catch (Exception e) {} } if (sm.getType().length() >= MessageHeaders.EXIT.length() && sm.getType().equals(MessageHeaders.EXIT)) go = false; else { // Transform ! String processedXMLDoc = ""; String xslDoc = getXSL(sm.getFrom(), sm.getTo(), sm.getType()); if (verbose) System.out.println("Read:\n" + xslDoc); try { processedXMLDoc = wrapper.processTransformation(sm.getContent(), xslDoc /*defaultStyleSheet*/); if (verbose) System.out.println("\nResult :\n" + processedXMLDoc); System.out.println("Transformation done."); } catch (Exception e) { System.err.println("Ooops...\n"); e.printStackTrace(); } if (stepByStep) { if (pause != null) { System.out.println("Waiting for " + pause.longValue() + " milliseconds"); try { Thread.sleep(pause.longValue()); } catch (InterruptedException e) {} } else try { String s = _userInput("[Hit return to continue]"); } catch (Exception e) {} } // Send new document to destination this.aqWriter.writeQ(new B2BMessage(sm.getFrom(), sm.getTo(), sm.getType(), processedXMLDoc)); this.aqWriter.flushQ(); } } if (frame.isVisible()) frame.setVisible(false); System.exit(0); } private String getXSL(String from, String to, String task) { if (verbose) System.out.println("Processing From " + from + " to " + to + " for " + task); String xsl = ""; String stmt = "SELECT XSL FROM STYLESHEETS WHERE APPFROM = ? AND APPTO = ? AND OP = ?"; try { PreparedStatement pStmt = conn.prepareStatement(stmt); pStmt.setString(1, from); pStmt.setString(2, to); pStmt.setString(3, task); ResultSet rSet = pStmt.executeQuery(); while (rSet.next()) xsl = _dumpClob(conn, ((OracleResultSet)rSet).getCLOB(1)); rSet.close(); pStmt.close(); } catch (SQLException e) { } catch (Exception e) { } return xsl; } static String _dumpClob (Connection conn, CLOB clob) throws Exception { String returnStr = ""; OracleCallableStatement cStmt1 = (OracleCallableStatement) conn.prepareCall ("begin ? := dbms_lob.getLength (?); end;"); OracleCallableStatement cStmt2 = (OracleCallableStatement) conn.prepareCall ("begin dbms_lob.read (?, ?, ?, ?); end;"); cStmt1.registerOutParameter (1, Types.NUMERIC); cStmt1.setClob (2, clob); cStmt1.execute (); long length = cStmt1.getLong (1); long i = 0; int chunk = 100; if (verbose) System.out.println("Length to read from DB : " + length); while (i < length) { cStmt2.setClob (1, clob); cStmt2.setLong (2, chunk); cStmt2.registerOutParameter (2, Types.NUMERIC); cStmt2.setLong (3, i + 1); cStmt2.registerOutParameter (4, Types.VARCHAR); cStmt2.execute (); long readThisTime = cStmt2.getLong (2); String stringThisTime = cStmt2.getString (4); // System.out.print ("Read " + read_this_time + " chars: "); returnStr += stringThisTime; i += readThisTime; } cStmt1.close (); cStmt2.close (); return returnStr; } private String _userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } }
package B2BDemo.Broker; /** * Implements the AQ Broker-Transformer. * This "Message Broker" uses 4 message queues, provided by * Oracle8i Advanced Queuing. * AQ Broker uses the threads described in BrokerThread * Each thread is waiting on one queue, and writing on another. * The message broker uses - for this demo - two threads : * One from retailer to supplier * One from supplier to retailer * 2 Threads := 4 queues * * When a message is recieved, the broker knows : * where it comes from (origin) * where it goes to (destination) * what for (operation) * Those three elements are used as the primary key for the * stylesheet table (belonging to the AQ schema) to fetch the * right sXSL Stylesheet from the database, in order to turn * the incoming document into an outgoing one fitting the requirements * of the destination application. * * @see BrokerThread * @see B2BMessage * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.sql.*; import oracle.AQ.*; import java.io.*; import java.awt.*; import java.awt.event.*; import javax.swing.*; import oracle.sql.*; import oracle.jdbc.driver.*; //import oracle.bali.ewt.border.UIBorderFactory; //import oracle.bali.ewt.olaf.OracleLookAndFeel; import B2BDemo.AQUtil.*; import B2BDemo.*; import B2BDemo.XMLUtil.*; public class MessageBroker extends Object { private static boolean stepByStep = false; private static boolean verbose = false; private static Integer pauseTime = null; XSLTWrapper wrapper = null; // To get the style sheet from its CLOB Connection conn = null; String userName = AppCste.AQuser; String password = AppCste.AQpswd; String dbUrl = AppCste.AQDBUrl; public MessageBroker() { XMLFrame frame = new XMLFrame("Message Broker"); /** try { OracleLookAndFeel.setColorScheme(Color.cyan); // OracleLookAndFeel.setColorScheme("Titanium"); UIManager.setLookAndFeel(new OracleLookAndFeel()); SwingUtilities.updateComponentTreeUI(frame); frame.setBackground(UIManager.getColor("darkIntensity")); } catch (Exception e) { System.err.println("Exception for Oracle Look and Feel:" + e ); } */ //Center the window Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); Dimension frameSize = frame.getSize(); if (frameSize.height > screenSize.height) { frameSize.height = screenSize.height; } /** if (frameSize.width > screenSize.width) { frameSize.width = screenSize.width; } */ frameSize.width = screenSize.width / 3; // frame.setLocation((screenSize.width - frameSize.width)/2, (screenSize.height - frameSize.height)/2); frame.setLocation(frameSize.width, (screenSize.height - frameSize.height)/2); // frame.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); frame.setVisible(true); AQReader aqr = null; AQWriter aqw = null; // Initialize AQ reader and writer aqw = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppTwo_QTab", "AppTwoMsgQueue"); aqr = new AQReader(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppOne_QTab", "AppOneMsgQueue"); wrapper = new XSLTWrapper(); if (conn == null) _getConnected(); BrokerThread retail2supply = new BrokerThread("Retail to Supply", aqr, aqw, wrapper, conn, verbose, stepByStep, pauseTime, frame); aqw = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppFour_QTab", "AppFourMsgQueue"); aqr = new AQReader(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppThree_QTab", "AppThreeMsgQueue"); BrokerThread supply2retail = new BrokerThread("Supply to Retail", aqr, aqw, wrapper, conn, verbose, stepByStep, pauseTime, frame); retail2supply.start(); supply2retail.start(); System.out.println("<ThreadsOnTheirWay/>"); } private void _getConnected() { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection (dbUrl, userName, password); } catch (Exception e) { System.out.println("Get connected failed : " + e); System.exit(1); } } private static void setRunPrm(String[] prm) { for (int i=0; i<prm.length; i++) { if (prm[i].toLowerCase().startsWith("-verbose")) verbose = isolatePrmValue(prm[i], "-verbose"); else if (prm[i].toLowerCase().startsWith("-help")) { System.out.println("Usage is:"); System.out.println("\tjava Intel.iDevelop.MessageBroker"); System.out.println("\tparameters can be -verbose, -step, -help"); System.out.println("\tparameters values can be (except for -help):"); System.out.println("\t\tnone - equivalent to 'y'"); System.out.println("\t\ty"); System.out.println("\t\ttrue - equivalent to 'y'"); System.out.println("\t\tn"); System.out.println("\t\tfalse - equivalent to 'n'"); System.out.println("\t\t-step can take a value in milliseconds"); System.exit(0); } else if (prm[i].toLowerCase().startsWith("-step")) { String s = getPrmValue(prm[i], "-step"); try { pauseTime = new Integer(s); System.out.println("Timeout " + pauseTime); stepByStep = true; } catch (NumberFormatException nfe) { pauseTime = null; if (s.toUpperCase().equals("Y") || s.toUpperCase().equals("TRUE")) stepByStep = true; else stepByStep = false; } } else System.err.println("Unknown parameter [" + prm[i] + "], ignored."); } } private static boolean isolatePrmValue(String s, String p) { boolean ret = true; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { String val = s.substring(s.indexOf("=") + 1); if (val.toUpperCase().equals("Y") || val.toUpperCase().equals("TRUE")) ret = true; else if (val.toUpperCase().equals("N") || val.toUpperCase().equals("FALSE")) ret = false; else { System.err.println("Unrecognized value for " + p + ", set to y"); ret = true; } } } return ret; } private static String getPrmValue(String s, String p) { String ret = ""; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { ret = s.substring(s.indexOf("=") + 1); } } return ret; } public static void main(String args[]) { // java B2BDemo.OrderEntry.MessageBroker -verbose[=[y|true|n|false]] -step[=[y|true|n|false]] -help if (args.length > 0) setRunPrm(args); new MessageBroker(); } }
package B2BDemo.AQUtil; /** * This class is a wrapper around the Advanced Queuing facility of Oracle 8i. * Used to dequeue a message. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.sql.*; import oracle.AQ.*; import java.io.*; public class AQReader extends Object { Connection conn = null; AQSession aqSess = null; String userName = ""; String qTableName = ""; String qName = ""; AQQueueTable aqTable = null; AQQueue aq = null; public AQReader(String userName, String password, String url, String qTable, String qName) { this.userName = userName; this.qTableName = qTable; this.qName = qName; aqSess = createSession(userName, password, url); aqTable = aqSess.getQueueTable(userName, qTableName); System.out.println("Successful getQueueTable"); // Handle to q aq = aqSess.getQueue(userName, qName); System.out.println("Successful getQueue"); } public AQSession createSession(String userName, String pswd, String url) { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(url, userName, pswd); System.out.println("JDBC Connection opened"); conn.setAutoCommit(false); // Load Oracle 8i AQ Driver Class.forName("oracle.AQ.AQOracleDriver"); // Create the AQ Session aqSess = AQDriverManager.createAQSession(conn); System.out.println("AQ Session successfully created."); } catch (Exception e) { System.out.println("Exception : " + e); e.printStackTrace(); } return aqSess; } public B2BMessage readQ() throws AQException { AQMessage message; AQRawPayload rawPayload; // Read with REMOVE option AQDequeueOption dqOption = new AQDequeueOption(); dqOption.setDequeueMode(AQDequeueOption.DEQUEUE_REMOVE); message = aq.dequeue(dqOption); System.out.println("Successfull dQueue"); rawPayload = message.getRawPayload(); try { conn.commit(); // Commit the REMOVE } catch (Exception sqle) { System.err.println(sqle.toString()); } return (B2BMessage)deserializeFromByteArray(rawPayload.getBytes()); } private static Object deserializeFromByteArray (byte[] b) { ByteArrayInputStream inputStream = new ByteArrayInputStream(b); try { ObjectInputStream ois = new ObjectInputStream(inputStream); return ois.readObject(); } catch (Exception e) { System.err.println("deserializeFromByteArray failed :" + e); return null; } } }
package B2BDemo.AQUtil; /** * This class is a wrapper around the Advanced Queuing facility of Oracle 8i. * Used to enqueue a message. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.sql.*; import oracle.AQ.*; import java.io.*; public class AQWriter extends Object { Connection conn = null; AQSession aqSess = null; String userName = ""; String qTableName = ""; String qName = ""; public AQWriter(String userName, String password, String url, String qTable, String qName) { this.userName = userName; this.qTableName = qTable; this.qName = qName; aqSess = createSession(userName, password, url); } public void flushQ() { if (conn != null) { try { conn.commit(); } catch (SQLException e) {} } } public void closeConnection() { if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } public AQSession createSession(String userName, String pswd, String url) { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection(url, userName, pswd); System.out.println("JDBC Connection opened"); conn.setAutoCommit(false); // Load Oracle 8i AQ Driver Class.forName("oracle.AQ.AQOracleDriver"); // Create the AQ Session aqSess = AQDriverManager.createAQSession(conn); System.out.println("AQ Session successfully created."); } catch (Exception e) { System.out.println("Exception : " + e); e.printStackTrace(); } return aqSess; } public void writeQ(B2BMessage sm) throws AQException { AQQueueTable qTable; AQQueue q; qTable = aqSess.getQueueTable(userName, qTableName); System.out.println("Successful getQueueTable"); // Handle to q q = aqSess.getQueue(userName, qName); System.out.println("Successful getQueue"); // Q is identified, let's write AQMessage message; AQRawPayload rawPayload; message = q.createMessage(); byte[] bArray = serializeToByteArray(sm); rawPayload = message.getRawPayload(); rawPayload.setStream(bArray, bArray.length); AQEnqueueOption eqOption = new AQEnqueueOption(); q.enqueue(eqOption, message); } private static byte[] serializeToByteArray (Object o) { ByteArrayOutputStream outStream = new ByteArrayOutputStream(); try { ObjectOutputStream oos = new ObjectOutputStream(outStream); oos.writeObject(o); return outStream.toByteArray(); } catch (Exception e) { System.err.println("serialize2ByteArray failed : " + e); return null; } } }
package B2BDemo.AQUtil; /** * This class decsribes the structure of the messages used in this demo * Subject to changes in 817 * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.io.Serializable; public class B2BMessage extends Object implements Serializable { String from; String to; String type; String content; public B2BMessage(String f, String t, String typ, String c) { this.from = f; this.to = t; this.type = typ; this.content = c; } public String getFrom() { return this.from; } public String getTo() { return this.to; } public String getType() { return this.type; } public String getContent() { return this.content; } }
package B2BDemo.AQUtil; /** * A main for tests - Not used in the demo itself. * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ import java.sql.*; import oracle.AQ.*; import java.io.*; import B2BDemo.*; public class ReadStructAQ extends Object { public static void main(String[] args) { AQReader aqr = new AQReader(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "objMsgsStruct_QTab", "structMsgQueue"); // Loop while EXIT is not recieved boolean goLoop = true; while (goLoop) { B2BMessage sm = aqr.readQ(); System.out.println("Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType() + "\nContent >\n" + sm.getContent()); if (sm.getType().equals("EXIT")) goLoop = false; } System.out.println("<bye/>"); } }
package B2BDemo.AQUtil; import B2BDemo.*; /** * Used in the demo to stop the queues and the applications waiting on them. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class StopAllQueues extends Object { /** * Constructor */ public StopAllQueues() { AQWriter aqw1 = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppOne_QTab", "AppOneMsgQueue"); aqw1.writeQ(new B2BMessage(MessageHeaders.APP_B, MessageHeaders.APP_A, MessageHeaders.EXIT, "")); aqw1.flushQ(); AQWriter aqw2 = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppTwo_QTab", "AppTwoMsgQueue"); aqw2.writeQ(new B2BMessage(MessageHeaders.APP_B, MessageHeaders.APP_A, MessageHeaders.EXIT, "")); aqw2.flushQ(); AQWriter aqw3 = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppThree_QTab", "AppThreeMsgQueue"); aqw3.writeQ(new B2BMessage(MessageHeaders.APP_B, MessageHeaders.APP_A, MessageHeaders.EXIT, "")); aqw3.flushQ(); AQWriter aqw4 = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppFour_QTab", "AppFourMsgQueue"); aqw4.writeQ(new B2BMessage(MessageHeaders.APP_B, MessageHeaders.APP_A, MessageHeaders.EXIT, "")); aqw4.flushQ(); } /** * main * @param args */ public static void main(String[] args) { StopAllQueues stopAllQueues = new StopAllQueues(); } }
package B2BDemo.AQUtil; /** * A Main for tests - Not used in the demo itself. * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ /** * A main for tests */ import java.sql.*; import oracle.AQ.*; import java.io.*; import B2BDemo.*; public class WriteStructAQ extends Object { private static BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in)); static AQWriter aqw = null; public static void main(String[] args) { try { aqw = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "objMsgsStruct_QTab", "structMsgQueue"); String messSubject = ""; String messTxt = ""; String messOrigin = ""; String messDestination = ""; try { messOrigin = userInput("Message Origin > "); messDestination = userInput("Message Destination > "); messSubject = userInput("Message Subject > "); messTxt = userInput("Message Text > "); } catch (Exception e){} // Write the queue B2BMessage sm = new B2BMessage(messOrigin, messDestination, messSubject, messTxt); aqw.writeQ(sm); try { String s = userInput("Written"); } catch (Exception ne) {} aqw.closeConnection(); try { String s = userInput("Closed !"); } catch (Exception ne) {} } catch (Exception e) { System.err.println("Arghh : " + e); e.printStackTrace(); try { String s = userInput("..."); } catch (Exception ne) {} } } private static String userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } }
The Supplier uses the following scripts:
package B2BDemo.Supplier; import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.io.*; import java.util.*; import java.net.*; import java.sql.*; import oracle.xml.sql.query.*; import oracle.xml.sql.dml.*; import org.w3c.dom.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; import B2BDemo.AQUtil.*; import B2BDemo.*; import B2BDemo.XMLUtil.*; /** * This class implements the Frame suggesting to ship the order.' * * @see SupplierWatcher in the same package. * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class SupplierFrame extends JFrame { private BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in)); private static boolean stepByStep = false; private static boolean verbose = false; private static Integer pause = null; private XMLFrame frame = null; AQReader aqr; XMLtoDMLv2 x2d = null; String userName = "supplier"; String password = "supplier"; String url = null; String currOrdId = ""; DOMParser parser = new DOMParser(); AQWriter aqw = null; BorderLayout borderLayout1 = new BorderLayout(); JPanel jPanel1 = new JPanel(); BorderLayout borderLayout2 = new BorderLayout(); JPanel southPanel = new JPanel(); JButton shipButton = new JButton(); JPanel centerPanel = new JPanel(); JLabel ordMessage = new JLabel(); /** * Constructs a new instance. */ public SupplierFrame(boolean v, boolean s, Integer p, XMLFrame f, String url) { super(); this.verbose = v; this.stepByStep = s; this.pause = p; this.frame = f; this.url = url; try { jbInit(); } catch (Exception e) { e.printStackTrace(); } } /** * Initializes the state of this instance. */ private void jbInit() throws Exception { this.getContentPane().setLayout(borderLayout1); this.setSize(new Dimension(400, 300)); shipButton.setText("Ship Order"); shipButton.setEnabled(false); shipButton.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { shipButton_actionPerformed(e); } }); ordMessage.setText("Waiting for Orders"); ordMessage.setFont(new Font("Dialog", 1, 20)); jPanel1.setLayout(borderLayout2); this.setTitle("Supplier Watcher"); this.getContentPane().add(jPanel1, BorderLayout.CENTER); jPanel1.add(southPanel, BorderLayout.SOUTH); southPanel.add(shipButton, null); jPanel1.add(centerPanel, BorderLayout.CENTER); centerPanel.add(ordMessage, null); } public void enterTheLoop() { // Initialize AQ reader aqr = new AQReader(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppTwo_QTab", "AppTwoMsgQueue"); // Initialize XSL Transformer x2d = new XMLtoDMLv2(userName, password, url); // Initialize the AQ Writer aqw = new AQWriter(AppCste.AQuser, AppCste.AQpswd, AppCste.AQDBUrl, "AppThree_QTab", "AppThreeMsgQueue"); boolean go = true; while (go) { String ordIdValue = ""; String custIdValue = ""; B2BMessage sm = aqr.readQ(); if (verbose) System.out.println("Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType() + "\nContent >\n" + sm.getContent()); else System.out.println("Recieved\nFrom > " + sm.getFrom() + "\nTo > " + sm.getTo() + "\nType > " + sm.getType()); String xmlDoc = sm.getContent(); if (xmlDoc != null && xmlDoc.length() > 0) { try { this.frame.setXMLDocument(sm.getContent()); } catch (Exception e) { e.printStackTrace(); } } if (stepByStep) { if (pause != null) { System.out.println("Waiting for " + pause.longValue() + " milliseconds"); try { Thread.sleep(pause.longValue()); } catch (InterruptedException e) {} } else try { String s = _userInput("[Hit return to continue]"); } catch (Exception e) {} } if (sm.getType().equals(MessageHeaders.EXIT)) go = false; else { System.out.println("Inserting"); TableInDocument d[] = new TableInDocument[2]; d[0] = new TableInDocument("ROWSET", "ROW", "ORD"); d[1] = new TableInDocument("ITEMS", "ITEM_ROW", "LINE_ITEM"); try { String XMLDoc = sm.getContent(); x2d.insertFromXML(d, XMLDoc); System.out.println("Document processed."); // We want to read elements parser.setValidationMode(false); try { parser.parse(new InputSource(new ByteArrayInputStream(XMLDoc.getBytes()))); XMLDocument xml = parser.getDocument(); XMLElement elmt = (XMLElement)xml.getDocumentElement(); NodeList nl = elmt.getElementsByTagName("ID"); // ORD ID for (int i=0; i<nl.getLength(); i++) { XMLElement ordId = (XMLElement)nl.item(i); XMLNode theText = (XMLNode)ordId.getFirstChild(); ordIdValue = theText.getNodeValue(); currOrdId = ordIdValue; break; // Just the first one !!! } nl = elmt.getElementsByTagName("CUSTOMER_ID"); // CUSTOMER ID for (int i=0; i<nl.getLength(); i++) { XMLElement ordId = (XMLElement)nl.item(i); XMLNode theText = (XMLNode)ordId.getFirstChild(); custIdValue = theText.getNodeValue(); } } catch (SAXParseException e) { System.out.println(e.getMessage()); } catch (SAXException e) { System.out.println(e.getMessage()); } catch (Exception e) { System.out.println(e.getMessage()); } } catch (Exception e) { System.err.println("Ooops:\n" + e); } this.shipButton.setEnabled(true); String custName = ""; try { PreparedStatement pStmt = x2d.getConnection().prepareStatement("Select C.NAME from CUSTOMER C where C.ID = ?"); pStmt.setString(1, custIdValue); ResultSet rSet = pStmt.executeQuery(); while (rSet.next()) custName = rSet.getString(1); rSet.close(); pStmt.close(); } catch (SQLException e) {} this.ordMessage.setText("Order [" + ordIdValue + "] to process for [" + custName + "]"); JOptionPane.showMessageDialog(this, "New Order Pending !", "Wake Up !", JOptionPane.INFORMATION_MESSAGE); } } frame.setVisible(false); } void shipButton_actionPerformed(ActionEvent e) { // Send message String doc2send = "<SHIP>" + currOrdId + "</SHIP>"; // sending XMLDoc in the Queue aqw.writeQ(new B2BMessage(MessageHeaders.APP_B, MessageHeaders.APP_A, MessageHeaders.UPDATE_ORDER, doc2send)); aqw.flushQ(); // Commit ! // Disable Button this.shipButton.setEnabled(false); // display wait message this.ordMessage.setText("Waiting for orders..."); } private String _userInput(String prompt) throws Exception { String retString; System.out.print(prompt); try { retString = stdin.readLine(); } catch (Exception e) { System.out.println(e); throw(e); } return retString; } }
package B2BDemo.Supplier; import java.awt.*; import java.awt.event.*; import javax.swing.*; //import oracle.bali.ewt.border.UIBorderFactory; //import oracle.bali.ewt.olaf.OracleLookAndFeel; import B2BDemo.XMLUtil.*; /** * This class implements the agent waiting on the queue where the orders are delivered. * When a message is read, the agent "wakes up" and suggests to ship the order. * Shipping the order will then fire a new B2B process to update the status of * the order in the Retailer database. * * @author Olivier LE DIOURIS - Partner Technical Services - Oracle Copr. */ public class SupplierWatcher { private static boolean stepByStep = false; private static boolean verbose = false; private static Integer pauseTime = null; private static String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; // Default value /** * Constructor */ public SupplierWatcher() { XMLFrame xmlFrame = new XMLFrame("Supplier"); /* try { OracleLookAndFeel.setColorScheme(Color.cyan); // OracleLookAndFeel.setColorScheme("Titanium"); UIManager.setLookAndFeel(new OracleLookAndFeel()); SwingUtilities.updateComponentTreeUI(xmlFrame); xmlFrame.setBackground(UIManager.getColor("darkIntensity")); } catch (Exception e) { System.err.println("Exception for Oracle Look and Feel:" + e ); } */ //Center the window Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); Dimension frameSize = xmlFrame.getSize(); if (frameSize.height > screenSize.height) { frameSize.height = screenSize.height; } /** if (frameSize.width > screenSize.width) { frameSize.width = screenSize.width; } */ frameSize.width = screenSize.width / 3; // xmlFrame.setLocation((screenSize.width - frameSize.width)/2, (screenSize.height - frameSize.height)/2); xmlFrame.setLocation((2 * frameSize.width), (screenSize.height - frameSize.height)/2); // xmlFrame.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); xmlFrame.setVisible(true); SupplierFrame frame = new SupplierFrame(verbose, stepByStep, pauseTime, xmlFrame, url); /* try { OracleLookAndFeel.setColorScheme(Color.cyan); // OracleLookAndFeel.setColorScheme("Titanium"); UIManager.setLookAndFeel(new OracleLookAndFeel()); SwingUtilities.updateComponentTreeUI(frame); frame.setBackground(UIManager.getColor("darkIntensity")); } catch (Exception e) { System.err.println("Exception for Oracle Look and Feel:" + e ); } */ //Center the window screenSize = Toolkit.getDefaultToolkit().getScreenSize(); frameSize = frame.getSize(); if (frameSize.height > screenSize.height) { frameSize.height = screenSize.height; } if (frameSize.width > screenSize.width) { frameSize.width = screenSize.width; } frame.setLocation((screenSize.width - frameSize.width)/2, (screenSize.height - frameSize.height)/2); // frame.addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); frame.setVisible(true); frame.enterTheLoop(); frame.setVisible(false); xmlFrame.setVisible(false); System.exit(1); } private static void setRunPrm(String[] prm) { for (int i=0; i<prm.length; i++) { if (prm[i].toLowerCase().startsWith("-verbose")) verbose = isolatePrmValue(prm[i], "-verbose"); else if (prm[i].toLowerCase().startsWith("-help")) { System.out.println("Usage iB2BDemo.Supplier.MessageBroker"); System.out.println("\tparameters can be -dbURL -verbose, -step, -help"); System.out.println("\tdbURL contains a string like jdbc:oracle:thin:@localhost:1521:ORCL"); System.out.println("\tparameters values can be (except for -help):"); System.out.println("\t\tnone - equivalent to 'y'"); System.out.println("\t\ty"); System.out.println("\t\ttrue - equivalent to 'y'"); System.out.println("\t\tn"); System.out.println("\t\tfalse - equivalent to 'n'"); System.out.println("\t\t-step can take a value in milliseconds"); System.exit(0); } else if (prm[i].toLowerCase().startsWith("-step")) { String s = getPrmValue(prm[i], "-step"); try { pauseTime = new Integer(s); System.out.println("Timeout " + pauseTime); stepByStep = true; } catch (NumberFormatException nfe) { pauseTime = null; if (s.toUpperCase().equals("Y") || s.toUpperCase().equals("TRUE")) stepByStep = true; else stepByStep = false; } } else if (prm[i].toLowerCase().startsWith("-dburl")) { url = getPrmValue(prm[i], "-dbURL"); } else System.err.println("Unknown parameter [" + prm[i] + "], ignored."); } } private static boolean isolatePrmValue(String s, String p) { boolean ret = true; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { String val = s.substring(s.indexOf("=") + 1); if (val.toUpperCase().equals("Y") || val.toUpperCase().equals("TRUE")) ret = true; else if (val.toUpperCase().equals("N") || val.toUpperCase().equals("FALSE")) ret = false; else { System.err.println("Unrecognized value for " + p + ", set to y"); ret = true; } } } return ret; } private static String getPrmValue(String s, String p) { String ret = ""; if (s.length() > (p.length() + 1)) // +1 : "=" { if (s.indexOf("=") > -1) { ret = s.substring(s.indexOf("=") + 1); } } return ret; } /** * main * @param args */ public static void main(String[] args) { if (args.length > 0) setRunPrm(args); try { UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName()); } catch (Exception e) { e.printStackTrace(); } new SupplierWatcher(); } }
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|