aspose file tools*
The moose likes JDBC and the fly likes Result set type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Result set type" Watch "Result set type" New topic
Author

Result set type

Nischal Topno
Ranch Hand

Joined: Nov 24, 2001
Posts: 45
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

Joined: Jun 25, 2001
Posts: 110
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Nov 24, 2001
Posts: 45
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

Joined: Oct 09, 2001
Posts: 566
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/


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

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


Groovy
Nischal Topno
Ranch Hand

Joined: Nov 24, 2001
Posts: 45
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Result set type