| TablePageData and SearchForm [message #1413318] |
Thu, 28 August 2014 05:10  |
Eclipse User |
|
|
|
Hi,
I am testing the cool new feature TablePageData. Without a searchform it is working fine. But let's say there is a TablePage with 3 Columns: IDColumn, NameColumn and TownColumn.
The corresponding SearchForm has two fields: NameField and TownField.
Without TablePageData the relevant lines in the OutlineService are:
public Object[][] getUserData(UserSearchForm formData){
StringBuilder statement = new StringBuilder();
statement.append(""
+"SELECT "
+" id, "
+" name, "
+" town "
+"FROM "
+" users "
+"WHERE 1=1 ");
if (!StringUtility.isNullOrEmpty(formData.getName().getValue())){
statement.append(" AND name like :name ");
}
if (!StringUtility.isNullOrEmpty(formData.getTown().getValue())){
statement.append(" AND town like :town ");
}
return SQL.select(statement.toString(), formData);
}
With the TablePageData the lines are:
public UserTablePageData getUserData(UserSearchForm formData){
UserTablePageData = new UserTablePageData();
StringBuilder statement = new StringBuilder();
statement.append(""
+"SELECT "
+" id, "
+" name, "
+" town "
+"FROM "
+" users "
+"WHERE 1=1 ");
if (!StringUtility.isNullOrEmpty(formData.getName().getValue())){
statement.append(" AND name like :name ");
}
if (!StringUtility.isNullOrEmpty(formData.getTown().getValue())){
statement.append(" AND town like :town ");
}
statement.append(""
+"INTO "
+" :{id} "
+" :{name}, "
+" :{town}");
SQL.selectInto(statement.toString(), formData, tablePageData);
return tablePageData;
}
But now I get a ProcessingException: expected a single value for ":{name}" but got multiple values.
Obviously the problem occurs because the field in the SearchForm an the column in the TablePageData have the same name.
It gets strange when I change the order of the bind bases:
SQL.selectInto(statement.toString(), tablePageData, formData);
I get all table rows and no error message if I leave the search fields empty. And so I thought this may be a workaround. But if I fill the name or the town field with a search string that should lead to some rows I get an empty (no rows) TablePageData. The strange thing is, that the debug log doesn't even show a sql query. So there is an empty TablePageData and no error message indicating that something has gone wrong.
Is there something I can do without changing the column or the search field names?
Kind regards,
Nils
|
|
|
|
|
|
| Re: TablePageData and SearchForm [message #1413466 is a reply to message #1413427] |
Thu, 28 August 2014 11:43  |
| Eclipse User |
|
|
|
Notice that in my example the name of the bind is absolutely free. To avoid the confusion with the OUT mentioned by Dennis, I have changed it to table.
Thanks a lot for the update of the documentation. It is nice to see contribution in this area.
This problem of 2 binds having conflicting names occurs in your case with a formData and a tablePageData, but I could imagine a lot of other situation where it could be a problem.
Example: reading from 2 formData in the same query select query for example.
PersonFormData with a property "id"
CompanyFormData with a property also called "id"
Select ... from ... where COMPANY_NR = :id /*from person formData*/ and PERSON_NR = :id /*from company formData*/
You should use the same trick:
SQL.select(query, new NVPair("company", companyFormData ), new NVPair("person", personFormData))
and in the query:
Select ... from ... where COMPANY_NR = :company.id and PERSON_NR = :person.id /*from company formData*/
I think the trick with 2 NVPair will also work for those cases.
I have referenced this forum thread as "TODO" in Sql Service > advanced binding
.
|
|
|
Powered by
FUDForum. Page generated in 0.07053 seconds
Home