0 
0 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.
Related posts:
Generate 'Delete with Confirmation' in JHeadstart applications Customizing BLAF for ADF Faces – easier than Skins for influencing colors and fonts ADF BC – Fun with Bind Parameters Part Four – Choosing between various Pre-Canned Queries by leveraging the View Link bind-parameter ADF 11g PanelDashboard; configure your own dashboard 
Automatic deployment .ear file to Oracle Application Server 10.1.3 (OAS) with Ant tasks
