Loading

Overview

Loading data in Servoy involves retrieving records from the database and making them available for user interaction within the application. This chapter discusses various methods to load records into foundsets, including practical use cases to illustrate when and why each method might be used. Unlike searching, which involves querying data based on specific criteria, loading data focuses on retrieving known records or sets of records.

One of the primary jobs of a foundset is to load records from the table to which it is bound. A foundset 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).

Foundset Loading Example:

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.

Record Loading Query Example:

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

A foundset's underlying query can change dramatically throughout the client session due to various events such as:

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

For further details, refer to the Database Manager's getSQL and getSQLParameters methods.

Loading Records Programmatically

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

Load by a Single PK

This is the simplest approach, which loads a single record by its primary key value.

Use Case: Navigating to a detailed view of a record from a list view.

Example:

foundset.loadRecords(123);

Load by PK Data Set

This approach loads records based on specified primary key data.

Use Case: Displaying a subset of records selected by the user. Example:

var ids = [1, 2, 3, 6, 9];  // an array of record PKs
var ds = databaseManager.convertToDataSet(ids); // convert the ids to a JSDataset
foundset.loadRecords(ds);  // load records

Notice the array was converted first to a JSDataset object. This object, which is like a 2-dimensional array, is used to provide support for composite primary keys.

Load by Another Foundset

This approach is useful to essentially copy the query of another foundset.

Use Case: Creating a copy of a foundset for manipulation without affecting the original. Example:

foundset.loadRecords(anotherFoundset);

Difference between foundset.loadRecords() and controller.loadRecords()

When using foundset.loadRecords(anotherFoundset), it copies the state of another foundset into the current one, but both foundsets remain independent from each other. This allows manipulation of the data without affecting the original foundset. In contrast, controller.loadRecords(anotherFoundset) fully replaces the foundset in the form, making the form use the new foundset until the original one is restored. This distinction is important when deciding whether to keep two foundsets separate or to change the data source entirely for a form.

See the Form's controller documentation for more details.

Load by Query: QBSelect

This approach loads records into a form foundset based on a query builder object. When the foundset is in find mode, the find states are discarded, the foundset will go out of find mode, and the foundset will be loaded using the query. It adds the query of the QBSelect as a "search" condition to the existing base query of the foundset. It preserves selection based on the primary key, otherwise selects the first record.

Use Case: Loading records based on a dynamic query built programmatically. Example:

var qb = datasources.db.example_data.orders.createSelect();
qb.result.addPk();
qb.where.add(qb.columns.product_id.eq(1));
foundset.loadRecords(qb);

You can also check the searching using Query Builder guide.

Load by Query: SQL String

This approach allows a SQL query fragment to be used to set the foundset's underlying query. There are certain restrictions on the form that a query can take. The query must return the primary key column(s) from the table to which the foundset is bound. It uses a SQL string to directly set the foundset's query, which is an older method and less preferred compared to QBSelect.

Use Case: Loading records based on specific SQL conditions. Example:

var sql = 'SELECT id FROM my_table WHERE my_table.column1 IN (?, ?, ?)';
var args = [1, 2, 3];
foundset.loadRecords(sql, args);

Using a SQL string is the older method and is not the preferred approach. Whenever possible, use the QBSelect method for better maintainability and flexibility..

---

Refer to the loadRecords API and in the reference guide for complete usage options. You can also check the loadRecords(foundset) controller API.