Versions Compared

Old Version 43

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 44

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

You can use the Azure SQL - Execute Snap to execute single simple DML (SELECT, INSERT, UPDATE, DELETE) type statements.

  • This Snap work works only with single queries.

  • For the comprehensive scripting functionality offered by the various databases, we recommend you to use the stored procedure functionality offered by their chosen database in the Stored Procedure Snap.

...

Multiexcerpt macro
nameBehavior change in Database Execute Snaps

  • In 4.26, when the stored procedures were called using the Database Execute Snaps, the queries were treated as write queries instead of read queries. So the output displayed message and status keys after executing the stored procedure.
    In 4.27, all the Database Execute Snaps run stored procedures correctly, that is, the queries are treated as read queries. The output now displays message key, and OUT params of the procedure (if any). The status key is not displayed.

  • If the stored procedure has no OUT parameters then only the message key is displayed with value success.

If you have any existing Pipelines that are mapped with status key or previous description then those Pipelines will fail. So, you might need to revisit your Pipeline design.


Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 0

  • Max: 1

  • Mapper

  • Copy

  • JSON Generator

  • The dynamic variables used in the execute query can be defined by providing values upstream.

  • If the input view is defined, then the where clause substitutes incoming values for a specific query.

Output

Document

 

  • Min: 0

  • Max: 1

  • JSON Formatter

  • Any document processing Snap can be used downstream.

  • The Snap produces documents in the output view.

  • 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.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

  • Stop Pipeline Execution: Stops the current Pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

...

  • The SQL statement must not be within quotes. 

  • The $<variable_name> parts of the SQL statement are expressions. In the below example, $id and $book.

Examples:

...

Multiexcerpt macro
nameME_DB_Execute_Snaps_Scenario1

Additionally, the JSON path is allowed only in the WHERE clause. If the SQL statement starts with SELECT (case-insensitive), the Snap regards it as a select-type query and executes once per input document. If not, it regards it as write-type query and executes in batch mode.

...

Multiexcerpt macro
nameME_DB_Execute_Snaps_Scenario2

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"

  • "emp='" + $emp + "'"

  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"

Note

Table name and column names must not be provided as bind parameters. Only values can be provided as bind parameters.

...

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. Ensure that you pass two consecutive single quotes in place of one within these values to escape the single quote 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'

...

Examples

Azure SQL - Execute Snap as a Standalone Pipeline

The following pipeline describes how the Snap functions as a standalone Snap in a pipeline:

  • Extract: The SQL statement, select * from <table_name>, extracts the Azure table data.

 

Typical Snap Configurations

The key configuration of the Azure SQL - Execute lies in how you pass the SQL statement to read Azure records. As it applies in SnapLogic, you can pass SQL statements in the following manner:

...

  1. Extract: The SQL Server Select Snap reads the data from the SQL Server Database.

  2. Load: The Azure SQL Execute Snap inserts the data into an Azure SQL table.

  3. Read: Another Execute Snap is used to read the data from the newly loaded table on the Azure SQL database.

Downloads

Multiexcerpt include macro
namedownload_instructions
pageOpenAPI

...