Talend community forum / Problem getting return value from MSSQL function

dbemis · 2012-05-24 20:45:50

Tags: [mssql]

I am using a tMSSqlSP component to call a function, pass a value, and get a return value. When I run it I get the error: "java.sql.SQLException: Parameter '2' not registered as output parameter. Call registerOutParameter()." I tried unchecking the Is Function box and adding it as an output parameter but unfortunately I get "java.sql.SQLException: Procedure or function GetBACForDealerCode has too many arguments specified." I ran profiler to see how it was calling and the function option seems to call it correctly.

Any ideas? Thanks!

Uploaded Images

pedro · 2012-05-25 03:44:45

Hi

Welcome to Talend Community!

You'd better add a column in 'Schema Column' and select 'Out' type for it.

Regards,
Pedro

dbemis · 2012-05-25 15:27:22

pedro wrote:
Hi

Welcome to Talend Community!

You'd better add a column in 'Schema Column' and select 'Out' type for it.

Regards,
Pedro

If I do that it errors with "java.sql.SQLException: Procedure or function GetBACForDealerCode has too many arguments specified"

pedro · 2012-05-28 03:59:12

Hi

Please show me the arguments of this procedure or function.
When selecting 'is function', you have to select output column from 'Return result in'.
When not selecting it, you need to add 'OUT' column in 'Parameters'.

Regards,
Pedro

dbemis · 2012-05-29 22:53:53

Attached is a screenshot - including the SQL behind the function. This example gives the error "java.sql.SQLException: Parameter '2' not registered as output parameter. Call registerOutParameter()."

Uploaded Images

Last edited by dbemis (2012-05-29 22:56:10)

pedro · 2012-05-30 04:18:53

Hi

After creating this function in my local MSSQL server, I know why you didn't get return value.
Modify the settings of tMSSqlSP as the following images.

Regards,
Pedro

Uploaded Images

dbemis · 2012-05-30 17:27:28

While that did work it doesn't seem like that should be correct - if I needed to keep passing the DealerCode variable through it would get overwritten in that scenario. Is that the intended behavior?

Thanks!

pedro · 2012-05-31 04:45:53

Hi

Got you.

The schema should be like this.
BAC
DealerCode

The 'BAC' column should be before the 'DealerCode' column.

Regards,
Pedro

Uploaded Images

Announcement

Unanswered posts

#1 2012-05-24 20:45:50

Problem getting return value from MSSQL function

#2 2012-05-25 03:44:45

Re: Problem getting return value from MSSQL function

#3 2012-05-25 15:27:22

Re: Problem getting return value from MSSQL function

pedro wrote:

#4 2012-05-28 03:59:12

Re: Problem getting return value from MSSQL function

#5 2012-05-29 22:53:53

Re: Problem getting return value from MSSQL function

#6 2012-05-30 04:18:53

Re: Problem getting return value from MSSQL function

#7 2012-05-30 17:27:28

Re: Problem getting return value from MSSQL function

#8 2012-05-31 04:45:53

Re: Problem getting return value from MSSQL function

Board footer