On this Page

{
firstName: "John". lastName: "Smith" 

Examples

Basic Use Case 1

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

In this pipeline the stored procedure 'date rs2 demo' from the 'enron' schema is invoked using the MySQL Stored Procedure Snap. The stored procedure is as below:

CREATE DEFINER=`mysqlqatest`@`%` PROCEDURE `date_rs2_demo`(IN inparam INT, OUT dc date, OUT dtc datetime, OUT tsc timestamp)
      BEGIN
           select dateC INTO dc from DateTypesDemo limit 1;
           select datetimeC INTO dtc from DateTypesDemo limit 1;
           select timestampC INTO tsc from DateTypesDemo limit 1;
           select * from DateTypesDemo;
           select * from AVemployees;	
      END

The value for the IN parameter is passed in the Parameter values property in the Snap: 

The output from the Snap are the OUT parameters and their values, and the result set from  the output parameters as seen in the images below:

Basic Use Case 2

In this example, the parameter values are passed to the MySQL Stored Procedure Snap through a Mapper Snap. 

In this case, the MySQL Stored Procedure Snap will invoke the following stored procedure:

DELIMITER $$

USE `enron`$$

DROP PROCEDURE IF EXISTS `basicDataTypes`$$

CREATE DEFINER=`mysqlqatest`@`%` PROCEDURE `basicDataTypes`(IN inParamInt INT, IN inParamVarchar VARCHAR(32), IN inParamBit BIT, OUT outParamInt INT, OUT outParamVarchar VARCHAR(32), OUT outParamBit BIT)
       BEGIN
            SET outParamInt= inParamInt;
            SET outParamVarchar= inParamVarchar;
            SET outParamBit= inParamBit;
       END$$

DELIMITER ;

In this stored procedure there is no result set; therefore, none will be generated when the pipeline is executed.

The Mapper Snap is used to pass the IN parameter's value to $id, the output preview can also be seen in the Output preview data section: 

The MySQL Stored Procedure Snap is configured as shown below. One of the IN parameters is passed as an expression. This is is the data received from the Mapper Snap:

As mentioned earlier, there will be no output result set because there is no result set from the stored procedure. The Result Set Ouput View will have no outputs and just the output parameters and their corresponding values will be shown by the Snap (in the Output Parameter output view). See the image below:

Typical Snap Configurations

The key configuration lies in how the SQL statement is passed. In this Snap it is possible to pass the values in the following ways:

• Without Expression
  The values can be passed directly in the Snap: 
  
  With Expression
  • Using Pipeline Parameters:
    The Parameter value is passed as a pipeline parameter.
    
    
      

    View file
    name 11.webp height 400

    

Advanced Use Case

The following pipeline is an example of how the MySQL Stored Procedure Snap is used in a scenario where data from a MySQL instance is inserted in a SQL instance. The pipeline can be downloaded from the Downloads section below:

In this pipeline there are two stored procedures that will be invoked - "enron.SP_Emp_Delete" and "enron.Multiple_Statements".

The first stored procedure is as shown below:

CREATE PROCEDURE enron.SP_Emp_Delete(IN empno1 INT,IN empno2 INT)
      BEGIN
           DELETE FROM sp_emp WHERE empid=empno1;
           DELETE FROM sp_emp WHERE empid=empno2;
      END

There are no OUT parameters in this stored procedure. No outputs will be shown in the Output Parameter output view.

The second stored procedure is as shown below:

CREATE PROCEDURE enron.Multiple_Statements(IN empno1 INT,empno2 INT, OUT result1  INT,OUT result2 INT)
      BEGIN
           INSERT INTO sp_emp (empid,ename) VALUES (empno1,'snaplogic1');
           INSERT INTO sp_emp (empid,ename) VALUES (empno2,'snaplogic12');
           SELECT empid INTO result1 FROM sp_emp WHERE ename='snaplogic1';
           SELECT empid INTO result2 FROM sp_emp WHERE ename='snaplogic2';
      END

This pipeline does the following ETL operations:

• Load: The first Mapper Snap maps the values "501", and "502" to the fields "empno1" and "empno2" in the target schema.
• Transform: The first MySQL Stored Procedure Snap invokes the stored procedure "enron.SP_Emp_Delete" that deletes the records in the table that match the values in the "empno1" and "empno2" fields.
• Load: The second Mapper Snap maps the values "501", "502", "snaplogic1", and "snaplogic12" to the "empno1", "empno2", "ename1", and "ename2" fields in the target schema.
• Load: The second MySQL Stored Procedure Snap invokes the stored procedure "enron.Multiple_Statements" that inserts the records into the target table.
• Extract & Load: The stored procedure "enron.Multiple_Statements" invoked by the second MySQL Stored Procedure Snap also extracts records from the table and writes the "empid" into the "result1" and "result2" fields where there is a matching record based on the "ename" field.
• Load: The data passed from the second MySQL Stored Procedure Snap is inserted into the specified SQL database by the SQL Server Insert Snap.
• Extract: The records in the specified SQL table are retrieved by the SQL Server Select Snap, demonstrating that the pipeline was executed successfully.

 The exported pipeline is available for download in the Download section.

Downloads