• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1161
  • Last Modified:

select all columns except one

I have table with around 280 columns.. and the first col. is ImageID (String) as Primary Key and rest are double values

i want to select all columns except the first ImageID col and store values as double[]

i used something like this
ResultSet rs = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE ImageID='"+imageName+"'");

but in this case it select all columns.. how can i leave the first column out and select rest??
0
max_dub
Asked:
max_dub
  • 10
  • 7
  • 2
  • +1
2 Solutions
 
CEHJCommented:
>>but in this case it select all columns.. how can i leave the first column out and select rest??

Just call rs.getDouble on all but the last column
0
 
CEHJCommented:
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
double[] values = new double[numberOfColumns - 1];
for(int i = 1;i < values.length;i++) {
      values[i - 1] = rs.getDouble(i);
 
}
0
 
gamebitsCommented:
With mysql you cannot leave a column out, what you ca do is select everything but choose not to display certain fields.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
max_dubAuthor Commented:
i am doing like this now, now it says, no data is available

ResultSet rs = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE ImageID='"+imageName+"'");
                  
ResultSetMetaData rsmd = rs.getMetaData();
                  
int numberOfColumns = rsmd.getColumnCount();
double[] values = new double[numberOfColumns-1];
int j = 0;
                  
for(int i = 2; i <= numberOfColumns; i++) {
                        values[j] = rs.getDouble(i);
                        log.warn(values[j]);
                        j++;
}

i want to leave the first column out and get rest of column.. wat am i doing wrong??
0
 
CEHJCommented:
>>now it says, no data is available

What happens when you run the same query from the command line?
0
 
max_dubAuthor Commented:
command line :)) not sure .. only eclipse

can it mean that the db didn't store the values earlier when i fill table, i am using an in-memory table of hsqldb

let me check that part
0
 
CEHJCommented:
Make sure that the query is valid first
0
 
max_dubAuthor Commented:
i don't understand, i use the same query in other tables also, works fine but looks like it doesn't execute for this one.. it giving errors still

java.sql.SQLException: No data is available
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.checkAvailable(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.getColumnInType(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.getString(Unknown Source)
      at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:175)
      at GUI.database.FeatureVectorDBUtils.getFeatureVectorAsDouble(FeatureVectorDBUtils.java:77)
      at GUI.database.ImageFVWrapper.getFVasDouble(ImageFVWrapper.java:12)
      at GUI.help.BuildClassifierHelper$ClassifyLoader.doInBackground(BuildClassifierHelper.java:62)
      at GUI.help.BuildClassifierHelper.doInBackground(BuildClassifierHelper.java:34)
      at GUI.help.BuildClassifierHelper.doInBackground(BuildClassifierHelper.java:1)
      at javax.swing.SwingWorker$1.call(Unknown Source)
      at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at javax.swing.SwingWorker.run(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)

the connection is there, the table exists too..

any ideas where i shud look??
0
 
CEHJCommented:
Please post definition of the table you're having trouble with
0
 
max_dubAuthor Commented:
this is how create the table -

DBTableFunctions.createTableFromString("CREATE CACHED TABLE "+tableName+" ("+"ImageID VARCHAR(256) PRIMARY KEY, "+colNames+") CREATE UNIQUE INDEX IMAGEID_INDEX ON "+tableName+"(ImageID)");

colNames are the 285 columns of double values

read the data from CSV file to fill the table -
InsertTableRows.createTableRows(colLabels, tableName, file, delimiter);

For each imageID, i try to read the values from the table

0
 
max_dubAuthor Commented:
try{
                        ResultSet rs = stmt.executeQuery("SELECT AVERAGERED FROM "+tableName);
                         if(rs!=null)
                               log.warn(rs.getDouble(2));
                               rs.close();
                   }
                   catch (SQLException e)      {
                         log.warn("rs is null");
                   }

so the exception is occuring here, now i am selecting only one column..
??
0
 
CEHJCommented:
Instead of

>> log.warn("rs is null");

do

 log.warn("rs is null");
 e.printStackTrace();

and post the output here
0
 
max_dubAuthor Commented:
java.sql.SQLException: No data is available
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.checkAvailable(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.getColumnInType(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.getString(Unknown Source)
      at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:175)
      at GUI.database.FeatureVectorDBUtils.getFeatureVectorAsDouble(FeatureVectorDBUtils.java:77)
      at GUI.database.ImageFVWrapper.getFVasDouble(ImageFVWrapper.java:12)
      at GUI.help.BuildClassifierHelper$ClassifyLoader.doInBackground(BuildClassifierHelper.java:62)
      at GUI.help.BuildClassifierHelper.doInBackground(BuildClassifierHelper.java:34)
      at GUI.help.BuildClassifierHelper.doInBackground(BuildClassifierHelper.java:1)
      at javax.swing.SwingWorker$1.call(Unknown Source)
      at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at javax.swing.SwingWorker.run(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
0
 
max_dubAuthor Commented:
rs is actually not null,

so when i call rs.getDouble(2), org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:175) is doing something which i can't seem to figure out
0
 
CEHJCommented:
Are you sure that table is created and contains data?
0
 
objectsCommented:
> rs is actually not null,

thats correct

your code should look more like:

                        ResultSet rs = null;
                    try {
                         rs = stmt.executeQuery("SELECT AVERAGERED FROM "+tableName);
                         if(rs.hasNext())
                               log.warn(rs.getDouble(2));
                         else
                               log.warn("Query returned no rows");
                   }
                   catch (SQLException e)      {
                         e.printStackTrace();
                   }
                   finally {
                                                        rs.close();
                   }
0
 
max_dubAuthor Commented:
thats object.. it works..

thou i am confused with the way its accessing the data .. mebbe it has to do something with way i create table..

when i do -> "SELECT * FROM "+tableName
everytime it returns the same value for rs.getDouble(2)

but when i do -> "SELECT * FROM "+tableName+" WHERE ImageID='"+imageName+"'"
it returns the correct value for each indiividual image.. thou its always row 1

0
 
max_dubAuthor Commented:
i meant to write "thanks Objects" in prev. post first line :))
0
 
objectsCommented:
> when i do -> "SELECT * FROM "+tableName

that'll return all rows, which is first is database dependant (but the same each time)

> but when i do -> "SELECT * FROM "+tableName+" WHERE ImageID='"+imageName+"'"

that only returns a single row (assuming imageID is a key)
0
 
max_dubAuthor Commented:
thanks very much objects

thanks CEHJ also, for helping out earlier
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now