• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL sttatement error

 
Ranch Hand
Posts: 647
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I am trying to execute the following query on oracle Db.
The query doesn't have any errors ,as when I run it outside my java program it runs fine and returns the result.It gives the following message when I try to run it from my java code.
The moment I remove the "upper" word it runs fine.

Can some one please let me know what is the problem.
Because of this error I am stuck and can't test my code further.

please answer to my question.

thanks,
trupti





The query received from Database Query is SELECT upper(CYCLE_CLOSE_FLAG),ACCESSIBLE_FLAG FROM SALES_RPT WHERE AGENCY_NBR = ? AND RPT_PERIOD_END_DATE = ?
05646222
2004-07-04
The result set is:weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResul
tSetImpl@8b
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava:6240)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:1557)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:148
2)
at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImp
l.getString(Unknown Source)
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Trupti Nigam,

The problem is that you're using a scalar function that is specific to Oracle, i.e. JDBC does not recognise it the way you're using it and is interpreting it as a column.

Try rewriting your query string to begin as follows:

That would work with most JDBC drivers. Unfortunately my reference source, which is a couple of years old now, suggests that Oracle drivers don't support that syntax.

Failing that, check out the documentation for your Oracle JDBC driver and see what it says about using scalar functions.

Jules
 
trupti nigam
Ranch Hand
Posts: 647
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Julian,
I think the problem is not with the query.As I get
the error at the following line.

if(rs.next())
{
System.out.println("Inside the rs.next");
salesReportTransferVO.accessible = rs.getStringDBConstants.SALES_RPT_ACCESSIBLE_FLAG);

salesReportTransferVO.cycleCloseFlag = rs.getString (DBConstants.SALES_RPT_CYCLE_CLOSE_FLAG);
}

Thanks,
trupti
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hmmm, good point. Didn't read your stack trace properly.

The problem is that, now you've applied the UPPER function to the column, the name of the column in the ResultSetMetadata will be different to that defined in your constant.

You can either use rs.getString(1) or find out what the modified name of the (now computed) column is from the ResultSetMetadata (it'll have UPPER in it somewhere; maybe UPPER(CYCLE_CLOSE_FLAG) or UPPER_CYCLE_CLOSE_FLAG at a guess) and adjust the column name string accordingly.

Hope that helps.

Jules
 
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Trupti,

Use the Oracle AS keyword to give an alias for your column.

So if you have a query like

SELECT UPPER(name) FROM employee

and then use

rs.getString("name") , it will give you an 'invalid column' error.

Change the query to

SELECT UPPER(name) AS name FROM employee .

If you run the query from some kind of Oracle client, by looking at the column header you can tell the 'column name' the data is being returned under.

HTH
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic