Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » MySql trouble with SearchForm(SearchForm is not working because of a error in the SQL statment.)
MySql trouble with SearchForm [message #1131991] Thu, 10 October 2013 23:20 Go to next message
Boris NN is currently offline Boris NNFriend
Messages: 3
Registered: October 2013
Junior Member
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 #1132755 is a reply to message #1131991] Fri, 11 October 2013 11:19 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 820
Registered: October 2011
Senior Member
This is stange because the MySqlSqlStyle should take care of this...

Can you tell the Scout version you are using?


How is your statement generated? Can you show the content of your OutlineService that loads this query?
Are you using a statement builder?

.
Re: MySql trouble with SearchForm [message #1150479 is a reply to message #1132755] Tue, 22 October 2013 20:14 Go to previous messageGo to next message
Boris NN is currently offline Boris NNFriend
Messages: 3
Registered: October 2013
Junior Member
I changed my code to a statement builder. It works very well, thank you. But know i have another problem. In my search form there is a checkbox field and now the data is filtered at start up. Is there a workaround?

[Updated on: Tue, 22 October 2013 20:14]

Report message to a moderator

Re: MySql trouble with SearchForm [message #1151030 is a reply to message #1150479] Wed, 23 October 2013 04:59 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 820
Registered: October 2011
Senior Member
Can you provide more information?

What should the checkbox do? Can you post the code corresponding to this CheckBox in your statement builder?

I am not sure what you mean with "workaround".
Re: MySql trouble with SearchForm [message #1152008 is a reply to message #1151030] Wed, 23 October 2013 19:15 Go to previous messageGo to next message
Boris NN is currently offline Boris NNFriend
Messages: 3
Registered: October 2013
Junior Member
Hi,

at the start of the TablePage the CheckBox should have the value "null" for no filtering on that column.

bewohnerStatementBuilder.setBasicDefinition(BewohnerSearchFormData.Abwesend.class, "Abwesend", DataModelConstants.OPERATOR_EQ);


Now I only get that rows where the column value is "false" but I´d like to see all rows - Like no filter is set.

Any idea?

Cheers,
Boris
Re: MySql trouble with SearchForm [message #1152805 is a reply to message #1152008] Thu, 24 October 2013 07:22 Go to previous message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 820
Registered: October 2011
Senior Member
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.

.
Previous Topic:JUnit test for the Scout Search Form
Next Topic:Switch log in user
Goto Forum:
  


Current Time: Mon Dec 22 02:05:29 GMT 2014

Powered by FUDForum. Page generated in 0.02031 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software