java.sql.SQLException:[Microsoft][ODBC Driver Manager] Invalid descriptor index

Question

I use the following code

try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:access");
    String sql = "Select * from table";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery( sql );
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    for (int i = 1; i <= columns; i++) {
        columnNames.addElement( md.getColumnName(i) );
    }
    while (rs.next()) {
        Vector row = new Vector(columns);
        for (int i = 1; i <= columns; i++){
            row.addElement( rs.getObject(i) );
        }
        data.addElement( row );
    }
    rs.close();
    stmt.close();
}catch(Exception e){
    System.out.println(e);
}

It displays:

java.sql.SQLException:[Microsoft][ODBC Driver Manager] Invalid descriptor index

How is this caused and how can I solve it?

Answer 1

I have had the same exact error, this out of an ODBC Express Driver for Delphi.

The solution I have found is:

Place your varchar(max) and or varbinary(max) fields at the end of your select Query. (Order in the table definition doesn't matter).

This really fixed it for us, thought to share it with you guys.

Answer 2

I doubt the exception is thrown by one of the lines in the posted code. I have my reasons to state so.

A SQLException with the message "Invalid descriptor index" is usually obtained when you read the result set incorrectly. There are various ways in which this scenario can manifest:

• Reading columns out of sequence. I'm afraid, some JDBC drivers will require you to read columns in order, starting at the first column. That's the way some drivers have been written; you cannot skip any columns when reading the resulting result set, as the drivers are actually reading a stream and converting objects in the stream to objects of the JDBC types.
• You might be reading a column, whose index is invalid, or whose column name doesn't match any of the returned columns in the result set. The simple resolution is to either fix the query to return the needed column, or fix your code to not read the absent column.

If you need to solve it, you need to know which one of the above conditions is true in your code, and rectify accordingly.