Skip to end of metadata
Go to start of metadata

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:

Configuring a Database Query Activity

The steps for configuring a database query 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.

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.

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

      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 connection. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects or fields to the database that you need to query or map to, or if you have additional search criteria that you wish to apply prior to retrieving database objects. This action refreshes all metadata for all areas within Cloud Studio, including within the transformation.

    • 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: 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: Click this button to save the configuration for this step and close the activity configuration.
  • Next: Click Next to save the configuration for this step and continue to the next 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 to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

Step 2 – Add Conditions

  • 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. If a field is identified by the database as a key, then this field must be included.

  • 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. To use variables, enter them using the square bracket syntax described in documentation for global variablesproject variables, or Jitterbit variables.

      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. 

    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: 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 Back to save the configuration for this step and return to the previous step.
  • Next: Click Next to save the configuration for this step and continue to the next step.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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 Back to save the configuration for this step and return to the previous step.

  • Finished: Click Finished to save the configuration for this step and close the activity configuration.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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.

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

      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 connection. 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. This action refreshes all metadata for all areas within Cloud Studio, including within the transformation.

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

        • 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: Click this button to save the configuration for this step and close the activity configuration.
  • Next: Click Next to save 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.
  • 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 to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

Step 2 – Add Conditions

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: Select the checkboxes of the fields you want included in the query. You can also select an entire object to select all of its fields at once. If a field is identified by the database as a key, then this field must be included.

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

      CAUTION: If providing a WHERE clause manually, you must enter it in the syntax that the database expects. 
      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. 

      CAUTION: Using ORDER BY on child field(s) will also affect the parent records.
  • Back: Click Back to save the configuration for this step and return to the previous step.
  • Next: Click Next to save the configuration for this step and continue to the next step.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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 Back to save the configuration for this step and return to the previous step.

  • Finished: Click Finished to save the configuration for this step and close the activity configuration.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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.

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: Click this button 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 to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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.

    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: 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: 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 Back to save the configuration for this step and return to the previous step.
  • Next: Click Next to save the configuration for this step and continue to the next step.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this step. A message will ask you to confirm that you want to discard changes.

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 Back to save the configuration for this step and return to the previous step.
  • Finished: Click Finished to save the configuration for this step and close the activity configuration.
  • Discard Changes: After making changes to this step, click Discard Changes to close the configuration without saving changes to this 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:

  • Transformation Pattern
  • Two-Target Archive Pattern
  • Two-Target Transformation Pattern

Other patterns are not valid using database query activities. In addition, if an operation uses a database activity as an operation step, it must also use a transformation as an operation step. For validation rules and operation patterns, see Operation Validity.

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 Nodes and Fields).

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 (or Command+Space on Mac) to bring up a list of autocomplete suggestions. The activity reference will be inserted 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, 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.

On This Page

Last updated:  Aug 07, 2019

  • No labels