aspose file tools*
The moose likes JDBC and the fly likes SQL sttatement error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL sttatement error" Watch "SQL sttatement error" New topic
Author

SQL sttatement error

trupti nigam
Ranch Hand

Joined: Jun 21, 2001
Posts: 613
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)
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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

Joined: Jun 21, 2001
Posts: 613
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
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL sttatement error