Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Introduction

A Database connection is configured using the Database connector, establishing access to a database. Once a connection is established, 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 script, or as a target to consume data in an operation or script.

Most ODBC- and JDBC-compliant databases are supported. For a complete list of supported databases, see Supported Endpoints and Protocols.

If you need to convert an existing Database connection from using an ODBC driver to using a JDBC driver, see Converting from ODBC to JDBC later on this page.

Creating or Editing a Database Connection

From the design canvas, open the Connectivity tab of the design component palette:

To configure a new Database connection, use the Show dropdown to filter on Connectors, and then click the Database connector block:

To configure an existing Database connection, use the Show dropdown to filter on Endpoints, and then double-click the Database connection block:

These open the configuration screen for the Database connection, covered next.

Anchor
configure
configure
Configuring a Database Connection

Configuration of a Database connection includes these fields:

Tip

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

  • Endpoint Name: Enter a name to use to identify the Database connection. The name must be unique be unique for each Database connection and connection and must not contain forward slashes slashes (/) or colons colons (:). As a specific connection and its activities are referred to as an endpoint, this This name is also used to identify the Database endpoint, which refers to both a specific connection and its activities.
  • Driver Type: If you wish to  To filter the list of drivers, select the button for the appropriate driver type.
  • Driver: Use the dropdown to select the appropriate driver for the database. Jitterbit Harmony currently supports 32-bit drivers.

    Info
    NOTE: Database drivers are automatically detected from the Harmony Agent. For Cloud Agents, Jitterbit provides commonly used drivers. 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. For reference information on configuring specific databases, see Database-Specific Information.
  • 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 will allow 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: Enter a custom port only if the database server is listening on a non-default port. This field will be is enabled only if the Use Default Port box is unchecked.
  • Optional Settings: Click to expand for additional 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 will be 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
      TIP: Consult database-specific documentation for creating manual connection strings. Examples can also be found at https://www.connectionstrings.com.
      Warning

      WARNING: 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 this button to verify the connection to the database. The test must be successful before you are able to save the connection configuration.

  • Save Changes: Click this button to save and close the connection configuration. If this button is not enabled, you must successfully test the connection first.

    Info

    NOTE: Unlike other connections, it is required to test a Database connection before any changes can be saved. This is required in order for the list of tables to be retrieved during Database activity configuration.

  • Discard Changes: After making changes to a new or existing configuration, click Discard Changes to click to close the configuration without saving. A message will ask asks you to confirm that you want to discard changes.
  • Delete: After opening an existing connection configuration, click Delete to 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

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

Use the Show dropdown to filter on Endpoints, and then click the Database connection block to display activities that are available to be used with a Database connection:

Image Added

For more information, see these activities:

  • Query: Queries data from a Database connection 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 connection and is used 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 connection and is used 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 connection and is used 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.)

Anchor
converting-from-odbc-to-jdbc
converting-from-odbc-to-jdbc
Converting from ODBC to JDBC

Converting a Database connection from using an ODBC driver to using a JDBC driver is usually a straightforward process. You may need to do so if you move a project from running on a Private Agent using an ODBC driver to a Jitterbit Cloud Agent Group which, as of Jitterbit Harmony 9.8, no longer support ODBC drivers in the Cloud Agent Groups.

If your connection uses the default options, you can quickly convert it following the steps outlined below. However, if you have used the option to specify additional connection string parameters or to construct the connection string manually, you will need to adjust those options to accommodate the differences between ODBC and JDBC.

Jitterbit Support is available to help you in converting your connection to JDBC if you require assistance with converting your Database connections or the revision of advanced options.

Converting Simple Database Connections

To convert a Database connection that currently uses an ODBC driver to one that uses a JDBC driver, follow these steps:

  1. Open the Database connection that uses an ODBC driver. In this example, a connection using the PostgreSQL driver was opened:

  2. Test the connection by clicking the Test button to make sure that it works:

  3. If the connection was successful, you can proceed with this conversion. If not, fix any errors before continuing.
  4. Check if any additional connection parameters were specified by clicking the Optional Settings at the bottom of the configuration screen:

    • If no additional connection parameters were specified beyond the defaults shown above, you can proceed with converting the Database connection following the remainder of these instructions.
    • If there are additional connection parameters specified other than the defaults shown above, see the next section on Converting Connections with Additional Connection Parameters.
  5. Change the Driver Type to JDBC, then use the Driver dropdown to select the appropriate JDBC driver. In this example, we selected the PostgreSQL JDBC driver.

  6. Test the revised connection by clicking the Test button to make sure that it works:

  7. Click the Save Changes button to save the revised Database connection.

Anchor
converting-connections-with-additional-connection-parameters
converting-connections-with-additional-connection-parameters
Converting Connections with Additional Connection Parameters

If your connection uses additional connection parameters beyond the default values shown above, either as Additional Connection String Parameters or the box Use Connection String was checked and the connection string was entered manually, additional work may be required to convert the connection from ODBC to JDBC. For example, the SQL Server ODBC Additional Connection String Parameters might be (to use NTLM Windows Authentication):

Code Block
integratedSecurity=true

An equivalent SQL Server jTDS JDBC driver Additional Connection String Parameters (to use NTLM Windows Authentication) would be:

Code Block
useNTLMv2=true

Connection string parameters are specific to the database driver you use (e.g. Microsoft SQL, MySQL, PostgreSQL, Oracle). Review the driver specifications to determine the required parameters. If you require assistance with converting connection strings or parameters for a JDBC driver, contact Jitterbit Support.

Panel
borderColor#65379B
titleColor#FFFFFF
titleBGColor#65379B
titleOn This Page
Table of Content Zone

Table of Contents
maxLevel3
minLevel2

Panel
borderColor#FF7C4C
titleColor#FFFFFF
titleBGColor#FF7C4C
titleRelated Articles
Panel
borderColor#00B886
titleColor#FFFFFF
titleBGColor#00B886
titleRelated Topics

HideElements
metastrue

Last updated: 

Lastmodifieddate