ODBC Connection error in Dynamics AX 2012

Now you might be using ODBC to connect to some external Datasource to AX.  A commonly followed mistake may bring you around an error message as under:

[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

 

The simplest reasons for this issue are under:

1)      Wrong sequence of reading data; as we can see piece of code, 27 is being used before 26; Its wrong.

cpProductStorageDimGroup            = resultSet.getString(27);   

cpItemGroup                         = resultSet.getString(26);

2)      Using same index more than one time; see below code, same index 27 is being used twice; which is wrong

cpProductStorageDimGroup            = resultSet.getString(27);   

cpItemGroup                         = resultSet.getString(27);

Conclusion:

The correct way to write the code is to call indexes/ fields in sequence and at the same time should not be repeated. The simplest techniques to use it declare one variable for each column. Then afterwards in you loop assign values from columns of database to your variables and use them accordingly. As sample code could be as under

str     someVariable1;

        real    someVariable2;

   

        LoginProperty                   loginProperty;

        OdbcConnection                  odbcConnection;

        Statement                       statement;

        ResultSet                       resultSet;

        str                             sql, criteria;

        SqlStatementExecutePermission   perm;

        ;

   

        loginProperty = new LoginProperty();

        loginProperty.setDSN("ODBCName");

        loginProperty.setDatabase("DatabaseName");

        odbcConnection = new OdbcConnection(loginProperty);

   

       

            sql = "SELECT * FROM TableName";

            perm = new SqlStatementExecutePermission(sql);

            perm.assert();

            statement = odbcConnection.createStatement();

            resultSet = statement.executeQuery(sql); 
      
        while (resultSet.next())

        {

            //<focus here sequence>

            someVariable1           = resultSet.getString(1);

            someVariable2           = resultSet.getReal(2);

            //</focus here>

            info(strFmt("Value 1: %1  - Value 2: %2",someVariable1,someVariable2));           

        }