1

I am using java to access mssql 2012 DB

I have a stored procedure "procX" with takes only 1, (say named as @p1) in parameter and returns a single result set. It works fine unless I try to read the return value before I get the result set. Code is below. If I remove "//" and get return value, I get null as the result.

cs = con.prepareCall("{? = call procX(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt("p1", 1);
cs.execute();         
//cs.getInt(1);
rs = cs.getResultSet();

And even if I read the return value after I get the resultset, this closes the result set.

Is there a way to read return value without making it impossible to get the resultset?

And as far as I can see this situation is not restricted for return value and occurs for any other output parameters, too.

Nuri Tasdemir
  • 9,720
  • 3
  • 42
  • 67
  • 1
    @tottiroma `CallableStatement` I guess. – John Woo Feb 04 '13 at 01:48
  • yes i am using CallableStatement – Nuri Tasdemir Feb 04 '13 at 01:52
  • what are you thinking about this `? = call procX(?)`? stored procedure doesn't return a value like a function does but in stored procedure you can set ouput parameter. – John Woo Feb 04 '13 at 01:53
  • no. every stored procedure returns a 0 by default unless there is an error. also you can return any other value – Nuri Tasdemir Feb 04 '13 at 01:56
  • How did this not err out completely? You're overwriting the first parameter and never setting the second. – stan Feb 04 '13 at 01:57
  • "@p1" is the name of my input in storedprocedure. its index in call is 2. You can set it by index or by name. and the first index is belong to "return_value" as in "?= call...". this is the way to get return value of a stored procedure. it is output of type int – Nuri Tasdemir Feb 04 '13 at 02:05

2 Answers2

1

Looks like you have to get resultset(s) before reading any of the output parameters.

You can get the detail from https://stackoverflow.com/a/5576442/1519458

Community
  • 1
  • 1
Nuri Tasdemir
  • 9,720
  • 3
  • 42
  • 67
0

It is interesting,

  • firstly : I do not know what kind of driver are you using but try to change and provide us information will be the same behavior or no.
  • secondly: if you are using Microsoft JDBC Driver for SQL Server try to tune logger and see where is problem, this library using java.util.logging.Logger for logging before/after executing each operations.
  • I think you are using java 7, try to downgrade and run it on Java 6, Why ? Because there is some interesting logic (with Closeable interface) in Microsoft JDBC Driver for SQL Server for getValue method which called from getInt method.
Sergii Zagriichuk
  • 5,389
  • 5
  • 28
  • 45
  • I found the answer but cannot add it before 10 hours (my reputation is not enough to answer my own question) – Nuri Tasdemir Feb 04 '13 at 03:13
  • Looks like you have to get resultset(s) before reading any of the output parameters. You can get the detail from http://stackoverflow.com/a/5576442/1519458 – Nuri Tasdemir Feb 04 '13 at 03:14
  • @araqnoon it is good explanation, related to driver implementation from Microsoft. The getInt method has next workflow, is getValue->getgetterparsms (or like this)->**close active stream** I think this line is failed your Result Set. – Sergii Zagriichuk Feb 04 '13 at 03:19