2 Replies Latest reply: Sep 9, 2013 4:22 PM by cjones RSS

    Applied ViewCriteria not being added to query

    cjones

      JDeveloper 11.1.2.2

       

      I have a ViewCriteria that is supposedly being applied to a ViewObject, but I am getting a "Attempt to set a parameter name that does not occur in the sql" error.  I know that the ViewCriteria is being applied, but for some reason, the 'criteria' in the ViewCriteria is not being added to the query that is being sent to the database.

       

      Here is the method where the error is occuring:

      public List<SelectItem> getCategorySelectItems()
          {
              categorySelectItems = new ArrayList<SelectItem>();
              
              BindingContainer bindings = ADFUtils.getBindingContainer();
              DCIteratorBinding dcIteratorBinding = (DCIteratorBinding) bindings.get(ITERATOR_NAME_CATEGORY_SITEID_PHASEID);
              MytrainCategoriesVOImpl mytrainCategoriesVO = (MytrainCategoriesVOImpl) dcIteratorBinding.getViewObject();
              
              mytrainCategoriesVO.setbind_SiteId(GuidoUtils.getSelectedSiteId());        
              mytrainCategoriesVO.setbind_PhaseId(selectedPhaseId);
              
              ViewCriteriaManager vcm = mytrainCategoriesVO.getViewCriteriaManager();
              String[] vcs = vcm.getApplyViewCriteriaNames();
              for(String vcname : vcs){
                  logger.finer("Applied ViewCriteria: " + vcname);
              }
              
              mytrainCategoriesVO.executeQuery();
              
              while(mytrainCategoriesVO.hasNext())
              {
                  MytrainCategoriesVORowImpl mytrainCategoriesVORow = (MytrainCategoriesVORowImpl) mytrainCategoriesVO.next();
                  SelectItem newSelectItem = new SelectItem(mytrainCategoriesVORow.getCategoryId(), mytrainCategoriesVORow.getTitle());
                  categorySelectItems.add(newSelectItem);
              }
              
              return categorySelectItems;
          }
      

       

      The debugging piece where I write out the getApplyViewCriteriaNames() shows that both the "site_id" and "phase_id" criteria are being applied, but the SQL query that is sent to the server does not contain the "where phase_id = :bind_PhaseId" section, even though the query DOES contain the "where site_id = :bind_SiteId" section.

       

      Any ideas why it would be applying one ViewCriteria and not the other, even though the ViewCriteriaManager says that both criteria are being applied?

        • 1. Re: Applied ViewCriteria not being added to query
          Timo Hahn

          One problem I seeIs that you do all this in the view controller, whereas you should do this in the model layer. Depending on how the view criteria is build (ignore nulls,  required bind variables) the sql builder omits posts of the where clause.

          What might happen is that one of your bind variables are set to null without your knowledge. To be sure that your variables are set correctly you need to log the message from the executeQuery method. a sample on how to do this can be found here http://wp.me/pcBZk-2d

          Third method dumps the query and it's parameters.

           

          Timo

          • 2. Re: Applied ViewCriteria not being added to query
            cjones

            Looks like the bind variable was being fed a null value (selectedPhaseId in the code above), which was causing the error.

             

            Ahhh the joys of working on someone else's code...