java.sql.SQLException: Invalid column index

Question

I have a SQL query which I want to use to count components into table.

private DCDataObj dc;

    public class DCDataObj
    {

        private int datacenter;             //  Datacenters
        ..............

        public DCDataObj(int datacenter............)
        {
            this.datacenter = datacenter;
            ...............
        }

        public int getDatacenter()
        {
            return datacenter;
        }

        public void setDatacenter(int datacenter)
        {
            this.datacenter = datacenter;
        }

        ............
    }

ps = conn.prepareStatement("SELECT COUNT(1) AS CNT FROM COMPONENTSTATS CS, COMPONENTTYPE CT "
        + " WHERE CS.COMPONENTTYPEID = CT.COMPONENTTYPEID AND CT.COMPONENTTYPEID IN ( "
        + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " //  10
        + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " //  20
        + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " //  30
        + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) " //  40
        + " GROUP BY CT.NAME ORDER BY CT.NAME");

ps.setInt(1, 1000);
...............

ResultSet result = ps.executeQuery();
            while (result.next())
            {

                dc = new DCDataObj(
                        result.getInt(1),
                        ...............

Here is the complete source code: http://pastebin.com/YMvqBPpV

I get this error message: java.sql.SQLException: Invalid column index

Is this design problem or the problem is into the SQL query?

Are you getting the error at ps.setInt or result.getInt? — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Feb 26, 2013 at 20:52
The full stacktrace should show you the line on which the problem is. — beny23
– beny23, Commented Feb 26, 2013 at 20:53

Sotirios Delimanolis · Accepted Answer · 2013-02-26 21:14:57Z

1

I saw your full source code and you have a bunch of result.getInt(INDEX). Since you are only doing SELECT COUNT(1), there is only one column, so for any value of INDEX other than 1, the getInt() will fail.

Change your query to SELECT <LIST> ... where LIST is a comma separated list of column names from which you want to retrieve values.

edited Feb 26, 2013 at 21:14

answered Feb 26, 2013 at 20:55

Sotirios Delimanolis

281k62 gold badges718 silver badges744 bronze badges

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

6 Comments

Sotirios Delimanolis Over a year ago

There is no "correct" query, what is it you want your query to return?

Peter Penzov Over a year ago

I want the query to return the number of each component.

Sotirios Delimanolis Over a year ago

Then why are you creating a new DCDataObj(...) object after your query. You should just be creating an int as int count = result.getInt(1);

Peter Penzov Over a year ago

Yes but I want to fill the Java Object with the number of each component. With one SQL query I want to get the number of each component

Sotirios Delimanolis Over a year ago

Your class for that Object doesn't have a field for count. You're going to have to store the count differently.

|

Collectives™ on Stack Overflow

java.sql.SQLException: Invalid column index

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related