Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Java and Sybase nested stored procedures

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!

I have nested Sybase stored procedures, the main procedure calls a helper stored proc.
The main procedure returns 2 result sets before calling the helper store proc and helper store proc returns 1 resultset. After the helper store proc is called, the main store proc returns 2 more resultset. When I run it using SQL client tool the main procedure(and it's helper) execute well and returns rows.

I need to call this store proc in a java code and display all the resultset in screen. I am using Statement:execute(<exec sp> to execute the store proc and then statement.getResultSet() to get each resultset. The loop is controlled by statement.getMoreResults() .

It displays the first 2 resultsets from main Store Proc. Then the control goes to helper store proc. It executes the helper store proc and displays the resultset from helper. But then it stops there and doesnot
come back to main store proc to execute the remaining resultset.

What I suspect is that, the statement object is getting overlaid when the call goes to helper proc. May be that Statement object maintains an internal list for Resultset and that list is over-written when helper proc is executed.

Any idea as to how to code to handle multiple nested resultset ?

~ RNS.



My Sybase stored procedure code looks like -
=======================================================================
create procedure main_proc
as

select getdate()
select @@servername

exec proc2

select myCol1, myCol2, myCol3
from mytable
select db_name()

go

===========================================================================
Sybase Code for proc2

create procedure proc2
as
begin
select @@version
end
go

GRANT EXECUTE...
EXEC sp_procxmode 'dbo.proc2,'unchained'
go
====================================================================

Code snippet :-
--------------------
do {
int iit=0;
rs=stmt.getResultSet();
if ( rs != null ) {
ResultSetMetaData rsd = rs.getMetaData();
int nocols = rsd.getColumnCount();
for (int i=1; i<=nocols; i++)
{
System.out.println(rsd.getColumnName(i)));
}
while (rs.next())
{
for (int i=1; i<=nocols; i++)
{
System.out.println(rs.getString(rsd.getColumnName(i));
}
}
}
} while (stmt.getMoreResults());
 
author and iconoclast
Posts: 24203
43
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Welcome to JavaRanch!

We have a forum for JDBC and database questions here. I will move this topic there for you -- go see what people have to say!
 
To get a wish, you need a genie. To get a genie, you need a lamp. To get a lamp, you need a tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic