The Database connector for Jitterbit Harmony Cloud Studio provides an interface for entering user-provided input such as server credentials to create a Database connection. That connection provides the foundation to configure associated Database connector activities that interact with the connection. Together, a specific Database connection and its activities are referred to as a Database endpoint.
This connector is used to first configure a Database connection, establishing access to a database, and then used to configure one or more Database activities associated with that connection as a source or target within an operation or script:
- Query: Queries data from a Database endpoint and is intended to be used as a source in an operation or called in a script.
- Insert: Inserts new data in a Database endpoint and is intended to be used as a target in an operation or called in a script.
- Update: Updates existing data in a Database endpoint and is intended to be used as a target in an operation or called in a script.
- Upsert: Both updates existing data and inserts new data in a Database endpoint and is intended to be used as a target in an operation or called in a script. (Jitterbit Harmony supports Upsert activities for databases by using a combination of Query, Insert, and Update.)
As there is no database Delete activity, to delete data from a Database connection, use the
DBExecute function in a Jitterbit Script by specifying the SQL command to be executed against the database.
Accessing the Connector¶
The Database connector is accessed from the Connectivity tab of the design component palette by using the Show dropdown to filter on Connectors.
Once a connection is configured, activities associated with the endpoint are available by using the Show dropdown to filter on Endpoints and then clicking the Database connection block:
Supported Databases and Data¶
Most JDBC- and ODBC-compliant databases are supported.
In databases, Jitterbit does not support data with these encoding types or object names:
- Binary data
- Unicode/UTF data
- Tables or views with spaces in the names
Supported TLS Versions¶
Transport Layer Security (TLS) 1.2 is supported and recommended for JDBC database endpoints. TLS 1.0 and 1.1 are no longer supported on Cloud Agents and are disabled by default on Private Agents version 10.47 and later.
If a database endpoint no longer works after an upgrade to a 10.47 or later agent, add
enabledTLSProtocols=TLSv1.2 to the database connection string during configuration of the Database connection to force TLS 1.2 to be used (provided the protocol version is supported by the database endpoint).
On Private Agents, if TLS 1.2 is not supported by the database endpoint, TLS 1.0 or 1.1 can be re-enabled (though not recommended) by removing
TLSv1.1 from the
jdk.tls.disabledAlgorithms security property in the Private Agent
java.security configuration file.
The Private Agent
java.security configuration file is located in these default directories:
C:\Program Files\Jitterbit Agent\jre\lib\security
Installing Additional JDBC or ODBC Drivers¶
Database drivers are automatically detected from the Harmony Agent. For Cloud Agents, managed by Jitterbit, a set of commonly used JDBC drivers is supported and is already provided. For Private Agents, installed drivers are detected from the operating system where each Private Agent is installed. In addition, you can install other database drivers on Private Agents as needed. If you require the use of an ODBC driver, you can use a Windows Private Agent.
For general information and instructions on installing additional drivers on Private Agents, refer to Installing Additional ODBC or JDBC Drivers.
Special Characters in Database Table/Column Names¶
If using a Private Agent, you can specify characters used to define delimiters within database table/column names within the Private Agent configuration file under the
If using an ODBC database driver, note that some special characters in database table/column names are unable to be handled by the driver. For example, database fields that have an at sign (
@) are not compliant with SQL-based specifications and may not be supported. If the database uses such special characters in table/column names, as a workaround we recommend creating a view on the physical table that does not use the special character in column names and using that instead.
See Database-specific Information for reference information on configuring these databases:
- IBM DB2 (AS400)
- Microsoft Access
- Microsoft Excel
- Microsoft SQL Server
A number of database functions can be used within scripts to provide access to basic database interactions, including these:
For details on using these functions, see Database Functions.