Java JDBC ResultSet

[chrisn]

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());
        }
    }
}

[jerbo]

Not sure if you realy need the ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLYin 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 rowsThrows: 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
}

[chrisn]

Thanks! That does the trick.