This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Callable Statements: how do I name the input parameters? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Callable Statements: how do I name the input parameters?" Watch "Callable Statements: how do I name the input parameters?" New topic

Callable Statements: how do I name the input parameters?

verduka fox
Ranch Hand

Joined: Jan 18, 2001
Posts: 178
I have a jsp site that will access stored procedures on a Sybase Database using Callable Statements. I have established my connection with the database, and am successful calling stored procedures using the CallableStatement. My DBA has strongly recommended using named input parameters instead of just sending them in in the order they are defined in the stored procedure. For example, the stored procedure is defined as with a signature of:

and I am successful when I use the following:

I'm thinking that I should be able to specify a name for each parameter, something like:

When I try this, I get:

Does anyone know how to name the parameters? This would be beneficial in case the stored procedure changes and I have not yet changed my code; then it wouldn't throw an exception.
Or, do you know why this is not allowed? What is the recommended way of handling this situation?
Thanks for your help.

venugopal askani

Joined: Aug 20, 2001
Posts: 16
Hi Verduka,
I understand ur concern. But as far as I know you cannot use the CallableStatement as
stmt = conn.prepareCall("{call p_get_patients(userid=NIESTE,division_id=null,active_only=0)}");
because you are basically the method prepareCall which is already defined. The definition goes like this

{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}

I hope you understand that you are basically trying to call a method which is already defined. I mean you ought to have the same signature, same no of variables and so on..
Venugopal Askani
verduka fox
Ranch Hand

Joined: Jan 18, 2001
Posts: 178
Thanks for your reply, Venugopal. Are you saying that there is no way to set the parameters to a CallableStatement in the same way that you can get values from a resultset? For example, in a resultset, I can say rs.getString("account_number") instead of using rs.getString(1). I'd like this same functionality for setting the parameter values for the CallableStatement. Do you know how I accomplish this?
Also, is there a way to set the program associated with the connection? In Sybase Central in the processes view, I can see which connection is associated with which application. However, my jsp site does not list a program name. How do I do this? It would make managing the connections easier.
Thanks for your help.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: Callable Statements: how do I name the input parameters?
It's not a secret anymore!