12 JDBC

You can configure Java Database Connectivity (JDBC) for relational database access from an Oracle Stream Analytics application.

Oracle Stream Analytics supports JDBC 4.0. To download JDBC, go to http://java.sun.com/products/jdbc/download.html.

This chapter includes the following sections:

12.1 Database Access

The JDBC API provides a standard, vendor-neutral way for applications to connect to and interact with database servers and other types of tabular resources that support the JDBC API.

A database driver can implement the JDBC javax.sql.DataSource interface to define a database connection factory. Applications use the DataSource objects to obtain database connections (java.sql.Connection). An application obtains a connection and interacts with the data resource by sending SQL commands and receiving results.

Oracle Stream Analytics provides a DataSource abstraction that encapsulates a JDBC driver DataSource object and manages a pool of pre-established connections. Also, the Oracle WebLogic Server WLConnection interface provides methods that enable access to and manipulation of Oracle data sources. For more information, see Oracle Stream Analytics Data Sources.

Oracle Stream Analytics provides the Oracle 12c thin driver. Optionally, you can use your own JDBC driver. See Access a Database Driver with bootclasspath.

12.1.1 Oracle JDBC Driver

Oracle Stream Analytics includes the Oracle 12c Thin driver for use with Java SE 7. The JDBC Thin driver is a pure Java, Type IV driver that you can be use 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.

The Oracle 12c Thin drive is in the following JAR file:

/Oracle/Middleware/wlserver/modules/com.bea.oracle.ojdbc6_1.0.0.0_11-2-0-3-0.jar

For more information, see:

12.1.2 Supported Databases

Oracle Stream Analytics servers support different databases depending on the type of JDBC driver you use.

Oracle JDBC Driver

Using the Oracle JDBC driver, you can access the Oracle Database 12c release. See Oracle JDBC Driver.

SQL Server Type 4 JDBC Driver from DataDirect

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

Microsoft SQL Server 2012
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.

12.2 Oracle Stream Analytics Data Sources

An Oracle Stream Analytics DataSource provides a JDBC data source connection pooling implementation that supports the JDBC 4.0 specification.

Applications reserve and release Connection objects from a data source with the standard DataSource.getConnection and Connection.close APIs respectively.

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

Figure 12-1 Oracle Stream Analytics Data Source

Description of "Figure 12-1 Oracle Stream Analytics Data Source"

You must use the Oracle Stream Analytics server default data source or configure your own Oracle Stream Analytics DataSource in the server's config.xml file if you want to access a relational database in any of the following ways. See Schema Reference for Oracle Stream Analytics.

From an Oracle CQL processor rule.
Event record and playback.
From a cache loader or store.

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.

12.2.1 Default Data Source Configuration

By default, the Oracle Stream Analytics server creates a local transaction manager. The transaction manager depends on a configured RMI object, as described in JMX Configuration Objects. Oracle Stream Analytics server guarantees that there will never be more than one transaction manager instance in the system.

If a database is unavailable when you start Oracle Stream Analytics server, by default, an Oracle Stream Analytics server data source retries every 10 seconds until it creates a connection. The retries enable the Oracle Stream Analytics server to start when a database is unavailable. You can change the retry interval by providing a value for the connection-creation-retry-frequency-seconds child element of the connection-pool-params element. A value of zero disables connection retry.

12.2.2 Custom Data Source Configuration

The Oracle Stream Analytics 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.

When you create an Oracle Stream Analytics domain with the Configuration Wizard, you can optionally configure a JDBC data source that uses one of the two supported Data Direct JDBC drivers. In this case the wizard updates the config.xml file for you. When you configure the data source, you provide basic information, such as the database you want to connect to, and the connection user name and password.

You can also update the config.xml file manually by adding a data-source element as the following example shows.

<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 Stream Analytics server transaction manager or configure one with the transaction-manager element in config.xml as the example shows. The transaction manager depends on a configured RMI object, as described in JMX Configuration Objects.

If a database is unavailable when you start Oracle Stream Analytics server, by default, an Oracle Stream Analytics server data source retries every 10 seconds until it creates a connection. The retries enable Oracle Stream Analytics server to start when a database is unavailable. The example changes the retry interval in the config.xml file by providing a value for the connection-creation-retry-frequency-seconds child element of the connection-pool-params element. In the example, the value is 60 seconds.

For a full list of child elements of the data-source element such as the connection-pool-params and data-source-params elements, see Schema Reference for Oracle Stream Analytics. For information about security configuration tasks that affect JDBC, see Configure JDBC Security.

12.2.3 Get the Native JDBC Connection

The Java API Reference for Oracle WebLogic Server provides a WLConnection interface that contains methods for getting and manipulating Oracle data sources. For example, the following Java code gets the native Oracle database connection from the pooled connection object.

private DataSource ods;
private Connection wlConnection;
private OracleConnection connection;

wlConnection = ods.getConnection();
connection = (OracleConnection) ((WLConnection) wlConnection)
.getVendorConnection();

Note:

Close pooled connections when you finish and do not use a native connection object after the pooled connection is closed.

12.3 Configure Access to a Database with an Oracle JDBC Driver

This section explains the procedure to configure access to a database with an Oracle JDBC driver.

The Oracle JDBC driver is installed with Oracle Stream Analytics and ready to use.

Configure access to a database using the Oracle JDBC driver:

Configure the data source in the server config.xml file.
1. To update the Oracle Stream Analytics server config.xml file using the Configuration Wizard, see Create a Standalone-Server Domain.
2. To update the Oracle Stream Analytics server config.xml file manually, see Custom Data Source Configuration.
  
  The url element for the Oracle JDBC driver has the following form. See also Custom Data Source Configuration.
```
<url>jdbc:oracle:thin:@HOST:PORT:SID</url>
```
If Oracle Stream Analytics is running, restart it so it reads the new data source information.

For more information, see "Start and Stop Servers".

12.4 Configure Database Access with Microsoft SQL Server JDBC Driver

To access a data source with a Microsoft SQL server JDBC driver, add the wlsqlserv.jar and the fmwgenerictoken.jar files to the -Xbootclasspath.

-Xbootclasspath/a:/Oracle/Middleware/my_oep/oracle_common/modules/datadirect/wlsqlserver.jar:/Oracle/Middleware/my_oep/oracle_common/modules/datadirect/fmwgenerictoken.jar

-Xbootclasspath/a:/Oracle/Middleware/oracle_common/modules/datadirect/wlsqlserver.jar:/Oracle/Middleware/oracle_common/modules/datadirect/fmwgenerictoken.jar

Add the following SQL server data source configuration to the config.xml file.

<data-source>
  <name>ds-sqlserver-datadirect-driver</name>
  <data-source-params>
    <jndi-names />
    <global-transactions-protocol>OnePhaseCommit
    </global-transactions-protocol>
  </data-source-params>
  <connection-pool-params>
   <credential-mapping-enabled></credential-mapping-enabled>
   <test-table-name>SQL SELECT 1</test-table-name>
   <initial-capacity>5</initial-capacity>
   <max-capacity>20</max-capacity>
   <capacity-increment>1</capacity-increment>
  </connection-pool-params>
  <driver-params>
    <use-xa-data-source-interface>true</use-xa-data-source-interface>
    <driver-name>weblogic.jdbc.sqlserver.SQLServerDriver</driver-name>
    <url>
 jdbc:weblogic:sqlserver://hostname:port;databaseName=fmwcerts;SelectMethod=cursor
    </url>
    <properties>
      <element>
        <value>sa</value>
        <name>user</name>
      </element>
      <element>
        <value>{AES}XcrEKM8RegvOT3jZ4d46WQ==</value>
        <name>password</name>
      </element>
    </properties>
  </driver-params>
</data-source>

12.5 Configure Access to a Different Database Driver or Driver Version

In some cases, you might need to use a different version of the Oracle Database driver or Data Direct drivers than the version bundled with Oracle Stream Analytics, or you might need to use a database driver other than the Oracle Database driver or Data Direct drivers.

12.5.1 Access a Database Driver with an Application Library Built With bundler.sh

This procedure describes how to create an OSGi bundle for your driver using the bundler utility and deploy it on the Oracle Stream Analytics server.

See Developing Applications for Event Processing with Oracle Stream Analytics.

Execute the bundler.sh script to create an OSGi bundle that contains your driver.

The bundler.sh script is in the /Oracle/Middleware/my_oep/oep/bin directory. The following example lists the bundler.sh command-line options and Table 12-1 describes them.

Note:

There is no Windows support for bundler.sh (no bundler.cmd).

bundler.sh
    -source <jar> 
    -name <name> 
    -version <version> 
    [-factory <class>+] 
    [-service <interface>+] 
    [-stagedir <path>] 
    [-targetdir <path>]

Table 12-1 bundler.sh Command Line Options

Argument	Description
`-source`	The path of the source JAR file to be bundled.
-`name`	The symbolic name of the bundle. The root of the target JAR file name is derived from the name value.
-`version`	The bundle version number. All exported packages are qualified with a version attribute with this value. The target JAR file name contains the version number.
-`factory`	An optional argument that specifies a space-delimited list of one or more factory classes that are to be instantiated and registered as OSGi services. Each service is registered with the OSGi service registry with name (`-name`) and version (`-version`) properties.
-`service`	An optional argument that specifies a space-delimited list of one or more Java interfaces that are used as the object class of each factory object service registration. If no interface names are specified, or the number of interfaces specified does not match the number of factory classes, then each factory object will be registered under the factory class name.
`-stagedir`	An optional argument that specifies where to write temporary files when creating the target JAR file. Default: `./bundler.tmp`
`-targetdir`	An optional argument that specifies the location of the generated bundle JAR file. Default: current working directory (`.`).

The following example shows how to use the bundler.sh to create an OSGi bundle for an Oracle JDBC driver.

bundler.sh \
    -source /scratch/drivers/com.bea.oracle.ojdbc6_1.0.0.0_11-2-0-3-0.jar \
    -name oracle12c \
    -version 12.1.3 \
    -factory oracle.jdbc.xa.client.OracleXADataSource oracle.jdbc.OracleDriver \
    -service javax.sql.XADataSource java.sql.Driver \
    -targetdir /scratch/stage

The source JAR is an Oracle driver located in the C:\drivers directory. The name of the generated bundle JAR is the concatenation of the -name and -version arguments and is created in the C:\stage directory. The bundle JAR contains the files that the following example shows:

1465 Thu Jun 29 17:54:04 EDT 2006 META-INF/MANIFEST.MF
1540457 Thu May 11 00:37:46 EDT 2006 ccom.bea.oracle.ojdbc6_1.0.0.0_
  11-2-0-3-0.jar
1700 Thu Jun 29 17:54:04 EDT 2006 com/bea/core/tools/bundler/Activator.class

The command-line options specify that there are two factory classes to instantiate and register as an OSGi service when the bundle is activated, each under a separate object class as the following table shows.

Table 12-2 Factory Class and Service Interface

Factory Class	Service Interface
`oracle.jdbc.xa.client.OracleXADataSource`	`javax.sql.XADataSource`
`oracle.jdbc.OracleDriver`	`java.sql.Driver`

Each service registration will be made with a name property set to oracle12c and a version property with a value of 12c. the following example shows the Oracle Stream Analytics server log messages showing the registration of the services.

...
INFO: [Jun 29, 2006 5:54:18 PM] Service REGISTERED: { version=12c, name=oracle12c, objectClass=[ javax.sql.XADataSource ], service.id=23 }
INFO: [Jun 29, 2006 5:54:18 PM] Service REGISTERED: { version=12c, name=oracle12c, objectClass=[ java.sql.Driver ], service.id=24 }
INFO: [Jun 29, 2006 5:54:18 PM] Bundle oracle12c STARTED
...

Copy the bundler JAR to the Oracle Stream Analytics server library extensions directory.

Because your Oracle Stream Analytics application is an application library that contains a driver, you copy it to the Oracle Stream Analytics server library extensions directory. By default the library extensions directory is in /Oracle/Middleware/user_projects/domains/<domainname>/ <servername>/modules/ext/.

In the Oracle Stream Analytics server config.xml file, create a custom data-source element for your driver version and add a driver-params child element as the following example shows.

For more information, see Server Configuration Files.

    <driver-params>
      <url>jdbc:oracle:thin:@lcw2k18:1531:lcw101</url>
      <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name
      <properties>
        <element>
          <name>user</name>
          <value>scott</value>
        </element>
        <element>
          <name>password</name>
          <value>{3DES}EoIfSBMhnW8=</value>
        </element>
        <element>
          <name>com.bea.core.datasource.serviceName</name>
          <value>oracle12c</value>
        </element>
        <element>
          <name>com.bea.core.datasource.serviceVersion</name>
          <value>12.1.3</value>
        </element>
        <element>
          <name>com.bea.core.datasource.serviceObjectClass</name>
          <value>javax.sql.XADataSource</value>
        </element>
      </properties>
      <use-xa-data-source-interface>true</use-xa-data-source-interface>
    </driver-params>

Table 12-1 describes the relevant properties.

Table 12-3 driver-params Properties

Property Description

Property	Description
`com.bea.core.datasource.serviceName`	Specifies the value of the `serviceName` registration property. This must match the `NAME` property in your `Activator` class.
`com.bea.core.datasource.serviceVersion`	Specifies the value of the `serviceVersion` registration property. This must match the `VERSION` property in your `Activator` class.
`com.bea.core.datasource.serviceObjectClass`	Specifies the interface name of the OSGI service registration.

com.bea.core.datasource.serviceName

Specifies the value of the serviceName registration property.

This must match the NAME property in your Activator class.

com.bea.core.datasource.serviceVersion

Specifies the value of the serviceVersion registration property.

This must match the VERSION property in your Activator class.

com.bea.core.datasource.serviceObjectClass

Specifies the interface name of the OSGI service registration.

Stop and start the Oracle Stream Analytics server.

For more information, see Start and Stop Servers.

12.5.2 Access a Database Driver with bootclasspath

Optionally, you can use the bootclasspath to access your own JDBC driver.

Oracle recommends that you use an application library instead, as described in Access a Database Driver with an Application Library Built With bundler.sh.

Go to the server directory of the domain that you want to configure.

By default, the server directory is in /Oracle/Middleware/my_oep/user_projects/domains/<domainname/<servername>/.
In a text editor, open the start script for your platform.
Add the -Xbootclasspath/a option to the Java command that executes the wlevs.jar file and set the -Xbootclasspath/a option to the full pathname of the JDBC driver you are going to use.

For example, to use the Windows Oracle thin driver, update the java command in the start script as follows with everything on one line:
```
%JAVA_HOME%\bin\java -Dwlevs.home=%USER_INSTALL_DIR% -Dbea.home=%BEA_HOME%  
-Xbootclasspath/a:\Oracle\Middleware\my_oep\oep\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 
```
Configure the data source in the server's config.xml file:
1. To update the Oracle Stream Analytics server config.xml file using the Configuration Wizard, see Create a Standalone-Server Domain.
2. To update the Oracle Stream Analytics server config.xml file manually, see Custom Data Source Configuration.
If Oracle Stream Analytics is running, restart it so it reads the new java option and data source information.

For more information, see "Start and Stop Servers".