Talend Open Studio : Stored Procedure Returning null

[mitulvyas]

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

[TRF · Accepted solution]

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.

[mitulvyas]

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

[Anonymous]

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;

tOracle_SP_scenario.jpg

[archienesss]

---

@TRF wrote:

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.

---

Hello TRF,

would it be possible for you to send a sample job? I've tried your solution from How to call a procedure from MS sql server? but to no avail. I'm particularly interested what to do with tJava and tJavaFlex components from your solution. Thanks in advance