Table filtering, aka Query by example, is a great feature of ADF tables. This feature provides filterboxes on top of a column(es).
and filters the data based on the values in the filterbox:
This works like a charm on an Oracle database, but when you use MySQL the filtering doesn’t work and actually doesn’t retrieve any data at all. As you can understand, this is not expected and definitely not wanted behaviour.
The first step in solving this behaviour was to enable logging (either via the weblogic diagnostic console or by adding -Djbo.debugoutput=console to the Java Options in the project properties Run configuration). In the log console we see that the SQL statement for this table collection / ViewObject now includes a where clause that includes the table filter value(s): ‘WHERE ( ( (EmployeesEo.FIRST_NAME LIKE ( :vc_temp_1 || ‘%’) ) ) )‘. This where clause with the % appended to the variable is automatically performed by the ADF framework.
As it turned out, this concatenation of the value with the % (:vc_temp || ‘%’) was the cause of our problem. This concatenation with a double pipe is a SQL92 standard. However with MySQL the concatenation is done with the concat function and not the double pipe. Thus this statement causes an exception somewhere in MySQL, and the result is that no data is returned.
The solution is actually quite easy because you can configure the ‘strictness’ of MySQL to the SQL standard via the SQL mode. In this case: SET sql_mode = ‘PIPES_AS_CONCAT’. This value can also be set in the my.cnf or my.ini files or provided at startup via the: –sql-mode=”PIPES_AS_CONCAT” option. Now the filtering works as expected.
