Eclipse Community Forums: Eclipse Scout » Lookup Call Parameterization

Home

Home » Eclipse Projects » Eclipse Scout » Lookup Call Parameterization

Lookup Call Parameterization [message #1063100]

Wed, 12 June 2013 06:14

Eclipse User

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

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 #1063142 is a reply to message #1063107]

Wed, 12 June 2013 09:04

Eclipse User

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;
  }
}

Attachment: MyLookupCall.png
(Size: 3.79KB, Downloaded 975 times)

Re: Lookup Call Parameterization [message #1063424 is a reply to message #1063107]

Thu, 13 June 2013 03:15

Eclipse User

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

Eclipse User

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

Eclipse User

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

Eclipse User

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

Eclipse User

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] by Moderator

Re: Lookup Call Parameterization [message #1063632 is a reply to message #1063600]

Thu, 13 June 2013 10:41

Eclipse User

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:

-=] Back to Top [=-

Current Time: Wed Jul 23 12:51:27 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter