Skip Headers

Oracle® OLAP Developer's Guide to the OLAP API
10g Release 1 (10.1)

Part Number B10335-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

3 Connecting to a Data Store

This chapter explains the procedure for connecting to a data store through the OLAP API.

This chapter includes the following topics:

3.1 Overview of the Connection Process

When an application gains access to data through the OLAP API, it uses a connection provided by the Oracle implementation of the Java Database Connectivity (JDBC) API from Sun Microsystems. For information about using this JDBC implementation, see the Oracle Database JDBC Developer's Guide and Reference.

The Oracle JDBC classes that you use to establish a connection to Oracle OLAP are in the classes12.jar file. For information about getting the JDBC Java archive (jar) file, see Appendix A, " Setting Up the Development Environment".

3.1.1 Connection Steps

The procedure for connecting involves loading an Oracle JDBC driver, getting a connection through that driver, and creating two OLAP API objects that handle transactions and data transfer.

These steps are described in the topic "Establishing a Connection" .

3.1.2 Prerequisites for Connecting

Before attempting to make an OLAP API connection to an Oracle database, ensure that the following requirements are met:

  • The Oracle Database instance is running and was installed with the OLAP option.

  • The Oracle Database user ID that you are using for the connection has access to the relational schemas on which the data store is based.

  • The Oracle JDBC and OLAP API jar files are on your application development computer and are accessible to the application code. For information about setting up the required jar files, see Appendix A, " Setting Up the Development Environment".

3.2 Establishing a Connection

To make a connection, perform the following steps:

  1. Load the JDBC driver for the connection.

  2. Get a JDBC OracleConnection from the DriverManager.

  3. Create a TransactionProvider.

  4. Create a DataProvider.

These steps are explained in more detail in the rest of this topic.

Note that the TransactionProvider and DataProvider objects that you create in these steps are the ones that you use throughout your work with the data store. For example, when you create certain Source objects, you use methods of this DataProvider object.

3.2.1 Step 1: Load the JDBC Driver

The following line of code loads a JDBC driver and registers it with the JDBC DriverManager.

Example 3-1 Loading the JDBC Driver for a Connection

catch(ClassNotFoundException e)
  System.out.println("Could not load the JDBC driver. " + e);

After the driver is loaded, you can use the DriverManager object to make a connection. For more information about loading Oracle JDBC drivers, see the Oracle Database JDBC Developer's Guide and Reference.

3.2.2 Step 2: Get a Connection from the DriverManager

The following code gets a JDBC OracleConnection object from the DriverManager.

Example 3-2 Getting a JDBC OracleConnection

String url = "jdbc:oracle:thin:@myhost:1521:orcl";
String user = "global";
String password = "global";
oracle.jdbc.OracleConnection conn = null;
  conn = (oracle.jdbc.OracleConnection)
         java.sql.DriverManager.getConnection(url, user, password);
Catch(SQLException e) 
  System.out.println("Connection attempt failed. " + e);

This example connects the user global, who has the password global, to a database with the SID (system identifier) orcl. The connection is made through TCP/IP listener port 1521 of host myhost. The connection uses the Oracle JDBC thin driver.

There are many ways to specify your connection characteristics using the getConnection method. See the Oracle Database JDBC Developer's Guide and Reference for details.

After you have the OracleConnection object, you can create the required OLAP API objects, TransactionProvider and DataProvider.

3.2.3 Step 3: Create a TransactionProvider

TransactionProvider is an OLAP API interface that is implemented for Oracle OLAP by the ExpressTransactionProvider concrete class. In your code, you create an instance of ExpressTransactionProvider, as in the following example.

Example 3-3 Creating a TransactionProvider

ExpressTransactionProvider tp = new ExpressTransactionProvider();

A TransactionProvider is required for creating a DataProvider.

3.2.4 Step 4: Create a DataProvider

DataProvider is an OLAP API abstract class. The concrete class ExpressDataProvider extends DataProvider. The following lines of code create and initialize an ExpressDataProvider.

Example 3-4 Creating a DataProvider

ExpressDataProvider dp = new ExpressDataProvider(conn, tp);
catch(SQLException e)
  System.out.println("Could not initialize the DataProvider. " + e);

A DataProvider is required for creating a MetadataProvider, which is described in Chapter 4, " Discovering the Available Metadata".

3.3 Getting an Existing Connection

If you need access to the JDBC OracleConnection object after the connection has been established, you can call the getConnection method of your ExpressDataProvider. The following line of code calls the getConnection method of dp, which is an ExpressDataProvider.

Example 3-5 Getting an Existing Connection

oracle.jdbc.OracleConnection currentConn = dp.getConnection();

3.4 Executing DML Commands Through the Connection

Some applications depend on the run-time execution of Oracle OLAP data manipulation language (DML) commands or programs. DML commands and programs execute in an analytic workspace outside the context of MDM metadata, which is intrinsic to the OLAP API. Therefore, such commands and programs do not operate on MDM objects, such as an MdmMeasure or an MdmDimension. Instead, they operate on DML objects, such as a variable or a dimension. The MDM and DML contexts are related but distinct.

To execute DML commands or programs in an analytic workspace, create an OLAP API SPLExecutor object, specifying the JDBC OracleConnection object that you want to use. Note that the data manipulation language is sometimes referred to as a stored procedure language (SPL).

Example 3-6 creates and initializes an SPLExecutor object with a JDBC OracleConnection object called conn. With the executeCommand method of the SPLExecutor object, it passes to Oracle OLAP a DML command that attaches an analytic workspace named myworkspace.

For the complete code for the following example, see the example programs available from the Overview of the Oracle OLAP Java API Reference.

Example 3-6 Executing DML Commands

SPLExecutor dmlExec = new SPLExecutor(conn);
catch(SQLException e)
  System.out.println("Cannot initialize the SPL executor. " + e);
String returnVal = dmlExec.executeCommand('aw attach myworkspace');

For information about using the DML, see the Oracle OLAP DML Reference. For more information about using an SPLExecutor, see the Oracle OLAP Java API Reference.

3.5 Closing a Connection

If you are finished using the OLAP API, but you want to continue working in your JDBC connection to the database, then use the close method of your DataProvider to release the OLAP API resources. In the following code, the DataProvider is named dp.


When you have completed your work with the data store, use the close method of the JDBC OracleConnection object. In the following example, conn is the OracleConnection object.

Example 3-7 Closing a Connection

catch(SQLException e) 
  System.out.println("Cannot close the connection. " + e);