Online Queries
Contents Hide
There are occasions where it may be better to query server data directly from the server database every time, instead of storing data in a mobile device's local database.
It would be inefficient to synchronise a server table containing a very large number of records and store the entire record set on the device, especially if the entire record set is not always required for the task at hand. In such a case, online queries can be used to retrieve a subset of the data just in time for when it is required with minimal synchronisation overhead.
In another case, a table may have data which is rapidly changing and updated on the server. Any data stored on the device is likely to be out of date when queried locally. Online queries can be used to retrieve the latest data from the server, ensuring the data is current.
Typically large data sets can be divided into smaller subsets by querying based on the context of the task performed by the application. This way the entire data set is never stored on the local database, saving synchronisation time and storage space on the device.
When an online query is executed, a snapshot of the server database table is saved to a local file on the device. Online queries use a different synchronisation engine to allow a faster retrieval of server data with lower synchronisation overhead.
Online queries must be used in conjunction with a sync rule from server to client. Furthermore, the data source of the online query (or the default data source) must be set as a 'Server Data Source' for the online query to be able to execute on the server. This is defined in BrightServer's BEP file under the Sync Points dialog.
Creating an Online Query
To create an online query, set the Online Query attribute of a Query to true. Any type of query (Standard, Stored Procedure, Advanced SQL, Script) can be used as an online query. The query icon should change to a red icon.
The Data Source property is optional. Online queries are different to normal queries because when they are used in a sync rule to get data from the server table, they do not use the same synchronisation engine. Online queries get executed against a server Data Source defined by BrightServer. If the Data Source is left as blank, then the Default Server Data Source is used when executing the querying.
It is important, in this case, that a Default Server Data Source is defined in the BEP configuration for the server. For more information on server Data Sources see Database Data Source and Script Server Data Source in the BrightServer Sync Points chapter.
If a server Data Source other than the default will be used, then these Data Sources must be discovered from a running BrightServer or BEP project. This action can be performed from the context menu of the BSP project.
For more information on discovering server Data Sources, see Discover Data Sources in the BrightBuilder Projects chapter.
Once the Data Sources have been discovered, one can be selected from the Data Source property of the query.
Query Types
Online query set, the type of query defined may be altered via the query's 'Type' property. Depending on the type, there may be additional fields to set within the application and/or configured on the server, and as such, one must ensure that BrightServer and/or the SQL database are correctly set up correctly, depending on the configuration.
Standard or Advanced SQL
These types return data from the server via standard query statements, which are processed from BrightServer. The queries sent by the PDA to BrightServer may logically be the same, but the design of it in BrightBuilder may be using Standard or Advance to represent the data being retrieved. The data retrieved is a data set only.
Script Query
This type can be set with the 'Script Query' property selection, and is unique as a payload and script name must be specified. The payload is a string value set before the synchronisation takes place and should passed to the rule when it is run. When the rule is executed, the script specified in the properties will run it's execute() method with the payload as its input, and return a set of records as designed for the application.
For more information, please refer to BrightServer > Scripts > Online Query.
RPC/Remote call
This will run the stored procedure specified by the name field, on the data source defined in the query's properties. The stored procedure itself may be defined either on BrightServer via scripts, or on the SQL server itself - based on the data source. This type of query differs from the above, as not only does it have input and output parameters, but it can also output a result set on top of this data. Stored procedures/RPCs may only be specified as online queries, and will produce an error on validation if this is not adhered to.
For more information, please refer to BrightServer > Scripts >RPC (Remote Procedure Calls).
Using an Online Query
Online queries must be used in conjunction with a sync rule. When a sync rule using the same query is executed, the result is cached locally on the device.
The sync rule should be executed before the data is required in the application.
When the same query is used to display data in list views, combo boxes, etc, the local cache is the source of the data.
The following is an example where the online query is used in a sync rule before the online query is used to present data.
We have an online query with one parameter as part of its condition and a sync rule which uses the online query and assigns a global variable to the query parameter.
To use the online query the following expression could be used.
//Context - Set the parameter for the query in the sync rule
global.GlobalVariableCustID = local.CurrentCustomerID;
//Execute the sync rule with the online query to cache the latest data to file
Synchroniser.DisableAll();
Synchroniser.EnableSyncRule("SyncProductsOnline", true);
Form.ShowSyncDialog(true);
// At this point the sync result is cached in a file and the file cache becomes the data source wherever the online query is used
//Use the query in a variety of ways - listview, combo box, form query, query object, etc to view latest cached data
//...Queries
QueryProductsOnline.pCustomerID = local.CurrentCustomerID;
local.FirstProduct = QueryProductsOnline.PRODUCT_ID;
//...refresh a Listview which uses the online query
listviewProducts.Refresh();
//...Query object
Query.SetQuery("QueryProductsOnline");
Query.SetQueryParam("pCustomerID", local.CurrentCustomerID);
WHILE(Query.HasRecord())
{
//...
Query.GoToNext();
}
Data retrieved by a sync rule is saved locally under the query sub folder of the BrightForms installation directory. Each query’s sync result is stored in an XML file with the same name as the query, e.g. QueryProductsOnline.xml. This file can be deleted as the next synchronisation using the query will recreate the file with the latest data.
Furthermore, as the record set for each online query is static, it is not completely necessary to specify parameters again; any reference to the online query's data will return the same set, regardless the parameter set, if any.
Limits
A BrightServer setting limits the number of records returned by an online query. This acts as a protection against querying too many records from a large table. By default this limit is set to 200 records. This is configurable in the BrightServer Runtime Server Configuration.
