Result set type

[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

[Mahesh Mamani]

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]

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:

System.out.println( rs.getString( 1 ) );

Jamie

[Nischal Topno]

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]

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]

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]

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