-1

We are facing unpredictable error saying that 'missing in or out parameter at index:7' for a procedure call using Callable statement in Java. On each procedure call we are not closing the connection. Does this is the problem?

Note: Code implementation is not direct code, which calls procedure. We have internal framework that takes care of DB call So Its difficult to post complete code.

Hareesh
  • 694
  • 6
  • 18

2 Answers2

1

Lacking a code snippet (it would be helpful if you can post it) we can only speculate about possible causes. One thing to point out with inout parameters is that you need to both set input values for them, statement.setX(n,value) and register them as output parameters, statement.registerOutParameter(n,type) each time they are used. Refer to this link from Oracle JDBC documentation for a more complete example:

http://docs.oracle.com/javadb/10.10.1.2/ref/rrefjdbc75719.html

You will also want to be aware the WebSphere Application Server data sources cache CallableStatements by default (upon CallableStatement.close), which involves invoking the CallableStatement.clearParameters which has the effect of clearing the parameter values and releasing resources held by them. It is possible that it might also clear the registration of out parameters. To experiment with disabling the statement cache, configure statementCacheSize=0 on the data source. Alternately, if using JDBC 4.0 or higher, an individual statement can be made non-cacheable via CallableStatement.setPoolable(false). To be clear, I'm not recommending disablement of statement caching as a solution, only as an experiment to help you narrow down the cause.

njr
  • 3,399
  • 9
  • 7
  • Code implementation is not direct code, which calls procedure. We have internal framework that takes care of DB call So Its difficult to post complete code. But your answer would help me in resolving the implementation. Will except your answer once it is resolved based on your suggestions. – Hareesh Jan 14 '17 at 06:58
0

Unless you are creating a connection yourselves, you do not need to close the connection. However, you do need to close the CallableStatement.

Also, missing in or out parameter is an indication that the procedure requires more parameters bound to it when compared to the number or parameters specified in the callable statement.

If the above is not the case, then you might be getting it because you are not binding a parameter in the statement.

Example:

call MY_PROC(?,?)

you need to bind 2 parameters in java code for it. But as you are saying that it does not happen always, maybe one of your parameter binding is in an if condition that is not satisfied when the error occurs.

MozenRath
  • 9,652
  • 13
  • 61
  • 104