my dog learned polymorphism*
The moose likes JDBC and the fly likes Help with CallableStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help with CallableStatement" Watch "Help with CallableStatement" New topic
Author

Help with CallableStatement

Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Hello,
I need to run the following query in my JSP:

Where myFunc is a function stored in my Oracle database. When I execute the query in Toad, it provides the expected results. The Java API says to use CallableStatement, however, I'm totally confused on how to use it, if it is indeed the answer. CallableStatement seems to be for stored procs and not for stored functions? Any help would be appreciated, this is the last piece of my app and it's due tomorrow.
Thanks,
RK
[ February 20, 2002: Message edited by: Rich Knight ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

if this worked from a SQL editor, it should work with a simple Statement.


CallableStatements are used to call the stored procedure directly, where as in your case, the select statement should call the function, just as was done with your query in TOAD.
let me know if this is correct,
Jamie
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
stored Functions work with select statement since they have a return value. but if they have any in/out or out parameter then u can't access them via the select statement. for that purpose u jave to use callablestamement.
as below
Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Thanks for your replies.
prabhat,
Thanks for posting the code. If I have to call a function with parameters I use the setXXX(), correct? However, I don't see where I place my actual SQL query. I'm new to SQL so forgive me if it's obvious.
Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Giving things a try, I tried the following code and it returned a "null" value for "returnVal". Am I in the ballpark? Thanks.
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
things to remeber when using function or procedures indivisually..
1. use begin and end tags.
2. register the parameters first before stting the valus
---
now what u did wrong in ur code ..
..
1. there is only one parameter .. see only one Question mark

2.

so u set the first parameter.
3. where is the second parameter?
there is no second question mark so there is value null simple as that.

do �something like this

? r the placeholders for the values.
[ February 21, 2002: Message edited by: prabhat kumat ]
Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Thanks a lot. I follow you, and see my mistakes. I now realize that I didn't pose my initial question accurately. 'myFunc' is a function that decrypts an ID. So when I pass it in 'joe' it decrypts Joe's ID and returns that value. What I'm confused about is how to build my statement incorporating the query, ie., how to tell 'myFunc' what table 'joe' resides ('users') in and what field ('id') of joe's record to decrypt. Does that make sense, or am I just totally missing it?
Thanks for your patience, here's the code for the function in question if it would help matters.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Rob: did you try using the statement object instead of CallableStatement? your problem is different than just calling a stored procedure. So you can not just use CallableStatement interface on your sql statement. Your function is embedded in your sql statement, so it is the query that will call the stored procedure at the database end of things.
It is like executing the following:
"select UPPER(name) from customer where...". You would use a statement, even though the SQL statement calls the function UPPER() which is a DB Stored procedure. You don't have to use callable statements and the like because the DB takes care of the calls from the SQL query. Since you are able to execute myFunc from a query using TOAD, you should have no problem using the same query with myFunc in it using a Statement or PreparedStatement.

let me know if this works,
If it doesn't, then you'll have to do some messy workarounds.
Jamie
[ February 21, 2002: Message edited by: Jamie Robertson ]
[ February 21, 2002: Message edited by: Jamie Robertson ]
Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Jamie,
Yes, it was my initial thought to just use Statement, however I got an "Invalid column name" exception when I tried to access the returned value via the "<%=rs.getString("id")%>" expression below (commenting it out removes the error, but of course that renders the page useless). I'll give your suggestions a try. Thanks.

[ February 21, 2002: Message edited by: Rich Knight ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Sorry for calling you Rob
anyways, this is what I wanted to know!
Yes, it was my initial thought to just use Statement, however I got an "Invalid column name" exception when I tried to access the returned value via the "<%=rs.getString("id")%>" expression below (commenting it out removes the error, but of course that renders the page useless)...

This is a totally different error than you think, and a simple one at that( don't hit your head too hard on the table when you fix this ). When you call rs.getString("id") it throws the error because the column name is no longer "id". it is "myFunc(id)" (I think, you'd have to use resultsetMetaData.getColumnNames to be sure). Therefore, you are trying to reference an invalid column name. To fix this, change the rs.getString("id") to rs.getString(1) and it will work.
Jamie
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
however if ur goal is to get only the encrypted value then above suggestion is good .. only it needs a bit of modifications to suit ur needs ..

hope it may solve ur problem
[ February 21, 2002: Message edited by: prabhat kumat ]
Rich Knight
Greenhorn

Joined: Feb 05, 2002
Posts: 14
Thanks a lot Jamie and prabhat, it works! Hind sight is 20/20 but I can't believe I went on that wild goose chase! Yall have been a huge help. As I get more into java I hope that I can be as much of a help to others. Thanks again!
 
Consider Paul's rocket mass heater.
 
subject: Help with CallableStatement
 
Similar Threads
Error trying to call a stored function.
querytimeout not working
Can I call a Java Stored Procedure from EJB
How to obtain a resultset using a stored procedure
Issue with BatchExection of CallableStatement with StoredProcedures in DB2