Skip to Content

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

✕

Database connection

Introduction

A Database connection, created using 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. Once a connection is configured, you can create instances of Database activities associated with that connection to be used either as sources (to provide data in an operation) or as targets (to consume data in an operation).

Note

This connector supports the Enable Re-authentication on Change organization policy. If enabled, a change to the Server Name or Login in an endpoint's connection will require users to re-enter the Password for the connection.

Create or edit a Database connection

A new Database connection is created using the Database connector from one of these locations:

An existing Database connection can be edited from these locations:

Configure a Database connection

Each user interface element of the Database connection configuration screen is described below.

Database connection configuration

Tip

Fields with a variable icon support using global variables, project variables, and Jitterbit variables. Begin either by typing an open square bracket [ into the field or by clicking the variable icon to display a list of the existing variables to choose from.

  • Connection name: Enter a name to use to identify the Database connection. The name must be unique for each Database connection and must not contain forward slashes (/) or colons (:). This name is also used to identify the Database endpoint, which refers to both a specific connection and its activities.

  • Driver type: To filter the list of drivers, select the button for the appropriate driver type. Cloud agents support a limited set of JDBC drivers only, while private agents support both ODBC and JDBC drivers and you can install additional drivers (see Database drivers on Jitterbit private agents for more information).

  • Driver: Use the list to select the database driver. The available drivers depend on whether you are using cloud agents or private agents (see Database drivers). When you select a database driver, the remaining fields available in this configuration screen may change, as only the fields applicable to the selected database driver are shown.

  • Server name: Enter the name, URL, or IP address of the database server.

  • Database name: If applicable, enter the name of the database. This field may not be present for database drivers for which this field is not applicable, such as Oracle.

  • Login and Password: If applicable, enter a username and password that allows access to the database. You can leave these fields blank if no username or password is required.

  • Use Default Port: Keep this checkbox selected to use the default port. The default port differs depending on the selected driver. If you don't want to use the default port, clear the Use Default Port checkbox and enter the port to use in the Port field below.

  • Port: (This field is enabled only if the Use Default Port box is unchecked.) Enter a custom port only if the database server is listening on a non-default port.

  • Optional settings: Click to expand for additional optional settings:

    database connection optional settings

    • Optimize for Bulk Data: Available only when either the Oracle or SQL Server jTDS database driver is selected, select this checkbox to improve performance when working with large amounts of data.

    • Transaction Isolation Level: If applicable, use the dropdown to select the Transact-SQL (T-SQL) isolation level. Driver-specific transaction isolation levels are not supported. The DEFAULT option uses the driver or database server default transaction isolation level.

    • Timeout (sec): To set a limit for how long to wait for a successful connection to the database, enter a number of seconds here. The default is 300 seconds (5 minutes).

    • Fetch Size: If using a JDBC driver, this tells the driver how many rows should be fetched when more rows are required. This can be used to improve the processing of large datasets.

    • Additional Connection String Parameters: To add additional parameters to the database connection string, enter them here. Note that this field is disabled if you have selected the option below to use a connection string.

    • Use Connection String: Select this checkbox to enter a connection string in the field below. If selected, this disables and overrides the Additional Connection String Parameters above. If selected and the Connection String field is left blank, the Server Name, Login, and Password as configured above are used.

    • Connection String: If applicable, enter a connection string into the text box. The checkbox above must be selected in order for this field to be enabled.

      Tip

      Consult database-specific documentation for creating manual connection strings. Examples can also be found at https://www.connectionstrings.com.

      Note

      The connection string will be visible in clear text during configuration, so you may not want to include the username and password in the string. Instead of including these parameters and values in the string, use the Login and Password fields provided as part of the UI. They will then be appended to the connection string when needed, but will not be visible on the screen.

  • Test: Click to verify the connection to the database. When you test a Database connection, the quote character used by the database, either a single or a double quote, is identified. If you don't test the connection at this time, the opening and closing quote information will be fetched when you configure an activity.

  • Save Changes: Click to save and close the connection configuration.

  • Discard Changes: After making changes to a new or existing configuration, click to close the configuration without saving. A message asks you to confirm that you want to discard changes.

  • Delete: After opening an existing connection configuration, click to permanently delete the connection from the project and close the configuration (see Component dependencies, deletion, and removal). A message asks you to confirm that you want to delete the connection.

Next steps

Menu actions for a connection and its activity types are accessible from the project pane and design component palette. For details, see Actions menus in Connector basics.

After configuring a Database connection, you can configure one or more Database activities associated with that connection to be used either as a source to provide data to an operation or as a target to consume data in an operation, or you can use the Database connection in a script.

Configure activities

These activity types are available:

  • 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.)

Use Database connections in scripts

Database connections can be referenced in a script using script functions that use a databaseId as a parameter:

Jitterbit Script functions

JavaScript Jitterbit functions

For more details on referencing endpoints in scripts, see Endpoints in Jitterbit Script or Endpoints in JavaScript.