//---------------------------------------------------------------------------||
// xmlfromdb2.java                                                           ||
//                                                                           ||
// This file shows how to generate XML data from a JDBC query.  For this     ||
// code to work, you need to have DB2 installed, along with the XML Extender ||
// and the sample database built in the XML Extender tutorial.  If you use   ||
// another database, or another database server, you'll need to change the   ||
// JDBC initialization code appropriately.                                   ||
//                                                                           ||
// You'll also need to change the DbOwner, DbUserid, and DbPasswd variables  ||
// to the values for your system.                                            ||
//---------------------------------------------------------------------------||
// Written 17 February 2000 by Doug Tidwell.                                 ||
//---------------------------------------------------------------------------||
//                                                                           ||
// (C) Copyright IBM Corp. 1999, 2000.  All rights reserved.                 ||
//                                                                           ||
// US Government Users Restricted Rights Use, duplication or                 ||
// disclosure restricted by GSA ADP Schedule Contract with IBM Corp.         ||
//                                                                           ||
// The program is provided "as is" without any warranty express or           ||
// implied, including the warranty of non-infringement and the implied       ||
// warranties of merchantibility and fitness for a particular purpose.       ||
// IBM will not be liable for any damages suffered by you as a result        ||
// of using the Program. In no event will IBM be liable for any              ||
// special, indirect or consequential damages or lost profits even if        ||
// IBM has been advised of the possibility of their occurrence. IBM          ||
// will not be liable for any third party claims against you.                ||
//                                                                           ||
//---------------------------------------------------------------------------||

// Import base Java classes
import java.io.*;
import java.net.*;
import java.util.*;
import java.lang.*;
import java.awt.*;
import java.sql.*;
import java.util.*;
import java.text.*;


// Import servlet classes
import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.xerces.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;

/**
 * This class illustrates how to parse an XML document and use the resulting
 * document tree.  The document tree conforms to the W3C's DOM standard.
 */

public class xmlfromdb2 extends HttpServlet
{
	static String url=null;

  // Strings to hold password info
	static String DbOwner = null;
	static String DbUserid = null;                             
	static String DbPasswd = null;

  // Strings to build the JDBC URL
	static String Db      = "db2";                                   
	static String DbName  = "sales_db";
	static String JDBCDriver = "COM.ibm.db2.jdbc.app.DB2Driver";
	static String DbSource ="jdbc:" + Db + ":" + DbName;

  // JDBC connections
  static int [] Constat = {0,0};
	static Connection con0;
	static Connection con1;
	private static boolean dbconnect = false;
  
  static
  {
    // When the first instance of this class is created, we register
    // the driver with DriverManager.  
    try
    {
      Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
    }
    catch (Exception e)
    {
      System.out.println("Can't get the driver!"); e.printStackTrace();
    }
  }

  // We'll connect to the database during init()

  public void init(ServletConfig cfig)
  {
    
    /////////////////////////////////////////////////////////////////
    // Be sure to change these three strings appropriately, or the //
    // servlet won't work.                                         //
    /////////////////////////////////////////////////////////////////
    
    DbUserid = "xxxxxxxx";
    DbPasswd = "xxxxxxxx";
    DbOwner = "xxxxxxxx";

    // URL is jdbc:db2:sales_db
    url = "jdbc:" + Db + ":" + DbName;

    try
    {
      // if webserver and DB on same machine and using same userid and password,
      // connect with default id/password
      if (DbUserid.equals ("null")) 
      {
        con0 = DriverManager.getConnection(url);
        con1 = DriverManager.getConnection(url);
      }
      // execute these lines if webserver and DB not on same machine
      // connect with specific id/password
      else
      {
        con0 = DriverManager.getConnection(url, DbUserid, DbPasswd);
        con1 = DriverManager.getConnection(url, DbUserid, DbPasswd);
      }

      con0.setAutoCommit(false);
      con1.setAutoCommit(false);
      if ((con0 != null) && (con1 != null))
        dbconnect = true;
    }
    catch( Exception e )
    {
      e.printStackTrace();
    }
    if (con0 == null)
      System.out.println("con0 is null");
    if (con1 == null)
      System.out.println("con1 is null");
  }

  // service() is where the real work gets done
  public void service(HttpServletRequest req, HttpServletResponse res) 
      throws ServletException, IOException
  {
    PrintWriter out = res.getWriter();

    // We hardcoded the SQL statement here; this would be more
    // sophisticated if we qualified the query based on user input.
    String query = "select order from " + DbOwner + ".sales_order_view";

    res.setContentType("text/xml");

    try
      {
        ConInfo index = new ConInfo();
        Connection con = getCon(index);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        // Now we create an XML document.  The first thing we
        // need to do is write the XML header.  Notice also that
        // we use the flights document type definition (DTD) to
        // tell the XML parser what tags we're using and how the
        // tags relate to each other.
        
        out.print("<?xml version=\"1.0\" ?>\n");
        out.print("<orders>\n");
        
        DOMParser parser = new DOMParser();
        
        // Display the result set.  We take each row and convert the
        // fields into the tags we're using.  rs.next() returns
        // false when there are no more rows.
        while (rs.next())
          { 
            String nextOrder = rs.getString(1).trim();
            Document doc = null;
            StringReader sr = new StringReader(nextOrder);
            InputSource iSrc = new InputSource(sr);
            
            try
              {
                parser.parse(iSrc);
                doc = parser.getDocument();
              }
            catch (Exception e)
              {
                System.err.println("Sorry, an error occurred: " + e);
              }
            
            if (doc != null)
              printDOMTree(doc, out);
          }
        
        rs.close();
        stmt.close();
        putCon(index.index);
      }
    catch (Exception e) {e.printStackTrace();}
    out.print("</orders>\n");
  }


  /** Prints the specified node, recursively. */
  public void printDOMTree(Node node, PrintWriter out) 
  {
    int type = node.getNodeType();
    switch (type)
    {
      // print the document element
      case Node.DOCUMENT_NODE: 
        {
          printDOMTree(((Document)node).getDocumentElement(), out);
          break;
        }

        // print element with attributes
      case Node.ELEMENT_NODE: 
        {
          out.print("<");
          out.print(node.getNodeName());
          NamedNodeMap attrs = node.getAttributes();
          for (int i = 0; i < attrs.getLength(); i++)
          {
            Node attr = attrs.item(i);
            out.print(" " + attr.getNodeName() + 
                      "=\"" + attr.getNodeValue() + 
                      "\"");
          }
          out.print(">");

          NodeList children = node.getChildNodes();
          if (children != null)
          {
            int len = children.getLength();
            for (int i = 0; i < len; i++)
              printDOMTree(children.item(i), out);
          }

          break;
        }

        // handle entity reference nodes
      case Node.ENTITY_REFERENCE_NODE: 
        {
          out.print("&");
          out.print(node.getNodeName());
          out.print(";");
          break;
        }

        // print cdata sections
      case Node.CDATA_SECTION_NODE: 
        {
          out.print("<![CDATA[");
          out.print(node.getNodeValue());
          out.print("]]>");
          break;
        }

        // print text
      case Node.TEXT_NODE: 
        {
          out.print(node.getNodeValue());
          break;
        }

        // print processing instruction
      case Node.PROCESSING_INSTRUCTION_NODE: 
        {
          out.print("<?");
          out.print(node.getNodeName());
          String data = node.getNodeValue();
          {
            out.print(" ");
            out.print(data);
          }
          out.print("?>");
          break;
        }
    }

    if (type == Node.ELEMENT_NODE)
    {
      out.print("</");
      out.print(node.getNodeName());
      out.print('>');
    }
  } // printDOMTree(Node, PrintWriter)

  // get a database connection for a statement to use
  private synchronized Connection getCon(ConInfo index)
  { 
    int i = 0;
        
    if (Constat[0] == 0)
    {
      index.index = 0;
      Constat[0]=1;
      return (con0);
    }   
    else if (Constat[1] == 0)
    {
      index.index = 1;
      Constat[1]=1;
      return (con1); 
    }   
    else 
    {
      index.index = -1;
      return(null); 
    }
  }

  // release the database connection so it can be reused
  private synchronized void putCon(int index)
  {
    Constat[index] = 0;
  }
} //end of class parseXML

class ConInfo
{
  int index;
   
  ConInfo()
  {
    this.index = 0;
  }  
} //end class ConInfo