MySQL Multi Execute

Overview

This Snap allows you to execute multiple SQL statements for each document. Each set of statements is executed as one transaction (per document). The Snap will substitute values from an incoming document into the SQL statement if it finds a JSONPath substring in the statement. If a substitution value is missing in the incoming document then the document will be written to an error view.

Multiple documents cannot be executed as part of one transaction, the relationship is one transaction per document.

This Snap only supports write operations.

The account being used must have auto commit set to false, otherwise the Snap will fail with a configuration exception.

CAUTION: You can drop your database with it, so be careful.

Prerequisites

A valid account with appropriate privileges to execute the SQL statements in the target MySQL database.

Limitations

Works in Ultra Tasks if Batch Size is not selected.

Snap views

Type Description Examples of upstream and downstream Snaps
Input Each input document contains one or more SQL statements to execute. The Snap processes all statements within a transaction.
Output Each output document contains the execution results for all SQL statements, including row counts affected and any result sets returned.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description
Label

String		 Required.The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.
Executions Required.A list of statements that are executed as one transaction.
  • Path

    A JSONPath that returns a list or object from the input document.

    Example: jsonPath($, "$ADJUSTMENTS[*]")

  • SQL Statement

    A SQL statement that uses the result of the path as part of the statement substitution.

    Examples:

    Insert into ADJUSTMENTS VALUES ($BUS_AREA_CD, $CR_FG, $FUNC_AREA_CD, $LGCL_DEL_FG, $MAIN_MCC_FG, $PR_EFF_DT, $SO_ADJ_AM, $SO_ADJ_CD)

    Without using expressions

    Using expressions

    • "EMPNO=$EMPNO and ENAME=$EMPNAME"
    • email = $email
    • emp=$emp
    • "emp='" + $emp + "'"
    • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
    CAUTION: Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled.
    Warning: Single quotes in values must be escaped

    Any relational database (RDBMS) treats single quotes (') as special symbols. So, single quotes in the data or values passed through a DML query may cause the Snap to fail when the query is executed using MySQL. Ensure that you pass two consecutive single quotes ('') or a single quote preceded by a backslash (\') in place of each single quote within these values to escape it through these queries.

    For example:

    If String To pass this value Use
    Has no single quotes Schaum Series 'Schaum Series'
    Contains single quotes O'Reilly's Publication 'O''Reilly''s Publication' OR 'O\'Reilly\'s Publication'
Number of retries

Integer

 Specifies the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

Example: 3

Default value: 0
Retry interval (seconds)

Integer

 Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.

Example: 10

Default value: 1
Snap execution

Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute. Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only. Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled. Disables the Snap and all Snaps that are downstream from it.

Examples