[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.
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
