7 Configuring JDBC for Oracle CEP

7.1 Overview of Database Access from an Oracle CEP Application

Oracle CEP supports Java Database Connectivity (JDBC) 3.0 (see http://java.sun.com/products/jdbc/download.html#corespec30) for relational database access.

The JDBC API (see http://java.sun.com/javase/technologies/database/index.jsp) provides a standard, vendor-neutral mechanism for connecting to and interacting with database servers and other types of tabular resources that support the API. The JDBC javax.sql.DataSource interface specifies a database connection factory that is implemented by a driver. Instances of DataSource objects are used by applications to obtain database connections (instances of java.sql.Connection). After obtaining a connection, an application interacts with the resource by sending SQL commands and receiving results.

Oracle CEP provides the following JDBC drivers:

• Oracle 10.2.0 thin driver (see Section 7.1.1, "Oracle JDBC Driver")

• SQL Server Type 4 JDBC Driver from DataDirect (see Section 7.1.2, "Type 4 JDBC Driver for SQL Server from DataDirect")

Optionally, you can use your own JDBC driver (see Section 7.5, "Configuring Access to a Database Using Your Own Database Drivers").

Oracle CEP also provides a DataSource abstraction that encapsulates a JDBC driver DataSource object and manages a pool of pre-established connections. For more information, see Section 7.2, "Description of Oracle CEP Data Sources".

7.2 Description of Oracle CEP Data Sources

Oracle CEP DataSource provides a JDBC data source connection pooling implementation that supports the Java Database Connectivity (JDBC 3.0) specification. Applications reserve and release Connection objects from a data source using the standard DataSource.getConnection and Connection.close APIs respectively.

Figure 7-1 shows the relationship between data source, connection pool, and Connection instances.

Figure 7-1 Oracle CEP Data Source

Description of "Figure 7-1 Oracle CEP Data Source"

You must use the Oracle CEP server default data source or configure your own Oracle CEP DataSource in the server's config.xml file if you want to access a relational database:

• From an Oracle CQL processor rule.

  See "Configuring an Oracle CQL Processor Table Source" in the Oracle CEP IDE Developer's Guide for Eclipse.

• From an EPL processor rule.

  See "Configuring an EPL Processor" in the Oracle CEP IDE Developer's Guide for Eclipse.

• Event record and playback.

  See "Storing Events in the Persistent Event Store" in the Oracle CEP IDE Developer's Guide for Eclipse.

• From a cache loader or store.

  See:

  • "Configuring an Oracle CEP Local Cache Store" in the Oracle CEP IDE Developer's Guide for Eclipse

  • "Configuring an Oracle Coherence Cache Loader or Store" in the Oracle CEP IDE Developer's Guide for Eclipse

You do not have to configure a DataSource in the server's config.xml file if you use the JDBC driver's API, such as DriverManager, directly in your application code.

For more information, see:

• Section 7.2.1, "Default Data Source Configuration"

• Section 7.2.2, "Custom Data Source Configuration"

<data-source>
    <name>rdbms</name>
    <data-source-params>
        <global-transactions-protocol>None</global-transactions-protocol>
    </data-source-params>
    <connection-pool-params>
        <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
        <initial-capacity>5</initial-capacity>
        <max-capacity>10</max-capacity>
        <connection-creation-retry-frequency-seconds>
            60
        </connection-creation-retry-frequency-seconds>
    </connection-pool-params>
    <driver-params>
        <url>jdbc:oracle:thin:@localhost:5521:rdb</url>
        <driver-name>oracle.jdbc.OracleDriver</driver-name>
        <properties>
            <element><name>user</name><value>scott</value></element>
            <element><name>password</name><value>tiger</value></element>
        </properties>
        <use-xa-data-source-interface>true</use-xa-data-source-interface>
    </driver-params>
</data-source>
<transaction-manager>
    <name>TM</name>
    <rmi-service-name>RMI</rmi-service-name>
</transaction-manager>

7.3 Configuring Access to a Database Using the Oracle JDBC Driver

The Oracle JDBC driver is automatically installed with Oracle CEP and ready to use. For more information, see Section 7.1.1, "Oracle JDBC Driver".

To configure access to a database using the Oracle JDBC driver:

1. Configure the data source in the server's config.xml file:

   1. To update the Oracle CEP server config.xml file using the Configuration Wizard, see "Creating and Updating Oracle CEP Standalone-Server Domains" in the Oracle CEP Administrator's Guide.

   2. To update the Oracle CEP server config.xml file manually, see Section 7.2.2, "Custom Data Source Configuration."

   Note:

   The url element for the Oracle JDBC driver is of the form:

   <url>jdbc:oracle:thin:@HOST:PORT:SID</url>
   

2. If Oracle CEP is running, restart it so it reads the new data source information.

   For more information, see "Stopping and Starting the Server" in the Oracle CEP Administrator's Guide.

7.4 Configuring Access to a Database Using the Type 4 JDBC Drivers from Data Direct

The type 4 JDBC drivers from DataDirect for SQL Server are automatically installed with Oracle CEP and ready to use. For more information, see Section 7.1.2, "Type 4 JDBC Driver for SQL Server from DataDirect".

To configure access to a database using the Type 4 JDBC drivers from Data Direct:

1. Configure the data source in the server's config.xml file:

   1. To update the Oracle CEP server config.xml file using the Configuration Wizard, see "Creating and Updating Oracle CEP Standalone-Server Domains" in the Oracle CEP Administrator's Guide.

   2. To update the Oracle CEP server config.xml file manually, see Section 7.2.2, "Custom Data Source Configuration."

   Note:

   The url element for the type 4 JDBC drivers from DataDirect is of the form:

   <url>jdbc:weblogic:sqlserver://HOST:PORT</url>
   

2. If Oracle CEP is running, restart it so it reads the new data source information.

   For more information, see "Stopping and Starting the Server" in the Oracle CEP Administrator's Guide.

7.5 Configuring Access to a Database Using Your Own Database Drivers

Optionally, you can use your own JDBC driver with Oracle CEP. For more information, see Section 7.1, "Overview of Database Access from an Oracle CEP Application".

To configure access to a database using your own database drivers:

1. Update the server start script in the server directory of your domain directory so that Oracle CEP finds the appropriate JDBC driver JAR file when it boots up.

   The name of the server start script is startwlevs.cmd (Windows) or startwlevs.sh (UNIX), and the script is located in the server directory of your domain directory. The out-of-the-box sample domains are located in ORACLE_CEP_HOME/ocep_11.1/samples/domains, and the user domains are located in ORACLE_CEP_HOME/user_projects/domains, where ORACLE_CEP_HOME refers to the Oracle CEP installation directory, such as d:\oracle_cep.

   Update the start script by adding the -Xbootclasspath/a option to the Java command that executes the wlevs_3.0.jar file. Set the -Xbootclasspath/a option to the full pathname of the JDBC driver you are going to use.

   For example, if you want to use the Windows Oracle thin driver, update the java command in the start script as follows (updated section shown in bold):

   %JAVA_HOME%\bin\java -Dwlevs.home=%USER_INSTALL_DIR% -Dbea.home=%BEA_HOME%  -Xbootclasspath/a:%USER_INSTALL_DIR%\bin\com.bea.oracle.ojdbc14_10.2.0.jar -jar "%USER_INSTALL_DIR%\bin\wlevs_3.0.jar" -disablesecurity %1 %2 %3 %4 %5 %6 
   

   In the example, %USER_INSTALL_DIR% points to ORACLE_CEP_HOME\ocep_11.1.

2. Configure the data source in the server's config.xml file:

   1. To update the Oracle CEP server config.xml file using the Configuration Wizard, see "Creating and Updating Oracle CEP Standalone-Server Domains" in the Oracle CEP Administrator's Guide.

   2. To update the Oracle CEP server config.xml file manually, see Section 7.2.2, "Custom Data Source Configuration."

3. If Oracle CEP is running, restart it so it reads the new java option and data source information.

   For more information, see "Stopping and Starting the Server" in the Oracle CEP Administrator's Guide.