wood burning stoves 2.0*
The moose likes JDBC and the fly likes DISTINCT sql command and Invalid cursor error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DISTINCT sql command and Invalid cursor error" Watch "DISTINCT sql command and Invalid cursor error" New topic
Author

DISTINCT sql command and Invalid cursor error

david allen
Ranch Hand

Joined: Sep 27, 2002
Posts: 185
HI everyone,
Just need a bit of help with this error I keep getting.
I have written the following query.
rs = db.returnQuery("SELECT DISTINCT Venue.name FROM Venue, EventsVenues WHERE EventsVenues.venueID = Venue.venueID AND eventID = " + id);
while(rs.next())

venue.add(new Venue(rs.getString("name")))
***************
If I take out the DISTINCT word I dont get the error. The error is generated by rs.getString("name");
If someone could tell me why I would really appreciate it.
david
Dorothy Finkel-Laverty
Ranch Hand

Joined: Nov 24, 2001
Posts: 51
Usually, in Oracle, when you use the grouping functions, the name of the column changes - for example, if you said "SELECT a, count(b) from TABLE GROUP BY a", then the name of the column becomes COUNT(color), not color. I thought this might be the same issue, but when I tried a SELECT DISTINCT in Oracle, the name of the column appeared to be unchanged from the original. I did not have the same problem you saw, so I can't explain why it is happening, but I can suggest a solution.
The ResultSet.get methods all allow you to put EITHER the column name, OR a number. If you change the parameter to 1, I think you'll get what you want.
[ June 06, 2003: Message edited by: Dorothy Finkel-Laverty ]
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
Another solution if you would prefer not to rely upon the order of the coulmns in the query is to explicity specify a name for the columns in the SQL query.
For example:
select count(*) as "Record Count" from MyTable
HTH


Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DISTINCT sql command and Invalid cursor error