Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Lookup Call Parameterization
icon4.gif  Lookup Call Parameterization [message #1063100] Wed, 12 June 2013 06:14 Go to next message
marco giudici is currently offline marco giudici
Messages: 121
Registered: February 2013
Senior Member
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 06:53 Go to previous messageGo to next message
Andreas Hoegger is currently offline Andreas Hoegger
Messages: 173
Registered: February 2010
Senior Member
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 #1063142 is a reply to message #1063107] Wed, 12 June 2013 09:04 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 635
Registered: October 2011
Senior Member
There is also a page on the wiki: SqlLookupService

I can add to Andreas answer, that since the LookupCall is used as SQL bind, you can access all the getter of the LookupCall in the sql Statement.

For example ":master" is the SQL statement is a bind for the value returned by call.getMaster().
This can be extended. If you define a Variable in your lookup call:

index.php/fa/15231/0/

This produces a getter and a setter for m_validOn and you will be able to use it in the SQL statement:
public class MyLookupService extends AbstractSqlLookupService implements IMyLookupService {

  @Override
  public String getConfiguredSqlSelect() {
    return "SELECT  C.COMPANY_NR, " +
        "        C.NAME " +
        "FROM    COMPANY C " +
        "WHERE   :validOn BETWEEN C.START_DATE AND C.END_DATE " +
        "<key>   AND     C.COMPANY_NR = :key </key> " +
        "<text>  AND     UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " +
        "<all> </all> ";
  }
}


Here the Code of the LookupCall:
public class MyLookupCall extends LookupCall{

  private static final long serialVersionUID = 1L;
  private Date m_validOn;

  @Override
  protected Class<? extends ILookupService> getConfiguredService() {
    return IMyLookupService.class;
  }

  @FormData
  public Date getValidOn() {
    return m_validOn;
  }

  @FormData
  public void setValidOn(Date validOn) {
    m_validOn = validOn;
  }
}
Re: Lookup Call Parameterization [message #1063424 is a reply to message #1063107] Thu, 13 June 2013 03:15 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 121
Registered: February 2013
Senior Member
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 #1063531 is a reply to message #1063424] Thu, 13 June 2013 07:42 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 635
Registered: October 2011
Senior Member
Wait...

LookupCall is not a Service...
SERVICES.getService(FileOrderListLookupCall.class)

will always return null.

To parametrise your LookupCall on the client, you should consider execPrepareLookup(..). It is described in the wiki: LookupCall > 1.2 Members


With SERVICES.getService(<A LookupService Interface>.class), you can get a LookupService... You can then call getDataByText(..) on this service and pass your lookup call as parameter. But this pattern (looking for a LookupService direclty) is not very common, because often getDataByText(..) is called directly on the LookupCall. If the LookupCall is bound to a LookupService, the call will be delegated to the LookupService.
Re: Lookup Call Parameterization [message #1063543 is a reply to message #1063531] Thu, 13 June 2013 07:54 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 121
Registered: February 2013
Senior Member
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 #1063560 is a reply to message #1063543] Thu, 13 June 2013 08:16 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 635
Registered: October 2011
Senior Member
I my opinion you have 2 solutions:

1. Full SQL
Assuming that you put the checkbox boolean values in:
- m_categoryFilter boolean
- m_typeFilter boolean
And the value of your filter in:
- m_categoryValue String
- m_typeValue String

You can combine AND and OR conditions:
you will get something like this:
WHERE .... 
AND (0 = :categoryFilter OR main_category = :categoryValue)
AND (1 = :categoryFilter OR alt_category = :categoryValue)
AND (0 = :typeFilter OR type = :typeValue)


You let the database engine solve this for you.

If categoryFilter is not checked (== false) you want to match categoryValue with the main_category column.
If categoryFilter is check (== true) you want to match categoryValue with the alt_category column.
and so on...



2. Write your logic in java
If this gets to complicated, you can write your statement in java, depending on the values set in your parameter call.

A possibilty is to do it in execLoadLookupRows(..) method of your LookupService.

But depending on what you want to do you can implement your own LookupService, that directly extends AbstractLookupService, and do some SQL stuff in the different methods. (AbstractSqlLookupService can be used to get inspiration).



I hope this is clear enough.
Re: Lookup Call Parameterization [message #1063600 is a reply to message #1063560] Thu, 13 June 2013 09:28 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 121
Registered: February 2013
Senior Member
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 10:17]

Report message to a moderator

Re: Lookup Call Parameterization [message #1063632 is a reply to message #1063600] Thu, 13 June 2013 10:41 Go to previous message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 635
Registered: October 2011
Senior Member
Using '%:Ref%' directly in your statement can not work, because the statement processor does'nt do "replace by the string value" (see Top 10 JDBC Best Practices for Java Programmer, item #6: Use Bind variables instead of String concatenation).

If you want to concatenate an other string to :ref, you need to do it the way your database engine is expecting it.
This should work with Oracle:
MYCOLUMN LIKE '%' || :Ref || '%'


I remember that if you do MySql you will need to use CONCAT... You can search for "SQL String concatenation" in Google and you will find what is suitable for your db engine.
Previous Topic:Showing all lookupCalls at runtime
Next Topic:How to add more actionable Icons at the icons bar?
Goto Forum:
  


Current Time: Tue Jul 29 04:52:02 EDT 2014

Powered by FUDForum. Page generated in 0.02314 seconds