CODEWITHSUNDEEP
javajdbc
Tepken Vannkorn
Tepken Vannkorn

Reputation: 211

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

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?

Upvotes: 7

Views: 52856

Answers (6)

Usman YousafZai
Usman YousafZai

Reputation: 1120

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")

Output of dbcolumninfo()

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

Upvotes: 1

Hink
Hink

Reputation: 1103

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.

Upvotes: 7

Remco
Remco

Reputation: 1933

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.

Upvotes: 19

Shashika Fernando
Shashika Fernando

Reputation: 66

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");

Upvotes: 1

user1780677
user1780677

Reputation:

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

Upvotes: 1

Vineet Reynolds
Vineet Reynolds

Reputation: 76709

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.

Upvotes: 10

Related Questions