What is the meaning and use of registerOutParameter in callable statements

Question

I could have asked one of my colleagues this question but I figured that if I need to know this then so will other newbies so it is better to put it on stack overflow.

I am trying to work what the registerOutParameter function does. For example:

callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

I get that registerOutParameter returns values that are returned instead of, or in addition to, a ResultSet. I cannot imagine how that would occur or what a possible use case would be.

So you know this is not laziness, so far I have looked at:

• the oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html
• How to handle callableStatement.registerOutParameter(1, java.sql.Types.BOOLEAN);)
• https://msdn.microsoft.com/en-us/library/aa286457%28v=vs.60%29.aspx?f=255&MSPPError=-2147217396
• http://tutorials.jenkov.com/jdbc/callablestatement.html
• and a heap of stackoverflow questions.

It just seems this question is too basic to be covered.

Backgound infomation, I am doing a tutorial on stored procedures at www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

Answer 1

Your question is very tight to how stored procedures are executed in an RDBMS. Note that the execution of a stored procedure through a CallableStatement is very different from the execution of a SELECT query which returns a ResultSet. When executing such a query the RDBMS will return a description of the columns and the driver uses this description to know what to expect for the data.

Before executing a stored procedure, the JDBC specification says that you must call registerOutParameter for each OUT parameter. This is to indicate to the driver what data it should expect. Why? Because for many RDBMSs there is no way to describe a stored procedure. It's up to the user to define the out parameters through this API. The driver wouldn't be able to figure it out on its own. You can then execute the stored procedure and then call the getters on the CallableStatement to get the value of the OUT parameters that you have registered.