Versions Compared

Key

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

Introduction

A Database Query activity interacts with a Database connection to query data as a source in an operation. After configuring a Database connection, you can configure as many Database activities as you like for each Database connection.

Creating a Database Activity

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

Within the Endpoints filter, click the Database connection block to display activities that are available to be used with a Database connection:

To create an activity that can be configured, the activity must first be added to an operation on the design canvas. To add an activity to an operation, drag the activity block from the palette to the operation.

For more information about the parts of an operation and adding activities to operations, see Operation Creation and Configuration.

Accessing Menu Actions

After a Database activity has been added to an operation, menu actions for that activity are accessible from the project pane in both the Workflows and Components tabs, and from the design canvas:

  • Project Pane: In the Workflows or Components tab of the project pane, hover over an activity name and click the actions menu icon  to open the actions menu.

  • Design Canvas: Within the operation, click an existing activity block to open the actions menu.

Each of these menu actions is available:

  • View/Edit: This opens the activity configuration screen for you to configure the activity. For details, see Configuring a Database Query Activity later on this page.
  • Delete: This is used to permanently delete the activity (see Component Dependencies, Deletion, and Removal).
  • Rename: This positions the cursor on the activity name in the project pane for you to make edits.
  • View Dependencies: This changes the view in the project pane to display any other parts of the project that the activity is dependent on (see Component Dependencies, Deletion, and Removal).
  • Remove: Available only from the actions menu on the design canvas, this removes the activity as a step in the operation without deleting it from the project. When you remove an activity that is adjacent to a transformation, if schemas are provided within that activity, they will no longer be referenced by the transformation. Removed components can be accessed or permanently deleted from the project pane (see Component Dependencies, Deletion, and Removal).
  • Deploy: This deploys the activity and any components it is dependent on (see Component Deployment).
  • Configurable Deploy: This opens the deployment screen, where you can select project components to deploy (see Component Deployment).
  • Add to group: This opens a prompt to create a new custom group or to add the component to an existing group. Custom groups are an organizational tool to help organize a project (see Component Groups).
  • Duplicate: This creates a copy of the activity as a new, unreferenced component. Upon creating the component copy, the cursor is positioned on the component name within the project pane for you to rename the component.

Anchor
configuring-a-database-query-activity
configuring-a-database-query-activity
Configuring a Database Query Activity

The steps for configuring a Database Query activity differ depending on the use case and whether you already know the SQL statement:

  • Single-Object Query Using Wizard: Follow these steps to query only a single object, without joins, when the SQL statement is unknown.
  • Multiple-Object Query Using Wizard: Follow these steps to query multiple objects, with joins, when the SQL statement is unknown.
  • Manual Query: Follow these steps to query either single or multiple objects by manually providing a SQL statement. This option is available only with Database connections using a JDBC driver, and is not available for those configured using ODBC.

Anchor
single-object-query
single-object-query
Single-Object Query Using Wizard

Follow these steps to use the wizard if you are querying a single object, and do not need to create any joins with other objects. If you need to join multiple objects, refer to Multiple-Object Query Using Wizard, or if you already know the SQL statement and are using a JDBC driver see Manual Query.

Anchor
step-1-single-object-wizard
step-1-single-object-wizard
Step 1 – Enter Basic Information and Select Object

  • Name: Enter a name to use to identify the Database activity. The name must be unique for each Database Query activity and must not contain forward slashes (/) or colons (:).
  • Select Table(s): This section displays the tables available in the Database endpoint using any applied filters, after using the refresh icon  for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing. If fetching the objects times out even after applying filters, another option is to use a manual SQL statement.
    • View: Use the dropdown to filter the list of objects to tables, views, or system tables. By default, tables and views are shown.

    • Search: Enter any part of the object name into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

      • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box will automatically filter the list.

      • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

      Note
      CAUTION: For databases that do not support searching on objects, such as Microsoft Access, no results will be returned.
    • Refresh: Click the refresh icon  to load or reload tables from the Database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

    • Include the schema/owner in the table names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.
    • Select Table(s): The list of objects is displayed in a column on the left. Click on a single object only to add it to the table on the right. If instead you need to join multiple objects, refer to Multiple-Object Query Using Wizard.

      Tip
      TIP: If the list does not populate with available objects even after applying a filter, the Database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.
    • Table of Selected Objects: The selected object is displayed in a table on the right. The columns in this table are not relevant for single-object queries and these fields cannot be edited, except to remove the object (hover over the far right column and click the trash icon ). If instead you need to join multiple objects, refer to Multiple-Object Query Using Wizard.

  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Skip Wizard / Write SQL Statement: If present, do not click the words Skip Wizard / Write SQL Statement, as this will exit the wizard.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-2-single-object-wizard
step-2-single-object-wizard
Step 2 – Add Conditions

Tip

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

  • Select Fields: This section displays fields for the selected object(s) that are available in the Database endpoint. Objects that were selected in the previous step are shown in bold.
    • Search: Enter any part of the field name into the search box to filter the list of fields. The search is not case-sensitive. The listed results will be filtered in real time with each keystroke.
    • Select All: When using the search box to filter, you can use this checkbox to select all visible fields at once (objects and collapsed fields are not selected).
    • Select Fields: Select the checkboxes of the fields you want included in the query to have them automatically added to the SQL statement on the right. Unless using the search box, you can also select an entire object to select all of its fields at once.
  • SQL Statement: The query statement will autopopulate with the object and any fields selected on the left.
  • WHERE Clause (Optional): To add a WHERE clause, you can use these fields as input to help construct the clause, or you can manually enter the clause in the lower text box. For examples of WHERE clauses, see Database-Specific Information.
    • Object: Field: Use the dropdown to select a field from the selected object.
    • Operator: Use the dropdown to select the appropriate operator(s):

      Arithmetic
      +Add
      -Subtract
      *Multiply
      /Divide
      %Modulo
      Bitwise
      &AND
      |OR
      ^Exclusive OR
      Comparison
      =Equal to
      >Greater than
      <Less than
      >=Greater than or equal to
      <=Less than or equal to
      !=Not equal to
      Compound
      +=Add equals
      -=Subtract equals
      *=Multiply equals
      /=Divide equals
      %=Modulo equals
      &=Bitwise AND equals
      ^-=Bitwise exclusive equals
      |*=Bitwise OR equals
      Logical
      ALLTRUE if all the subquery values meet the condition
      ANDTRUE if all the conditions separated by AND are TRUE
      ANYTRUE if any of the subquery values meets the condition
      BETWEENTRUE if the operand is within the range of comparisons
      EXISTSTRUE if the subquery returns one or more records
      INTRUE if the operand is equal to one of a list of expressions
      LIKETRUE if the operand matches a pattern
      NOTDisplays a record if the condition(s) is NOT TRUE
      ORTRUE if any of the conditions separated by OR is TRUE
      SOMETRUE if any of the subquery values meets the condition
    • Value: Enter the desired value to use with the dropdown selections.

      Tip
      TIP: When using global variables in a WHERE clause, it is recommended to specify a default value so that script testing is possible. Otherwise, since global variables obtain their value at runtime, the syntax may be invalidated during testing if no default value is specified.
    • Add: Click the  link to automatically construct the clause based on the dropdown selections and entered value. The automatically constructed clause will appear in the lower text box. 

    Note
    CAUTION: If providing a WHERE clause manually, you must enter it in the syntax that the database expects. You can also manually include an ORDER BY clause in this section. Using ORDER BY on child field(s) will also affect the parent records.
    Tip
    TIP: To add multiple conditions to the WHERE clause, repeat the process by adding additional selections. Note that you may need to add a logical operator such as AND or OR to account for the additional conditions.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-3-single-object-wizard
step-3-single-object-wizard
Step 3 – Review Data Schema

  • Data Schema: The source data schema is displayed. If the operation uses a transformation, the data schemas will be displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Finished: Click to save the configuration for all steps and close the activity configuration.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
multiple-object-query
multiple-object-query
Multiple-Object Query Using Wizard

Follow these steps to use the wizard if you are querying multiple objects, and need to create joins between them. If you need to query a single object, refer to Single-Object Query Using Wizard, or if you already know the SQL statement and are using a JDBC driver see Manual Query.

Anchor
step-1-multiple-object-wizard
step-1-multiple-object-wizard
Step 1 – Enter Basic Information and Select Object

  • Name: Enter a name to use to identify the Database activity. The name must be unique for each Database Query activity and must not contain forward slashes (/) or colons (:).
  • Select Table(s): This section displays the tables available in the Database endpoint using any applied filters, after using the icon  for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing. If fetching the objects times out, even after applying filters, another option is to use a manual SQL statement.
    • View: Use the dropdown to filter the list of objects to tables, views, or system tables. By default, tables and views are shown.

    • Search: Enter any part of the object name into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

      • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box will automatically filter the list.

      • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

      Note
      CAUTION: For databases that do not support searching on objects, such as Microsoft Access, no results will be returned.
    • Refresh: Click the refresh icon  to load or reload tables from the Database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

    • Include the schema/owner in the table names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.
    • Select Table(s): The list of objects is displayed in a column on the left. Click on objects one at a time to add them to the table on the right. Any combination of tables, views, and/or system tables may be selected. If instead you need to query a single object without joins, refer to Single-Object Query Using Wizard.

      Tip
      TIP: If the list does not populate with available objects even after applying a filter, the Database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.
    • Table of Selected Objects: The selected objects are displayed in a table on the right. Each column is detailed below.

      • Table: The name of the selected object, including table, view, or system table.

      • Parent: On each child object being joined, use the dropdown to select the name of the object that should be the parent. This is needed to enforce transactional integrity of an object as well as to derive the link key(s) for a child object. 

      • Join Type: On each child object being joined, a dropdown will become available after you have assigned Link Keys (covered next). Use the dropdown to set the type of relationship between the selected objects:

        • One or More: This join type requires that every parent record has at least 1 child record, and possibly more child records.
        • One Only: This join type requires that every parent record has 1 and only 1 child record.
        • Zero or More: This join type allows every parent record to have either 0 or more child records.
        • Zero or One: This join type allows every parent record to have either 0 child records or only 1 child record.

      • Link Keys: On each child object being joined, when an object is selected as the Parent, a red Assign link appears within this field. Click Assign to open a separate window where you assign the link keys:

        • Parent Object: The parent object and list of fields is displayed in the left column. Any fields used as primary keys within the database will be indicated by a key icon . If you have a lot of fields, you may also use the search box to filter fields by name.

        • Child Object: The child object and list of fields is displayed in the right column. If you have a lot of fields, you may also use the search box to filter fields by name.

        • Assign Link Key(s): To assign a field as a link keys, drag a field from the parent object on the left to a field within the child object on the right, or vice versa. If you would like to assign multiple link keys, repeat this process.

        • Unassign Link Key(s): To unassign link keys, click on any field that has already been linked. The linked fields will become deselected.

        • Start Over: To clear all assigned link keys, click the Start Over link located below the parent list on the left.
        • Finish: Click the Finish button to save the assigned link keys and close the window. Under Link Keys, you should now see the number of assigned link keys, which you can click to reopen the link key assignment window. In addition, the Join Type dropdown becomes available for you to set the type of relationship between the selected objects:

      • Remove: To remove a selected object from the table, hover over the far right column and click the trash icon .
  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. This button will not be enabled until after you have specified any Parent objects, set the Join Type, and assigned Link Keys. The configuration will not be saved until you click the Finished button on the last step. 
  • Skip Wizard / Write SQL Statement: If present, do not click the words Skip Wizard / Write SQL Statement, as this will exit the wizard.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-2-multiple-object-wizard
step-2-multiple-object-wizard
Step 2 – Add Conditions

Tip

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

  • Source: This section displays fields for the selected object that are available in the Database endpoint. If a field is identified by the database as a key, then this field must be included.

    • Search: Enter any part of the field name into the search box to filter the list of fields for the selected object. The search is not case-sensitive. The listed results will be filtered in real time with each keystroke.
    • Select All: Select the checkbox to select all visible objects and fields at once (collapsed objects are not selected). This selection mode is not available if object-level selections are in use, but is available when using the search box.
    • Select Fields: Select the checkboxes of the fields you want included in the query to have them automatically added to the SQL statement on the right. You can also select an entire object to select all of its fields at once.
  • Table of Object Fields: The selected objects and their fields are displayed in tables on the right, with optional fields to add conditions and sorting:
    • Filter Condition: If desired, enter a condition in the form of the argument of a WHERE clause; that is, operator(s) followed by value(s). For examples of WHERE clauses, see Database-Specific Information.

      Note
      CAUTION: If providing a WHERE clause manually, you must enter it in the syntax that the database expects. 
      Tip
      TIP: When using global variables in a WHERE clause, it is recommended to specify a default value so that script testing is possible. Otherwise, since global variables obtain their value at runtime, the syntax may be invalidated during testing if no default value is specified.
    • Sort: If desired, use the dropdown to select from None, Ascending, or Descending to apply a sort order. Selecting the None option is the same as leaving this field blank. 

      Note
      CAUTION: Using ORDER BY on child field(s) will also affect the parent records.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-3-multiple-object-wizard
step-3-multiple-object-wizard
Step 3 – Review Data Schema

  • Data Schema: The source data schema is displayed. If the operation uses a transformation, the data schemas will be displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Finished: Click to save the configuration for all steps and close the activity configuration.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
manual-query
manual-query
Manual Query

Follow these steps if you know the SQL statement for the query and want to provide it manually. This option is available only with Database connections using a JDBC driver, and is not available for those configured using ODBC. If you do not already know the statement or if the connection uses ODBC, follow either Single-Object Query Using Wizard or Multiple-Object Query Using Wizard above.

Anchor
step-1-manual-query
step-1-manual-query
Step 1 – Enter Basic Information

  • Name: Enter a name to use to identify the Database activity. The name must be unique for each Database Query activity and must not contain forward slashes (/) or colons (:).
  • Select Table(s): Do not use this section, as it is not relevant for manual queries. This section is applicable only when using the Database Query wizard (refer to Single-Object Query Using Wizard or Multiple-Object Query Using Wizard).
  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: This button should not be enabled, as it is applicable only when using the Database Query wizard (refer to Single-Object Query Using Wizard or Multiple-Object Query Using Wizard).
  • Skip Wizard / Write SQL Statement: Click Skip Wizard / Write SQL Statement to continue to the next step in providing the manual SQL statement.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-2-manual-query
step-2-manual-query
Step 2 – Write or Paste SQL Statement

  • Write or Paste SQL Statement: Enter the SQL query statement into the text box, including any WHERE or ORDER BY clauses as applicable. Using ORDER BY on child field(s) will also affect the parent records. The query statement must be provided in the syntax that the database expects. To use variables, enter them using the square bracket syntax described in documentation for global variablesproject variables, or Jitterbit variables. For examples of WHERE clauses, see Database-Specific Information.

    Info
    NOTE: All SQL statements should be ANSI-SQL compliant for the ODBC or JDBC driver of the database you are accessing. There is a 2,000-character limit to the SQL statements. A recommended workaround to the character limitation, if needed, is to create database views for long SQL statements.
    Tip
    TIP: When using global variables in a WHERE clause, it is recommended to specify a default value so that script testing is possible. Otherwise, since global variables obtain their value at runtime, the syntax may be invalidated during testing if no default value is specified.
    Tip
    TIP: You can also use User-Defined Functions (UDF) within the SQL statement using the <UDF> function. This function lets you use any database function supported by the target database, such as the TO_DATE function in Oracle, or the CONVERT() function in SQL Server. See details under Database Functions.
  • Test Query: Click this button to validate whether the query is valid. If the query is valid, a list of the fields and information about them will be retrieved and displayed in a table similar to that shown below. If the query is not valid, relevant error messages will be displayed. The query must be valid in order to enable the Next button.

    • Name: The name of the field in the object.

    • Type: The database-specific data type of the field.

    • Primary Key: This indicates whether the field is defined as a primary key within the database. A field that is defined as a primary key is used in indexing the database's information. Primary keys are set within the database and cannot be changed within Jitterbit Harmony.

    • Nullable: This indicates whether the field can be set to null.

    • Dismiss: Click Dismiss to close the window.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Anchor
step-3-manual-query
step-3-manual-query
Step 3 – Review Data Schema

  • Data Schema: The source data schema is displayed. If the operation uses a transformation, the data schemas will be displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Finished: Click to save the configuration for all steps and close the activity configuration.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Next Steps

After configuring a Database Query activity, you can use it within an operation or script as described below. In addition, if you are running into record limits imposed by the target, you may want to use chunking.

Completing the Operation

After configuring a Database Query activity, complete the configuration of the operation by adding and configuring other activitiestransformations, or scripts as operation steps (see Operation Creation and Configuration). You can also configure an operation's operation settings, which include the ability to chain operations together that are in the same or different workflows (see Operation Settings).

Database Query activities can be used as a source with these operation patterns:

Other patterns are not valid using Database Query activities. See the validation patterns on the Operation Validity page.

This example setup depicts one common operation setup using a Database Query activity:

Within a transformation, if using the option to mirror a schema provided by a Database activity using a single table (without joins), the resulting mirrored schema will automatically be created with an additional node called row. When fields within this additional node are mapped to, the node becomes a loop node to allow all records to be looped through (see Loop Nodes under  under Nodes and Fields).

In addition, if joining database tables, note that the values of a child table's joined fields default to the values of the parent table's joined fields. A child table's joined fields cannot be mapped to.

When ready, deploy and run the operation (see Operation Deployment and Execution) and validate behavior by checking the operation logs (see Operation Logs).

Using Database Activities in Scripts

Database Query activities that have already been configured as part of an operation can also be referenced in a script for use with certain script functions that use a databaseId as a parameter. For example:

To add a configured Database activity to a script to be referenced by one of these functions, drag the configured activity from the Endpoints tab of the script component palette to the script. Or, if you already know the function you want to use, add it from the Functions tab first; then position the cursor after the opening parenthesis of the function and press Control+Space to display a list of autocomplete suggestions. Select an activity to insert the activity reference into the script.

For more details on referencing activities in scripts, see Endpoints on the Jitterbit Script page.

Using Chunking

When using chunking on an operation where the source is a Database activity, the data will be chunked in the transformation, not when the data is retrieved. The target data will first be written to numerous temporary files (one for each chunk). These files will then be combined to one target file. 

For instructions and best practices on using chunking, see Operation Options.