One Star

[resolved] How to use tMysqlSP with tParseRecordSet to get result of 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 2:
Call stored proc that has one IN param and produces result data.
To do this I have created a job with the following:
tFixedFlowInput---Row/Main--->tMysqlSP---Row/Main--->tParseRecordSet---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
                                                               User_ID                 Integer
                                                               User_Name            String
                                                               User_Active            String
The tMysqlSP has the following parameters defined:
Schema Column                Type          
param_ID                         IN
The SP Name is "etl_test_select_proc"
In the db the "etl_test_select_proc" has the following:
DELIMITER $$

DROP PROCEDURE IF EXISTS etl_test_select_proc$$
CREATE DEFINER = 'root'@'localhost'
PROCEDURE etl_test_select_proc (IN param_ID int)
BEGIN
 SELECT
   5 AS Users_ID,
   'TestUser' AS Users_Name,
   'Active' AS Users_Active
 FROM users
 WHERE Users_ID = param_ID;
END
$$
DELIMITER ;

I have tested this procedure using the db directly and it works fine.
The tParseRecordSet has the following schema:
Column              Type                --->         Column                  Type          
param_ID           Integer                           
User_ID              Integer                            User_ID                 Integer
User_Name         String                              User_Name            String
User_Active         String                              User_Active            String

The tLogRow was just connected to tParseRecordSet and the "Sync columns" was pressed.
When I run this job I get the following error:
Starting job StoredProcDemo at 16:48 05/10/2015.
connecting to socket on port 3854
connected
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
Cannot cast from Integer to ResultSet
at test.storedprocdemo_0_1.StoredProcDemo.tFixedFlowInput_3Process(StoredProcDemo.java:1078)
at test.storedprocdemo_0_1.StoredProcDemo.runJobInTOS(StoredProcDemo.java:1754)
at test.storedprocdemo_0_1.StoredProcDemo.main(StoredProcDemo.java:1509)
Job StoredProcDemo ended at 16:48 05/10/2015.


What I have done wrong here?

Thanks for your time,
Scott
2 REPLIES
One Star

Re: [resolved] How to use tMysqlSP with tParseRecordSet to get result of stored proc

Ok, just worked this one out!!
The tParsedRecordSet has a property in the Basic settings table called "Prev.Comp.Column List".  The trick is to define a column of type Object (I called my ProcResult) in the tMysqlSP (Output) schema, and then add this column to the tMysqlSP Parameters on the Basic settings tab.  Set it's type to RECORD SET.
Then in the tParseRecordSet component set the "Prev.Comp.Column List" property to the RECORD SET field you just added to the tMysqlSP.
Then in the tParseRecordSet component (Output) schema, add the columns that will be in the data returned by the proc.
Also in the tParseRecordSet components Basic settings tab there is a Attribute table.  In this table are the column names added to the output schema.  In the value column you need to put the column enclosed in strings.  eg;
Column                 Value          
Users_ID               "Users_ID"
Users_Name          "Users_Name"
Useres_Active        "Users_Active"
Also I found that the Null Reference error I was getting was fixed by connecting the tMysqlConnection I was using via OnSubjobOK to the tFixedFlowInput which is the start of the flow, as when I debugged the java I found that the connection was in instantiated.
Hope this helps someone ;-)
Regards,
Scott
One Star

Re: [resolved] How to use tMysqlSP with tParseRecordSet to get result of stored proc

hi,
how to use MysqlSP component whose resultset is dynamic??