Getting error as " java.sql.SQLException: Invalid column index" while executing SQL query through java

Question

Can anyone suggest what i am doing wrong here, trying to print the SQL query output in console, but getting error as " java.sql.SQLException: Invalid column index".

import java.io.*;  
import java.sql.*;  

public class RetrieveFile {  
    public static void main(String args[]) throws Exception {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@123.43.23.43:8080/orcl", "Test", "*****");

            PreparedStatement ps = con.prepareStatement("select * from MSG where MSD='1234'");
            ResultSet rs = ps.executeQuery();
            // rs.next();//now on 1st row
            while (rs.next()) {

                int numberOfColumns = 0;
                for (int column = 1; column >= numberOfColumns; column++) {

                    if (column > 1)
                        System.out.print(", ");
                    System.out.print(rs.getString(column));
                }
            }

            con.close();

            // System.out.println("success" + (rs.getString(1)));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

I am getting some part of response, but end getting the following error.

ERROR:

null, 1234, 389, OUR, NOW, USD, 0, 0, FR1, wert, USD, 0, null, 0, 0, null, DR, null, null, 0, 0, null, null, null, null, null, null, null, null, null, 0, 2, 2019-06-11 00:00:00.0, null, null, null, null, null, null, null, java.sql.SQLException: Invalid column index at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:379) at RetrieveFile.main(RetrieveFile.java:20) Picked up JAVA_TOOL_OPTIONS: -Duser.home=C:\Users\45060849

Please include the table structure of MSG in output Thanks in advance.

Why for (int column = 1; column >= numberOfColumns; column++)? Your code doesn't make any sense whatsoever. — user207421
– user207421, Commented Jul 24, 2019 at 10:26

deHaar · Accepted Answer · 2019-07-24 12:48:14Z

1

In case you neither know the number of columns, nor their data types, you have to check them via ResultSetMetaData.
The following example does it in a separate method, which does not yet consider every possible SQL data type, but some very common ones.

public static void main(String args[]) throws Exception {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@123.43.23.43:8080/orcl", "Test", "*****");

        PreparedStatement ps = con.prepareStatement("select * from MSG where MSD='1234'");
        ResultSet rs = ps.executeQuery();

        try {
            printResultColumns(rs);
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }

        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

You basically request the amount of columns, use it as iteration border and then you can check the type of column i. That is required due to different methods for different data types.

public static void printResultColumns(ResultSet resultSet) throws SQLException {
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int columnCount = rsmd.getColumnCount();

    while (resultSet.next()) {
        // you get a single result row in here, not the entire ResultSet
        for (int i = 1; i <= columnCount; i++) {
            switch (rsmd.getColumnType(i)) {
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
            case Types.CHAR:
                System.out.println(resultSet.getString(i));
                break;
            case Types.DOUBLE:
                System.out.println(resultSet.getDouble(i));
                break;
            case Types.INTEGER:
                System.out.println(resultSet.getInt(i));
                break;
            case Types.DATE:
                System.out.println(resultSet.getDate(i).toString());
                break;
            case Types.TIMESTAMP:
                System.out.println(resultSet.getTimestamp(i).toString());
                break;
            case Types.BOOLEAN:
                System.out.println(resultSet.getBoolean(i));
                break;
            case Types.DECIMAL:
            case Types.NUMERIC:
                System.out.println(resultSet.getBigDecimal(i));
                break;
            default:
                System.out.println("This column type (" 
                        + rsmd.getColumnTypeName(i)
                        + ") is currently not supported in method \"printResultColumns\""
                        + ".\nAdd it as case there.");
            }
        }
    }
}

edited Jul 24, 2019 at 12:48

answered Jul 24, 2019 at 8:26

deHaar

18.7k11 gold badges48 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

Routray Over a year ago

thanks for the response, now i am getting java.sql.SQLException: Invalid column name.

deHaar Over a year ago

@Routray Have you adjusted the query String to include "... MSG AS message ..."?

Routray Over a year ago

sorry it was miss from my side, now i have updated, but getting error as "ORA-00933: SQL command not properly ended"

deHaar Over a year ago

@Routray my solution is wrong, I will soon provide a better one.

deHaar Over a year ago

@Routray Have you tried my answer? Does it work? Are there any problems?

|

Mysterious Wolf · Accepted Answer · 2019-07-24 08:21:05Z

1

The java.sql.SQLException: Invalid column index means that a column with the index you are trying to access doesn't exist.

The variable numberOfColumns is always smaller than the column variable, so you are trying to get values from columns with indexes larger than the column count.

answered Jul 24, 2019 at 8:21

Mysterious Wolf

3731 gold badge5 silver badges22 bronze badges

Comments

yeahseol · Accepted Answer · 2019-07-24 08:25:37Z

0

int numberOfColumns = 0;
for (int column = 1; column >= numberOfColumns; column++) {

column> = numberOfColumns condition will call infinitely getString (column).
Modify the for statement.

try this

numberOfColumns = 0;
while(rs.next()) {
  numberOfColumns++;
  String s = rs.getString(numberOfColumns);
  //...
}

answered Jul 24, 2019 at 8:25

yeahseol

3951 silver badge6 bronze badges

2 Comments

Routray Over a year ago

thanks for the update, i have tried the above query but still facing the same issue: int numberOfColumns = 0; while(rs.next()) { numberOfColumns++; String s = rs.getString(numberOfColumns); for (int column = 1; column >= numberOfColumns; column++) { if(column > 1) System.out.print(", "); System.out.print(rs.getString(column));

Mark Rotteveel Over a year ago

rs.next() returns true for a new row, each row can have 1 or more columns. Your solution counts the number of rows, and return the column corresponding to the row number. When there are more rows than columns, this will still throw an exception (and it is unlikely this is what the OP wants or needs).

Collectives™ on Stack Overflow

Getting error as " java.sql.SQLException: Invalid column index" while executing SQL query through java

3 Answers 3

21 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

21 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related