Connecting to Remote Databases with JDBC
The JDBC standard allows you to connect to third-party databases and perform queries against the remote database. Redwood Server allows you to interface with JDBC. You can execute calls in database-specific dialects as well as stored procedures.
JDBC, OracleJob, and SQLPLUS Definition Types with Oracle Databases
If you need to execute calls against an Oracle database, consider the following options:
- JDBC Definition Type - requires a JDBC Process Server, the output is stored on the central Redwood Server. Accepts both PL/SQL calls and JDBC queries. Numerous parameter mapping options are available, see JDBC Definition Type
- SQLPLUS Definition Type - requires a Platform Agent with
sqlplusexecutable, the output is stored on the Platform Agent. Accepts PL/SQL and SQL Plus code. Parameters are accessed and set as substitution variables. - OracleJob Definition Type - requires Redwood Server to be installed in an Oracle database, the output is stored on the central Redwood Server. Accepts a single anonymous PL/SQL block per Process Definition. Parameters are bound using OracleNamed bind mode.
Note: On on-site environments, JDBC requires the ProcessServerService.JDBC.limit license key.
Tabs and Fields
| Tab | Field | Description |
|---|---|---|
| Database | Name | The name must start with a US ASCII letter, can contain only US ASCII letters, numbers, and underscores (_). Limited to 80 characters. |
| Database | Description | Free form text, can contain any printable UTF-8 character combination. Limited to 255 characters. Note that the browsers must be able to display the character. |
| Database | Application | The application to use for the database. |
| Database | Connection > Library | The library with the JDBC driver if you do not wish to use a driver on the class path |
| Database | Connection > Jdbc Driver Class Name | The class name of the JDBC driver; see the documentation for your JDBC driver for more information. |
| Database | Connection > Jdbc Url | Two options are available to specify the JDBC connect string; using host, port (optional), and database specifier (Simple) or specifying the JDBC URL (Advanced). Note that the Simple method is only supported for specific databases. |
| Database | Connection > Auto Commit Style |
The type of commit policy you require: Transaction Based (default) - A commit is performed at the end of every process run. This allows you to roll back (revert) all SQL queries on failure. Auto Commit On - A commit is performed after each query. Auto Commit Off - No commit is performed. You are expected to code commits where you see fit. |
| Database | Connection User | The JDBC credential to use for the database connection. |
| Documentation | Documentation | Free form text, can contain any printable UTF-8 character sequence. |
| Connect Pool Settings | Max Number Of Connections | Maximum number of physical connections the Process Server can establish to the remote database. |
| Connect Pool Settings | Pooling State | Controls the connection pool. When set to Disabled (default), connection pooling is disabled. Accepts values Enabled and DisabledForProcesses (connection pooling is disabled for JDBC and OracleJob processes source only and enabled for REL expressions, for example). |
| Connect Pool Settings | Driver Properties | List of Java properties for the JDBC driver |
| Connect Pool Settings | Max Connect Time (ms) | The maximum amount of time to keep a connection in the pool. |
| Connect Pool Settings | Max Idle Time (ms) | The maximum amount of time a connection may remain idle before it is closed. |
| Connect Pool Settings | Max Abandoned Time (ms) | The maximum amount of time the Process Server will wait before closing the connection. |
| Connect Pool Settings | Frequent Users | Users requiring specific maximum connections and/or idle times. |
| Connect Pool Settings | Max Connections Per Frequent User | Maximum number of physical connections per user (from Frequent Users list) the Process Server can establish to the remote database. |
| Connect Pool Settings | Min Idle Connections Per Frequent User | Minimum number of idle connections the Process Server should keep open for users from the Frequent Users list. |
| Connect Pool Settings | Max Connections Per Non-Frequent User | Maximum number of physical connections per user (for users not on the Frequent Users list) the Process Server can establish to the remote database. |
| Connect Pool Settings | Initialization Query | A query that will be called each time a connection is opened. |
| Connect Pool Settings | Validation Query | A query that is performed after a successful login and after any severe SQL exception. |
| Connect Pool Settings | Severe SQL Exceptions | Comma-separated list of exceptions that the Process Server should consider fatal, causing the Process Server to issue the ValidationQuery. |
| Connect Pool Settings | Non Transient Connect SQL Exceptions | Comma-separated list of exceptions that should be considered non-transient (exceptions that should prevent the Process Server from retrying). |
| Connect Pool Settings | Invalid User Name Or Password SQL Exceptions | Comma-separated list of exceptions that should be mapped to invalid username or password. |
JDBC Drivers
Note: There are a number of JDBC drivers for different databases. Always use the latest for your specific database version. Some JDBC drivers only allow connecting to newer database versions, ensure the JDBC driver is compatible with your database version.
The following is required to use the JDBC driver:
- JDBC driver (jar file)
- driver name
- database URL
- database name and username/password
The JDBC drivers usually have a driver name, for example oracle.jdbc.OracleDriver is the name of Oracle's thin client. Drivers also require a database URL, for example, the Oracle thin client can use jdbc:oracle:thin:@<host>:<port>:<SID> or jdbc:oracle:thin:@<host>:<port>/<service>. Always read the documentation that is provided with your JDBC driver. For example, on Oracle 12c, you connect to a PDB using the service name jdbc:oracle:thin:@<host>:<port>/<service>.
Connection Pool
You enable a connection pool to increase the performance of JDBC, OracleJob processes, and REL expressions making calls to remote databases. Note that when connection pooling is enabled and a JDBC or OracleJob process starts running, instead of creating a connection to the remote database, the Process Server uses a connection from a connection pool. Once the process has reached a final status, the connection is not closed but returned to the pool for another process to use. Special caution needs to be taken when you enable connection pooling, because you cannot make assertions about variables holding initial values, for example, or that locks are automatically released when processing finishes.
You can partially enable connection pooling for REL expressions, for example, and keep it disabled for JDBC and OracleJob processes; this is controlled by the Pooling State.
Process Flow
- You put the JDBC driver (jar file) into the CLASSPATH of your application server (AS), alternatively, you can upload it into a Redwood Server Library.
- You create a Process Server for JDBC using the wizard or Process Server for JDBC manually.
- You create one or more credential(s) for JDBC (also covered in the JDBC Process Server topic).
- You create one or more JDBC Process Definitions, you can use the JDBC Process Definition Wizard to jump-start.