This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "DISTINCT sql command and Invalid cursor error" Watch "DISTINCT sql command and Invalid cursor error" New topic

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 FROM Venue, EventsVenues WHERE EventsVenues.venueID = Venue.venueID AND eventID = " + id);

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.
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

Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Don't get me started about those stupid light bulbs.
subject: DISTINCT sql command and Invalid cursor error
It's not a secret anymore!