aspose file tools*
The moose likes JDBC and the fly likes Distinct select giving a problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Distinct select giving a problem" Watch "Distinct select giving a problem" New topic
Author

Distinct select giving a problem

Nischal Topno
Ranch Hand

Joined: Nov 24, 2001
Posts: 45
Hello,
Say a field (AFIELD) in my table (MYTABLE) contains the following data:
AFIELD
======
NOKIA
NOKIA
SAMSUNG
When i create a ResultSet(rs) using the following query,
SELECT AFIELD FROM MYTABLE
the rs.next() returns true for three times (which is correct).
But when i create a ResultSet(rs) using the following query,
SELECT DISTINCT AFIELD FROM MYTABLE
still the rs.next() returns true for three times (which is wrong). Also it gives an "Invalid cursor state" error when trying to fetch the the record for the third time using getString("AFIELD").
Please let me know what's going wrong and where?
I am using the java 1.3.1_02-b02 JDK. The statement is (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).
Please advice.
Nischal
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
That sounds seriously wrong. What data do you see when you do the query direct in the db command line?
Try this query and see what you get as well:
SELECT afield, COUNT(*) FROM mytable
GROUP BY afield

Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
 
Consider Paul's rocket mass heater.
 
subject: Distinct select giving a problem