You can configure Java Database Connectivity (JDBC) for relational database access from an Oracle Event Processing application.
Oracle Event Processing supports JDBC 4.0. To download JDBC, go to http://java.sun.com/products/jdbc/download.html
.
This chapter includes the following sections:
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 Event Processing 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 Event Processing Data Sources.
Oracle Event Processing provides the Oracle 12c thin driver. Optionally, you can use your own JDBC driver. See Access a Database Driver with bootclasspath.
Oracle Event Processing 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:
Oracle Event Processing 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
An Oracle Event Processing 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.
GUID-C27979DB-F0DB-4646-94F7-C939A653BD64.htm#CEPAG-GUID-E4DC834E-EDD8-4CF1-91E0-9E6EC8584E64__i1018302 shows the relationship between data source, connection pool, and Connection
instances.
Figure 12-1 Oracle Event Processing Data Source
You must use the Oracle Event Processing server default data source or configure your own Oracle Event Processing DataSource
in the server's config.xml
file if you want to access a relational database in any of the following ways. See Oracle Fusion Middleware Schema Reference for Oracle Event Processing.
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.
By default, the Oracle Event Processing server creates a local transaction manager. The transaction manager depends on a configured RMI object, as described in JMX Configuration Objects. Oracle Event Processing 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 Event Processing server, by default, an Oracle Event Processing server data source retries every 10 seconds until it creates a connection. The retries enable the Oracle Event Processing 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.
The Oracle Event Processing 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 Event Processing 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 Event Processing 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 Event Processing server, by default, an Oracle Event Processing server data source retries every 10 seconds until creates a connection. The retries enable Oracle Event Processing 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 Oracle Fusion Middleware Schema Reference for Oracle Event Processing. For information about security configuration tasks that affect JDBC, see Configure JDBC Security.
The Oracle Fusion Middleware Oracle WebLogic Server API Reference 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.
This section explains the procedure to configure access to a database with an Oracle JDBC driver.
The Oracle JDBC driver is installed with Oracle Event Processing and ready to use.
Configure access to a database using the Oracle JDBC driver:
Configure the data source in the server config.xml
file.
To update the Oracle Event Processing server config.xml
file using the Configuration Wizard, see Create a Standalone-Server Domain.
To update the Oracle Event Processing 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 Event Processing is running, restart it so it reads the new data source information.
For more information, see "Start and Stop Servers".
To access a data source with a Microsoft SQL server JDBC driver, add the wlsqlserv.jar
and the fmwgenerictoken.jar
files to the -Xbootclasspath
as follows:
-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>
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 Event Processing, or you might need to use a database driver other than the Oracle Database driver or Data Direct drivers.
This procedure describes how to create an OSGi bundle for your driver using the bundler utility and deploy it on the Oracle Event Processing server.
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:
To update the Oracle Event Processing server config.xml
file using the Configuration Wizard, see Create a Standalone-Server Domain.
To update the Oracle Event Processing server config.xml
file manually, see Custom Data Source Configuration.
If Oracle Event Processing is running, restart it so it reads the new java
option and data source information.
For more information, see "Start and Stop Servers".