wood burning stoves 2.0*
The moose likes JDBC and the fly likes getting the resultset of a stored procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting the resultset of a stored procedure?" Watch "getting the resultset of a stored procedure?" New topic
Author

getting the resultset of a stored procedure?

Sam Doder
Ranch Hand

Joined: Feb 06, 2008
Posts: 204
I am learning stored procedures but I am having trouble getting the data

Here is my stored procedure




The stored procedure called proc1
I am using oracle sql developer.

My java code snippet to call the stored procedure is



But when I try to get the result set it gives me null.

I guess I want to know how I can have a stored procedure return stuff like resultsets back to java code .

Thanks for any help
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
How are you executing the call? For a proc that returns one result set, you should use executeQuery():
See http://download.oracle.com/javase/tutorial/jdbc/basics/sql.html and read the notes about which execute method to use for other types of procs.

By the way, I hope your procedure is just an example. I strongly recommend against writing a stored procedure that simply concatenates a table name and runs the query. You're creating a blatant SQL injection hole, because the caller could pass anything, including a subquery:
In fact, a procedure just like your example is featured on TheDailyWTF:
http://thedailywtf.com/Articles/For-the-Ease-of-Maintenance.aspx


Bill Karwin is the author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Sam Doder
Ranch Hand

Joined: Feb 06, 2008
Posts: 204
yes , this is just an example I know that it has security issues.

I have always used just preparedStatements instead of callableStatements and just used strings with ? ? and set the values of the fields... Then executeQuery or Update.

But now using stored procedures I am curious will the executeQuery , executeUpdate return a vaild ResultSet I was getting Null when I used execute(). But maybe execute() function was the problem.

Also execute is the only way to go if I have multiple update / queries in my stored procedure.
What I am unsure about is how to set the stored procedure to return a ResultSet if I use execute().
Or what other function would you use other then execute() if you had to multiple sql update / query statements in your stored procedure?

I know execute returns a Object then I should beable to cast it to a resultset provided it is a result set.
The problem is how do I get the stored procedure to return a resultset.

NOTE for my example above I would just use the executeQuery to get the result set I believe but just imagine a huge stored procedure.

Thanks

Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Sam Doder wrote:
I guess I want to know how I can have a stored procedure return stuff like resultsets back to java code .


Try something like this (tested on Oracle 11g2):


Results:
Sam Doder
Ranch Hand

Joined: Feb 06, 2008
Posts: 204
ok thanks.

But I am curious you use OracleCallableStatement

Is their away that you can just use the CallableStatement object in the standard JDBC class.

Because using oracles function is not as portable i.e ... oracle.jdbc.OracleTypes.CURSOR

When I try to use registerOutParameter from a CallableStatement statement I don't know what the parameter in place of oracle.jdbc.OracleTypes.CURSOR should be?

Question 2

I can from a JDBC Connection use a statement , preparedStatement , or CallableStatement.

Correct me if I am wrong.
But the statement cann't have ? in it .. It must be a Hardcoded SQL statement (fix).
PreparedStatements extend the statement and provide a way to dynamically customize the query string using ? ? ? ...etc
They say it is precompiled.

CallableStatement is what I am alittle confused about it extends PreparedStatement but it is used to call a stored procedure on the database.

What is the benifits of using this over just setAutoCommit(false) for the connection and then use a whole bunch of preparedStatements with java if statement (for the equivalent logic of pl/sql) then commit it or rollback. Wouldn't it have the same amount of functionality and work the same way as just creating a stored procedure on the database and calling it with a CallableStatement?

Even if the stored procedure is huge and contains alot of DDL and DML statements I would think you could do the equivalent with a ton of preparedstatements. Is their some kind of performance/memory space benfit to using one over the other?
Or is this just a matter of preference. Because I would imaging the difference is weather you want to write the code in PL/SQL call it thru java or just write it in java using SQL query's.

Seems cleaner (provided you know pl/sql) to compile the stored procedurce on the database it self and just call the procedure using callable statements thru java. Plus if you do it this way you are running the queries at the database level and not thru the java code if their is a benifit to that in any way? (maybe speed???)

Thanks for any info

Is their somewhere I can get a good tutorial on pl/sql and stored procedures ... i.e a list of all the possible key words and how to use them (hyperlink please)





 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting the resultset of a stored procedure?