I have read the following text from this site, ------ A CallableStatement object that you execute in a batch can contain output parameters. However, you cannot retrieve the values of the output parameters. If you try to do so, a BatchUpdateException is thrown. -------
Its sounds strange to me. Why "batch can contain output params BUT we can't retrieve it"?
My thinking: ------------ I think that it doesn't make sense to have OUT param in batch updates as it would be update queries but my confusion is "why even allow OUT param in that case AND then say that it will not work "
Question2: ---------- What is the purpose of the return param from the stored procedure if we can use OUT params? Why do we need support for both in JDBC API? Is it because different databases might behave differently and to cover them the JDBC API supports both?
Please throw some light on these questions.
Also, do you know any typical issue faced with CallableStatement? (Well, I am gonna be interviewed and I have not used Stored Procedures as much as they become database dependent and porting becomes issue so please help me).
Some JDBC vendor does not support that implementation, i wonder why like the case of MS SQL (i tried it). I just don't know with other vendor like Oracle. So the implementation depends on your JDBC vendor as far as i know. [ July 15, 2004: Message edited by: arnel nicolas ]
Joined: Nov 04, 2001
I wish if you can elaborate on your answer. I asked two questions and I am not sure what exactly did you answer.
I found no Internet site with that URL. Therefore, I couldn't really understand the context of your excerpt from the "this" Web site. So, assuming that you know the context of your excerpt, here is my guess at the answer to your first question:
The "CallableStatment" interface allows for invoking database stored procedures with OUT parameters. Also, since the "CallableStatement" interface extends the "Statement" interface, and the "Statement" interface supports batching, that means that the "CallableStatement" also supports batching. However (as your excerpt states), OUT parameters are not supported for "CallableStatement"s in a batch.
Now for your second question: Each database vendor that implements database stored procedures (and functions), does it differently. The JDBC API attempts to cover as many options as possible. If a JDBC implementation does not implement a method in the API, it simply needs to throw a "SQLException" stating that the relevant method is not implemented. Some database vendors only have stored procedures (and no functions), while other vendors have both functions and procedures. Some vendors allow OUT parameters, while others don't. But, as I said before, the JDBC API attempts to cover as many of the possible database operations (regardless of the vendor) as it can, so that it can be used with nearly all the various DBMS's that there are.
Now for your "bonus" question: Since I only work with the Oracle ORDBMS, the only issue I face is the aspects of PL/SQL that aren't implemented in Oracle's JDBC driver.