Page History
...
When a user types a value into a text field (which is bound to a specific column of the database table) and clicks out, the Servoy Application Server issues a SQL update command to the database to modify the selected record. The resulting change is also broadcast|display/DOCS/Data+Broadcasting|||||\ to all connected clients.
...
The fundamental unit of data binding in both the GUI and the API is the Servoy Foundset|display/DOCS/Foundsets+Concepts|||||\ object.
Client Cache
A Servoy client instance keeps track of which database records are in use. This is called the Client Cache and it optimizes performance by reducing the number of queries made to the database. Records are tracked by primary key. The first time the contents of a record are accessed, the Application Server must issue a query to the database on behalf of the client. The values for all of the columns of the record object are held in memory and therefore, subsequent access of the record will not produce anymore queries to the database. The user experience is greatly enhanced as one can browse quickly between forms and among cached records.
A record may fall out of the cache gracefully to conserve memory and is automatically reloaded the next time it is accessed. This happens at the discretion of the client's caching engine, which is highly optimized. Relieved of the burden of managing memory, the developer can focus on more important things.
...
A Foundset may be automatically updated when the client receives a Data Broadcast Event. If the data change affected the table to which the foundset is bound, the foundset will be refreshed to reflect the change.
Find Mode is a special mode that can be assumed by a foundset object to perform data searches using a powerful, high-level abstraction. When in Find Mode, the foundset's Data Providers, normally used to read/write data, are instead used to enter search criteria. Any data provider can be assigned a search condition which should evaluate to a String, Number or Date. Because forms typically bind to a foundset, criteria may be entered from the GUI by the user or programmatically.
A foundset enters Find Mode when its find method is invoked. This method returns a Boolean, because under certain circumstances, the foundset may fail to enter find mode. Therefore, it is good practice to enclose a find in an an if statement, so as not to accidentally modify the selected record. A foundset exits Find Mode when its search method is executed, upon which the foundset's SQL query is modified to reflect the expressed criteria and the matching records are loaded. The search method returns an integer, which is the number of records loaded by the find. However this doesn't necessarily represent the total number of matching records as foundset records are loaded in blocks.
Example:
Code Block |
---|
// Find all customers in the city of Berlin
if(foundset.find()){ // Enter find mode
city = 'Berlin'; // Assign a search criteria
foundset.search(); // Execute the query and load the records
}
|
Results in the foundset's SQL query:
Code Block |
---|
SELECT customerid FROM customers WHERE city = ? ORDER BY customerid ASC
|
...
Search Criteria with Logical AND
...
When multiple search criteria are entered for multiple data providers, the criteria will be concatenated with a SQL AND operator.
Example:
Code Block |
---|
// Find all customers in the city of Berlin AND in the postal code 12209
if(foundset.find()){ // Enter find mode city = 'Berlin'; // Assign city search criterion
postalcode = '12209' // Assign postal code criterion
foundset.search(); // Execute the query and load the records
}
|
Results in the foundset's SQL query:
Code Block |
---|
SELECT customerid FROM customers WHERE city = ? AND postalcode = ? ORDER BY customerid ASC
|
...
Multiple Find Records for Logical OR
...
It's important to note that when in Find Mode, a foundset will initially contain one record object. However, multiple record objects may be used to articulate search criteria. This has the effect that the criteria described in each record are concatenated by a SQL OR.
Example:
Code Block |
---|
// Find customers in the city of Berlin AND in the postal code 12209...
// OR customers in the city of San Francisco AND in the postal code 94117
if(foundset.find()){ // Enter find mode city = 'Berlin';
postalcode = '12209';
foundset.newRecord(); // Create a new search record
city = 'San Francisco'
postalcode = '94117';
foundset.search(); // Execute the query and load the records
}
|
Results in the foundset's SQL query:
Code Block |
---|
SELECT customerid FROM customers WHERE (city = ? AND postalcode = ?) OR (city = ? AND postalcode = ?) ORDER BY customerid ASC
|
...
Finding records through a relation
...
Find Mode is very flexible as searches can traverse the entire data model. When a foundset enters find mode, any foundset related to a search record can be used to enter criteria. Moreover, related foundsets can use multiple search records so any permutation of Logical AND / OR is possible.
Example:
Code Block |
---|
// Find customers that have 1 or more orders which were shipped to Argentina
if(foundset.find()){ // Enter find mode
customers_to_orders.shipcountry = 'Argentina'; // enter criteria in a related foundset
foundset.search(); // Execute the query and load the records
}
|
Results in the foundset's SQL query:
Code Block |
---|
SELECT DISTINCT customers.customerid FROM customers
LEFT OUTER JOIN orders ON customers.customerid=orders.customerid
WHERE orders.shipcountry = ? ORDER BY customers.customerid ASC
|
And there are no limitations to the number of traversals across related foundsets.
Example:
Code Block |
---|
// Find customers with one or more orders containing one or more products supplied by a vendor in USA
if(foundset.find()){
customers_to_orders.orders_to_order_details.order_details_to_products.products_to_suppliers.country = 'USA';
foundset.search();
}
|
...
Finding records within a related foundset
...
It is worth pointing out that related foundsets may be put into Find Mode as well. The foundset will maintain the constraints imposed by the relation in addition to the criteria specified in the data providers.
Example: This operation is nearly identical to the previous search on ship country, however it matters which foundset is in Find Mode. The difference is that this operation searches for order records of a particular customer.
Code Block |
---|
// Find orders of THE SELECTED CUSTOMER that were shipped to Argentina
if(customers_to_orders.find()){
customers_to_orders.shipcountry = 'Argentina';
customers_to_orders.search();
}
|
Results in the foundset's SQL query (notice the relation constraint is preserved):
Code Block |
---|
SELECT orderid FROM orders WHERE customerid = ? AND shipcountry = ? ORDER BY orderid ASC
|
...
Special Operators
...
Servoy's Find Mode provides several special operators that when used in combination can articulate the most sophisticated search requirements. Operators and operands should be concatenated as strings.
Operator | Description | Applicable Data Types | Example | ||
---|---|---|---|---|---|
|| | OR: Used to implement a logical OR for two or more search conditions in the same data provider | Any |
| ||
| | Format: Used to separate a value and an implied format. | Date |
| ||
! | Not: Used to implement a logical NOT for a search condition. | Any |
| ||
# | Sensitivity Modifier: Implies a case-insensitive search for text columns. Implies a match on entire day for date columns. | Text, Date |
| ||
^ | Is Null: Matches records where a column is not null. | Any |
| ||
^= | Is Null/Empty/Zero: Matches records where a column is null, empty string value or zero numeric value | Text, Numeric |
| ||
< | Less than: Matches records where the column is less than the operand | Any |
| ||
<= | Less than or equal to: Matches records where the column is less than or equals the operand | Any |
| ||
>= | Greater than or equal to: Matches records where the column is greater than or equals the operand | Any |
| ||
> | Greater than: Matches records where the column is greater than the operand | Any |
| ||
... | Between: Matches records where the column is between (inclusive) the left and right operands. | Any |
| ||
% | Wild Card String: Matches records based on matching characters and wild cards | Text |
| ||
_ | Wild Card Character: Matches records based on | Text |
| ||
\ | Escape Character: Used to escape other string operators | Text |
| ||
now | Now: Matches records where the condition is right now, including time | Date |
| ||
today | Today: Matches records where the condition is any time today | Date |
|
...
Complex Searches
...
Servoy's find mode can be used to easily satisfy even complex search requirements. Remember that any related foundset may be used to enter criteria and that any number of search records may be used in any foundset and any operators may be used in combination for every data provider.
Panel | ||
---|---|---|
| ||
I remember I was thinking that there were some gray areas when it comes to datasets and foundsets, and generally how Servoy is generating the SQL from these depending on relations etc. I noticed that there were often questions about that in the forum, and no definitive answers of course (but at least pointers to what approach gives you what), and in my company too, people are coming to me quite often with some of the following questions:
Seems like a lot of topics in one, really, but of course not necessarily all of these topics need to be addressed, or addressed at the same time, but I really think that these are things that need more explanations/clarifications/demonstrations. |
...