Page Comparison

On this Page

Table of Contents

maxLevel	2
exclude	Older Versions\|Additional Resources\|Related Links\|Related Information

Snap type:

Write

Description:

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.

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

Note
This Snap only supports write operations.

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

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

Expected upstream Snaps: A Mapper Snap, which transforms the input data into the exact structure that is needed by each statement of the Snap.
Expected downstream Snaps: The Snap will output one document for every document retrieved, hence any document processing Snap can be used down stream. Each output document will provide information about the statements that were executed.
Expected input: Document that conforms to variables needed for each statement.
Expected output: Document which provides information about the status of the execution, each statement is represented as an execution_<index>, where <index> is the row number in the Executions table. For convenience, each execution_<index> will provide the statement that was executed and the input data that was used to execute the statement.

Prerequisites:

[None]

Support and limitations:

Works in Ultra Task Pipelines if Batch Size is not selected.

Account:

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See MySQL Account for information on setting up this type of account.

Views:

Input

This Snap has exactly one document input view.

Output

This Snap has at most one document output view.

Error

This Snap has at most one document error view and produces zero or more documents in the view.

Note
Database write Snaps output all records of a batch (as configured in your account settings) to the error view if the write fails during batch processing.

Settings

Label

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)

Multiexcerpt include macro

name	ME_DB_Snaps_Query_Examples
page	Oracle - Update

Warning

title	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

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)

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

Multiexcerpt include macro

name	Snap Execution
page	SOAP Execute

Multiexcerpt include macro

name	Execution_Detail_Write
page	SOAP Execute

Examples

Update the order details and adjustments information into a Orders database (MySQL instance) in a single go

This example demonstrates how you can use the MySQL - Multi Execute Snap to execute multiple SQL queries on a MySQL database instance. The Pipeline contains order details and adjustments information (in the JSON Generator Snap) to be inserted and the SQL queries to be executed (in MySQL - Multi Execute Snap) for inserting this data into the corresponding tables in the MySQL database instance.

Download this Pipeline.

The Pipeline performs the following steps in that sequence.

Prepare input values using JSON Generator.
Define the table mappings using a Mapper.
Pass the values using these mappings to the multiple MySQL queries.

Example DDL file: Run the CREATE queries in this DDL file to create the required tables for running the sample SQL queries in MySQL Database.

Prepare input values using JSON Generator

JSON Generator

Input	Output

Define the table mappings using a Mapper

Mapper

Output

Pass the values using these mappings to the multiple MySQL queries

MySQL - Multi Execute

In path one, we select $adjustments; given the input, it returns a list of objects. The SQL statement can use variables that are the result of the path execution.

For example, jsonPath($, "$ADJUSTMENTS[*]) results in the following input for the SQL statement.

Code Block

 {
    "SO_ADJ_CD": "ZNAJ",
    "SO_ADJ_AM": "0.00",
    "LGCL_DEL_FG": "N",
    "CR_FG": "N",
    "MAIN_MCC_FG": "",
    "BUS_AREA_CD": "9600",
    "FUNC_AREA_CD": "A051",
    "PR_EFF_DT": ""
 },
 {
    "SO_ADJ_CD": "ZNAK",
    "SO_ADJ_AM": "0.00",
    "LGCL_DEL_FG": "N",
    "CR_FG": "N",
    "MAIN_MCC_FG": "",
    "BUS_AREA_CD": "9600",
    "FUNC_AREA_CD": "A051",
    "PR_EFF_DT": ""
 }

The INSERT statement creates a batch for every object in the list and execute it.

In path two, we select the $order_detail; given the input, it returns one object.

The INSERT statement executes one statement using the $order_detail object.

The statement, then, refers to the variables of the result using for example, $BUS_AREA_CD, not as the whole path to the variable in context of the input document: jsonPath($, "$ADJUSTMENTS[*].BUS_AREA_CD), instead the context is the result of the path evaluation.

A successful execution of all statements logs the transaction as successful.
Any failure of any of the statements causes a rollback of all issued operations for the given input document.

Output (Successful Execution)

One output document is written per input document. The output document provides executions, each reflecting the execution of one row.

The index of the execution_<index> represents the index of the statement in the Executions table.
Each execution provides information about the statement execution.
- When all statements are executed successfully, original key shows the data that was used as part of the statement execution (not the input document, but the data of the path evaluation).
- SqlStatement shows the statement that was executed.

Error details for troubleshooting SQL query executions

To gain a better understanding of the root cause in case of an unsuccessful query execution, enable the error view. The output displays the execution details with respective status (success / failure) of each execution.

In the example error output below, execution_1 of the current input document failed. The reason and resolution are provided, along with the stacktrace for debugging purpose.

Code Block

[
  {
    "execution_1": {
      "status": "failed",
      "sqlStatement": "Insert into wrongname VALUES (?, ?, ?, ?, ?)",
      "errorDocuments": [
        {
          "error": "Failed to flush batch.",
          "reason": "Table 'integration.wrongname' doesn't exist",
          "original": {
            "SO_LN_ITM_NR": "000010",
            "SO_DTL_CRT_DT": "2014-02-12",
            "SO_DTL_CLOSE_DT": "2014-02-12",
            "FXD_VALU_DT": "2014-02-12",
            "ORD_LN_CNCL_DT": ""
          },
          "resolution": "Please address the issue reported by the database.",
          "stacktrace": "org.jooq.exception.DataAccessException: 
          SQL [Insert into wrongname VALUES (?, ?, ?, ?, ?)]; 
          Table 'integration.wrongname' doesn't exist\n...."
        }
      ]
    },
    "execution_0": {
      "status": "success",
      "sqlStatement": "Insert into ADJUSTMENTS VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      "successfulDocuments": [
        {
          "message": "success",
          "status": 2,
          "original": {
            "SO_ADJ_CD": "ZNAJ",
            "SO_ADJ_AM": "0.00",
            "LGCL_DEL_FG": "N",
            "CR_FG": "N",
            "MAIN_MCC_FG": "",
            "BUS_AREA_CD": "9600",
            "FUNC_AREA_CD": "A051",
            "PR_EFF_DT": ""
          }
        },
        {
          "message": "success",
          "status": 2,
          "original": {
            "SO_ADJ_CD": "ZNAK",
            "SO_ADJ_AM": "0.00",
            "LGCL_DEL_FG": "N",
            "CR_FG": "N",
            "MAIN_MCC_FG": "",
            "BUS_AREA_CD": "9600",
            "FUNC_AREA_CD": "A051",
            "PR_EFF_DT": ""
          }
        }
      ]
    }
  }
]

Execute multiple SQL statements to insert, update and delete records from MySQL DB

This pipeline shows how to execute the multiple MySQL statements to insert records into the table, TAM_SAMPLE_PERSONS, using the MySQL Multi Execute Snap. We pass the values into the records via the upstream Mapper Snap.

The Mapper Snap maps the values to be updated into the table, TAM_SAMPLE_PERSONS .

In the MySQL Multi Execute Snap, the MySQL statement executions are as below:

Note that the Multi Execute Snap performs the Insert, Update and the Delete functions.

The successful execution of the pipeline displays the below output preview:

Downloads

Multiexcerpt include macro

name	download_instructions
page	OpenAPI

old

Attachments

upload

false

false	patterns	.slp, .zip, *.ddl
sortOrder	descending
sortBy	size

Insert excerpt

	MySQL Snap Pack
	MySQL Snap Pack
nopanel	true

Versions Compared

Old Version 28

New Version 29

Key

Settings

Examples

Update the order details and adjustments information into a Orders database (MySQL instance) in a single go

Prepare input values using JSON Generator

Define the table mappings using a Mapper

Pass the values using these mappings to the multiple MySQL queries

Error details for troubleshooting SQL query executions

Execute multiple SQL statements to insert, update and delete records from MySQL DB

Downloads