The path to the JDBC driver .jar file. For example, you can enter the following directory: C:/jdbc
If you do not specify a directory path, the Secure Agent gets the .jar file from the process-engine/ext directory.
You must specify one of the following values for the JDBC connection to work successfully:
- JDBC JAR directory. If you choose to specify the JDBC JAR directory, you can place the .jar file in any directory and specify the directory in the JDBC Jar Directory field.
- JDBC driver class name. If you choose to specify the JDBC driver class name, you must place the JDBC driver .jar file in the following directory:
process-engine/ext
JDBC Driver Class Name
The name of the JDBC driver class.
Based on the database, you can specify one of the following driver class names:
- IBM DB2: com.ibm.db2.jcc.DB2Driver
- Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
- MySQL: com.mysql.jdbc.Driver
- Oracle: oracle.jdbc.OracleDriver
- PostgreSQL: org.postgresql.Driver
You must specify one of the following values for the JDBC connection to work successfully:
- JDBC JAR directory. If you choose to specify the JDBC JAR directory, you can place the .jar file in any directory and specify the directory in the JDBC Jar Directory field.
- JDBC driver class name. If you choose to specify the JDBC driver class name, you must place the JDBC driver .jar file in the following directory:
process-engine/ext
Schema
Optional. The schema name, which varies by database. Use the following guidelines for the schema name:
- IBM DB2: Use the schema name to specify the correct object.
- Microsoft SQL Server: Use the schema name to specify the correct object.
- MySQL: Optional. The schema name is the database name.
- Oracle: Optional. The schema name is the user name.
- PostgreSQL: Use the schema name to specify the correct object.
If the JDBC connection URL does not provide enough context, you must enter a schema name to fetch the metadata.
User name
Required. User name to connect to the database.
Password
Required. Password to connect to the database.
Object Filter
Optional. A comma-separated list of object names.
TimeZone
Optional. The time zone of the database. Specify the value of the database time zone when the machine hosting the database and the machine hosting the Secure Agent are in different time zones.
Paginating Data with a JDBC Connection
When you use a JDBC connection, use OData to paginate data and process data in chunks.
For example, use the following OData URL to use the JDBC-MySQL-Connection connection to skip 1000 entries and select the next 100 entries in the ADDRESS table:
You can use the OData $skip parameter with the following databases:
•Oracle
•Microsoft SQL Server
•DB2
•MySQL
To use the $skip parameter with a DB2 or a Microsoft SQL Server database, you must specify both the $skip and $orderby values.
For example, use the following OData URL to use the JDBC-SQLSRVR-Connection connection to skip 1000 entries, select the next 50 entries, and order data by the ADDR_ID column of the ADDRESS table in a Microsoft SQL Server database:
You can use complex filter conditions that contain multiple logical operators and braces. For example, you can use the following filter condition:
$filter=AGE gt 20 and (SALARY gt 1500 or (SALARY gt 1000 and TITLE eq 'SE'))
You can also paginate data when you use complex filter conditions.
Note: When you use an OData URL to filter data from any database, in the fields that contain a string data type, you must provide two single quotes instead of one single quote in the $filter condition. For example, you must pass the email address as email''check@infa.com instead of email'check@infa.com when you want to filter data based on the email address.
Setting the Database Time Zone for the JDBC Connection
When you enable OData for a JDBC connection and read data that contains the date, time, datetime, or timestamp datatypes, the OData request returns incorrect values from the database. This error occurs when the time zone of the machine that hosts the database is not the same as the machine that hosts the Secure Agent. To avoid this error, you must select the appropriate time zone value of the database in the connection properties to manage the time zone conversion and return correct values.
JDBC Connection Read Attributes
The following table describes the read attributes that you can specify for a JDBC connection:
Read Attributes
Description
Isolation Level
Optional. Specifies the isolation level while reading from source object.
The value you specify determines the level of concurrency in transactions to avoid data inconsistency. Select from the following isolation levels:
- None
- TRANSACTION READ UNCOMMITTED
- TRANSACTION READ COMMITTED
- TRANSACTION REPEATABLE READ
- TRANSACTION SERIALIZABLE
Default is None.
Override Isolation Level
Optional. Overrides the isolation level if the database does not support it. If this option is checked, the task runs successfully with the default isolation level of the database.
Default is No.
JDBC Connection Write Attributes
The following table describes the write attributes that you can specify for a JDBC connection:
Write Attributes
Description
Number of retries
Optional. An integer value. Number of times to retry if the query execution fails due to a closed connection.
Default is 5.
Retry wait period
Optional. Interval in seconds between two retry attempts.