/* * 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); } } }