Get result from Oracle SQL query

Question

I have this Java Object which I will use to store counted elements from table:

private DCDataObj dc;

    public class DCDataObj
    {

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

        public DCDataObj()
        {
        }

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

        public int getDatacenter()
        {
            return datacenter;
        }

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

        ..........
    }

I use this SQL query to count the components into the Oracle table:

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

I get the result using this Java code:

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

    dc = new DCDataObj(
            result.getInt(1),
            result.getInt(2),
            result.getInt(3),
            ...........
            );

}

I get this problem when I execute the query:

java.sql.SQLException: Invalid column index

Can you help me how I can solve this problem?

UPDATE

The SQL query works. I get this result:

    CNT                    
---------------------- 
1                      
1                      
1  1

I suspect that the problem is into the return type. I suppose that I get the result as array. But can I somehow inset the result from the query into the Java object without using Array?

Your SQL statement returns a result set with two columns, COMPONENT_TYPE and CNT. Are you trying to retrieve the value for one of those columns from the first row of the result and pass that to your DCDataObj constructor? If so, are you trying to retrieve the CNT? Or the COMPONENT_TYPE? My guess would be the CNT. — Justin Cave
– Justin Cave, Commented Feb 26, 2013 at 19:37

rgettman · Accepted Answer · 2013-02-26 20:00:36Z

1

On each line, you end with a "?" but the next line starts with another "?" without a comma. Then you wind up with part of the string looking like ", ? ?," which is invalid JDBC syntax. You need commas in between all your "?" placeholders.

Try this, with commas added at the end of your lines "10", "20", and "30".

ps = conn.prepareStatement("SELECT CT.NAME AS COMPONENT_TYPE, 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");

EDIT

Now that I see your data, I see the problem. You cannot call getInt referencing the data, only the column header name or the 1-based column index. Also, your "COMPONENT_TYPE" is alphanumeric, please use getString instead of getInt. That also means you'll have to change your DCDataObj class to have a String for datacenter, not an int.

Try

dc = new DCDataObj(
   result.getString("COMPONENT_TYPE"),
   ...........
   );

or

dc = new DCDataObj(
   result.getString(1),
   ...........
   );

edited Feb 26, 2013 at 20:00

answered Feb 26, 2013 at 19:36

rgettman

179k30 gold badges282 silver badges365 bronze badges

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

3 Comments

rgettman Over a year ago

Your call to getInt was incorrectly referencing your data instead of the column header name. I've edited my answer to add the explanation.

Peter Penzov Over a year ago

I edited the code, but now I get this error java.sql.SQLException: Invalid column index

rgettman Over a year ago

You are attempting to get column #3, but you only define 2 columns in your query.

Marcelo Tataje · Accepted Answer · 2013-02-26 19:35:04Z

0

A colon in a bind variable or INTO specification was followed by an inappropriate name, perhaps a reserved word. You need to change the variable name and retry the operation. Did you try to get results from your query using pl/sql or SQL plus or your oracle terminal? Just to ensure you're executing the right query.

answered Feb 26, 2013 at 19:35

Marcelo Tataje

3,8911 gold badge29 silver badges56 bronze badges

Comments

rahimv · Accepted Answer · 2013-02-26 20:05:10Z

0

There is no column called "DATACENTER" fetched in the SELECT statement. It should be either COMPONENT_TYPE or CNT in result.getInt call.

answered Feb 26, 2013 at 20:05

rahimv

5794 silver badges12 bronze badges

Collectives™ on Stack Overflow

Get result from Oracle SQL query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related