Jump to: navigation, search

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

m (Category changed)
(Properties)
 
(3 intermediate revisions by 2 users not shown)
Line 18: Line 18:
 
{{ScoutProp|SqlSelect}}
 
{{ScoutProp|SqlSelect}}
  
{{note|TODO|SqlSelect: what should be the result}}
+
The property SqlSelect expects a SQL query which is used to load the records for the lookup call.
  
It is possible to add some special tags, that define SQL code that is kept, depending on the <code>getDataBy***(LookupCall call)</code> method called on the Lookup Service:
+
Here is an example of a such SQL query:
* <code><key></code> and <code></key></code>
+
* <code><text></code> and <code></text></code>
+
* <code><all></code> and <code></all></code>
+
* <code><rec></code> and <code></rec></code>
+
 
+
The <code>call</code> is attached to the SQL query as binding. Therefore all getters on the call, are available in the query. (<code>:key</code> for <code>call.getKey()</code>, <code>:text</code> for <code>call.getText()</code>, <code>:master</code> for <code>call.getMaster()</code> ...)
+
 
+
Here an example of a such SQL query:
+
 
<source lang="sql">
 
<source lang="sql">
 
select language_id, name, null, null, null, null, null, 1, null, 1  
 
select language_id, name, null, null, null, null, null, 1, null, 1  
from language
+
from languages
 
<key>where language_id = :key</key>
 
<key>where language_id = :key</key>
 
<text>where upper(name) like upper('%'||:text||'%')</text>
 
<text>where upper(name) like upper('%'||:text||'%')</text>
 +
</source>
 +
 +
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 <code>LookupCall</code> itself is bound to the SQL statement. Therefore every public property of the <code>LookupCall</code> can be used as binding variable in the query. (<code>:key</code> for <code>call.getKey()</code>, <code>:text</code> for <code>call.getText()</code>, <code>:master</code> for <code>call.getMaster()</code> ...)
 +
 +
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.
 +
 +
<source lang="java">
 +
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> ";
 +
  }
 +
}
 +
</source>
 +
 +
{{note|'''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)
 +
}}
 +
 +
When the SQL Statement is executed, the Lookup-call is passed as bind-variable. You can access every properties of your call (see {{ScoutLink|Concepts|LookupCall#Members|lookupCall members}}):
 +
 +
<source lang="java">
 +
public String getConfiguredSqlSelect() {
 +
  return "language_id, name, null, null, null, null, null, 1, null, 1 " +
 +
      " from languages" +
 +
      " <key>where language_id = :key</key>" +
 +
      " <text>where upper(name) like upper('%'||:text||'%')</text> " +
 +
      " and nvl(start_date, to_date('19000101', 'yyyymmdd')) < NVL(:validityTo, to_date('99990101', 'yyyymmdd')) " +
 +
      " and nvl(end_date, to_date('99990101', 'yyyymmdd')) > NVL(:validityFrom, to_date('19000101', 'yyyymmdd')) ";
 +
}
 
</source>
 
</source>
  
Line 49: Line 95:
  
 
== 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}}

Latest revision as of 15:52, 26 February 2013


Scout
Wiki Home
Website
DownloadGit
Community
ForumsBlogTwitter
Bugzilla
Bugzilla


A SQL lookup Service is a specific type of Lookup Service that works with a database.

  • implements: I obj.pngILookupService
  • extends: C obj.pngAbstractSqlLookupService


Description

A SQL lookup Service provide a way to implement a Lookup Service that is very efficient to configured, if the call 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 getConfiguredXxxxxx() methods.

SqlSelect

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 languages
<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)


When the SQL Statement is executed, the Lookup-call is passed as bind-variable. You can access every properties of your call (see lookupCall members):

public String getConfiguredSqlSelect() {
  return "language_id, name, null, null, null, null, null, 1, null, 1 " +
      " from languages" +
      " <key>where language_id = :key</key>" +
      " <text>where upper(name) like upper('%'||:text||'%')</text> " +
      " and nvl(start_date, to_date('19000101', 'yyyymmdd')) < NVL(:validityTo, to_date('99990101', 'yyyymmdd')) " +
      " and nvl(end_date, to_date('99990101', 'yyyymmdd')) > NVL(:validityFrom, to_date('19000101', 'yyyymmdd')) ";
}


SortColumn

Note.png
TODO
TODO: SortColumn


Events

Defined with execXxxxxx() methods.

LoadLookupRows

Note.png
TODO
TODO: LoadLookupRows


See Also