File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to obtain a resultset using 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 "How to obtain a resultset using a stored procedure" Watch "How to obtain a resultset using a stored procedure" New topic
Author

How to obtain a resultset using a stored procedure

Ashish Kothari
Ranch Hand

Joined: May 16, 2003
Posts: 37
my problem is that I am firing a select query in stored procedure .
I want to call this stored procedure in jsp page or bean and want to store select query output in a resultset .
Though we can use callable to call stored procedure but I don't know how to get resultset using CallableStatement.
please help
Paul Christopher
Greenhorn

Joined: Jun 02, 2003
Posts: 1
Use the following Cursor declaration in Oracle to retrieve the resultset

CREATE OR REPLACE PACKAGE P_Sample_Package
AS
TYPE generic_cursor_type IS REF CURSOR;
END;
/
CREATE OR REPLACE PROCEDURE P_Sample_Procedure (c1 OUT P_Sample_Package.generic_cursor_type)
AS
BEGIN
OPEN c1 FOR SELECT val_id, val_cd FROM TBPVAL_LKUP;
END;
/
and the following Java Statament to loop thru the Resultset
CallableStatement_Ex obj = new CallableStatement_Ex();
oracle.jdbc.pool.OracleDataSource ods;
ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc racle:thin:@url:1521:SID");
ods.setUser("userid");
ods.setPassword("password");
Connection c = ods.getConnection();
CallableStatement cs = c.prepareCall("call P_Sample_Procedure (?)");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
int i=0;
ResultSet rs = (ResultSet) cs.getObject(1);
while(rs.next())
{
System.out.println(rs.getString("val_id"));
System.out.println(rs.getString("val_cd"));
}
}
}
hope this will be useful.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Paul,
Pardon me, but I saw no mention in Ashish's post regarding the DBMS (s)he is using.
As far as I know, Oracle is not the only DBMS that supports stored procedures.
Also, Paul, your code looks incorrect to me (although I have not tested it -- did you?). Oracle does support the JDBC escape syntax, so I think that this line of your code:

should be replaced with:

In any case, Ashish, a (correct) version of the answer Paul has given you can be found at Oracle's Ask Tom Web site, at this Web page.
Good Luck,
Avi.
Surendran Velath
Greenhorn

Joined: Apr 23, 2003
Posts: 19
Hi Ashish,
Use the following code for any number of result sets returned from the procedure:
(Each select statement returns one resultset)
private Connection conn;
private CallableStatement cstt;
private ResultSet rs;
try
{
Class.forName( driver ).newInstance();
conn = DriverManager.getConnection(URL , user, pswd );
cstt = conn.prepareCall( "{call testproc()}" );
cstt.execute();
try
{
// cstt.getObject( 1 ); // for out params
rs = cstt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
while(rs.next())
{
for(int i=0; i<cols; i++)
System.out.print(rs.getString(i+1)+", ");
System.out.println();
}
while(cstt.getMoreResults())
{
System.out.println( " Next:");
rs = cstt.getResultSet();
rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
while(rs.next())
{
for(int i=0; i<cols; i++)
System.out.print(rs.getString(i+1)+", ");
System.out.println();
}
}
}
catch(SQLException sqe)
{
System.out.println( " No value got from stored procedure" );
sqe.printStackTrace ();
}
}
catch( Throwable e )
{
e.printStackTrace();
}
Ashish Kothari
Ranch Hand

Joined: May 16, 2003
Posts: 37
I thankful to you all.
Avi,I want to ask you a question ,suppose (SQL Server is the database or it can be any ,but in my case it is SQL-Server) i am firing more then one Select statement.
Then How would I proceed,suppose I want to proceed with the third select statement of Stored procedure and don't want to operate on first two.
please help
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Ashish,
Sorry, but I don't use SQL Server. In any case, your question is unclear to me. Are you trying to execute only certain sections of your stored procedure?
Avi.
Ashish Kothari
Ranch Hand

Joined: May 16, 2003
Posts: 37
Avi,
I have more then one select statement in stored procedure ,but I want deal with one of them only ,so I how would I pick resultset of that specific select statement.That is all I want to know.
thanks and regards.
Ashish
Surendran Velath
Greenhorn

Joined: Apr 23, 2003
Posts: 19
If you know it is the third procedure for sure then you can ignore the first two after fetching the ResultSets.
If not, then use a condition that is returned by your ResultSet to make sure it is the one you want.
Any way you will get the ResultSet in the order of appearance of SELECT statement inside the Procedure
Ashish Kothari
Ranch Hand

Joined: May 16, 2003
Posts: 37
Thank you Surendran,Actually I didn't read your last message.
It would be lot helping.regards.
Ashish
 
GeeCON Prague 2014
 
subject: How to obtain a resultset using a stored procedure