/*
 * Tutorial_2.java
 *
 * Copyright 2004-2006 by SAP AG. All Rights Reserved.
 * SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP 
 * products and services mentioned herein as well as their respective logos 
 * are trademarks or registered trademarks of SAP AG in Germany and in several 
 * other countries all over the world. All other product and service names 
 * mentioned are the trademarks of their respective companies. Data contained 
 * in this document serves informational purposes only. National product 
 * specifications may vary.
 *
 * These materials are subject to change without notice. These materials are 
 * provided by SAP AG and its affiliated companies ("SAP Group") for 
 * informational purposes only, without representation or warranty of any kind, 
 * and SAP Group shall not be liable for errors or omissions with respect to 
 * the materials. The only warranties for SAP Group products and services are 
 * those that are set forth in the express warranty statements accompanying 
 * such products and services, if any. Nothing herein should be construed as 
 * constituting an additional warranty.
 */
package com.sap.ip.bi.sdk.samples;

/*
 * This sample class depends on the presence of your JDBC  
 * database's drivers in your classpath. If your JDBC database
 * is not properly configured, this example will fail.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.resource.spi.ManagedConnectionFactory;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.omg.cwm.resource.relational.Column;
import org.omg.cwm.resource.relational.Table;

import com.sap.exception.IBaseException;
import com.sap.ip.bi.sdk.dac.connector.IBIConnection;
import com.sap.ip.bi.sdk.dac.connector.IBIRelational;
import com.sap.ip.bi.sdk.dac.connector.jdbc.JdbcConnectionConstants;
import com.sap.ip.bi.sdk.dac.connector.jdbc.JdbcManagedConnectionFactory;
import com.sap.ip.bi.sdk.dac.relational.query.IBICommandProcessor;
import com.sap.ip.bi.sdk.dac.relational.query.IBIQuery;
import com.sap.ip.bi.sdk.samples.servlet.MinimalServletContainer;
import com.sap.ip.bi.sdk.util.impl.BIResourceProperties;
import com.sapportals.connector.connection.IConnectionFactory;
import com.sapportals.connector.connection.IConnectionSpec;

/**
 * Getting Started - 
 * 
 * Demonstrates how to connect to a JDBC database using
 * the BI JDBC Connector, retrieve a table from the data source,
 * create a query, execute it, and render the result set into an
 * HTML table. 
 * 
 * View the HTML rendered by this servlet in the following file: 
 * [SDK archive]/docs/examples/tutorial_2.result.html
 *
 * @author SAP
 * @version 3.50
 * @since 3.50
 */
public class Tutorial_2 extends HttpServlet {
  private final static String CONTENT_TYPE = "text/html";

  // Load connection properties.
  private static Properties connProp =
    new BIResourceProperties(Helpers.class, ".jdbc");

  public void init(ServletConfig config) throws ServletException {
    super.init(config);
  }

  // Processes the HTTP Get request.

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);

    PrintWriter out = response.getWriter();

    try {
      // ********************************************************
      // Connect to a JDBC database via the BI JDBC Connector.
      // ********************************************************
      ManagedConnectionFactory mcf;
      IConnectionFactory cf;
      IConnectionSpec cs;

      // Setting up the connectionDescriptor.
      // The URL used to connect to a JDBC database is vendor-
      // specific. JDBC URLs have the following components:
      // <protocol>:<subprotocol>:<subname>
      // where <protocol> is always "jdbc". Both <subprotocol>
      // and <subname>, however, are vendor-specific, dependent upon
      // the actual JDBC driver used.
      mcf = new JdbcManagedConnectionFactory();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();

      // Set the properties needed for the JDBC connection
      // See Helpers.jdbc.properties (and optional
      // Helpers.jdbc.local.properties) for details.

      // user
      cs.setPropertyValue(
        JdbcConnectionConstants.USERNAME.toString(),
        connProp.getProperty(JdbcConnectionConstants.USERNAME.toString()));
      // password
      cs.setPropertyValue(
        JdbcConnectionConstants.PASSWORD.toString(),
        connProp.getProperty(JdbcConnectionConstants.PASSWORD.toString()));
      // URL
      cs.setPropertyValue(
        JdbcConnectionConstants.URL.toString(),
        connProp.getProperty(JdbcConnectionConstants.URL.toString()));
      // JDBC driver
      cs.setPropertyValue(
        JdbcConnectionConstants.DRIVERNAME.toString(),
        connProp.getProperty(JdbcConnectionConstants.DRIVERNAME.toString()));

      // Establishing the connection.
      // The IBIRelational interface provides an entrypoint to access
      // metadata and execute queries.
      IBIConnection connection = (IBIConnection) cf.getConnectionEx(cs);
      IBIRelational rel = connection.getRelational();

      // ********************************************************
      // Retrieve metadata.
      // ********************************************************
      // We retrieve the metadata using the getTables method provided
      // by IBIRelational.
      List tables = rel.getTable();
      if (tables == null || tables.size() == 0) {
        throw new ServletException("no tables found");
      }
      // Retrieve the first table in the list.
      Table table = (Table) tables.get(0);

      // Retrieve the list of columns of the chosen table.
      List columns = table.getFeature();
      if (columns == null || columns.size() == 0) {
        throw new ServletException("no columns found");
      }
      // ********************************************************
      // Create a query and a command processor associated with 
      // this query. Unlike OLAP queries created via IBIOlap,
      // relational queries have no initial state.
      // ********************************************************
      IBIQuery query = rel.createQuery();
      IBICommandProcessor commandProcessor = query.getCommandProcessor();

      // ********************************************************
      // Add the table.
      // As relational queries support multiple tables and even
      // multiple instances of the same table in one and the same
      // query, each instance of a table in the query has to be
      // uniquely identifiable. This is achieved by using references
      // (String) identifying the particular instance of the table
      // in the query. These references can either be provided when
      // adding the table or (as below) generated by the command 
      // processor when adding the table.
      // ********************************************************
      String tref = commandProcessor.addTable(table);

      // ********************************************************
      // Add the columns.
      // Add all the columns of the table to the query.
      // ********************************************************
      for (Iterator c = columns.iterator(); c.hasNext();) {
        Column column = (Column) c.next();
        commandProcessor.addColumn(column, tref);
      }

      // ********************************************************
      // Execute the query and retrieve the result set.
      // ********************************************************
      ResultSet result = query.execute();

      // ********************************************************
      // Render the result set.
      // We use BIDataSetTableModel for rendering to a table. 
      // This provides a projection of the data set into a 
      // tabular view where cells can be accessed by row and 
      // column coordinates.
      // We also rely on Helpers.java for HTML doctype 
      // and stylesheet information to help format the output.
      // ********************************************************
      out.println(Helpers.getDocTypeDefinition());
      out.println("<html>");
      out.println("<head><title>Tutorial_2</title>");
      out.println(Helpers.getStyleSheetDefinition());
      out.println("</head><body>");

      // print the generated SQL statement for this query
      out.println(
        "<p><b>SQL Statement that was executed:</b><br> <code>"
          + (String)rel.getNativeRepresentation(query)
          + "</code><p>");

      // Display the result set of the query.
      Helpers.renderResultset(out, result);

      out.println("</body>");
      out.println("</html>");
    }
    // Catch errors.
    catch (Exception e) {
      // $JL-EXC$
      e.printStackTrace();
      if (e instanceof IBaseException)
        out.println("Error: " +
                ((IBaseException)e).getNestedLocalizedMessage());
      else  
        out.println("Error: " + e.getMessage());        
    }
  }

  public void destroy() {
  }

  public static void main(String[] args) {
    if (args.length == 1) {
      MinimalServletContainer.executeServlet(new Tutorial_2(), args[0]);
    } else {
      MinimalServletContainer.executeServlet(new Tutorial_2(), System.out);
    }
  }

}