• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Result set type

 
Nischal Topno
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If a result set is TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE, and if the query selects a DISTINCT, why does the resultset.next() return true even after there are not records. It gives "Invalid cursor state" error.
Say, I have a table MYTABLE with a field MYFIELD with the following data in MYFIELD:
========
NOKIA
ERRICSON
NOKIA
========
The following is my code:
--------------------------------------------------
import java.sql.*;
public class hello {
public static void main (String args[]) {
Connection con=null;
Statement stmt;
ResultSet rs;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:Nischal", uid, pwd);
stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery("SELECT DISTINCT MYFIELD FROM MYTABLE");
while(rs.next()){
System.out.println(rs.getString("MESSAGEID"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if (con!=null) {
con.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
--------------------------------------------------
Strangely, if I use TYPE_FORWARD_ONLY, or use SELECT statement without DISTINCT then it works perfectly.
Is there any method to find the number of records fetched in a ResultSet (something like rs.recordcount in ADO/VB6)
Please Help
Nischal
 
Mahesh Mamani
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Is there any method to find the number of records fetched in a ResultSet (something like rs.recordcount in ADO/VB6)
Well am not sure of any function in resultset to find number of records, the alternative what u can do is in the select statement, give a count(*) with the same conditions/checks...Then u can get the count retrieved from the query fired...
Hope It Helps

MSM
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nischal, you have done most of this correctly. rs.next() will only return true if there is another row to be processed. The problem is in your getString portion of code. The name of the column is invalid. Once you use a function on a column, you can not access the column by the original column name, so the best idea is to access the row using an integer:

Jamie
 
Nischal Topno
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jamie,
The invalid column name is a mistake (copy & paste mistake).
"MESSAGEID" is the actual field name I am using. In my example code above it should read as "MYFIELD".
Nischal
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Nischal Topno:
If a result set is TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE, and if the query selects a DISTINCT, why does the resultset.next() return true even after there are not records. It gives "Invalid cursor state" error.
Say, I have a table MYTABLE with a field MYFIELD with the following data in MYFIELD:
========
NOKIA
ERRICSON
NOKIA
========
The following is my code:
--------------------------------------------------
import java.sql.*;
public class hello {
public static void main (String args[]) {
Connection con=null;
Statement stmt;
ResultSet rs;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:Nischal", uid, pwd);
stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery("SELECT DISTINCT MYFIELD FROM MYTABLE");
while(rs.next()){
System.out.println(rs.getString("MESSAGEID"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if (con!=null) {
con.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
--------------------------------------------------
Strangely, if I use TYPE_FORWARD_ONLY, or use SELECT statement without DISTINCT then it works perfectly.
Is there any method to find the number of records fetched in a ResultSet (something like rs.recordcount in ADO/VB6)
Please Help
Nischal

Hi Nischal,
you asked this question in another thread but you never replied to my reply. Plus the fact that you have been coding in Visual Basic, this amounts to a serious misdemeanour. Only joking.
What database are you using? What JDBC driver?
ResultSet doesn't have a .size() method, which would be nice. Try looking at CachedRowSet
http://developer.java.sun.com/developer/technicalArticles/javaserverpages/cachedrowset/
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
If your Driver supports ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE then do the following:
rs.last();
int count = rs.getRow();
count contains the number of rows in ResultSet.
------------------

Originally posted by Nischal Topno:
If a result set is TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE, and if the query selects a DISTINCT, why does the resultset.next() return true even after there are not records. It gives "Invalid cursor state" error.
Say, I have a table MYTABLE with a field MYFIELD with the following data in MYFIELD:
========
NOKIA
ERRICSON
NOKIA
========
The following is my code:
--------------------------------------------------
import java.sql.*;
public class hello {
public static void main (String args[]) {
Connection con=null;
Statement stmt;
ResultSet rs;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:Nischal", uid, pwd);
stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery("SELECT DISTINCT MYFIELD FROM MYTABLE");
while(rs.next()){
System.out.println(rs.getString("MESSAGEID"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if (con!=null) {
con.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
--------------------------------------------------
Strangely, if I use TYPE_FORWARD_ONLY, or use SELECT statement without DISTINCT then it works perfectly.
Is there any method to find the number of records fetched in a ResultSet (something like rs.recordcount in ADO/VB6)
Please Help
Nischal
 
Nischal Topno
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,
Thanks for all the suggestion.
I have found the cause for the problem. I am using SQL Anywhere v.5.5.04 with jdbc dbc bridge, and i found out that it is not supported in SQL Anywhere. I tried out the same in SQL Server 7.0 (with jdbc dbc bridge) & oracle (with jdbc driver) and it works perfectly fine.
Thanks one again to you all
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic