Example 6-31: Java stored procedure to retrieve and store Web stock quotes

import javax.servlet.http.*;
import oracle.xml.parser.v2.*;
import org.w3c.dom.*;
import java.net.URL;
import java.sql.*;
import java.io.*;
import JTidyConverter;
import XMLDocuments;
import Examples;

public class CaptureQuotes {
  private static CaptureQuotes cq = null;
  private Connection         conn = null;
  private JTidyConverter      jtc = null;
  private XSLStylesheet     sheet = null;
  private CallableStatement  stmt = null;
  private static final String YQUOTES = "http://quote.yahoo.com/q?d2=v1&o=d&s=";
  public CaptureQuotes(Connection conn) {
    this.conn = conn;
  }
  // Oracle8i Java Stored Procedure debugging entrypoint for testing.
  public static void debug_main() throws Exception {
    storeLatestQuotesFor("ORCL,INTC,MSFT");
  }
  // Static method to expose as Java Stored Procedure
  public static void storeLatestQuotesFor(String symbolList) throws Exception {
    if (cq == null) {
      cq = new CaptureQuotes(Examples.getConnection());
      cq.initialize();
    }
    cq.retrieve(symbolList);
  }
  // Retrieve Yahoo Quotes and Save quote data in a table
  private void retrieve(String symbolList) throws Exception {
    if (symbolList != null && !symbolList.equals("")) {
      URL yahooUrl  = new URL(YQUOTES+symbolList.replace(',','+'));
      // Convert the dynamically-produced Yahoo Quotes page to XML doc
      XMLDocument yahooquotes = jtc.XMLifyHTMLFrom(yahooUrl);
      // Transform the document using our stylesheet into <QuoteStream>
      // getting the transformed result in a DocumentFragment
      XSLProcessor xslt       = new XSLProcessor();
      DocumentFragment result = xslt.processXSL(sheet,yahooquotes);
      // Get the document element of the transformed document
      XMLElement e = (XMLElement)result.getFirstChild();
      // Search for all <Quotes> in the resulting <QuoteStream>
      NodeList quotes = e.selectNodes(".//Quote");
      int matches = quotes.getLength();
      // Loop over any quotes retrieved; insert each by calling stored proc
      for (int z = 0; z < matches; z++) {
        XMLNode curQuote = (XMLNode)quotes.item(z);
        // Bind the 1st stored procedure argument to valueOf Ticker attribute
        stmt.setString(1,curQuote.valueOf("@Ticker"));
        // Bind the 2ND stored procedure argument to valueOf Price attribute
        stmt.setString(2,curQuote.valueOf("@Price"));
        // Execute the stored procedure to process this quote
        stmt.executeUpdate();
      }
      conn.commit();
    }
  }
  // Setup proxy server, Cache XSL Transformation, and Callable Statement
  private void initialize() throws Exception {
    if (jtc == null) {
      // Make sure the Servlet can "see" through the corporate firewall...
      System.setProperty("proxySet","true");
      System.setProperty("proxyHost","yourproxyserver.you.com");
      System.setProperty("proxyPort","80");
      // Construct a JTidyConverter. We can use the same one over and over.
      jtc   = new JTidyConverter();
      XMLDocuments.enableXMLDocURLs();
      // Read the Yahoo2Xml.xsl stylesheet from an xmldoc:// URL in the DB
      URL u = new URL("xmldoc:/transforms/YahooQuotes-to-QuoteStream.xsl");
      InputStream styleSource = u.openStream();
      // Cache a new stylesheet. Not threadsafe in 2.0.2.7 but ok for demo.
      sheet = new XSLStylesheet(styleSource,null); // No base URL needed here!
      // Cache a reusable CallableStatement for invoking the PL/SQL Stored Proc
      stmt = conn.prepareCall("BEGIN insert_quote(?,?,SYSDATE); END;");
    }
  }
}