File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Two questions on CallableStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Two questions on CallableStatement" Watch "Two questions on CallableStatement" New topic
Author

Two questions on CallableStatement

Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
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
arnel nicolas
Ranch Hand

Joined: Dec 16, 2003
Posts: 149
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 ]
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
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
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
Any more input?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Two questions on CallableStatement