Oracle® OLAP Developer's Guide to the OLAP API 10g Release 1 (10.1) Part Number B10335-02 |
|
|
View PDF |
This chapter explains the procedure for connecting to a data store through the OLAP API.
This chapter includes the following topics:
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".
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" .
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".
To make a connection, perform the following steps:
Load the JDBC driver for the connection.
Get a JDBC OracleConnection
from the DriverManager
.
Create a TransactionProvider
.
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.
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
try { Class.forName("oracle.jdbc.driver.OracleDriver"); } 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.
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; try { 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
.
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.
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); try { dp.initialize(); } 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".
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
.
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); try { dmlExec.initialize(); } 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.
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
.
dp.close();
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.