Why it says "java.sql.SQLException: invalid column name"

Question

I'm trying to get results from DB

        String strCommand = "select TO_CHAR (realdate, 'YYYYMMDD'), PURCHASE_PRICE, SELLING_PRICE from CURRENCY_VI where RATE_NAME='EUR'";

        cs.setQueryTimeout(m_nTimeout);

        ResultSet rs = cs.executeQuery(strCommand);

        while (rs.next()){
            System.out.println("!!!\n\nDATE = " + rs.getString("realdate") + " PURCHASE_PRICE = " + rs.getString("PURCHASE_PRICE") + " SELLING_PRICE = " + rs.getString("SELLING_PRICE"));
        }

It says that rs.getString("realdate") - "java.sql.SQLException: invalid column name", why?

Without rs.getString("realdate") everything works fine.

Actually, table has this column

  CREATE TABLE "GPB"."CURRENCY_VI" 
   (           "REALDATE" DATE, 
                "PURCHASE_PRICE" FLOAT(126), 
                "SELLING_PRICE " FLOAT(126), 
                "RATE_NAME" VARCHAR2(20 BYTE)
   )

Thank you!

score 11 · Accepted Answer · answered Sep 15 '11 at 09:09

I think you are not selecting realdate. You are selecting TO_CHAR (realdate, 'YYYYMMDD'), and that column gets that name. You could do something like this:

TO_CHAR (realdate, 'YYYYMMDD') as myrealdate

and select that. (with `rs.getString("myrealdate") ofcourse, not with realdate)

score 4 · Answer 2 · answered Sep 15 '11 at 09:09

There is no column realdate in your SELECT clause. You select TO_CHAR (realdate, 'YYYYMMDD'), which is not the same thing. You might want to try using getString("TO_CHAR (realdate, 'YYYYMMDD')") and if that doesn't work use AS to give that column a name:

select TO_CHAR (realdate, 'YYYYMMDD') AS realdate, PURCHASE_PRICE, SELLING_PRICE from CURRENCY_VI where RATE_NAME='EUR'

Alternatively you can use the column-index based selection: rs.getString(1) (note that the index in JDBC is always 1-based).

score 2 · Answer 3 · answered Sep 15 '11 at 09:10

You're selecting the function TO_CHAR (realdate, 'YYYYMMDD') on the REALDATE field, not the field itself. Add an alias to it and use that alias to retrieve the result.

SELECT TO_CHAR (realdate, 'YYYYMMDD') as realdate_str, ....

rs.getString("realdate_str");

score 0 · Answer 4 · answered Sep 15 '11 at 09:09

0

"realdate" is not in ur select query make ur query like this

String strCommand = "select REALDATE, PURCHASE_PRICE, SELLING_PRICE from CURRENCY_VI where RATE_NAME='EUR'";

answered Sep 15 '11 at 09:09

swan

2,301
3
24
48

Why it says "java.sql.SQLException: invalid column name"

4 Answers4