 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / June 2004Select distinct or group by does not work
Hi, I'm using the default JDBC-ODBC bridge to read an Access database. When I mak e a query as simple as SELECT DISTINCT field_a FROM table_a, it retrieves a ResultSet with as many real rows as expected, but with a row count equal to the a SELECT * FROM table_a. For a table like this: Name Address --------- ---------- name_1 address_1 name_1 address_1 name_1 address_1 And a select like this: SELECT DISTINCT Name FROM table Being 'rs' equal to the resulting ResultSet, I get: a) rs.next(); --> true rs.next(); --> true (!!!!!) If I try to get any field, an exception is thrown b) rs.last(); rs.getRow(); == 4 (!!!!) So it seems it loads only the right row, but the number of total rows. The same holds for "GROUP BY" If anyone could bring any light, it'd been really welcome. Thanks in advance, - Leny > I'm using the default JDBC-ODBC bridge to read an Access database. > When I mak e a query as simple as SELECT DISTINCT field_a FROM > table_a, it retrieves a ResultSet with as many real rows as expected, > but with a row count equal to the a SELECT * FROM table_a. I think Access prefers DISTINCTROW instead of DISTINCT. GROUP BY can also be used as an alternative to DISTINCT. > For a table like this: > [quoted text clipped - 14 lines] > rs.next(); --> true (!!!!!) If I try to get any field, an > exception is thrown Could it be the way you are accessing fields (columns is the accepted name)? Through the bridge, it is recommended that you access columns in order beginning with resultset column #1. BTW, what is the exception? Otherwise, I'm just shooting in the dark. > b) rs.last(); > rs.getRow(); == 4 (!!!!) [quoted text clipped - 3 lines] > > The same holds for "GROUP BY" An example would help here.  SignatureLee Fesperman, FirstSQL, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) > > I'm using the default JDBC-ODBC bridge to read an Access database. > > When I mak e a query as simple as SELECT DISTINCT field_a FROM [quoted text clipped - 28 lines] > > BTW, what is the exception? Otherwise, I'm just shooting in the dark. There is no exception. The OP claimed that result set size was 4 while he expected it to be 1. My guess would be that the table did not contain three rows with "name_1" but more rows with four differing "Name" values. The fact that this query on a table with assumed three rows returns four values is quite suspicious. Note: you might have "name_1" and "name_1 " (i.e. trailing spaces) which are distinct values. Regards robert > > > rs.next(); --> true (!!!!!) If I try to get any field, an > > > exception is thrown [quoted text clipped - 7 lines] > There is no exception. The OP claimed that result set size was 4 while he > expected it to be 1. What are you talking about? The OP said "If I try to get any field, an *exception* is thrown." I simply asked what was the exception. SignatureLee Fesperman, FirstSQL, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) > > > > rs.next(); --> true (!!!!!) If I try to get any field, an > > > > exception is thrown [quoted text clipped - 10 lines] > What are you talking about? The OP said "If I try to get any field, an *exception* is > thrown." I simply asked what was the exception. Ooops, sorry, apparently I overlooked that one. Then there are several aspects: the exception as well as the number of records returned. My attention was drawn on the number of records aspect only because of the subject. Of course we should learn the exception's message. Kind regards robert > > There is no exception. The OP claimed that result set size was 4 while he > > expected it to be 1. > > What are you talking about? The OP said "If I try to get any field, an *exception* is > thrown." I simply asked what was the exception. It's a SQLException with the following message from the Access driver: "Invalid cursor state". You can go to the last row and get the row number (4), but you can only read the first row (as expected from the query). If you try to .next() and read any field, that exception is thrown. - Leny > > Could it be the way you are accessing fields (columns is the accepted > name)? Through the [quoted text clipped - 13 lines] > Note: you might have "name_1" and "name_1 " (i.e. trailing spaces) which are > distinct values. I have already checked (and rechecked) the name of the fields. On the other hand, I should get *4* distinct rows in that case. I only get 4 if I move to the last row and get the row number. Trying to load any field from any row beyond the first throws a SQLException... Regards, - Leny > > > Could it be the way you are accessing fields (columns is the accepted > > name)? Through the [quoted text clipped - 15 lines] > > I have already checked (and rechecked) the name of the fields. No, not field names but field content. > On the > other hand, I should get *4* distinct rows in that case. I only get 4 > if I move to the last row and get the row number. Trying to load any > field from any row beyond the first throws a SQLException... Could you please post the complete code? Otherwise it's quite difficult to analyse this. robert Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.