3 Connecting to a Data Store

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".

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.

try 
{
  Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{
  System.out.println("Could not load the JDBC driver. " + e);
}

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

ExpressTransactionProvider tp = new ExpressTransactionProvider();

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

Getting an Existing Connection

To use 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.

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

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. The conn objects is an OracleConnection. The executeCommand method of the SPLExecutor 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.

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

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.

dp.close();     // dp is the DataProvider

When you have completed your work with the data store, use the OracleConnection.close method.

try
{
  conn.close();     // conn is the OracleConnection
}
catch(SQLException e) 
{
  System.out.println("Cannot close the connection. " + e);
}