Forums Register Login

Help with CallableStatement

+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Giving things a try, I tried the following code and it returned a "null" value for "returnVal". Am I in the ballpark? Thanks.
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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!
I am mighty! And this is a mighty small ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 4437 times.
Similar Threads
Issue with BatchExection of CallableStatement with StoredProcedures in DB2
Can I call a Java Stored Procedure from EJB
querytimeout not working
Error trying to call a stored function.
How to obtain a resultset using a stored procedure
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 13:04:06.