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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Hi Scott,


Thanks for posting that you resolved it by yourself.


Best regards
Sabrina