Skip to Content

Turn your connections into holiday cash with our new Customer Referral Program! Learn more

✕

Database connector for Jitterbit Integration Studio

Summary

The Database connector establishes access to a database via a JDBC or ODBC driver provided with Jitterbit cloud agents or installed on Jitterbit private agents.

Note

Jitterbit offers a number of application-specific connectors for commonly used databases. For a complete list, see Connectors.

The Database connector provides an interface for creating a Database connection, the foundation used for generating instances of Database activities. These activities, once configured, interact with the database through the connection.

The Database connector is accessed from the design component palette's Project endpoints and connectors tab (see Design component palette).

Connector overview

This connector is used to first configure a Database connection. Activity types associated with that connection are then used to create instances of activities that are intended to be used as sources (to provide data in an operation) or targets (to consume data in an operation).

Together, a specific Database connection and its activities are referred to as a Database endpoint:

Database activity types

  • 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. (Harmony supports Upsert activities for databases by using a combination of Query, Insert, and Update.)

Tip

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.

Note

This connector is a native Integration Studio connector, which may be referred to by Jitterbit when communicating changes made to connectors. The release schedule for native Integration Studio connectors is based on the cadence of Harmony portal web applications.

Supported versions and limitations

Supported databases

Most JDBC- and ODBC-compliant databases are supported. Your choice of agent also affects which driver type you can use, as cloud agents support JDBC drivers only, while private agents support both ODBC and JDBC drivers.

Cloud agents

Cloud agent groups support JDBC drivers only. A set of commonly used JDBC drivers is provided:

  • Amazon Redshift
  • IBM DB2 (AS400)
  • MySQL
  • Oracle (JDBC 19.7)
  • Oracle NetSuite
  • PostgreSQL
  • SQL Server jTDS
  • SQL Server MS JDBC
  • Sybase jTDS

Private agents

Private agents support JDBC drivers and ODBC drivers. For ODBC drivers, the architecture of the driver must match the system of the agent. That is, 64-bit agents support 64-bit ODBC drivers.

These JDBC drivers are provided by 64-bit Windows and Linux private agents:

  • Amazon Redshift
  • IBM DB2 (AS400)
  • IBM DB2 (JCC)
  • MySQL
  • Oracle (JDBC 19.7)
  • Oracle NetSuite
  • PostgreSQL
  • SQL Server jTDS
  • SQL Server Microsoft
  • SQLite
  • Sybase jTDS

These ODBC drivers are bundled with the 64-bit Windows private agent installation:

  • PostgreSQL ANSI (x64)
  • PostgreSQL Unicode (x64)

This ODBC driver is bundled with the 64-bit Linux private agent installation:

  • PostgreSQL-jitterbit

In addition, you can install other database drivers on private agents as needed. For general information and instructions on installing additional drivers on private agents, refer to Install additional ODBC or JDBC drivers.

Supported data

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.3 and 1.2 are supported for JDBC database endpoints.

On private agents, 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.

Troubleshooting and how-tos

If you experience issues with the Database connector, these troubleshooting steps are recommended:

  1. Ensure the Database connection is successful by using the Test button in the configuration screen. If the connection is not successful, the error returned may provide an indication as to the problem.

  2. Check the operation logs for any information written during execution of the operation.

  3. Enable operation debug logging (for cloud agents or for private agents) to generate additional log files and data.

  4. If using private agents, you can check the agent logs for more information.

  5. For additional troubleshooting considerations, see Operation troubleshooting.

In addition, the following pages provide detailed instructions on accomplishing certain tasks with integration projects that use the Database connector:

Additional resources

A number of database functions can be used within scripts to provide access to basic database interactions, including these:

  • CacheLookup
  • CallStoredProcedure
  • DBCloseConnection
  • DBExecute
  • DBLoad
  • DBLookup
  • DBLookupAll
  • DBRollbackTransaction
  • DBWrite
  • SetDBInsert
  • SetDBUpdate
  • SQLEscape
  • Unmap
  • <SEQUENCE>
  • <SQLIDENTITY>
  • <UDF>

For details on using these functions, see Database functions.