Invalid cursor state - Am I completely stupid?

[Kyle]

I am going crazy trying to accomplish the simplest of tasks: retrieve
data via JDBC. I get a [Microsoft][ODBC Driver Manager] Invalid
cursor state SQLException whenever I access the Resultset.get*
methods. And, yes, I have remembered to call the .next() method
before the 1st access. I am using the JDBC-ODBC bridge and get the
same error with both Access and SQL Server databases. The query
executes and the Resultset exists as I can read the ResultSetMetaData
object and successfully read the number of columns and column names.
So, the problem definitely is in the fetching of the data.

Please embarass me and enlighten me as to what stupid mistake I am
making.

import java.sql.*;
public void runquery() {
ResultSet rs = null;
Statement sql_stmt = null;
Connection conn = null;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbcdbc:LDXTables",
"sa", "");
sql_stmt = conn.createStatement();

sql_stmt.execute("SELECT * FROM testtable");
rs = sql_stmt.getResultSet();

if (rs!=null){
while (rs.next());
{
//ODBC Invalid cursor state error occurs on the
..getInt or .getString call
String str = String.valueOf(rs.getInt(1)) +
rs.getString(2) + "\n";
}
}
} catch (ClassNotFoundException ex) {
} catch (SQLException ex) {
} catch (Exception ex) {
} finally {
try {
rs.close();
} catch (SQLException ex) { }
try {
sql_stmt.close();
} catch (SQLException ex) { }
try {
conn.close();
} catch (SQLException ex) { }
}
}

 

[Ann]

I am going crazy trying to accomplish the simplest of tasks: retrieve
data via JDBC. I get a [Microsoft][ODBC Driver Manager] Invalid
cursor state SQLException whenever I access the Resultset.get*
methods. And, yes, I have remembered to call the .next() method
before the 1st access. I am using the JDBC-ODBC bridge and get the
same error with both Access and SQL Server databases. The query
executes and the Resultset exists as I can read the ResultSetMetaData
object and successfully read the number of columns and column names.
So, the problem definitely is in the fetching of the data.

Please embarass me and enlighten me as to what stupid mistake I am
making.

import java.sql.*;
public void runquery() {
ResultSet rs = null;
Statement sql_stmt = null;
Connection conn = null;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbcdbc:LDXTables",
"sa", "");
sql_stmt = conn.createStatement();

sql_stmt.execute("SELECT * FROM testtable");
rs = sql_stmt.getResultSet();

if (rs!=null){
while (rs.next());
{
//ODBC Invalid cursor state error occurs on the
.getInt or .getString call
String str = String.valueOf(rs.getInt(1)) +
rs.getString(2) + "\n";
}
}
} catch (ClassNotFoundException ex) {
} catch (SQLException ex) {
} catch (Exception ex) {
} finally {
try {
rs.close();
} catch (SQLException ex) { }
try {
sql_stmt.close();
} catch (SQLException ex) { }
try {
conn.close();
} catch (SQLException ex) { }
}
}

Did you remember to do the setup stuff using "ODBC Data Source
Administrator"
to map the file to a DSN?

 

[Klixx0r]

Hi m8, I think ur problem is with ur stmt object, uve created it
with parameters, as default its read only and foward only cursor
mode... try it with other pars like scrollable ... I think this
will solve it, I had similar problem before.

L8er

 

[Kyle]

Yes, a connection is successfully made, and the query executes
successfully as I can read the ResultSetMetaData object and indeed,
the column names and number of columns are correct in the
ResultSetMetaData object. The rs.next() method returns true and then
I get the "Invalid Cursor State" error when calling the .getString or
..getInt methods. Same error when trying this against both Access and
SQLServer databases.

Driving me cuh-razy.

 

[John C. Bollinger]

I am going crazy trying to accomplish the simplest of tasks: retrieve
data via JDBC. I get a [Microsoft][ODBC Driver Manager] Invalid
cursor state SQLException whenever I access the Resultset.get*
methods.

If that is actual text from the exception's message then it suggests
that there is a problem on the ODBC side. That could mean you have
configured your ODBC DataSource incorrectly -- for instance, there may
be an access permission problem.

In troubleshooting this it is probably worth your while to examine the
SQLException in more detail. For instance, there may be more exceptions
chained to this one (see SQLException.getNextException()). You can also
access a vendor-specific error code via SQLException.getErrorCode() that
you may be able to use to extract information from Microsoft's knowledge
base. There is even some chance that SQLState string will be
illuminating. Read the SQLException API docs for details.

And, yes, I have remembered to call the .next() method

before the 1st access. I am using the JDBC-ODBC bridge and get the
same error with both Access and SQL Server databases. The query
executes and the Resultset exists as I can read the ResultSetMetaData
object and successfully read the number of columns and column names.
So, the problem definitely is in the fetching of the data.

Please embarass me and enlighten me as to what stupid mistake I am
making.

import java.sql.*;
public void runquery() {
ResultSet rs = null;
Statement sql_stmt = null;
Connection conn = null;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbcdbc:LDXTables",
"sa", "");
sql_stmt = conn.createStatement();

sql_stmt.execute("SELECT * FROM testtable");
rs = sql_stmt.getResultSet();

if (rs!=null){
while (rs.next());
{
//ODBC Invalid cursor state error occurs on the
.getInt or .getString call
String str = String.valueOf(rs.getInt(1)) +
rs.getString(2) + "\n";
}
}

[...]

I don't see anything inherently wrong with that JDBC-wise. If, as I
wrote above, there is a permission problem, it may be that the empty
password in getConnection() is not being communicated correctly. You
could try assigning and using a password; you could also try finding and
using a form for the DB URL that incorporates the username and password
directly.


John Bollinger
(e-mail address removed)

 

[abenm613]

I am currently experiencing a similar problem. First, I execute a
query and get a result set in a pushdown list via the following code:
-----------------------------------------------------
private void getTable(String searchKey, String searchStart)
{
try{
String query = "SELECT lastname, firstname FROM students
WHERE ("+searchKey+" = '"+searchStart+"')";
System.out.println("searchStart = "+ searchStart + "\n");
System.out.println(query);
statement = connection.createStatement();
System.out.println("statement has been created");
resultSet = statement.executeQuery(query);
System.out.println("query executing but not yet completed");
displayResultSet(resultSet);
System.out.println("result set displayed");
}
catch(SQLException sqlex){
sqlex.printStackTrace();
}
}
-----------------------------------------------------------
It works perfectly so far. My next step is to extract the fields of a
given record in the result set. For this purpose, I'm adding a
selection listener to the list containing the results of the previous
query. For testing purposes, at this point I'm just trying to display
the data in a DOS window. Here is the method I'm trying to implement:
-----------------------------------------------------------
searchResults.addListSelectionListener(
new ListSelectionListener(){
public void valueChanged(ListSelectionEvent e)
{
try
{
rsMetaData = resultSet.getMetaData();
System.out.println("The name of first column is
"+rsMetaData.getColumnLabel(1));
System.out.println("\nThe number for the lastname column is:
"+resultSet.findColumn("lastname"));

if(!resultSet.next()){
System.out.println("No next provided");
}
while(resultSet.next()){
String referenceValue = resultSet.getString(1);
processing(referenceValue);
}
}
catch(SQLException sqlex)
{
System.out.println("Something is going wrong in selectionlistener");
sqlex.printStackTrace();
}
}
}
);

 

[Nicky]

Hello

"+rsMetaData.getColumnLabel(1));
System.out.println("\nThe number for the lastname column is:
"+resultSet.findColumn("lastname"));

At this point the resultset is before the first row

if(!resultSet.next()){
System.out.println("No next provided");
}

There, you have called next().
Therefore the resultset is on the first row

while(resultSet.next()){

There you call next() again, the resultset goes to the *second* row.
If there is no second row, the while loop never executes.

String referenceValue = resultSet.getString(1);
processing(referenceValue);
}
}

try:

do {
} while (resultSet.next());

or remove the code that makes the first call to next():

 

[abenm613]

if(!resultSet.next()){

There, you have called next().
Therefore the resultset is on the first row

-------------------
Okay, suppose I did move the cursor one row forward. But why does it
returns false already at this time? By the way, someone told me that
the problem might be that the cursor is initially at the last row, and
that's why there is no next element at the beginning? So, I try
calling previous() or first() before getting to next(). But this makes
things even worse: it tells me that "Exception has occured during event
dispatching. java.lang.UnsupportedOperationException
Why is it and how to correct it? I suspect that my resultSet is empty,
in spite of the fact that the original query has run successfully. Is
it possible? If so, how to solve the problem?