[resolved] How to use tMysqlSP to call a stored proc

One Star

[resolved] How to use tMysqlSP to call a stored proc

Hi there,

I want to call a MySQL stored proc.  So I have started developing a couple of Proof Of Concept jobs to work out how this is done.  I found the tMysqlSP component and have the following scenario I want to do:

Scenario 1:
Call stored proc that has one IN param and a couple of OUT params and produces no result data.

To do this I have created a job with the following:

tFixedFlowInput---Row/Main--->tMysqlSP---Row/Main---tLogRow

The tFixedFlowinput has the following schema:

Column         Type    
param_ID      Integer

It has a single value of "5" assigned to the param_ID column.

The tMysqlSP has the following schema:

Column              Type                --->         Column                  Type          
param_ID           Integer                           param_ID               Integer
                                                               param_Username   String
                                                               param_Active         String

The tMysqlSP has the following parameters defined:

Schema Column                Type          
param_ID                         IN
param_Username              OUT
param_Active                    OUT

The SP Name is "etl_test_out_param_proc"

In the db the "etl_test_out_param_proc" has the following:

DELIMITER $$

DROP PROCEDURE IF EXISTS etl_test_out_param_proc$$

CREATE DEFINER = 'root'@'localhost'
PROCEDURE etl_test_out_param_proc (IN param_ID int, OUT param_Login varchar(255), OUT param_Active varchar(255))
BEGIN
-- Simulate Looking Up User Details
 SELECT
   'TestUser',
   'Active' INTO param_Login, param_Active;
END
$$

DELIMITER ;



I have tested this procedure using the db directly and it works fine.

The tLogRow was just connected to tMysqlSP and the "Sync columns" was pressed.

When I run this job I get the following error:

Starting job StoredProcDemo at 15:26 05/10/2015.

[statistics] connecting to socket on port 3986
[statistics] connected
Exception in component tMysqlSP_2
java.lang.NullPointerException
at test.storedprocdemo_0_1.StoredProcDemo.tFixedFlowInput_2Process(StoredProcDemo.java:775)
at test.storedprocdemo_0_1.StoredProcDemo.runJobInTOS(StoredProcDemo.java:1480)
at test.storedprocdemo_0_1.StoredProcDemo.main(StoredProcDemo.java:1235)
[statistics] disconnected
Job StoredProcDemo ended at 15:26 05/10/2015. [exit code=1]



What I have done wrong here?


Thanks for your time,

Scott


Accepted Solutions
One Star

Re: [resolved] How to use tMysqlSP to call a stored proc

This problem was fixed by adding a OnSubjobOk like from the tMysqlConnection component to the tFixedFlowInput component.  The NullPointerException was caused by the connection not being instantiated when the query was run.

Hope this helps someone.

Regards,

Scott

All Replies
One Star

Re: [resolved] How to use tMysqlSP to call a stored proc

This problem was fixed by adding a OnSubjobOk like from the tMysqlConnection component to the tFixedFlowInput component.  The NullPointerException was caused by the connection not being instantiated when the query was run.

Hope this helps someone.

Regards,

Scott
Moderator

Re: [resolved] How to use tMysqlSP to call a stored proc

Hi Scott,


Thanks for posting that you resolved it by yourself.


Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now