4 Replies Latest reply: Mar 3, 2014 10:12 AM by rukbat RSS

    java.sql.SQLException: Invalid column name when calling sql

    Tshifhiwa

      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)