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 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.

...

Perhaps the most intuitive approach is to programmatically change the foundset's selected index property. 

Example: The example below iterates over the entire foundset using a for loop.

for(var i = 1; i <= foundset.getSize(); i++){
	foundset.setSelectedIndex(i);
	// operate on the selected record
}

Directly Accessing a Record Object

Accessing Data Provider Values as an Arrayapproach is to programmatically change the foundset's selected index property. 

Example: The example below iterates over the entire foundset using a for loop. 

for(var i = 1; i <= foundset.getSize(); i++){
	foundset.setSelectedIndex(i);
	// operate on the selected record
}

Accessing a Record Object

While setting the selected index of the foundset is sometimes necessary, it also contains some overhead and therefore is not always the most efficient way to iterate over a foundset. However, one can iterate in a similar manner, access a record object without changing the selected index of a foundset by using the getRecord method of the foundset.

Example This example iterates over the foundset, but does not affect the selected index. The performance will be better than the previous example, and will not have any side effects in the UI if the foundset is bound to a form.

for(var i = 1; i <= foundset.getSize(); i++){
	var rec = foundset.getRecord(i);	// does not affect the selected index
}

Accessing Data Provider Values as an Array

Sometimes the purpose of iterating over a foundset is to access all of the values for a particular data provider. The most efficient way to do this is to obtain an array of values for the foundset's data provider using the getFoundSetDataProviderAsArray method of the databaseManager API.

Example This example shows how to access all the values in a foundset for a single data provider. Iterating over a simple array offers better performance over normal foundset iteration.

var ids = databaseManager.getFoundSetDataProviderAsArray(foundset,'order_id');
for(i in ids){
    var id = ids[i];
}

Related Foundsets

Foundsets are often constrained or filtered by a Relation. In this situation, the foundset is said to be a Related Foundset and its default SQL query will include in its Where Clause, the parameters by which to constrain the foundset.

...

*Example *This is a simple example which filters records in a products table based on the criterion that the status is not discontinued

var success = databaseManager.addTableFilterParam('crm_server','products','product_status','!=',globals.STATUS_DISCONTINUED,'productfilter');

Example This example shows a two filters using the IN operator

// Filter products within an array of product codes
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200]);


// Filter orders using a subselect
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"')

Example This example shows how to filter an entire server connection by passing <null> for the table name. This is ideal for multi-tenant architectures

// all tables that have the companyid column should be filtered
var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', currentcompanyid)

...