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?

At what line exactly is it been thrown? You can find the line number in the 1st line of the stacktrace. The exception suggests that you're attempting to access a wrong/unknown column in the resultset. But the code snippet in your question doesn't give that away. — BalusC, Jun 15 '11 at 16:55
Thanks for the code update. But the exception handling is very poor. Replace that `System.out.println(e)` line by `e.printStackTrace()` or just `throw e`. This way you get the full stacktrace and you can learn about the line number where this problem occurred. — BalusC, Jun 15 '11 at 17:08

Remco · Answer 1 · 2015-07-06T20:09:30.343

18

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.

edited Jul 06 '15 at 20:09

answered Jul 10 '13 at 09:14

Remco

1,713
1
13
14

score 10 · Answer 2 · answered Jun 15 '11 at 17:07

10

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.

answered Jun 15 '11 at 17:07

Vineet Reynolds

76,006
17
150
174

I tried to list my SQL statement to : "SELECT RoomID, RoomName, RoomSize, Floor From tblRoom". This time it doesn't display the error message, but it won't generate the result in my JTable – Tepken Vannkorn Jun 15 '11 at 17:19
@Tepken, Is that the same as "SELECT * FROM tblRoom" ? i.e. are there 4 columns in tblRoom? – Vineet Reynolds Jun 15 '11 at 17:23
sure they are. i searched from other resources and they told me such thing. – Tepken Vannkorn Jun 15 '11 at 17:24
Strange. I think you can send a bill to Microsoft for finding a bug. – Vineet Reynolds Jun 15 '11 at 17:30
3

"Reading columns out of sequence..." I changed this in my case, had a coffee, slaughtered a chicken then it all went away like magic. Thanks. – ian_scho Apr 03 '12 at 16:08
@ian_scho: this is my typical SQL Server troubleshooting workflow. – BenDundee May 14 '14 at 19:13

score 7 · Answer 3 · answered Aug 12 '15 at 11:19

7

I know this bug for many years by using ODBC driver with PHP. Try to place your text and image columns at the end of select list.
Dont use

select * from t

but enumerate rigidly

select plain_column1, plain_column2, .... image_column from t

Unfortunately Microsoft doesn't get tired by fixing the bug. JDBC driver works OK.

answered Aug 12 '15 at 11:19

Hink

1,054
1
15
31

Opposite worked in my case, previously i was passing query `select plain_column1, plain_column2, .... image_column from t` which was giving me error, but when i changed the query to `select * from t` then exception resolved. – Shams Sep 14 '18 at 13:57

score 1 · Answer 4 · answered Mar 17 '13 at 17:19

That will occur if you're trying to get the resultset variable value in the index value of 0. For example: Consider a table which has 5 columns:

ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
while(rs.next())
{
   for(int i=0;i<5;i++)
   //This will throw the exception
   System.out.println(rs.getString(i)); //Since the value will be returned from 1 not 0
   //The below code was the right way
   System.out.println(rs.getString(i+1));
}

score 1 · Answer 5 · answered Jan 15 '15 at 05:08

I got an error

SEVERE: null java.sql.SQLException: [Microsoft][SQL Server Native Client 10.0]Invalid Descriptor Index

code was

String sqlStr = "select soldItems.payment as aa, Sysuser.name as sname, Books.Name as abookName, soldItems.Qunt as qunt, soldItems.date as soldBooks from Sysuser inner join soldItems on soldItems.CustomerId=Sysuser.id inner join Books on Books.bookId=soldItems.bookId where CustomerId='" + cusId + "' and PaymentDone is NULL";
    System.out.println(sqlStr);
    DbConnection con = new DbConnection();
    con.getConnection();
    ResultSet rs = con.getData(sqlStr);
    while (rs.next()) {
        int i = 0;


        dataArry[i][0] = rs.getString("abookName");
        dataArry[i][1] = rs.getString("qunt");
         dataArry[i][2] = rs.getString("aa");
        dataArry[i][3] = rs.getString("soldBooks");

        i++;
    }

the fix is rs.getString needs to be in the same order of the SQL

so the code needs to be

       dataArry[i][2] = rs.getString("aa");
        dataArry[i][0] = rs.getString("abookName");
        dataArry[i][1] = rs.getString("qunt");

        dataArry[i][3] = rs.getString("soldBooks");

score 1 · Answer 6 · answered Sep 21 '22 at 10:31

I have explained @Remco answer with example which is simple and short and helped me to solve my problem. Find the column info of MYTABLE first and place the column at the end with max values(suppose varchar(max) and or varbinary(max)). Try the example below.

With Error

library(DBI)
library(ODBC)
myquery<- dbGetQuery(con,"SELECT * FROM MYTABLE")

Error in Result_fetch....:  Invalid Descriptor Index

Solution

dbcolumnInfo(dbSendWuery(con,"SELECT * FROM MYTABLE")

From the results for DateTimeSampled is varchar(max). Place this column at the end of MYTABLE using following query.

myquery<- dbGetQuery(con,"SELECT [PROCHI],[hb_extract],
[QuantityValue],[QuantityUnit],[Interpretation],
[LabNumber],[LocalClinicalCodeValue],[DateTimeSampled]
FROM MYTABLE")

Enjoy SQL with R

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

6 Answers6

Linked