Versions Compared

Key

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

Multiexcerpt include
MultiExcerptNamedesign-studio-banner
PageWithExcerptDesign Studio

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. Currently, Jitterbit only supports 32-bit drivers.

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

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. 

Info
NOTE: Because these key fields are defined within the database environment, you cannot change their setting in the Transformation Wizard.

Database Types

Most ODBC- or JDBC-compliant databases are supported. See Supported Endpoints and Protocols for a list of specific database types. The following are specific recommendations for various database types.

Working 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: 

Working 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:

Working 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.

Info
NOTE: Some databases only require a file path for the Database Name field; no file name is required. In the table below, note that the DBASE and Paradox Database Name entries do not provide any file name. In addition, note that some flat-file databases will require a manual connection string.

Database Type

Driver

Database Name

Additional Connect String Parameters (if required)

Access

Microsoft Access Driver (*.mdb)

\\solar\saturn\demo\access\demo.mdb


Access (FileDSN)

Microsoft Access Driver (*.mdb)

\\solar\saturn\demo\access\demo.mdb

FileDSN=Access.dsn;

DBASE

MicrosoftdBase Driver (*.dbf)

\\solar\saturn\demo\dbase


DBASE (FileDSN)

MicrosoftdBase Driver (*.dbf)

\\solar\saturn\demo\dbase

FileDSN=dbase.dsn;

Excel

Microsoft Excel Driver (*.xls)

\\solar\saturn\demo\excel\demo.xls


Excel (FileDSN)

Microsoft Excel Driver (*.xls)

\\solar\saturn\demo\excel\demo.xls

FileDSN=Excel.dsn;

Paradox

Microsoft Paradox Driver (*.db)

\\solar\saturn\Paradox

ParadoxNetPath=\\solar\saturn\Paradox;DefaultDir=\\solar\saturn\Paradox;

Paradox (FileDSN)

Microsoft Paradox Driver (*.db)

\\solar\saturn\Paradox

FileDSN=Paradox.dsn;

For more information, see these pages:

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

Table of Contents
maxLevel3
minLevel2
indent20px

Panel
borderColor#FF7C4C
titleColor#FFFFFF
titleBGColor#FF7C4C
titleRelated Articles
Content by Label
showLabelsfalse
max1000
showSpacefalse
sorttitle
cqllabel in ("database","endpoints") and space = "DOC"
Panel
borderColor#00B886
titleColor#FFFFFF
titleBGColor#00B886
titleRelated Topics
Content by Label
showLabelsfalse
max1000
showSpacefalse
sorttitle
cqllabel in ("source-overview","target-overview") and space = "DOC"

HideElements
metastrue
titletrue

Last updated: 

Lastmodifieddate