Configure the JDBC execution command

Use the variables you created to configure the command that Orchestration executes on the JDBC target.

Before you begin

Create the input variables you need in the Inputs form before you can advance to the Execution Command stage.

Role required: activity_creator, admin

Note: You can test the JDBC connection between the MID Server and the target without having to run the acvitity in a workflow context. For details, see Test JDBC activity template inputs.

Procedure

  1. Drag variables from the list of inputs and drop them into command fields.
    The system formats the variable in the proper syntax for the command.
    Figure 1. JDBC execution command
    JDBC execution command
  2. Complete the fields shown in the table.
    Table 1. JDBC execution command fields
    Field Description
    Input Input variable builder. Create variables to map to available fields.
    Note: The auto-mapping feature provides the variables when enabled.
    JDBC Connection Appropriate JDBC connection for the database. The connection selected provides the activity with the following information:
    • Target database information, such as server and data base names.
    • Connection URL
    • Target database port (if different from standard port number)
    • Database instance name

    For more information, see JDBC connections.

    Credential JDBC credential to use for this connection.
    SQL statement Customer provided SQL statement. This field is hidden when the Stored Procedure check box is selected.
    By default, this activity is allowed to run the following SQL statements:
    • select
    • insert
    • update
    • delete
    • show
    • create
    • describe
    The JDBC Activity template allows you to execute any ANSI SQL or stored procedure on a target database, if the supplied credential has permission. As an added security measure, the operations that the JDBCOrchestrationProbe can execute are controlled by a MID Server property which restricts the SQL commands that can be used by the JDBC Activity template. This ensures you cannot use potentially destructive commands, such as drop database. The mid.property.jdbc_operations contains the MID Server property controls.
    Note: You cannot run multiple statements of different types. For example, you cannot run a select statement and an update statement together, but you can run two insert statements. However, you cannot run multiple select statements in this activity.
    Auto-map to Automatically maps the entire payload to selected variables. The choices in this field are:
    • None
    • Local Variable: Translates the entire payload into a JSON object and places it in the data bus. This allows for post-processing manipulation in JavaScript. This selection causes the entire data field on the right to disappear and the inputs structure to be autopopulated with these default variables:
      • output
      • totalRows
      • errorMessage
      • eccSysId
    • Output Variable: Automatically populates the output variables in the activity with the same default variables used as inputs for the local variable.
    Note: No parsing rules are available with auto-mapping selections.
    Maximum rows Maximum number of records to retrieve from database.
    Connection timeout How long the activity waits to make the connection. This field is populated automatically from the data source, but can be changed for this activity.
    Query timeout Elapsed time to wait after running the query until the data is returned. This field is populated automatically from the data source, but can be changed for this activity.
    Required MID Server capabilities MID Server with the appropriate capabilities for connecting to the external data source. By default, the system selects a MID Server with JDBC capabilities.
    Use stored procedure Selects whether to use a stored procedure that contains preconfigured input parameters. When this check box is selected, the system hides the SQL statement field and displays the Stored procedure name and Stored procedure parameter fields.
    Stored procedure name Name of your stored procedure. The system populates the input parameters using values from this stored procedure. This field is displayed when the Use stored procedure check box is selected.
    Note: Stored procedures can only execute select statements.
    Stored procedure parameters Parameters from the selected stored procedure. Add the parameter values you want for each type.
    • Mode: Parameter type.
    • SQL Type: Data type of the parameter.
    • Name: Parameter name.
    • Value: Value you want passed to the parameter.
    JDBC activity stored procedure
  3. Click Save.
  4. Click Continue to advance to the Outputs stage.