MySQL - Stored Procedure
On this Page
| Snap Type: | Write | ||||||
|---|---|---|---|---|---|---|---|
| Description: | This Snap executes a stored procedure in a MySQL database and writes the values of OUT and INOUT parameters to the output views. ETL Transformations & Data FlowThe Snap invokes the Stored procedure, and the ETL operations are carried out as specified on the stored procedure. Ideally, there is no data flow from the Snap to the database, however, the results computed in the store procedure or the result of queries executed in the stored procedure can be fetched by the Snap through output parameters. Input & Output
{
firstName: "John". lastName: "Smith"
Modes
| ||||||
| Prerequisites: | None at the moment. | ||||||
| Limitation and Known Issues | When MySQL execute Snaps (MySQL - Execute and MySQL - Multi Execute) are followed by MySQL non-execute Snaps, such as MySQL - Insert, MySQL - Merge, and so on, the following error is displayed when executed:
This happens due to a known issue in the MySql Connector. For more information about this issue, see MySQL Bug #65378. | ||||||
| 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. | ||||||
| Schema Name | The schema name where the procedure resides. The property is suggestible and will return all schemas of the DB. The values can be passed using the pipeline parameters, but not the upstream parameter. Example: dbo Default value: [None] | ||||||
| Stored procedure name | Required. Stored procedure to execute. The property is suggestible and will return all procedures of the DB. If a schema is defined then it will return all procedures of that schema. Example: addSeventeen Default value: [None] | ||||||
| Parameter values | Select the input parameter values for the stored procedure. Example: Suppose there is a stored procedure named createGreeting. It has three parameters p1,p2, p3 and p4. p1 and p2 are input parameters of type VARCHAR2, p3 is INOUT parameter of type VARCHAR2, then three string values provided. The first value goes to p1, the second to p2, and the third to p3. Quotation marks should not be used when setting input parameters unless they are part of the input value itself, since they are treated as escaped plain characters. For example, if the desired input value is TEST_VALUE1, the input parameter should be set as TEST_VALUE1, if you set it as "TEST_VALUE1", it will be recognized as \"TEST_VALUE1\". Default value: [None] If you do not provide any parameters in the Snap, you can still view the required input parameters in the Target Schema of the upstream Mapper Snap. The parameters are dynamically populated based on the column keys of the stored procedure; you can map the parameters and pass them as an input to the Stored Procedure Snap. | ||||||
| Pass through | If selected, the input document will be passed through to the output views under the key 'original'. The document will be passed to the Parameter output view, and it will also be passed to the Result Set output view if there is output sent from this view. This property applies only to the Execute Snaps with SELECT statement or the Select Snaps. Default value: Not selected | ||||||
Snap execution | Select one of the three modes in which the Snap executes. Available options are:
| ||||||
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.
- Using Pipeline Parameters:
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
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.