Listing 1

// Get list of orders placed since yesterday
ResultSet PurchaseOrder = stmt.executeQuery (
"select CUSTOMERID, ORDERID, QUANTITY, ITEM, EXTRADETAIL
from ORDERS
where ORDERDATE > {fn CURDATE()} - 1");

while (PurchaseOrder.next() ) {
int CustomerId = PurchaseOrder.getInt ("CUSTOMERID");
int OrderId = PurchaseOrder.getInt ("ORDERID");
int Quantity = PurchaseOrder.getInt ("QUANTITY");
String Item = PurchaseOrder.getString ("ITEM");
String Item = PurchaseOrder.getString ("EXTRADETAIL");

// internal business rules dictate how purchase order is processed
...

}

Listing 2

// Iterate through customer sites to see if there are orders

// we'll use two JDBC connections ... one for our internal Oracle tables and one to the
// XML streaming information from the web

// Oracle ...
Connection Oraclecon = DriverManager.getConnection (OracleDriverURL, uid, pwd);
Statement stmt = Oraclecon.createStatement()

// XML ...
Connection XMLCon = DriverManager.getConnection (XMLDriverURL, Xuid, Xpwd);
Statement XMLstmt = XMLCon.createStatement();

// Internally, we'll have an Oracle table called CUSTOMERS that tracks all our customers
// as well as from which URL we can obtain their order information ... provided they can
// post e-orders

// For Customers that can process orders online ... get the URL where we collect order info.
// The URL will have a form similar to HTTP://www.acme.com/MyCoorders.asp
ResultSet Customer = stmt.executeQuery (
"select CUSTOMERID, CUSTOMERURL
from CUSTOMERS
where ONLINEORDERS = 'T' ");

while (Customer.next() ) {
int CustomerId = Customer.getInt ("CUSTOMERID");
String CustomerURL = Customer.getString ("CUSTOMERURL");

// see if the customer placed any orders by reading a dynamically generated XML document
// that contains their order information from their website and, if so,
// insert the order into our ORDERS table
ResultSet NewOrder = XMLstmt.executeQuery (
"select QUANTITY, ITEM, DETAIL from " + CustomerURL +
"where ORDERDATE > {fn CURDATE()} - 1");

while (NewOrder.next() ) {

// customer placed order ... gather data and insert the order into our Oracle ORDERS
table
...

}

}

Listing 3

// Build a copy of the catalog as an XML Document that can be exposed to customers

// Query for the products currently available and include the FOR XML AUTO clause
// which will instruct SQL Server to return the result as an XML Document
ResultSet OurCatalog = stmt.executeQuery (
"select PRODUCTID, DESCRIPTION, PRICE from PRODUCTS
where PRODUCTFORSALE = 1 FOR XML AUTO");

while (OurCatalog..next() ) {
// 3 columns selected but only 1 returned ... the XML Document that corresponds
// to the result set produced from the query
String XMLCatalog = OurCatalog.getString (1);

// the XML Document now sits in our XMLCatalog data structure which can
// be written to a file

...
}

Listing 4

// Build a copy of the catalog as an XML Document that can be exposed to customers

// Query for the products currently available, which will result in an XML-formatted result
StringBuffer OurCatalog = new StringBuffer();
OurCatalog.append ("select xml_element ('product-id',p.PRODUCTID),");
OurCatalog.append (" xml_element('product-description',p.DESCRIPTION),");
OurCatalog.append (" xml_element('product-price',p.PRICE) ");
OurCatalog.append (" from PRODUCTS p where p.PRODUCTFORSALE = 1");

// Construct object to execute
JXTRQuery ProductCatalog = new JXTRQuery (conn, new String (OurCatalog));

// Execute, generate XML, and write to file
...
generateImplicitRoot=true;
ProductCatalog.executeWriter (systemOutWriter,
generateImplicitRoot,systemOutWriter.getEncoding,2);