Lookup Call Parameterization [message #1063100] |
Wed, 12 June 2013 10:14 |
|
Hi All,
I need to configure my lookup call with 4 different parameters. In base of the value of this parameters I need to insert a specific where clause.
In my project, the user can activate or not these four filter and if one is active I need to insert relative where clause.
Is this possible?
I already create variable on Lookup Call in shared section, but I have problem to manage correctly these variable in Lookup Service in the method "getConfiguredSqlSelect".
Thanks in advance for any help and explanation
|
|
|
Re: Lookup Call Parameterization [message #1063107 is a reply to message #1063100] |
Wed, 12 June 2013 10:53 |
Eclipse User |
|
|
|
Hi Marco
Since the AbstractSqlLookupService accesses the getConfiguredSqlSelect for each request. You are able to parametrize your SQL in the mentioned method.
E.g.
@Override
protected String getConfiguredSqlSelect() {
StringBuilder statementBuilder = new StringBuilder("SELECT l.language_id, l.name ");
statementBuilder.append("FROM languages l, user_language ul ");
statementBuilder.append("WHERE ul.language_id = l.language_id ");
statementBuilder.append("AND ul_spoken_language IN (");
boolean first = true;
for (String sl : SERVICES.getService(IUserService.class).getSpokenLanguages()) {
if (first) {
first = false;
}
else {
statementBuilder.append(", ");
}
statementBuilder.append("'").append(sl).append("'");
}
statementBuilder.append(")");
statementBuilder.append("<key>AND l.language_id = :key</key>");
statementBuilder.append("<text>AND upper(l.name) LIKE upper('%'||:text||'%')</text>");
return statementBuilder.toString();
}
Hope this helps.
-andreas
|
|
|
|
Re: Lookup Call Parameterization [message #1063424 is a reply to message #1063107] |
Thu, 13 June 2013 07:15 |
|
Hi Andreas,
your post is very usefull and I follow it.
Now I have one problem because I set the parameters with a Lookup Call in Shared section and in the method "getConfiguredSqlSelect" see every time this Lookup Call equals to null. I'm sure that his my mistake but I don't understand where is this mistake.
I try to retrieve my Lookup Call in Lookup Service in this way:
SERVICES.getService(FileOrderListLookupCall.class)
|
|
|
|
Re: Lookup Call Parameterization [message #1063543 is a reply to message #1063531] |
Thu, 13 June 2013 11:54 |
|
Ok, I understand my mistake, but my problem remains open.
I try to clarify:
the user can combine four different filter and two of these depends on a checkbox. I save in "Lookup calls" with execPrepareLookup all value of this filter and also the status of the checkbox.
With this information, I need to build the right query; where the "SELECT" and "FROM" clause are fixed and the "WHERE" clause has one part fixed and one part that depends on the selection that the user made.
How can I implement this thing? Have you any suggestion?
Thanks in advance for your help
|
|
|
|
Re: Lookup Call Parameterization [message #1063600 is a reply to message #1063560] |
Thu, 13 June 2013 13:28 |
|
Thank you Jeremie for your tips. I followed solution 1 and I solved my problem.
Now I have another little question: one of this filter is a string that it's necessary search in a specific column of the table. In this case I need to use the command "LIKE" in this way
MYCOLUMN LIKE '%<string>%'
As can be done with the solution 1?
I try in two different ways but none worked.
First way
+ " And (1 = :UseRef OR MYCOLUMN LIKE '%:Ref%')"
+ " And (0 = :UseRef OR MYCOLUMN LIKE '%')"
Second way
Ref="'%kkkk%'";
...
+ " And (1 = :UseRef OR MYCOLUMN LIKE :Ref)"
+ " And (0 = :UseRef OR MYCOLUMN LIKE '%')"
Have you any suggestions?
Thanks again
[Updated on: Thu, 13 June 2013 14:17] Report message to a moderator
|
|
|
|
Powered by
FUDForum. Page generated in 0.04590 seconds