JDBC execute stored procedure with return value and output parameters

Question

Hi

I have a stored procedure that takes 1 input value, 2 output parameters and also returns a value on execution

Over the internet i saw references of using

CallableStatement cstmt = conn.prepareCall("{call ? = spName(?, ?, ?)}");

cstmt.registerOutParameter(1, Types.INTEGER);

cstmt.setObject(2, Types.INTEGER);

cstmt.registerOutParameter(3, Types.NVARCHAR);

cstmt.registerOutParameter(4, Types.NVARCHAR);

But this gives me the error "Incorrect syntax near '{'"

Then i decided to do as the Sql Management Studio:

CallableStatement cstmt = conn.prepareCall("exec ? = spName ?, ?, ?");

cstmt.registerOutParameter(1, Types.INTEGER);

cstmt.setObject(2, Types.INTEGER);

cstmt.registerOutParameter(3, Types.NVARCHAR);

cstmt.registerOutParameter(4, Types.NVARCHAR);

But this gives me the error "Incorrect syntax near '='"

I think this is because the query gets transformed to "exec @P1 OUT = spName @P2, @P3 OUT, @P4 OUT"and it doesn't work either on SQL Management Studio because of the 'OUT' before '='

And this leaves me blocked because it doesn't work either way.

Can someone give me some directions?

Thank you!

Answer 1

Hi,

I could be off here but it looks to me like @P1 is already defined as an output parameter. You may want to try without the "OUT".

Try this until an MVP or MSFT Eng reply.

I am setting up a test environment on my end but it will take me some time as I am trying to finish a project today.

Good Luck.

Frank.

---

Frank Garcia

Answer 2

Thank you for your answer.

In debug mode i can see that none of the P# parameters are redefined.

I tried also the P1 without the "OUT" and the error was "Index 0 is out of range" :-\

Answer 3

Hi,

What about using sql profiler to find out the generated code and pasting it directly in qa? Sorry, I am backed up on a project with a passed deadline and i'm trying to finish it today so I have not been able to setup a test environment.

Frank.

---

Frank Garcia

Answer 4

Got it!

It's conn.prepareCall("{? = call spName(?, ?, ?)}");

and not conn.prepareCall("{call ? = spName(?, ?, ?)}");

Thank you for your time!

Answer 5

Great! Thank you for posting the resolution.

Frank.

---

Frank Garcia