How to check if an SP that doesn't return ResultSet completed ok? 1

[royc75]

Hello,

I am running a certain Stored Procedure using CallableStatament. . The boolean value from the execute() method return true only if a ResultSet was return yet this one doesn't return any ResultSet.
So, my question is, how can I test if the Stored Procedure completed succefully?

 

[sedj]

That should be controlled by the SP - if there is an error executing it, either the SP developer should return an exception, or error code, or the db itself should propogate the error.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[royc75]

Got it, but once the SP return an exception or error code, how do I identify it in my code? Does it throws SQLException?

 

[royc75]

I think I got it sedj, I will use registerOutParameter of CallableStatament...

 

[royc75]

One qwuestion though, in order to read the return parameters I must call the SP using this syntax: {?= call <procedure-name>[<arg1>,<arg2>, ...]}
and not this: {call <procedure-name>[<arg1>,<arg2>, ...]}
as I usually do, right?

 

[sedj]

Depends on your db and driver.

What we always do is have two OUT parameters in all our SP's - one for an error code (non zero means something bad happened), and also a message which contains the error if something did go wrong. Then after the execute(), we check the status code, and if non-zero, we throw an exception with the message from the db.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[royc75]

Sounds like a solution to me, but how can you return two parameters from the SP side?

 

[stefanwagner]

a) Return an array or a list, queue, stack, ...

Object [] two = new Object [2];
two [0] = new Integer (i);
two [1] = message;
return two;

b) Create a Container for two elements:

return new Dual (i, message);

c) Return a string to parse:

return message + ":" + i;

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

You don't even need to do that.

On the Java side, you just call registerOutParameter() on the two values (one being NUMBER, one being VARCHAR2), and then in the SQL SP, just specifiy two values as above, and set them accordingly.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[royc75]

Hi again,

I am trying to implement this, here is my code:
CallableStatement cs = connection.prepareCall("{? = call MakeDepositReport (?,?,?,?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(1, "1");
cs.setString(2, "2");
cs.setString(3, "3");
cs.setString(4, "4");
cs.execute();

Yet I receive an error: Invalid parameter binding(2) before registering the out parameter it worked fine. Any idea why...?

 

[royc75]

Solved it! As you can see, I have registered both out and in under the same index. Sometimes you can't what is right in front of you...