This discussion is archived
7 Replies Latest reply: Feb 2, 2013 2:21 AM by Dimitar Dimitrov RSS

adf table filter

466963 Newbie
Currently Being Moderated
adf filterable table
filter works only on numeric columns ,on text columns "No data...."
  • 1. Re: adf table filter
    Dimitar Dimitrov Expert
    Currently Being Moderated
    You are wrong. I have a couple of applications (in ADF 11.1.1.2, 11.1.1.5 and 11.1.1.6) that feature table filters on text columns and everything works fine.

    What is your ADF version? What values does the text column contain and what criteria do you enter in the filter field?

    Dimitar
  • 2. Re: adf table filter
    466963 Newbie
    Currently Being Moderated
    jdev 11.1.1.6
    table mysql
    fields that don't work varchar
  • 3. Re: adf table filter
    466963 Newbie
    Currently Being Moderated
    jdev 11.1.1.6
    table mysql
    fields that don't work varchar
  • 4. Re: adf table filter
    Dimitar Dimitrov Expert
    Currently Being Moderated
    The information you supplied is not sufficient. Please, give us the following information:

    1. Post the criteria that you enter in the filter field;
    2. Post a short sample of values contained in the text column (that the criteria is applied on);
    3. Do you use any kind of a LOV component (e.g. a <af:selectOneChoice> component) in order to display the text values in the table on the screen?

    Dimitar
  • 5. Re: adf table filter
    466963 Newbie
    Currently Being Moderated
    No LOV components
    EX: field nume java string from mysql varchar(100) .... ION
    I tried ION , "ION",'ION ,like 'ION"...... in criteria , result "NO data to diaplay
    I tried also with other valuers , on ather text fields , nothing.
  • 6. Re: adf table filter
    466963 Newbie
    Currently Being Moderated
    same thing in jdev 11g release 2
  • 7. Re: adf table filter
    Dimitar Dimitrov Expert
    Currently Being Moderated
    I created a simple test application in JDev/ADF 11.1.1.6 and I can confirm the problem you encountered. My test application is very simple. It consists of a single ADF Faces page containing a read-only <af:table> with a filter. The <af:table> is based on an ADF ViewObject on a MySQL 5.5 DB table containing two varchar columns.

    The problem happens when a MySQL DB is used and it is related to the MySQL's SQL syntax. The problem is caused by a wrong WHERE-clause criterion generated by ADF when the user enters some condition in a filter field backed by a DB column of character datatype. ADF generates a WHERE-clause similar to this one:
    WHERE MyTable.col1 LIKE ( ? || '%' )
    This would be a correct WHERE-clause for many SQL-databases, but it is not correct for MySQL, because in MySQL the operator || does not perform a string concatenation but it acts as a logical OR. In this way the expression <tt>( ? || '%' )</tt> is not evaluated to a string but to a boolean value (e.g. 0 or 1), so the operator LIKE fails to filter the rows correctly.

    You can inspect the generated SQL-query and the values of the bind variables yourself by switching ADF diagnostics on (e.g. set -Djbo.debugoutput=console to the runtime configuration of your ViewController project).

    JDeveloper/ADF 11.1.1.6 certification matrix says that MySQL 5.5 is certified for both JDeveloper IDE and ADF, so in my opinion you can submit an official SR if you have a valid support contract which covers Oracle ADF.

    A good workaround would be to implement a custom SQLBuilder or a custom ViewCriteria adapter that patches the problem, but it would require a lot of efforts. Alternatively, you can try to implement a tricky workaround by subclassing the ViewObjectImpl class and overriding some of its query-related methods in order to replace the substring <tt>LIKE ( ? || '%')</tt> with <tt>LIKE CONCAT( ?, '%' )</tt> in the generated SQL query. This is tricky, so I do not recommend you this way either.

    However, if you decide to follow the later alternative, you can override both methods <tt>ViewObjectImpl.buildQuery(...)</tt> in a custom base ViewObjectImpl class (or in a custom ViewObjectImpl class of particular VOs only) as follows:
      @Override
      protected String buildQuery(int noUserParams, boolean forRowCount)
      {
        String query = super.buildQuery(noUserParams, forRowCount);
        if (query!=null) {
          query = query.replace( "LIKE ( ? || '%')", "LIKE CONCAT( ?, '%' )");
        }
        return query;
      }
    
      @Override
      protected String buildQuery(int noUserParams, boolean forRowCount, String selClause, String fromClause, String whereClause, int subQueryLevel)
      {
        String query = super.buildQuery(noUserParams, forRowCount, selClause, fromClause, whereClause, subQueryLevel);
        if (query!=null) {
          query = query.replace( "LIKE ( ? || '%')", "LIKE CONCAT( ?, '%' )");
        }
        return query;
      }
    Dimitar

    Edited by: Dimitar Dimitrov on Feb 2, 2013 12:07 PM

Legend

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