7 Configuring JDBC for Oracle CEP

This section contains information on the following subjects:

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:

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.1.1 Oracle JDBC Driver

Oracle CEP includes the Oracle 10.2.0 Thin driver packaged in the following JAR files:

  • ORACLE_CEP_HOME/modules/com.bea.oracle.ojdbc5_11.1.0.6.jar: for use with Java SE 5.

  • ORACLE_CEP_HOME/modules/com.bea.oracle.ojdbc6_11.1.0.6.jar: for use with Java SE 6.

The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client side. The JDBC Thin driver communicates with the server using SQL*Net to access the Oracle Database.

For more information, see:

7.1.2 Type 4 JDBC Driver for SQL Server from DataDirect

Oracle CEP provides a Type 4 JDBC driver from DataDirect for high-performance JDBC access to the SQL Server database. The Type 4 JDBC driver is optimized for the Java environment, allowing you to incorporate Java technology and extend the functionality and performance of your existing system.

The Oracle CEP Type 4 JDBC drivers from DataDirect are proven drivers that:

  • Support performance-oriented and enterprise functionality such as distributed transactions, savepoints, multiple open result sets and parameter metadata.

  • Are Java EE Compatibility Test Suite (CTS) certified and tested with the largest JDBC test suite in the industry.

  • Include tools for testing and debugging JDBC applications.

For more information, see:

7.1.3 Supported Databases

Oracle CEP server supports different databases depending on the type of JDBC driver you use:

7.1.3.1 Databases Supported by the Oracle JDBC Driver

Using the Oracle JDBC driver, you can access the following Oracle databases:

  • Oracle Database 10g release 2 (10.2)

For more information, see Section 7.1.1, "Oracle JDBC Driver".

7.1.3.2 Databases Supported by the Type 4 JDBC Driver for SQL Server from DataDirect

Using the SQL Server Type 4 JDBC Driver from DataDirect, you can access the following SQL Server databases:

  • Microsoft SQL Server 2005

  • Microsoft SQL Server 2000

  • Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)

  • Microsoft SQL Server 2000 Enterprise Edition (64-bit)

  • Microsoft SQL Server 7.0

For more information, see Section 7.1.2, "Type 4 JDBC Driver for SQL Server from DataDirect".

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 follows
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:

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:

7.2.1 Default Data Source Configuration

By default, the Oracle CEP server creates a local transaction manager. The transaction manager in turn depends on a configured RMI object, as described in Section 6.2.2, "rmi Configuration Object." Oracle CEP server guarantees that there will never be more than one transaction manager instance in the system.

If a database is unavailable at the time you start Oracle CEP server, by default, an Oracle CEP server data source retries every 10 seconds until it can create a connection. This allows the Oracle CEP server to successfully start up even if a database is unavailable. You can change the retry interval in the Oracle CEP server config.xml file using the connection-pool-params element connection-creation-retry-frequency-seconds child element. Setting this element to zero disables connection retry.

For more information, see Section 7.2.2, "Custom Data Source Configuration".

7.2.2 Custom Data Source Configuration

The Oracle CEP server config.xml file requires a configuration element for each data source that is to be created at runtime that references an external JDBC module descriptor.

This section describes how to configure a custom data source configuration. For more information on default data source configuration, see Section 7.2.1, "Default Data Source Configuration".

When you create an Oracle CEP domain using the Configuration Wizard, you can optionally configure a JDBC data source that uses one of the two DataDirect JDBC drivers; in this case the wizard updates the config.xml file for you. You configure the data source with basic information, such as the database you want to connect to and the connection username and password. You can also use the Configuration Wizard to update an existing server in a domain and add new data sources.

For more information, see:

You can also update the config.xml file manually by adding a data-source element as Example 7-1 shows.

Example 7-1 Custom Data Source Configuration in Oracle CEP Server config.xml

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

A data source depends on the availability of a local transaction manager. You can rely on the default Oracle CEP server transaction manager or configure one using the transaction-manager element of config.xml as Example 7-1 shows. The transaction manager in turn depends on a configured RMI object, as described in Section 6.2.2, "rmi Configuration Object."

If a database is unavailable at the time you start Oracle CEP server, by default, an Oracle CEP server data source retries every 10 seconds until it can create a connection. This allows the Oracle CEP server to successfully start up even if a database is unavailable. Example 7-1 shows how you can change the retry interval in the Oracle CEP server config.xml file using the connection-pool-params element connection-creation-retry-frequency-seconds child element. Setting this element to zero disables connection retry.

For the full list of child elements of the data-source element, in particular the connection-pool-params and data-source-params elements, see "Server Configuration XSD Schema: wlevs_server_config.xsd" in the Oracle CEP IDE Developer's Guide for Eclipse.

For information on security configuration tasks that affect JDBC, see Section 4.8.3, "Configuring JDBC Security".

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.