I have succesfully set up a Connection and a System DSN and conected to an SQL Server database. I have succesfully created a statement returning RecordSet and executed it.

The problem is that when I try and get the data from it, it appears to be empty and gives me '[Microsoft][ODBC Driver Manager] Invalid cursor state' . My code is :


package pacJDBC;

import java.sql.*;

//This class explores the concepts of JDBC and Database access
//using Java
public class DBAccess
{
///constructors
public DBAccess()
{
}

public static void main(String[] args)
{
DBAccess dbaccess = new DBAccess();
dbaccess.execute();
}

private Connection connect()
{
Connection con = null;

try
{
//JDBc driver (comes with J2 SDK 1.4)
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
//JDBC url to use with JDBC driver. Uses a System DSBN (test1)
String url = "jdbc:odbc:test1";
//database login credentials
String username = "sa";
String password = "";

// Load the JDBC driver
Class.forName(driverName);

// Create a connection to the database
con = DriverManager.getConnection(url, username, password);
}
catch (ClassNotFoundException e)
{
// Could not find the database driver
System.out.println("Could not find the driver");
}
catch (SQLException e)
{
// Could not connect to the database
System.out.println("Could not connect to the database");
}

return con;
}

private void execute()
{
String strSQL;
Connection con = null;
Statement stmt;
ResultSet rst;

strSQL = "Select * from Categories";
con = connect();

try
{
//create a static, scrollable, insensitive statement
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rst = stmt.executeQuery(strSQL);

System.out.println(rst.getString("CategoryName"));
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
}

Not sure if you realy need the ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY in your stmt = con.createStatement() line

Here is a snippet of code from a program I use. It connects to a DB2 database, but is pretty much like yours.

Only thing I can see is you never use the rs.next() method.
.next() method JavaDocs:

Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.
If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.
Returns: true if the new current row is valid; false if there are no more rows
Throws: SQLException if a database access error occurs

So, it appears you are at the begening (before the first record.)



// Database Variables (Declared before main method)
public static Connection con = null;


(Main method....)

// Make sure database driver is available
try {
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
}
catch (Exception e) {
System.err.println("\n Error loading DB2 Driver...\n" + e);
System.exit(1);
}

// Make sure the database is up and running and connect to it if it is
try {
String url = "jdbc:db2:" + targetDB; //Target DB is ODBC Name

con = DriverManager.getConnection(url);
}
catch (Exception e) {
System.err.println("\n Error connecting to the database...\n" + e);
con = null;
System.exit(1);
}

String SQLQuery = "SELECT * FROM Mydatabase";

ResultSet rs = null; // Create Result set
Statement stmt = con.createStatement();
rs = stmt.executeQuery(SQLQuery); // Execute the query

// Now qwery the fields
while (rs.next()) {
System.out.println(rs.getString("MyFieldName"));
......repeat as needed
}

Thanks! That does the trick.