ADF table filtering on MySQL is failing

Aino Andriessen
0 0
Read Time:1 Minute, 26 Second

Table filtering, aka Query by example, is a great feature of ADF tables. This feature provides filterboxes on top of a column(es).

Image

and filters the data based on the values in the filterbox:

Image

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.

About Post Author

Aino Andriessen

Aino Andriessen is principal consultant and expertise lead 'Continuous Delivery'. His focus is on Oracle Fusion Middleware ADF and SOA development, Continuous Delivery, architecture, improving the software development proces and quality management. He is a frequent presenter at Oracle Open World, ODTUG Kaleidoscope, UKOUG Technology Conference and OUGN Vårseminar. He writes articles and publishes at the AMIS technology blog (http://technology.amis.nl/blog/).
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Next Post

How to install feature pack 4 for the BPM suite

For the BPM people (and other interested people): Oracle has released feature pack 4 of the oracle BPM suite. It contains lots of nice new features, but i found it difficult to install using the accompanying release notes. Luckily Niall Commiskey thought the same, found out how it should be […]
%d bloggers like this: