This discussion is archived
7 Replies Latest reply: Aug 1, 2013 6:17 AM by 1004003 RSS

How to do SQL Based Validation in Extended EO?

1004003 Newbie
Currently Being Moderated

Hi,

I am new to OAF. I have a requirement on an OAF Page that when a new record is created system should check if the same Value already exists in one of our custom tables then it should raise a warning.

I guess I will have to code this logic in extended EO. I checked the Framework toolbox tutorial examples in which they are using VVO's and Entity experts to do the SQL Based Validation but how can I do it in extended EO?

 

Can we make direct SQL calls in EO?

 

Can someone help please?

  • 1. Re: How to do SQL Based Validation in Extended EO?
    keerthioaf Expert
    Currently Being Moderated

         Hey hi there ,

     

        On click of a button you can get handle of the submit action and use the below code to validate if the record already exist

     

      String codeUpper = pageContext.getParameter("Code"); // get your field values

        String actionUpper = pageContext.getParameter("Action");

        String seqNoUpper=pageContext.getParameter("PacelineStdSeqNum");

        if(pageContext.getParameter("Save") !=null)

        {

         //from page context obtain the value of code and action . 

        String codeValue = pageContext.getParameter("Code");

        String actionValue = pageContext.getParameter("Action");

        String stdSeqNumValue = pageContext.getParameter("PacelineStdSeqNum");

         //declare the variable to count the number of records returned by the query .

         int result =0;

         //obtain the JDBC Connection .

         Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();  

          try 

    {

         //Query to check the combination for records.

         String Query = "SELECT count(*) count from SDA_PACELINE_STD where CODE=:1 AND ACTION=:2";

         PreparedStatement stmt = conn.prepareStatement(Query);

         stmt.setString(1, codeValue);

         stmt.setString(2, actionValue);

         for(ResultSet resultset = stmt.executeQuery(); resultset.next();)  

      {

         pageContext.writeDiagnostics(this,"Query Executed",1);  

         pageContext.writeDiagnostics(this,"Query Executed",2);

         result = resultset.getInt("count"); 

         pageContext.writeDiagnostics(this, "Query Executed"+ result, 1);

         pageContext.writeDiagnostics(this, "Query Executed"+ result, 2);

       

      }

    }

    catch(Exception exception)  

     

    {

    //Incase of any error while running the query throw an exception.

    throw new OAException("Error in Staffing Query"+exception, OAException.ERROR);  

    }  

     

     

    if(result >0)   // result > 0 suggest that record combination already exists

    {

     

     

    throw new OAException("The Record Combination Already Exists", OAException.INFORMATION);  

    }

     

     

      Let me know if its not clear .

     

    --Keerthi

  • 2. Re: How to do SQL Based Validation in Extended EO?
    anakkala Newbie
    Currently Being Moderated

    Hi Keerthi - I have similar requirement. When i did the same as you suggested, I get Illegal Acces error java.lang.IllegalAccessError oracle/jdbc/driver/OraclePreparedStatement

     

    is this code to execute SQL queries can be excecuted from Controller or this should be placed in AM?

  • 3. Re: How to do SQL Based Validation in Extended EO?
    keerthioaf Expert
    Currently Being Moderated

      Hi there ,

     

      Well , as per MVC design pattern standard it should be done in AMImplementation.java , but you can import the libraries in the controller

    and you can perform sql query execution .

     

    import oracle.apps.fnd.framework.OAApplicationModule;

    import oracle.apps.fnd.framework.OAException;

    import java.sql.*;

    import java.sql.PreparedStatement;

     

     

    Regards ,

    Keerthi

  • 4. Re: How to do SQL Based Validation in Extended EO?
    PreethamChandra Journeyer
    Currently Being Moderated

    This happens in case you are running this on R12.

     

    Use java.sql.PreparedStatement instead of OraclePreparedStatement.


    Here is how to use


    http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/


    Java JDBC Programming Examples - Use of prepared statement

  • 5. Re: How to do SQL Based Validation in Extended EO?
    anakkala Newbie
    Currently Being Moderated

    it worked.. thanks keerthi and Preetham

  • 6. Re: How to do SQL Based Validation in Extended EO?
    keerthioaf Expert
    Currently Being Moderated

         Hi there ,

     

         Glad to know it worked , its always good practice to close a thread if you got the issue fixed.

     

        Regards ,

         Keerthi

  • 7. Re: How to do SQL Based Validation in Extended EO?
    1004003 Newbie
    Currently Being Moderated

    Thanks Kreeethi, it solved my purpose too.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points