On this Page
Table of Contents | ||||
---|---|---|---|---|
|
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).
| |||||||||||||||
Prerequisites: | [None] | |||||||||||||||
Support and limitations: | Works in Ultra 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: |
| |||||||||||||||
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. Example:
| |||||||||||||||
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 | |||||||||||||||
|
|
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.
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,
In path two, we select the $order_detail; given the input, it returns one object.
The statement, then, refers to the variables of the result using for example,
| |||
Output (Successful Execution) | |||
One output document is written per input document. The output document provides executions, each reflecting the execution of one row.
|
Error details for troubleshooting SQL query executions
To gain a better understanding on what went wrong in 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
Attachments | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|