MySQL - Multi Execute
On this Page
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 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: |
| |||||||||||
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:
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. Single quotes in values must be escaped Any relational database (RDBMS) treats single quotes ( For 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 | |||||||||||
Snap execution | Select one of the three modes in which the Snap executes. Available options are:
| |||||||||||
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, {
"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": ""
}
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 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.
[
{
"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
Important steps to successfully reuse Pipelines
- Download and import the Pipeline into SnapLogic.
- Configure Snap accounts as applicable.
- Provide Pipeline parameters as applicable.
Snap Pack History
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.