*
The moose likes JDBC and the fly likes A real challenge... resultsets of rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "A real challenge... resultsets of rows" Watch "A real challenge... resultsets of rows" New topic
Author

A real challenge... resultsets of rows

Joe Angott
Greenhorn

Joined: Oct 13, 2000
Posts: 7
Ok, here's the skinny...
I have an Oracle stored function in a package that I call feeding it a single parameter. This is done using a callable statement. It then sends me back multiple rows of multiple columns of data.
Question #1: How do I define my out parameter portion of my call so that it is understood by the JDBC that I will be getting back a rowset?
Question #2: Once I get back my mountain of data, how do I go about parsing out the fields in each row? I think I might have a grasp on this one, but would like to know for sure. I think if I go through the result set and parse based on an index corisponding to the position of the field in that row it will give me my desired results.
I know this is probably not an easy one, but I would love for someone to prove me wrong.
Thanks,
Joe
AIM user id LSIJAVAJOE
Joe Angott
Greenhorn

Joined: Oct 13, 2000
Posts: 7
Well I did some more extensive research and found this example (shown in part) from OTN, part of Oracle's online documentation.

try {

// If resultset is open, close it.
if (m_resultSet != null)
m_resultSet.close();

// Prepare a Callable Statement, to make a call the PLSQL function demo_refcursor.getRefCursor, and obtain the returned REFCURSOR
CallableStatement l_call =
m_connection.prepareCall("{? = call " + "demo_refcursor.getRefCursor }");

// set the Out Parameter type to be of type CURSOR
l_call.registerOutParameter(1, OracleTypes.CURSOR);
l_call.execute(); // Execute the statement

// Cast the returned parameter, (defined as type, OracleTypes.CURSOR) to a JDBC result-set. setFirstFive displays the first five rows of the returned REFCURSOR
m_resultSet = (ResultSet)l_call.getObject(1);

// Display the next five records from the newly opened resultset
displayNextFive();
m_GUI.m_nextRecord.setEnabled(true);
// Enable the "Next Five Records" button
} catch (SQLException ex) {
// Trap SQL errors
m_GUI.putStatus("Error running package demo_refcursor:");
m_GUI.appendStatus(ex.toString());
}

That's the guts of the Oracle Example, not too bad considering the lack of examples in most of Oracle's database reference material. Now here is the end result of my and other's research, and the answer to my own question. I hope it can help someone else.
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*; // comes from Oracle's classes1.2.zip
try {
CallableStatement retriveOrderStatusCall = conn.prepareCall("{? = call Order_Object.query_status_object(?) }");
retriveOrderStatusCall.setString(2, orderId);
retriveOrderStatusCall.registerOutParameter(1,OracleTypes.CURSOR);
retriveOrderStatusCall.execute();
retriveOrderStatusData =(ResultSet)retriveOrderStatusCall.getObject(1);

while(rs.next()){
System.out.print("Orderid "+rs.getString(1)+ " ");
System.out.print("Product "+rs.getString(2).trim()+ " ");
System.out.println("Status "+rs.getString(3).trim()+ " ");

}
There's the skinny, I hope this helps someone with a similar delima. If you were searching for the answer to help me, I appreciate the effort.
AOL IM LSIJAVAJOE.
[This message has been edited by Joe Angott (edited March 06, 2001).]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: A real challenge... resultsets of rows