Page History

Database Connectivity

Database Neutral

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.

...

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

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.

...

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

...

 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';

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.

...