Database¶

Databases can be defined as a database source or database target within Jitterbit Studio. This page provides general information about using databases in Jitterbit.

Drivers¶

Jitterbit communicates with databases through ODBC or JDBC. Cloud Agents support a provided set of JDBC drivers. Private Agents support both ODBC and JDBC drivers. In addition to provided drivers, you can install additional ODBC and JDBC drivers on Private Agents.

SQL Statements¶

All SQL statements written using the Formula Builder during Transformation Mapping should be ANSI-SQL compliant for the ODBC or JDBC driver of the database you are accessing. There is a 2000-character limit to the SQL statements. A recommended workaround to the character limitation, if needed, is to create database views for long SQL statements.

Authentication¶

Valid credentials are required to connect to your database. Generally this consists of either DSN (Data Source Name) or username/password.

Unsupported Data Types¶

These items are not supported in the current version of the software:

• Binary data
• Unicode/UTF data
• Tables and 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 source or Database target 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 or 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:

• Windows: C:\Program Files\Jitterbit Agent\jre\lib\security
• Linux: /opt/jitterbit/jre/lib/security

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 [DbDrivers] section.

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.

User-Defined Functions¶

The User Defined Functions (UDF) tag is found in the Formula Builder Database Functions folder. This function lets you use any database function supported by a database, such as the ToDate() function in Oracle, or the Convert() function in SQL Server.

Update Field Value¶

The Update Field Value updates a field from a source database to a given value after an operation is complete. This value is used when performing routine database batch operations to ensure that data that has already been processed during a previous operation is not processed again. It is also useful if the operation is part of a workflow and needs to set the status of a field to the next value indicating that it has completed successfully or a failure occurred.

Primary Keys¶

Primary keys are defined within your database environment. A field that is defined as a primary key is used in indexing the database's information. When applicable, Jitterbit can automatically identify your database's key fields.

Database Types¶

Most ODBC- or JDBC-compliant databases are supported. The following are specific recommendations for various database types.

Work with Oracle Databases Using TNSNames.ora¶

The use of JDBC drivers is highly recommended for Oracle databases. However, if you want to use ODBC, it is recommended that you use the Oracle ODBC drivers that come with the version of the database you are accessing. You will need to install the Oracle Client and ODBC driver on the same server that runs Jitterbit.

In using the Oracle Client, you must upload a valid TNSNames.ora file into the appropriate directory which references the database that you wish to access. To reference the database in Jitterbit, enter its TNSNames.ora reference into the Serverfield, together with the appropriate user ID and password.

For more information, see these pages:

• Configure an Oracle ODBC Database Source
• Configure an Oracle ODBC Database Target
• Use Proprietary ODBC or JDBC Drivers with Jitterbit

Work with Microsoft SQL Server Databases¶

Microsoft SQL Server databases are accessible from Jitterbit Agents running on Windows or Linux, although Windows ODBC is recommended for optimized performance. Jitterbit supports the following authentication options:

• MS SQL ODBC on Windows using Windows authentication or SQL Server authentication
• MS SQL JDBC on Windows using SQL Server authentication
• MS SQL JDBC on Linux using SQL Server authentication

For more information see:

• Configure a Microsoft SQL Database Source
• Configure a Microsoft SQL Database Target

Work with File-Based Databases¶

File-based databases must make use of the Private Agent architecture. In order to use file-based databases such as Microsoft Access, the database file must reside on the local file system on the Agent machine.

The location and authentication information for flat-file databases is found in either the source or target database definition. To access the database, you do not need to define a Server, only a Database Name.

This table provides examples on accessing file databases with additional connection string parameters if required.

For more information, see these pages:

• Connecting to Microsoft Access
• Connecting to Microsoft Excel Spreadsheets