Page History

...

Fortunately, the Servoy Application Server issues a Data Broadcast Event to all clients whenever a record is inserted, updated or deleted by another Servoy Client. This notification prompts each client to automatically update its cache providing the end users a shared, real-time view of the data.

...

This functionality is provided by default for all Servoy client types. There is nothing that a developer needs to do to enable it. However, the developer may augment the default functionality by implementing the Solution's onDataBroadcast event handler and invoking specific business logic.

...

• External changes were made using the Raw SQL Plugin to update a specific database table. (This plugin bypasses the Data Binding layer and will not be reflected in the client cache. 
• External changes are known to have been made on a specified table because a another application or service was invoked from Servoy client session.
• External changes may have been made to a specific table by another application, but it is not known for sure. In this situation it may be ideal to periodically update the client caches using a Headless Client , which is a server-side client session that can perform automated, scheduled operations.

  Note
  For more information, see the flushAllClientsCache method in the programming reference guide.

...

• External changes were made using the Raw SQL Plugin to update a specific database records and the primary keys are known. 
• Another application or service was invoked from Servoy client session affecting change to specific records who's primary keys are known.

...

...

...

Saving Data Changes

...

Rolling Back Data Changes

...

What about Deleting Records?

It is important to note that record deletes are not part of the In-Memory transaction. When a record is deleted, the instructions are sent to the database immediately and the delete cannot be rolled back.

...

...

Often, there can be several different forms which are bound to the same table. In most cases the forms will share the same foundset and thus provide a unified view. For example, imagine a form showing a list of customer records, where clicking on one of the records switches to another form showing a detailed view of only the selected record. The forms will automatically stay in sync and there is no need to coerce the forms to show the same record. Exceptions to this behavior include scenarios where forms are shown through different Relations, or have been explicitly marked to use a separate foundset.

Loading Records

One of the primary jobs of a Foundset is to load records from the table to which it is bound. A Foundset object is always based on an underlying SQL query, which may change often during the lifetime of the Foundset. However the query will always take the form of selecting the Primary Key column(s) from the table and will also always include an Order By clause, which in its simplest form will sort the results based on the Primary Key column(s).

SELECT customerid FROM customers ORDER BY customerid ASC

After retrieving the results for Primary Key data, the Foundset will issue subsequent SQL queries to load the matching record data in smaller, optimized blocks. This query happens automatically in an on-demand fashion to satisfy the Foundset's scrollable interface. 

SELECT * FROM customers WHERE customerid IN (?,?,?,?,?,?,?,?) ORDER BY customerid ASC

...

Loading Records Programmatically

...

var sql = 'select id from my_table where my_table.column1 in ?,?,?;
var args = [1,2,3];
foundset.loadRecords(sql, args);

Sorting

All foundsets contain a sorting definition that determines the order in which records are loaded and displayed. Sorting is always expressed in the ORDER BY clause of a foundset's query and thus handled by the database.

A foundset's sorting definition is encapsulated in a String property, which can be programmatically read using the getCurrentSort method, and written using the sort method. 

Parameters for this property include an ordered list of one or more data providers, each of which having a sort direction, either ascending or descending. The string takes a form such that each data provider and its sort direction are separated by white space. The direction is abbreviated either asc or desc. Multiple data providers are separated by commas.

...

The result is that all records are sorted by last name. But in the case where the last names are the same, then the first name is used.
|| last_name || first_name ||

...

The Foundset maintains a Selected Index, a cursor with which to step through the records. If the selected index equals or exceeds the size of the Foundset, the Foundset will automatically issue another query to load the next batch of primary key data. Thus the Foundset loads record data and grows dynamically with the changing Selected Index property. There are two methods used to get/set the foundsets selected index. They are getSelectedIndex and setSelectedIndex respectively.

Example: In the example below, note that the foundset's size changes after the selected index has changed. The foundset's cache grows dynamically

// Foundset size grows dynamically as the Foundset is traversed
foundset.getSize(); // returns 200
foundset.setSelectedIndex(200);
foundset.getSize(); // returns 400 because the foundset loaded the next 200 record pks

Iterating over a Foundset

Often, as part of some programming operation, it is necessary to iterate over part or all of a foundset. There are several approaches to iterating, each having their appropriate usage. General a Javascript for or while statement is used to control the flow of execution.

...

Accessing a Record Object

...

Accessing Data Provider Values as an Array

...

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.

...

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.

...

Foundsets are typically updated on a record-by-record basis, either as the user operates on a foundset-bound GUI component, or through programmatic interactions. However, sometimes it is necessary to perform an update to an entire foundset. For performance reasons, it is not advised that this be done by programmatically iterating over the foundset's records. Rather, it is recommended that batch updates be performed using the JSFoundsetUpdater API.

The Foundset Updater API is ideal to use for the following situations:

...

var count = 0;
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
while(fsUpdater.next())
{
	fsUpdater.setColumn('degrees',count++);
}

Find Mode

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.

...

Find mode can be programmatically cancelled by invoking the loadAllRecords method of the foundset. The foundset will revert to the query prior to entering find mode. Within the Smart Client the user can cancel Find mode by pressing Escape. This will trigger the loadAllRecords command of the Form to which the foundset is bound.  

...

This is a simple metadata property to indicate that a column is a foreign key to another table in the same database. One can set the Foreign Type property to the target table. This provides metadata so developers will know that a column is used as a foreign key. Servoy will use this information when new relations are created between the tables and auto-fill the keys. This property is also used by the mergeRecords method method of the databaseManager API to update any affected related records, such that they'll reference a new key. 

Exclude Flag

Enabling a column's Excluded Flag will completely exclude a column from the Servoy runtime environment. This means that Servoy will exclude this column for every query that it issues. This option is ideal to enforce that certain columns are never available in a Servoy application.

...

Servoy supports the use of Universally Unique Identifiers (UUID). A UUID is a 16-byte number which can be (practically) guaranteed to be unique across computing environments, making it ideal to use for sequences in scenarios where traditional numeric sequences are not adequate, for example when syncing data which is generated offline. It is generally not feasible to store UUIDs as numeric data types because the number is so large. Rather UUIDs are most easily stored as 36-character strings. When using a text column to store UUIDs, one should mark the column's UUID flag. Thus, Servoy will provide programmatic access to this column in the form of a built-in UUID data type, which allows both string and byte representation.

...

The UID parameter that was supplied at the time of login, entered at the time of record creation.

...

The UID parameter that was supplied at the time of [login|https://wiki.servoy.com:8443/display/DOCS/Security#Security-login|Link to login method in reference guide]of login, reentered each time the record is modified.

...

Servoy will call a named sequence in the database to populate the value. The column will be populated and available prior to inserting the record

...

Database Identity

...

The sequential values are managed and populated by the database. The column is not populated until after the record is inserted

UUID Generation

Servoy will automatically populate a text column with a textual representation of a UUID. Be sure that the column's UUID Flag is also enabled.

...

Servoy provides an opportunity to implement validation rules at the column level. There are several built-in validation rules, which may be implemented at design-time. Additionally, custom validation rules may be written in as a JavaScript method which is bound to a column. A validation event occurs at the moment a record's value for a column changes. This may be the result of a user's action or some code which is executed. When validation fails, a Servoy Exception is raised for Invalid Input, which may be trapped in a solution's onError event hanlder.

Numeric Range Validation

...

Servoy offers the flexible pattern matching capability of Regular Expressions as a means to apply validation rules to Text columns. Providing a RegEx value will enforce that any value entered into the Text column must match on the expression. RegEx is an excellent way to match on patterns, such as phone numbers, email addresses, and much more. RegExLib is a useful site containing user-generated libraries of expressions to suit many needs.

...

Servoy supports object persistence using String Serialization, which involves the conversion of a runtime object into a string format, which can then persist in a database column. When the column is read from the database, the persistent string will be deserialized back into a runtime object. Because Servoy uses JavaScript as its scripting language, runtime objects will be serialized into standard JSON format. 

//  Construct an object to capture some custom settings and write it directly to a Text column called 'custom_settings'
var obj = new Object();
obj.name = 'foobar';
obj.message = 'Hello World'; 

// at this point it is serialized into the string: "{name:'foobar',message:'Hello World'}"
custom_settings = obj;
databaseManager.saveData();

// ...read object properties at a later time...
application.output(custom_settings.message + 'My name is: ' + custom_settings.name);

// For Example
my_data_provider.property = 'Foobar'; This will have no effect on the data provider

// Instead
var obj = my_data_provider; // read the data provider into a runtime object
obj.property = 'Foo Bar';   // Modify the Object's instance properties
my_data_provider = obj;     // And reassign it to the data providerdatabaseManager.saveData();

...

This method is called anytime a value is written to the data provider. It will be called regardless of the origin of the action, i.e. GUI event or programmatically. It will be called before data is committed to the database.

Parameters

Object - The value that is being written to the data provider

String - The column's data type: TEXT, INTEGER, NUMBER, DATETIME, MEDIA

Returns

Object - The converted value that will actually be written to the data provider.

...

This method is called anytime a value is read from the data provider. It will be called when it is displayed in the GUI or read programmatically.

Parameters

Object - The value that is being read from the data provider

String - The column's data type: TEXT, INTEGER, NUMBER, DATETIME, MEDIA

Returns

Object - The converted value that will actually be displayed in the GUI and read programmatically.

...

The application talks to a database that is storing dates as 8-character text columns to support legacy applications. By setting the Converted Object Type setting to DATETIME, Servoy will treat the column as a date object. Moreover, the two conversion methods written by the developer should assume the Object Value is a Date object.

/**
 * This method converts Text data stored in the database column, presenting it as Date object
 * @parameter {Object} value The value stored in the column
 * @parameter {String} columnType The data type of the column
 * @returns {Object} The value that was converted
 * @properties={typeid:24,uuid:"16BDC049-E63B-47C4-B49C-595D916FD51B"}
 */
function dbToObj(value, columnType) {
	return utils.dateFormat(value,'MMddyyyy');
}

Calculations

A Calculation is very much like a database column, except that its value, rather than being stored, is dynamically computed each time it is requested. This is achieved by creating a JavaScript function which is bound to a database table. The function takes no arguments and returns a value, which like a real database column, is declared to be one of the five general data types. The scope of the JavaScript function is an individual record object. Therefore any of the record's other data providers, and related foundsets are immediately available, as well as global variables and globally related foundsets.

A calculation is declared at the solution level, and is available throughout the solution in which it is declared, as well as any modules containing it. To support this, there is one JavaScript file per table, per solution, which holds calculation functions. For example, the database tables 'orders' may have a corresponding file 'orders_calculations.js' in any solution. And this file could contain many individual calculation functions.

...

An onRecordXXX event is bound to a global method, which is invoked when the event occurs. The record that is modified is passed in as an argument and the method can veto the change by returning false or throwing an exception.

Parameters

JSRecord - the record object that is to be inserted, updated or deleted

Returns

Boolean - Return true from this method to allow the change to commit to the database. Returning false will result in a Servoy Exception  with with a SAVE FAILED error code, which may be handled in the solution's onError event handler.

Example

This is an example of an onRecordDelete handler for an invoices table. The data rule is that posted invoices will never be deleted by a the application.

...

An afterRecordXXX event is bound to a global method, which is invoked when the event occurs.

Parameters

JSRecord   - the record object that was recently inserted, updated or deleted

...

Servoy provides high-level filtering functionality which may be applied to any database table using the addTableFilterParam method of the databaseManager API. Table filters have the following properties:

...