| MySql trouble with SearchForm [message #1131991] |
Thu, 10 October 2013 19:20  |
Eclipse User |
|
|
|
Hi,
I am very new to Scout so please be gentle with me, maybe I did something wrong.
1. I created a MySqlSqlService -> Selected Statments worked fine.
2. I created a SearchForm -> Filter dosen´t
I used the debug Mode an figured out that the SQL Statement has an Error: It has an || for concart strings but mysql needs a concat().
Service Class:
public class MySqlSqlService extends AbstractMySqlSqlService implements IService2 {
@Override
protected String getConfiguredJdbcMappingName() {
return "jdbc:mysql://192.168.1.1:3306/test";
}
@Override
protected String getConfiguredPassword() {
return "#######";
}
@Override
protected String getConfiguredUsername() {
return "#######";
}
}
LOG:
SQL with binds:
SELECT ID,
LASTNAME,
FIRSTNAME,
BIRTHDAY,
DISPLAYNAME
FROM RESIDENTS
WHERE 1 = 1
AND UPPER(FIRSTNAME) LIKE UPPER(:firstName || '%')
IN :firstName => ? [VARCHAR Boris]
SQL PLAIN Log:
SELECT ID,
LASTNAME,
FIRSTNAME,
BIRTHDAY,
DISPLAYNAME
FROM RESIDENTS
WHERE 1 = 1
AND UPPER(FIRSTNAME) LIKE UPPER('Boris' || '%')
Any idea?
Cheers,
Boris
|
|
|
|
|
|
|
| Re: MySql trouble with SearchForm [message #1152805 is a reply to message #1152008] |
Thu, 24 October 2013 03:22  |
| Eclipse User |
|
|
|
I think your problem, is that with a checkbox, there is no graphical difference between Boolean value == null and value == false. This is why the Statement builder treats null and false the same way (0 value in the SQL bind).
Assuming you have a table with an assigned_state column. When the "Assigned" checkbox is checked, you want to add following statement in the where conditions: assigned_state='A'. Here is how you can do it:
setBasicDefinition(MyFormData.Assigned.class, " ((0 = :a) OR (t.assigned_state='A'))",DataModelConstants.OPERATOR_NONE);
This example can be extended. In a lot of case I know we do a sub query to find if the record needs to be included or not.
From a UI point of view, when the user need to have a Yes/No search criteria in a SearchForm, we use radio buttons or a smart field instead of a checkbox because we want to be able to distinguish between "Search criteria not set", "Search criteria == Yes" and "Search criteria == No".
I will try to update the wiki.
I hope it helps.
.
|
|
|
Powered by
FUDForum. Page generated in 0.04129 seconds
Home