File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes multiple resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "multiple resultset" Watch "multiple resultset" New topic

multiple resultset

Devaki Nihar

Joined: Apr 30, 2005
Posts: 20
I am using sql server version 2000
Orion 1.5.2
jdk 1.4

I am facing one problem is
In my session bean query to get records ,i am using compute function which returns multiple
records ,It works fine

Code :
--Got connection as conn
--Created Statement as stmt
--initialised resultset and resultmetadata as rs and rsmd

suppose my query is something like:

select emp.empid,emp.empname,dept.deptname
from emp,dept
where emp.deptid=dept.deptid
and dept.deptid>3
order by dept.deptid
Copute count(emp.empid) by dept.deptid

It display me all the emp's data whose deptid>3 ordered and grouped by deptid
but it display count of emp's only for the last dept in the group

[edited to add code tags]
[ April 30, 2005: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33098

Welcome to JavaRanch!

The compute clause is meant to present summary information. That is why it is limited to one line.

I don't think there is a way to get all that information in standard SQL. You could do another query (using group by) to calculate the totals. However, it is easier and more efficient to just do the counting in Java. You are returning the department already. If there is more than one department with the same name, you can return the deptId too. Just count the number of records as you loop through the resultset.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: multiple resultset
It's not a secret anymore!