AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  problem when using "Group by" statements with resultsets...          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message JP Estrada via JavaKB.com Guest Posted: Thu Mar 17, 2005 11:00 pm    Post subject: problem when using "Group by" statements with resultsets... hi guys...why do i get an "invalid cursor state" error when i execute a query with a group by? the query works fine in MS Access but when i use it with a resultset, it gives that error....thanks... -- Message posted via http://www.javakb.com Back to top GreyBeard Guest Posted: Fri Mar 18, 2005 1:46 am    Post subject: Re: problem when using "Group by" statements with resultsets On Thu, 17 Mar 2005 23:00:48 +0000, JP Estrada via JavaKB.com wrote: Quote: hi guys...why do i get an "invalid cursor state" error when i execute a query with a group by? the query works fine in MS Access but when i use it with a resultset, it gives that error....thanks... What database is your 'resultset' pointing at? Not all databases know how to 'group by'. /FGB Back to top joeNOSPAM@BEA.com Guest Posted: Fri Mar 18, 2005 2:53 am    Post subject: Re: problem when using "Group by" statements with resultsets I have seen JDBC drivers from vendors whose DBMSes supported 'group by' fail to handle them! Joe Weinstein at BEA Back to top GreyBeard Guest Posted: Fri Mar 18, 2005 2:59 am    Post subject: Re: problem when using "Group by" statements with resultsets On Thu, 17 Mar 2005 18:53:12 -0800, [email]joeNOSPAM (AT) BEA (DOT) com[/email] wrote: Quote: I have seen JDBC drivers from vendors whose DBMSes supported 'group by' fail to handle them! Joe Weinstein at BEA Hi Joe! Yes, I concur with that. OP's problem could be drivers or end database. Perhaps OP could provide more info than 'it works in Access'. Source of drivers, target database, perhaps even java version might be of interest. /FGB Back to top Bjorn Abelli Guest Posted: Fri Mar 18, 2005 7:55 am    Post subject: Re: problem when using "Group by" statements with resultsets "GreyBeard" wrote... Quote: Joe Weinstein wrote: I have seen JDBC drivers from vendors whose DBMSes supported 'group by' fail to handle them! Joe Weinstein at BEA Hi Joe! Yes, I concur with that. OP's problem could be drivers or end database. Perhaps OP could provide more info than 'it works in Access'. Source of drivers, target database, perhaps even java version might be of interest. Not to mention the query in question. Though "Group By" is supported by most DBMSes, the placement of it is more restricted in some... // Bjorn A Back to top JP Estrada via JavaKB.com Guest Posted: Fri Mar 18, 2005 1:48 pm    Post subject: Re: problem when using "Group by" statements with resultsets im using j2sdk 1.4.2_06 .... the sql statement with a group by works fine when i run it in ms access 2003...but when i execute the same query with a resultset, and traverse through it with a while(rs.next) { } i get that "invalid cursor state"... why is that? -- Message posted via http://www.javakb.com Back to top Bjorn Abelli Guest Posted: Fri Mar 18, 2005 2:46 pm    Post subject: Re: problem when using "Group by" statements with resultsets "JP Estrada via JavaKB.com" wrote... Quote: im using j2sdk 1.4.2_06 .... the sql statement with a group by works fine when i run it in ms access 2003... but when i execute the same query with a resultset, and traverse through it with a while(rs.next) { } i get that "invalid cursor state"... why is that? To sum up the follow-up questions you've been asked to provide answers to, for us to be able to give you a plausible solution... - What database is your 'resultset' pointing at? - What JDBC-driver are you using? - How is the query in question formulated? And to fill up with another question... - What are you doing *within* the iteration through the ResultSet? To me, it now sounds like the query in question very well could be valid, as you now say that the error occurs as you "traverse through the ResultSet", and not at the execution of the statement... Please provide a SSCCE: http://www.physci.org/codes/sscce.jsp // Bjorn A Back to top Juan Paulo Estrada via Ja Guest Posted: Sat Mar 19, 2005 2:01 am    Post subject: Re: problem when using "Group by" statements with resultsets ok here are the info.. 1. im not sure what you mean by 'what database' my resultset is pointing at..could you please elaborate.. i used ms access 2003..but im not sure if thats what you mean... 2. to connect to the db i just do this Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); c1 = DriverManager.getConnection("jdbc:odbc:theDB","",""); stat=c1.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 3. here is my simple query: SELECT SUM(qty) as sum1, prod_code FROM tab1 WHERE stk_date=#3/10/2005# GROUP BY prod_code 4. here is what i do in my iteration.. while(reS.next()) { System.out.println(reS.getString("prod_code")); } after all of the prod_code field values are displayed get this [Microsoft][ODBC Driver Manager] Invalid cursor state -- Message posted via http://www.javakb.com Back to top Bjorn Abelli Guest Posted: Sat Mar 19, 2005 10:36 am    Post subject: Re: problem when using "Group by" statements with resultsets "Juan Paulo Estrada via JavaKB.com" wrote... Quote: ok here are the info.. Aah, now we're getting somewhere... ;-) Quote: 1. im not sure what you mean by 'what database' my resultset is pointing at..could you please elaborate.. i used ms access 2003..but im not sure if thats what you mean... That's exactly what I meant. Now we know that the target database is MS Access 2003, with all its limitations... Quote: 2. to connect to the db i just do this Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); c1 = DriverManager.getConnection("jdbc:odbc:theDB","",""); stat=c1.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); I think you can see the error in logic here. As your query is an aggregation, it will not be updateable, no matter what parameters you're using when creating the statement. Try this instead: stat = c1.createStatement(); Quote: 3. here is my simple query: SELECT SUM(qty) as sum1, prod_code FROM tab1 WHERE stk_date=#3/10/2005# GROUP BY prod_code Perfectly valid query (for an Access database through the ODBC driver...). Quote: 4. here is what i do in my iteration.. while(reS.next()) { System.out.println(reS.getString("prod_code")); } Some drivers have difficulties in extracting the results with column names, though I don't think that's the case here... Quote: after all of the prod_code field values are displayed get this [Microsoft][ODBC Driver Manager] Invalid cursor state You can try my suggestion on p2, and see if that helps. // Bjorn A Back to top Juan Paulo Estrada via Ja Guest Posted: Sat Mar 19, 2005 11:49 am    Post subject: Re: problem when using "Group by" statements with resultsets yeah you're right!! of course aggregate functions will not be updatable! i didnt notice the way i created my statement object.... i used the c1.createStatement()...like you said the error's gone now... thanks a lot bjorn! -- Message posted via http://www.javakb.com Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.