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;"); } } } |