• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

A real challenge... resultsets of rows

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).]
 
Remember to always leap before you look. But always take the time to smell the tiny ads:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic