hi i have dynamic sql am using to call sql in my java code,my problem is when i call second column i get error
this is my code
public synchronized String getValueFromTableDate(String sSchema, String sTable, String sColumn1,String sValue1,String sReturn,String sColumn3,String sColumn4,String sColumn5)throws SQLException
{
Connection conn = getConnection(sSchema);
String s_returned= "";
PreparedStatement ps_getValue;
String s_getValue = "SELECT NVL(MAX("+sReturn+"),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM "+sTable+" WHERE "+sColumn1+" = ? AND "+sColumn3+" IS NOT NULL AND SUB_CATEGORY_FLOW = '"+sColumn4+"' AND SUB_CATEGORY_BOP_VERSION = '"+sColumn5+"'" ;
System.out.println("column table selected passed "+sReturn);
System.out.println("tablename passed "+sTable);
System.out.println("column1 name passed "+sColumn1);
System.out.println("column3 name passed "+sColumn3);
System.out.println("column4 name passed "+sColumn4);
System.out.println("column5 name passed "+sColumn5);
System.out.println("queryResults******* "+s_getValue);
ResultSet rs ;
if(conn != null)
{
try
{
ps_getValue = conn.prepareStatement(s_getValue);
ps_getValue.setString(1, sValue1);
//ps_getValue.setString(2, sValue1);
rs = ps_getValue.executeQuery();
System.out.println("queryResultsAfter Query******* "+rs);
while(rs.next())
{
s_returned = rs.getString(sReturn);
}
}catch(SQLException sqe)
{
logger.log(Level.FATAL, "Errror found retrieving a value ",sqe);
throw new SQLException(sqe);
}finally
{
try
{
conn.close();
// rs.close();
// ps_getValue.close();
}catch(SQLException sqe)
{
logger.log(Level.FATAL, "Errror found closing statement and resultset",sqe);
throw new SQLException(sqe);
}
}
}
System.out.println("FinalqueryResultsAfter Query*******"+s_returned);
return s_returned;
}
is there a way i can pass value based on the value passed like decode
my rsults is
column table selected passed SUB_CATEGORY_DATE_ACTIVE
tablename passed SUB_CATEGORY
column1 name passed SUB_CATEGORY_CAT_CODE
column3 name passed SUB_CATEGORY_DATE_INACTIVE
column4 name passed OUT
column5 name passed 3
queryResults******* SELECT NVL(MAX(SUB_CATEGORY_DATE_ACTIVE),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'
queryResultsAfter Query******* com.mchange.v2.c3p0.impl.NewProxyResultSet@1d4c265
03 Mar 2014 09:54:57,285[236] - [091] DEBUG com.mchange.v2.async.ThreadPoolAsynchronousRunner - com.mchange.v2.async.ThreadPoolAsynchronousRunner@1ac2f9c: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@12679ed
03 Mar 2014 09:54:57,285[1747] - [091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@106082 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1786ce)
FinalqueryResultsAfter Query*******2014-03-03 09:54:57 i get first value but is wrong is not value in database
03 Mar 2014 09:54:57,286[1747] - [091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@106082 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1786ce)
column table selected passed SUB_CATEGORY_DATE_INACTIVE
tablename passed SUB_CATEGORY
column1 name passed SUB_CATEGORY_CAT_CODE
column3 name passed SUB_CATEGORY_DATE_INACTIVE
column4 name passed OUT
column5 name passed 3
queryResults******* SELECT NVL(MAX(SUB_CATEGORY_DATE_INACTIVE),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'
queryResultsAfter Query******* com.mchange.v2.c3p0.impl.NewProxyResultSet@f3f73a
03 Mar 2014 09:54:57,297[490] - [091] DEBUG com.mchange.v2.c3p0.impl.NewPooledConnection - com.mchange.v2.c3p0.impl.NewPooledConnection@937e07 handling a throwable.
java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3326)