Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Result set type

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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/
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Why is the word "abbreviation" so long? And this ad is so short?
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic