Two questions on CallableStatement

Hi all,

Question1:
----------

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).

Regards,
Maulin

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 ]

Hi arnel

I wish if you can elaborate on your answer. I asked two questions and I am not sure what exactly did you answer.

Thanks.
Maulin

Any more input?

Maulin,

I have read the following text from "this" site

You seem to have made a mistake with the URL:

http://this

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.

Good Luck,
Avi.