Quick Tutorial - How to Use Online Scripts

Description

This tutorial will explain how to utilise the flexible Online Script feature in the BrightXpress mobility suite.

Resources

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.

The online script is a straight forward, yet flexibly designed tool for developers. On PDA synchronisation, it takes in a single string payload which may be parsed by the script's execute() method to any developer specification. This execute method may then call other methods, reference databases, files and/or other data sources, call external web services or even generate entire record sets alone. It then returns these results in the form of an online resultset to the device.

Using this tool, developers may produce applications which are capable of handling complex operations via server side calculations (rather than on the device), operate over a combination of data sources for PDA consumption, or meet any other integration needs in a project which may not be achievable via the standard synchronisation process.

This tutorial will delve through an existing application and identify key characteristics of scripted online queries that must be implemented for this feature to be utilised, namely:

Creation of the script
Specifying the input (payload string)
Returning the RecordSet and FieldInfo
Calling the script from the Mobile Application

Creating an Online Script

A new online script may be created by right clicking on the Scripts node and selecting 'New Script'. The New Script Wizard will then appear where the online query script template may be selected.

On selection, and clicking 'Finish', the following stub will be created:

function execute(payload)

{

// TODO

return null ;

}

Additional comments above the stub are provided, indicating that the payload input is a string value, while the return type is a RecordSet (initially set to NULL).

Once created, the script may be established as a sync point in the BrightServer configuration via the sync panel. This is performed by dragging and dropping the 'User Defined Script' icon from the tool bar into the panel, and selecting the newly created online script's name under the 'Script' property.

In order to be used correctly for online queries, the data source property must be set to 'true' for the sync point. Once set, this property is represented by the their 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, allowing the return of 'flat' recordsets to BrightForms applications.

No client tables need to be defined as incoming/outgoing sync points, as queries are handled purely online.

From this point, the contents and behaviour of the script may be further specified.

Payloads

Online Server Queries operate on a concept of a free string payload whereby the developer can define the syntax of what is expected from devices to the server and how that affects the result set returned. It is the single input of an Online Script Query. The payload is sent with the sync rule of the query as a query parameter - and thus is typically loaded into a global variable or application setting for sync rules to reference.

One common way to pass information to the server is by comma separated value strings. These strings would need to be parsed into tokens based on delimiters, and then each separated value may be processed in whichever way. For example, the following code uses the Java String.Split() method to accomplish this.

// parse the payload, creating a list of string tokens

delims = new String(",");

tokens = payload.split(delims);

for (i = 0; i < tokens.length; i++)

{

// convert string value parsed into integer

n = parseFloat(tokens[i]);

ScriptSession.logInfo("payload["+ i + "] = " + n);

sum += n;

}

Please note the type casting after parsing the payload in a script. In some cases, the string values retrieved may need to be converted into other types. In the above example, Javascript's parseFloat() function is used, converting the string into a float for the sum operation to take place correctly.

RecordSet

BrightServer uses the RecordSet() object to return server records for script queries, including Online script queries. This record set is a group of records retrieved or generated by scripts on BrightServer. It is the single output of the Online SQL query, and is sent to the device in the form of an online query result set.

Records are created by specifying each field in the row with the add() method, in the column order of the recordset. Other methods such as get() and set() may also be used on existing record set data. The record is then added to the recordset with the method addRecord().

// Create a new recordset with table name "TBL_RESULT"

rs = new RecordSet("TBL_RESULT");

// (... set field info, add more records etc ...)

// Add a new record into the recordset

record = new Record();

record.add(new Integer(1));

record.add(payload);

record.add("Sum");

record.add(sum);

record.add(timeNow);

rs.addRecord(record);

// (... set field info, add more records etc ...)

// Return recordset when complete

return rs ;

Online script queries, or scripts in general do not necessarily need to return a populated record set. In these cases, the script would have a return line similar to:

return new RecordSet("EMPTY");

Because it is online, every time this script is run from the device, the previous resultset will be cleared. In order for BrightForms to correctly interpret the online query result set, the Field info for each column in the record set must be defined before the recordset is returned. Please refer to the following section for an example.

Field Info Definition

Field info definition needs to be done at any point prior to the return of the record set from the script. This is achieved by populating a vector of 'Field Info' and setting it to the record set with the method setFieldInfo(). The order of which the field information is set into the array is the order of the recordset's output columns. For example, the following field information vector:

vectFieldInfo = new Vector();

vectFieldInfo.add(new FieldInfo("ID", FieldInfo.INTEGER, true, 0, false));

vectFieldInfo.add(new FieldInfo("PAYLOAD", FieldInfo.STRING, false, 255, true ));

vectFieldInfo.add(new FieldInfo("OP_TYPE", FieldInfo.STRING, false, 255, true ));

vectFieldInfo.add(new FieldInfo("OP_RESULT", FieldInfo.DOUBLE, false, 0, true ));

vectFieldInfo.add( new FieldInfo("DT_CREATED", FieldInfo.DATETIME, false, 0, true ));

Would map to the following table:

And be set via the following code:

rs = new RecordSet("TABLE1");

rs.setFieldInfo(vectFieldInfo);

For more information, please refer to the BrightServer > Scripts > Using Data Types > Fields chapter of this documentation

Establishing the Online Query in BSPs

Online script queries are created in projects by firstly creating a new query, then setting its type to 'Script Query', and setting the 'Online Query' property of the query to 'true'. This will change the type of query editor in BrightBuilder to one that is suited to Online scripts, with Tables, Parameters and Output Fields.

Setting the online property to true will signal to BrightServer to call the execute() method for the defined data source with the specified payload data. This will occur when the query is called via a sync rule. The query will be highlighted as an online query as it will appear red in the projects tree.

The data source which the query operates on may be scanned from servers or existing projects, and added to the BSP via the 'Discover Data Sources From Server/File' options in the project's context. If this is not provided for the query, the default data source for the BrightServer instance will be used.

When editing the query, the outputs of the query are handled by the 'Tables' and 'Output Fields' tabs of the query editor. The tables and fields the query is to return in its result set must be defined via the existing tables within the project. This is done by selecting the parent and child tables with these fields, and then clicking on the 'Output Fields' tab to specify the exact fields and order they will appear in when returned by the BEP's execute() method.

Both the fields specified in the script's field info, and the types of the rows for the BSP's table(s) must match for the query to run successfully.

The parameters tab of the query editor allows the user to specify a parameter name for the online query's payload. This is mainly used for the sync rule, but may also be used when referencing the query data directly via Queries in the DST.

As the query is online, data will only be present for the query when there has been a synchronisation from the client with the sync rule and payload to the server. This may be achieved by creating a new sync rule, specifying the query under the 'Query' tab, and then assigning the payload parameter. This is typically set to reference either a constant, a global variable or a system app setting.

An example of a sync rule running for the online query is as follows:

// The following code will run the online script via the sync

Synchroniser.DisableAll();

Synchroniser.EnableSyncRule("SyncScriptOnline", 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("qSyncScriptOnline");

WHILE(Query.HasRecord())

{

local.vCodes = local.vCodes & Query.GetColumnValue("OP_TYPE") & "=" &

Query.GetColumnValue("OP_RESULT") & CHAR_NEWLINE;

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

Configure a BrightServer instance to run the configuration specified in ScriptOnline.bep. It contains a script which takes a string payload of comma separated integers. The sum, factorial, mean and median of the numbers are then populated in a resultset and sent to the device on the execute() call.

The application is designed to interface this online script by accepting a string payload in an edit control, and passing it to the server and script once the 'Calculate' button is tapped. Once the calculations are made server side, the application then displays the results returned from the query in its listview.

In order to have the application do this, perform the following on ScriptOnlineStart.bsp:

Create the new script online query
Discover the data source for the query from the Server.
Create a sync rule for the query, specifying the payload as the text edit control. Have this sync rule run when the Calculate button is tapped.
Have the listview reference the query, displaying the results in the grid.

A sample solution is contained in ScriptOnlineFinished.bsp.