Quick Tutorial - Using Online Queries
Description
This tutorial will explain what an online query is, and explain its usage on client devices.
Introduction
The BrightXpress mobility suite provides a flexible framework for end-to-end application development, with many methods to synchronise and retrieve data to and from the server database.
Online queries are one such tool, allowing an application to bypass a full sync to retrieve data from the server, reducing any unnecessary overhead when only a certain subset may be needed. It also may provide a snapshot of potentially constantly changing data on the server side for any application. This online query property can be activated for any Standard and Advanced SQL record sets.
Consider the diagram below. Both standard and advanced queries create an online query which is read by BrightServer. This query is then passed and executed on the server, returning a record set, which is then passed to the device as is (ie. flat). This record set may then be displayed in components such as listviews and combo boxes in a static, read-only fashion.
Online queries provide a tool for developers to offer end users 'semi online' capability on occasionally connected devices, which may be used to enhance user flow while meeting any real-time back end requirements. Aside from Standard and Advanced SQL queries, there also exists Stored Procedures and Remote Procedure Calls, which may be used to create even more complex operations.
All of this online capability may be set up in a simple, effective manner with BrightBuilder, by first exposing a BrightServer configuration sync points, and then by specifying a query as online. These steps are further explained in this tutorial, and the effect may be observed in the sample application.
BEP Setup
Online queries must firstly have their database or script defined in the BEP, and be exposed as a data source to BrightServer. These are defined using the Sync Points in BrightBuilder BEP projects. In the case of standard or advanced online queries, a database is needed.
Any database that is used for any sort of online querying must also be defined as a server data source. This is configured via the 'Server Data Source' property of a sync component. Once set, this property is represented by the sync point with an orange icon at the top right. Setting this property to 'true' will expose the sync point as a server data source, but will not affect the regular synchronisation behaviour.
Databases may thus be defined as regular sync points, but also be enabled for stored procedures and other types of online queries at the same time. Demonstrated below are two queries, 'SyncSendCustomers', a standard query sync process, and 'SyncExampleOnlineStd', an online synchronisation. As they both operate on the same database, they may be contained in one sync point, but in fact, many different types of syncs may be performed with online querying.
If there is more than one server data source, each will need to be assigned a unique ID. This is also specified in the sync point's properties panel, and is a numeric value. Online queries have a data source they operate upon, 'directing' them to the particular data source, based on this ID. The 'Default Server Data Source' option may also be set to true to have this source accessible when an ID is not specified by the application.
With the BEP set up, the application will then define queries and sync rules in accordance to these sync points, which will be explained in the following section.
BSP Setup
With the BrightServer configuration set up and running, Online Stored Procedure queries are established by setting properties in the Queries Panel of a project, and specifying parameters and outputs of those queries in BrightBuilder. The query may then be assigned to a sync rule, which, when run, will interface with the server's database records directly and populate the resultset of the query as defined.
Defining the query
When setting up an online query, the concept of tables, parameters and output are all the same as though they were not online. For more information on setting this up in an typical synchronisation context, please refer to the BrightBuilder > Queries section of the BrightXpress User Manual.
Setting a query as online
Queries are defined as online via the 'Online Query' property. Setting this to 'true' will specify to BrightServer that the query will operate outside of the standard server/client synchronisation processes, and instead use online querying when the sync rule is executed. Queries may be confirmed as online in the projects tree as they are marked with a red node.
Discovering Server Sources
Every online query must refer to a Data Source for its data, either defined explicitly or using the default data source in a BEP configuration.
Right clicking a project will display the option to discover data sources either from the server or files. Selecting 'server' will connect to a server, and retrieve a list of exposed data sources from the server's configuration, displaying the results in a dialog. The 'files' option will do this from opened BEP projects. Upon tapping the 'update' button, the BSP will register the sources into the project. Once registered, these data sources may be assigned to queries via drop down menu.
Using Online Queries in an Application
Once the query is defined, it may then be used to retrieve data from its specified data source. This is performed using outward sync rules in an application. The time the sync rule is executed will be the time the device is 'online' with the server, and the snapshot of data retrieved.
Setting up the sync rule is exactly the same as any other query, although must be 'Server to Client' in the 'Direction' tab.
The execution of the sync rule itself is also the same as any other query - the rule must be called after a Synchroniser.Connect() method, or enabled prior to then executed a Form.ShowSyncDialog() method, and as with any parameterised sync, input parameters need to be specified together with the sync rule, such as via globals or application settings.
An example of a sync rule running for the online query is as follows:
// The following code will run the online query via the sync dialog
Synchroniser.DisableAll();
Synchroniser.EnableSyncRule("SyncExampleOnlineStd", true);
Form.ShowSyncDialog(false);
After the sync rule runs, its previous recordset will be deleted, and any subsequent reference to the query will use the new recordset returned. As the result set is static, the most common place for it to be displayed is via a listview or combobox. Records may also be traversed via the Query object.
// refresh listview containing the query, will be updated with most recently retrieved resultset
listview1.refresh();
// load results of query as string into a temporary variable. parameters are not necessary, as the record set is static.
local.vCodes = "";
Query.SetQuery("qSyncExampleOnlineStd");
WHILE(Query.HasRecord())
{
local.vCodes = local.vCodes & Query.GetColumnValue("CUST_CODE") & ",";
Query.GoToNext();
}
Please note, that there is a limited number of records the server may return to the device. This figure is based on BrightServer's configuration, with the default being 200 records. For more information, please refer to BrightServer > Server Settings > Data Size Limits.
Exercise
BrightServer Configuration
Customer codes are retrieved by the device via online query, updated and then sent via conventional synchronisation. Devices may only retrieve records which match the user number - hard coded to 42 in this tutorial.
To set the BEP for this process up, create a database on localhost named "BrightTutorial". Run the OnlineQueryCreate_tbl.sql script on this newly created database to create the Customer table that the sample application will use. This is a simple 3 column table, with an automatically generated CUST_CODE ID.
Populate the newly created table - BS_CUSTOMER with any number of records.
After completing the above, execute the BEP server configuration or deploy/activate it on a BrightServer instance.
Application
The application is a simple test application used to retrieve server data and also query data from local tables. It behaves as follows:
The 'Get Allocated CustCodes' will return the CUST_CODEs of all records on the server's BS_CUSTOMER table where USER_ASSIGN is 42. The results will be displayed in the listview of 'Assigned Codes'
Selected codes in the table may be used to send data to the server with the 'Update + Send' button. This will deallocate the user assignment, and populate the CUST_NAME field for the record to the value specified by the edit control. The customer code listview is also refreshed after this, reflecting the current server state.
The count of the device's table may be queried at any time with the 'Count local BS_CUSTOMER' button. This number will typically be 0 if the 'Update + Send' of new records is successful, as the results shown in the listview are not present locally.
The starting point of the application does not contain the online query or sync rule to retrieve the following:
SELECT * FROM BS_CUSTOMER where USER_ASSIGN = ?
Validation errors will occur, as the points of synchronisation have no rule defined.
Complete the project, implementing the online query and its sync rule in the BSP. This may be done with either Standard or Advanced SQL queries. Test the record by creating records in the server BS_CUSTOMER table with the USER_ASSIGN column as 42 where necessary.
A possible solution is located in OnlineQueryFinished.bsp.