Talend Open Studio : Stored Procedure Returning null

Six Stars

Talend Open Studio : Stored Procedure Returning null

Hi All,

I am trying to implement a solution as below:

tJava --> tMSSQLRow (Which calls "exec <Stored Procedure> <input parameter>") --> tParseRecordSet --> tJavaFlex

The same SP works fine on Management Studio, as well as, Talend Sandbox; and returns the data.

But, somehow, it's not working in Talend Open Studio. And it's returning null.

Could someone provide some solution to that?

Thanks,

Mitul Vyas


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

Why do you use tMSSQLRow instead of tMSSqlSP to call the procedure?

Based on your job, the design should be:

tJava --> tMSSqlSP --> tParseRecordSet --> tJavaFlex

tMSSqlSP schema must have a column of datatype "Object".

Why does it works using Talend Big Data Sandbox? That is the question.


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

Why do you use tMSSQLRow instead of tMSSqlSP to call the procedure?

Based on your job, the design should be:

tJava --> tMSSqlSP --> tParseRecordSet --> tJavaFlex

tMSSqlSP schema must have a column of datatype "Object".

Why does it works using Talend Big Data Sandbox? That is the question.


TRF
Six Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi.

 

Sorry for responding very late.

 

Thanks TRF for response.

 

That worked in both the ways (using MSSQLRow as well as MSSQLSP components).

 

Regards,

Mitul Vyas

Four Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

 

I have similar scenario. I have a function which returns multiple records.How do we need to read these records from function output in Talend?

Which components we have to use? and what is the code required?


create type prcs_vars_obj is object (var varchar2(10), value varchar2(10));
create type prcs_vars_tab is table of prcs_vars_obj;

create or replace FUNCTION startJob_getPrcsVars_obj (
  ,p_RunDate          NUMBER)
RETURN prcs_vars_tab
IS  
    v_prcs_vars prcs_vars_tab := prcs_vars_tab();
    i integer := 0;
BEGIN  
for r in (
SELECT
   VAR
  ,to_char((((TO_NUMBER(TO_CHAR(VALUE, 'YYYY')) * 100) + (TO_NUMBER(TO_CHAR(VALUE, 'MM')))) * 100) + TO_NUMBER(TO_CHAR(VALUE, 'DD'))) AS VALUE
 FROM (
  SELECT
    CONCAT('DT-',  VAL)  AS VAR,
    to_date(to_char(p_RunDate), 'YYYYMMDD')+ (VAL*-1) AS VALUE
   FROM (
    SELECT 0 VAL FROM dual UNION ALL
    SELECT 1 VAL FROM dual UNION ALL
    SELECT 2 VAL FROM dual UNION ALL
    SELECT 3 VAL FROM dual UNION ALL
    SELECT 4 VAL FROM dual UNION ALL
    SELECT 5 VAL FROM dual UNION ALL
    SELECT 6 VAL FROM dual UNION ALL
    SELECT 7 VAL FROM dual
  ) A
) A
)
loop
    v_prcs_vars.extend;
    i := i+1;
    v_prcs_vars(i) := prcs_vars_obj(r.var, r.value);
end loop;
return v_prcs_vars;
END;

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

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