Page History

Database Connectivity

At its core, Servoy is a comprehensive platform to develop database-driven applications. As such, Servoy allows developers to connect to any standard Relational Database Management System (RDBMS). To achieve this, Servoy employs Java Database Connectivity (JDBC) technology. JDBC is a connectivity standard that allows Java-based applications to interact transparently with any database vendor that provides a compliant driver.

Servoy's Query Engine

The Servoy platform uses Structured Query Language (SQL), a standard communications protocol to issue requests to databases. The traditional development of database applications typically requires advanced SQL knowledge to adequately and efficiently retrieve data. Moreover, database vendors often adapt their own "flavors" of SQL, making database portability an issue. If an application uses non-standard SQL expressions, it cannot be easily deployed against databases other the the one for which it was developed.

The Servoy platform obviates the need for developers to write their own SQL in almost all scenarios. Instead Servoy dynamically generates all the SQL required to read and write data for all but the most complex scenarios. Servoy's generated queries are guaranteed to be optimized and database-neutral. At the same time, the Servoy platform is open and allows developers with the knowledge and preference for writing SQL to do so as well.

Named Connections

Developers will specify a Server Name, which maps to a specific database connection configuration (i.e. user, password, URL, etc). At run time, Servoy will automatically create a pool of connections for a given Server Name, which will be reused for the duration of the application server up time. Developers are always insulated from the complexities of connecting to the database.

...

For example, it is common to have separate databases for development/testing and production. Therefore, a Server Name could resolve to a test database in both Servoy Developer and an instance of Servoy Server used for staging. The same Server Name would resolve to a production database for an instance of Server Server used in production. Another example is for multiple, on-premise, deployments where local instances of Servoy Server rely on local database connections.

Switching Connections

While database connections can be changed transparently between different development and deployment contexts, they can also be changed within the same context. Servoy's API provides a means for developers to change, at run time, from one Server Name to another. For example, different application user groups may be required to use different connections to the same database. In another example, different customers may have their data stored in their own separate database. In both these cases, a specific Server Name is identified and used for an individual client session.

Connection Pooling

Servoy uses database connection pooling technology which provides significant benefits in terms of application performance, concurrency and scalability. Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. Moreover, the ongoing management of all of a database's connection sessions can impose a major limiting factor on the scalability of an application. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles and temporary tables all tend to increase based on the number of concurrent connection sessions. This limitation is overcome using Connection Pooling, whereby a limited number of connections is shared by a larger number of clients. When a client makes a new request for data, the application server briefly borrows a connection from the pool to issue the query, then returns it to the pool. In this manner an application can scale well beyond the limits of the database without compromising performance. Servoy's connection pools are configurable so that connectivity can be optimized to suit applications of various sizes.

Dynamic Data Binding

The Servoy platform provides a Graphical User Interface (GUI), as well as an Application Program Interface (API) which dynamically bind to database resources. This means the Servoy Application Server will dynamically generate and issue the SQL required to Read, Insert, Update and Delete database records in response to both the actions of the user and the behest of the developer.

...

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  to all other connected clients.

...

The fundamental unit of data binding in both the GUI and the API is the Foundset  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.

Data Broadcasting

What happens to the cache when the contents of a record are changed by another Servoy client session?

...

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.

Updating the Client Cache

What happens when data is changed outside of any Servoy client sessions?

...

Data Transactions in Servoy

Data manipulations in Servoy happen inside an in-memory transaction. When a record is created or modified, either by user action or by developer, nothing is committed to the database immediately. The Servoy Client tracks all newly-created and modified records, including which columns have changed, their former and latter values. As records are added or modified, the amount of information stored in the In-Memory transaction accrue until they are committed or rolled back. The duration of this In-Memory transaction can be short or long depending on the client's configurable Auto Save setting.

Auto Save: ON

By default, every Servoy client is started with the Auto Save setting initialized to on/true. This means that the In-Memory transaction is typically very short as changes are committed automatically as the user navigates the client session. Specific actions like clicking in a form's area, navigating to a different form, clicking a button, etc. all trigger a save event. Auto Save is ideal for situations where the user is intended to be able to make edits freely.

Auto Save: OFF

The developer may optionally set the Auto Save setting to off/false. This means that the length of the In-Memory transaction is controlled by the developer. As changes accrue, they are never committed until the developer programmatically invokes a save event. It is ideal to disable Auto Save for scenarios where the user is intended to perform edits in a controlled situation where a group of edits may be saved or rolled back all together.

...

The Anatomy of the In-Memory Transaction

Servoy provides a robust data API, giving the developer full access to the In-Memory transaction, which consists of a listing of all record objects that were added or modified. For each of these record objects, there is a listing of every column whose value was changed. For every modified column, there is a reference to the value before and after the edit. The transaction API also allows developers to distinguish between records that are newly-created and do not yet exist in the database, versus records that already exist in the database, but have outstanding edits.

Saving Data Changes

A developer can programmatically issue a save event, causing the contents of the In-Memory transaction to be automatically translated into instructions to insert/update database tables. A developer can optionally invoke a save event for a specific record only, leaving the rest of the transaction unaffected.

...

Rolling Back Data Changes

A developer can programmatically issue a command to rollback the contents of the entire In-Memory transaction, causing newly created records to be removed and modified records to be reverted to their state prior to the start of the In-Memory transaction. The developer can optionally choose to rollback changes for a specific record, leaving the rest of the transaction unaffected.

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.

The Servoy Foundset

The Servoy Foundset is a developer's window into Servoy's Data Binding layer. A single foundset always maps to a single database table (or view) and is responsible for reading from and writing to that table. From the user interface, a foundset controls which records are loaded and displayed, as well as how records are created, edited and deleted. From the developer's perspective, a foundset is a programmable object with specific behaviors and run-time properties that provide a high-level abstraction to facilitate low-level data operations.

Forms Bound to a Foundset

A Servoy Form is typically bound to a single database table and the form will always contain a single Foundset object which is bound to the same table. Much of the action in the user interface, such as a user editing data fields, directly affects the form's foundset. Conversely, actions taken on the foundset, such as programmatically editing data, is immediately reflected in the form.

...

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

...

• When a form to which it is bound is loaded
• When the loadRecords method is called programmatically
• When the sort definition is changed
• When it exits find mode

...

Loading Records Programmatically

...

The loadRecords method is used to directly modify the underlying query that loads PK data. There are several uses.

...

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.

...

Scrolling Result Set

The Foundset maintains a scrollable interface for traversing record data. This interface includes a numeric index for every record that is returned by the Foundset's query.

...

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

...

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.

...

Foundsets and Data Broadcasting

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.

Performing Batch Updates

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.

...

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.

...

 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.

...

 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.

...

 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.

...

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

...

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.

 // Cities of London or Berlin
city = 'Berlin||London';

 // exactly 01/01/2001 (00:00:00 implied)
orderdate = '01/01/2001|MM/dd/yyyy'

// Anything but Berlin
city = '!Berlin';

// i.e. Los Angeles, lOS aNGeLES
city = '#los angeles';

// any time on 01/01/2001
orderdate = '#01/01/2001|MM/dd/yyyy';

// All null contact names, not including empty strings
contactname = '^';

// All freights which are null or 0
freight = '^=';

// i.e. 50, 99.99, but not 100, 101
freight < 100;

// i.e. Atlanta, Baghdad, Berlin, but not Buenos Aires, Cairo
city <= 'Berlin';

// Any time on/after 12am new year's day 2001
orderdate '>=01/01/2001|MM/dd/yyyy';

// i.e. 100.01, 200, but not 99,100
freight > 100;

// Any time during the year 2001
orderdate = '01/01/2001...01/01/2002|MM/dd/yyyy';

// i.e.
frieght = 100...200

// i.e. London, Lyon, Madrid, Omaha, Portland
city = 'London...Portland'

city= 'New%';    // Starts with: i.e. New York, New Orleans
city = '%Villa%; // Contains: i.e. Villa Nova, La Villa Linda
city = '%s';     // Ends with: i.e. Athens, Los Angeles

// i.e. Toledo, Torino
city = '%To___o%';

// Escape the wild card, i.e. ...50% of Capacity...
notes = '%\%%';

// exact match on this second
creationdate = 'now';

// match on anytime today
orderdate = 'today';

Find Mode and the User Interface

The above examples deal with find mode in which find mode is entered, criteria are expressed and the search is run, all in a single action. The effect of the search is entirely up to the developer. However, find mode can also be entered in one action and searched in another action. In between, the user may manually enter values into fields to express the search criteria. They can then run the search action and a form's foundset will show the results of the search. Any of the above search criteria may be used.

...

...

Read-Only Fields

...

By default, even read-only fields will become editable for the duration of the find mode. This is often useful, because while a data provider may not be available to edit, in find mode, it becomes a vehicle to enter a search criterion and should be editable to the user. However, in some cases it may be desired that read-only fields remain so for the duration of find mode as well. Servoy provides a UI property which may be set through the Application API using the method setUIProperty.

...

/**
 * @AllowToRunInFind
 *
 * @properties={typeid:24,uuid:"088B830C-2A4F-483C-A135-5FA32A010AE9"}
 */
function doFind(){
	if(foundset.isInFind()){
		foundset.search();
		application.setUIProperty(APP_UI_PROPERTY.LEAVE_FIELDS_READONLY_IN_FIND_MODE, false)    // reset to the default
	} else {
		application.setUIProperty(APP_UI_PROPERTY.LEAVE_FIELDS_READONLY_IN_FIND_MODE, true);    // before entering find mode, enforce read-only fields
		foundset.find();
	}
}

Canceling Find Mode

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.

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.

Table Definitions

Servoy's data layer offers an assortment of design-time settings, defined at the table and column level,
to specify metadata and data-bound business rules.

Column Properties

Servoy will read column definitions from relational database tables. The properties that it reads include:

Column Name

This is the name that is returned by the JDBC Driver. The name will always be displayed as lower case. The column name is also used as the Data Provider ID when working with foundsets and records.

Data Type

While relation databases support many different data types, Servoy will generalize the data type of a column into one of five general types. In this way, Servoy can support a wide range of database vendors. The five generalized data types include the following:

1. Text: Any alpha-numeric characters (i.e. char, varchar, memo, CLOB, etc.)
2. Integer: Whole numbers (i.e. bit, short, long, bigint, etc.)
3. Number: Decimal numbers (i.e. float, double, etc)
4. Datetime: Temporal values (i.e. date, datetime, timestamp, etc.)
5. Media: Binary data (i.e. BLOB)

Column Length

For certain data types, databases must enforce the amount of storage allocated to single column for a single record. Data types which accommodate variable length entries, such as text, decimal numbers and binary data will have a length property. Servoy will infer and display this property in the column definition.

Row Identifiers

Servoy is designed to work with regular database tables as well as SQL Views. Regular database tables will have a primary key, consisting of one or more columns, who's value uniquely identifies a record in the table. Servoy will infer the primary key from the database table. However, in the case of SQL Views, which don't have a built-in primary key, the developer must specify which column(s) can be considered the unique row identifier.

Null Values Allowed

Relational database tables may enforce non-null constraints on certain columns, typically for primary key and other essential columns. Servoy will infer from any such constraints from database table and reflect

Column Meta Data

Column definitions include several metadata properties, which store information that is used both in development and at runtime.

Title

The Title property of a column is simply the human-readable name for a column. When a field is placed on a form with the Place with labels option, the label's text property will be initialized to the title property for the column to reach the field is bound. For multilingual applications, it is ideal to populate a column's title property with an i18n message key, thus allowing field labels to default to message key, which is translated at runtime.

Default Format

The Default Format property of a column will enforce the formatting that is used when the column is bound to a field element. The field element's format property will assume the default format of the column unless it is overridden in the element.

Foreign Type

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

UUID Flag

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.

Description

A column's description property is a simple container for additional metadata, such as programmer notes about the column's purpose, etc.

Auto-Enter Definitions

Servoy provides several ways in which a column may be automatically populated when a record is created. Some of the auto-enter options are also applicable when an existing record is updated. 

System Values

...

Database-Managed

...

Indicates that the value is deferred to the database at the time of insert. The value is populated and controlled by the database and it will not be overwritten from Servoy.

...

Creation User UID

...

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

...

Modification User UID

...

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], reentered each time the record is modified.

...

Creation Datetime

...

The current date and time on the client, entered at the time of record creation.

...

Creation Server Datetime

...

The current date and time on the application server, entered at the time of record creation.

...

Modification Datetime

...

The current date and time on the client, reentered each time the record is modified.

...

Modification Server Datetime

...

The current date and time on the application server, reentered each time the record is modified.

Custom Value

A custom value is simply a literal value (i.e. 'Blue', 1.5) which may be used as a default. This option is only available for Integer, Number and Text data types.

Database Default

This indicates that the value is deferred to the database at the time of insert. However, unlike Database-Managed system values, this value can be modified from Servoy after the record is inserted.

Lookup Value

Lookup Values provide the option to auto-enter a value that is contextual to the record being inserted. Options include any of the record's data providers, any data providers from foundsets related to the record, as well as an global relations or variables.

Sequence

Sequences may be used to auto-increment a column's value. This is ideal for populating primary key columns, which must be unique.

...

Servoy Sequence

...

This is a sequence which is defined in the application tier and managed by Servoy. The sequence will generate integer values using a given next value and step value.

...

In deployment, Servoy Sequences are stored in the repository database and there are options to recalculate the sequence's next value from existing data.

...

Database Sequence

...

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.

Column Validation

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 provides built-in numeric validation for Integer and Number data types. Providing upper and lower bounds will automatically enforce that any value entered is between (inclusive) the range provided. Providing only a lower bound will enforce that any value entered is greater-than-or-equal-to the bound. Providing only an upper bound will enforce that any value entered is less-than-or-equal-to the bound.

Size/Length Validation

Servoy provides built-in validation for the size/length of a value in a column. This rule is applicable to Text and Media data types. Setting the length property for Text columns will enforce that value entered has a length of characters which is less-than-or-equal-to the length specified in the rule. Setting the size property for Media columns will enforce that value entered has a size, measured in number of bytes, which is less-than-or-equal-to the size specified in the rule.

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

Email Validation

Servoy provides a built-in email validation rule, which enforces that any Text column matches a pattern which is similar to email addresses. This pattern is ideal for most use cases. However, developers may implement their own RegEx validation to ensure an exact match on the pattern of their choice.

Custom Validation

Apart from the built-in validation rules, Servoy allows developers to author business logic to enforce their own validation rule for a column. A Global Method may be bound to a column, such that when a validation event occurs for the column, the method is invoked. The value that is entered is passed into the method and a developer may then execute any evaluation of the value before returning a boolean value; true indicates that validation is successful.

/**
 * Custom Validation rule: Must be Dog or Cat (case insensitive)
 * @param {Object} obj The value that will be validated
 * @returns {Boolean} True when successful
 * @properties={typeid:24,uuid:"655B9F0E-A1A2-4B0B-84CD-8E299546DB57"}
 */
function validateColumn(obj) {
	return 'Dog'.equalsIgnoreCase(obj) || 'Cat'.equalsIgnoreCase(obj);
}

Column Conversion

Some scenarios require that a value be stored in a database column in one form and written to and read from the database column in another form. Servoy supports this requirement with a feature called Column Conversion and it has two applications, String Serialization and Global Method Conversion.

String Serialization

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. 

...

// 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();

Global Method Conversion

Servoy allows a database column to be bound to custom business logic, giving developers control over how a value is converted when it is written to, and read from the data provider.

...

The column is bound to two methods which facilitate the conversion between the Object Value and the Database Value. A developer may also specify an optional Object Data Type, prompting Servoy to provide the data in an alternate data type in lieu of the default column type. This is useful when values are stored in a non-standard storage type to accommodate legacy systems, but should be treated like standard data type in the runtime. 

...

Object to Database Method

...

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.

...

/**
 * This method auto-converts from client units to Celsius as the value is being written to the data provider
 * @parameter {Object} value The value of the runtime object
 * @parameter {String} columnType The data type of the column
 * @returns {Object} The value converted into celsius
 * @properties={typeid:24,uuid:"303ACB93-3B0E-4B9C-9550-D78FF17343C2"}
 */
function objectToDB(value, columnType) {

	// evaluate client unit settings
	switch(tempUnits){

		// Already in C, just return it as is
		case C :
			return value;

		// Fahrenheit,use conversion formula
		case F :
			return (5/9)*(value-32);

		// Kelvin,use conversion formula
		case K :
			return value - 273;
	}
}

...

Database to Object Method

...

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.

...

/**
 * This method converts database values (Celsius) into the current client units for degrees
 * @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 into current client units
 * @properties={typeid:24,uuid:"63C4D552-531C-48DB-A6C6-ED02F4603C20"}
 */
function dbToObject(value, columnType) {

	//	evaluate client unit settings
	switch(tempUnits){

		// Already using C, just return it as is
		case C :
			return value;

		// Fahrenheit, use conversion formula
		case F :
			return (9/5) * value + 32;

		// Kelvin, use conversion formula
		case K :
			return value + 273;
	}
}
}

...

Converted Object Type

...

One can optionally specify the data type of the Object Value. This is useful in situations where the stored value is a different data type than the object value

...

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

...

Notice that the related foundset, orders_to_order_details, a property of an order record, is immediately available. Also notice that the previous calculation, subtotal, may be referenced, just like any other column. Note that while, calculations are a JavaScript function, unlike regular methods, one does not use the parentheses to invoke their value. Both calculations may be placed on forms like any other data provider. (See image below - calculations are highlighted in green) 

...

Stored Calculations

...

Calculations may be optionally stored back to a real database column. This is called a Stored Calculation, and is achieved by creating a calculation which is the same name and data type as a real column. When the calculation is referenced, it will be executed and its result will be stored back in the database column. 

...

Calculation as a Record-Level Variable

...

In general, a calculation is a ready-only data provider, its value being generated each time it is read. However, in one exception a calculation may be used to cache data for a record, thus becoming a read/write in-memory data provider. This is done by creating a calculation which has no return statement. Such a calculation will be treated a little differently. It can actually store a value in memory for an individual record.

...

/**
 * @param {JSEvent} event
 *
 * @properties={typeid:24,uuid:"75E3667B-AED9-4E79-ADE8-CCBED66A8D2F"}
 */
function flagSelectedRecord(event){

	// toggles the flag between 0 & 1
	if(record_flag){
		record_flag = 0
	} else {
		record_flag = 1;
	}
}

Aggregations

An aggregation is a data provider which represents a database column that is aggregated over a set of records. At design-time aggregations have the following properties:

...

Table Events

Servoy provides an event model at the data layer, giving developers the opportunity to implement validation and execute business logic just before and after data changes are committed to the database. There are six Table Events, each of which may be bound to global methods.

...

/**/**
 * Record after-insert trigger.
 *
 * @param {JSRecord} record record that is inserted
 *
 * @properties={typeid:24,uuid:"92834B20-1CAC-472F-B022-DD97FEFEA792"}
 */
function afterRecordInsert(record) {
	if(record.projects_to_projects_users.newRecord()){				// create a link record
		record.projects_to_projects_users.user_id = globals.currentUserID;	// associate to current user
		databaseManager.saveData();
	}
}

Using Table Filters

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:

...