Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "Scout/Concepts/Sql Lookup Service"

(Properties: SqlSelect property explained more detailed)
m (See Also)
Line 81: Line 81:
  
 
== See Also ==
 
== See Also ==
 +
* {{ScoutLink|Tutorial/Minicrm|Lookup Calls and Lookup Services| Minicrm Tutorial: Lookup Calls and Lookup Services}}
 
* {{ScoutLink|Concepts|Lookup_Service|LookupService}}
 
* {{ScoutLink|Concepts|Lookup_Service|LookupService}}
 
* {{ScoutLink|Concepts|LookupCall|LookupCall}}
 
* {{ScoutLink|Concepts|LookupCall|LookupCall}}
 
* {{ScoutLink|Concepts|LookupRow|LookupRow}}
 
* {{ScoutLink|Concepts|LookupRow|LookupRow}}
 
* {{ScoutLink|Concepts|Server Plug-In|Server Plug-In}}
 
* {{ScoutLink|Concepts|Server Plug-In|Server Plug-In}}

Revision as of 05:33, 7 February 2012

The Scout documentation has been moved to https://eclipsescout.github.io/.

A SQL lookup Service is a specific type of The Scout documentation has been moved to https://eclipsescout.github.io/. that works with a database.


Description

A SQL lookup Service provide a way to implement a The Scout documentation has been moved to https://eclipsescout.github.io/. that is very efficient to configured, if the The Scout documentation has been moved to https://eclipsescout.github.io/. is resolved with a database.

Instead of implementing the 4 methods (getDataByKey(LookupCall call), getDataByText(LookupCall call), getDataByAll(LookupCall call), getDataByRec(LookupCall call)), it is possible to defined the behavior of the lookup service with some configuration properties and events.


Properties

Defined with The Scout documentation has been moved to https://eclipsescout.github.io/. methods.

The Scout documentation has been moved to https://eclipsescout.github.io/.

The property SqlSelect expects a SQL query which is used to load the records for the lookup call.

Here is an example of a such SQL query:

SELECT language_id, name, NULL, NULL, NULL, NULL, NULL, 1, NULL, 1 
FROM LANGUAGE
<key>WHERE language_id = :key</key>
<text>WHERE UPPER(name) LIKE UPPER('%'||:text||'%')</text>

Looking at this implementation we see that for each record returned by our lookup service we provide both a (unique) key and a text, which is a general characteristic of lookup services. Further we see that parts of the SQL statement are enclosed in tags. This is because a lookup can be performed in several ways:

  • Key-Lookup: Single-result lookup based on a unique key (e.g. when loading a form with a smartfield containing a value).
  • Text-Lookup: Multi-result lookup based on a textual search-term (e.g. when entering text into a smartfield).
  • All-Lookup: Unrestricted lookup that returns all available key-text pairs (e.g. when clicking the magnifier button on a smartfield).

Depending on the way the lookup is performed, only one SQL part in tags is used. If for example a Text-Lookup is performed, only the SQL code in the corresponding <text> tag is used, whereas the SQL code in the other tags is ignored.

As you might have noticed, the SQL statement contains two binding variables :key and :text. These bindings are available because the LookupCall itself is bound to the SQL statement. Therefore every public property of the LookupCall can be used as binding variable in the query. (:key for call.getKey(), :text for call.getText(), :master for call.getMaster() ...)

The above example showed a SQL statement with a complete variable where part. In a more complex query you probably have an additional fix where part which you do not want to add in the key, text and all section. If that's the case you should move the WHERE key word out of the tags like in the following example.

public class CompanyLookupService extends AbstractSqlLookupService implements ICompanyLookupService {
 
  @Override
  public String getConfiguredSqlSelect() {
    return "SELECT  C.COMPANY_NR, " +
        "        C.NAME " +
        "FROM    COMPANY C " +
        "WHERE   C.ASSET > 10000 " +
        "<key>   AND     C.COMPANY_NR = :key </key> " +
        "<text>  AND     UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " +
        "<all> </all> ";
  }
}
Note.png
'AbstractSqlLookupService'
In the above section we have shown you how to return key-text pairs in your lookup service implementation. There is nothing wrong about that, but it is also not the whole truth. The AbstractSqlLookupService actually allows you to return additional meta-data that controls how the returned data is presented to the users. You can for example return icon IDs, color codes, tooltip texts or font descriptions. The complete list and order of all columns supported by the AbstractSqlLookupService is as follows:
  • Object key
  • String text
  • String iconId
  • String tooltip
  • String background color
  • String foreground color
  • String font
  • Boolean enabled
  • Object parentKey used in hierarchical structures to point to the parents primary key
  • Boolean active (0,1)


The Scout documentation has been moved to https://eclipsescout.github.io/.

Note.png
TODO
TODO: SortColumn


Events

Defined with The Scout documentation has been moved to https://eclipsescout.github.io/. methods.

The Scout documentation has been moved to https://eclipsescout.github.io/.

Note.png
TODO
TODO: LoadLookupRows


See Also

Back to the top