7 Replies Latest reply: Feb 2, 2013 4:21 AM by Dimitar Dimitrov RSS

    adf table filter

    466963
      adf filterable table
      filter works only on numeric columns ,on text columns "No data...."
        • 1. Re: adf table filter
          Dimitar Dimitrov
          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
            jdev 11.1.1.6
            table mysql
            fields that don't work varchar
            • 3. Re: adf table filter
              466963
              jdev 11.1.1.6
              table mysql
              fields that don't work varchar
              • 4. Re: adf table filter
                Dimitar Dimitrov
                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
                  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
                    same thing in jdev 11g release 2
                    • 7. Re: adf table filter
                      Dimitar Dimitrov
                      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