Quick Tutorial - How to Use Stored Procedures

Description

This tutorial will explain how to set up, end-to-end, a database stored procedure call to the server from a client device.

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.

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.

There is also further support in the BrightXpress mobility suite for online queries, in the form of Stored Procedures and Remote Procedure Calls (RPC). They are exclusively online, and acts as a low overhead operation which still enables developers to incorporate specific routines for their mobile application through the server, process any range of inputs, outputs to and from the server, with addition to a record set if necessary.

Stored Procedures refer to procedures that are located on the server database. It requires a default database defined in the BEP configuration file in BrightServer, and requires the named stored procedure to be present. Any stored procedure on the SQL server may be called, provided all inputs/outputs are specified. After calling the stored procedure, the database will return parameters and outputs to BrightServer, which will then send the online query resultset to BrightForms.

The stored procedure feature of the BrightXpress mobility platform is a powerful tool, allowing the retrieval and processing any information in the backend, thus offering much greater flexibility for developers and applications on the platform. This tutorial will demonstrate how to interface with an existing stored procedure on the database, and how to call it in the application by:

Setting up the BEP's database sync point, and
Setting up the BSP's queries, data sources and point of execution

A sample configuration and application is also provided, demonstrating this process.

BEP Setup

A stored procedure must firstly have its database defined in the BEP, and be exposed as a data source to BrightServer. These are defined using the Sync Points in BrightBuilder BEP projects.

Any database that is used for online querying and/or stored procedures must 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 'SyncExampleSp', a sync process containing a stored procedure.

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 stored procedure and populate the output parameters and the resultset of the query as defined.

Setting up a new query

Create a new query of the type 'Stored Procedure/RPC". As this type of query is online only, BrightBuilder will automatically set the 'online query' property to true. This may be confirmed, as a query set to online will have its element icon in the projects tree turn red. If this is not checked, the project have a validation error once the project is deployed or released.

While this process will define that a stored procedure or a RPC will be used, it does not define which one will run, and on what data source. For this to be established, the query's Data Source from BrightServer needs to be defined, and these sources need to be discovered for the project. If no source is specified, the default data source will be used.

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.

Defining the query

Once the destination of the query has been defined with the query's data source, the particulars such as the inputs, outputs and records returned may be specified. This may be done with the query's panel of BrightBuilder.

When this screen opens, the input fields available are follows (top to bottom):

The name of the stored procedure as specified on the database should be defined in the 'Name' field.
Define input, output, and (single) in/out parameters. Inputs/In-Outs may be parameterised, and when ticked appear in the DST, or they may be static and defined as inputs.
Define output result set. This may consist of any number of columns, and must match the result set returned by the stored procedure/RPC.

Using the Query 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 instance when the stored procedure is called.

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 stored procedure via the sync dialog

Synchroniser.DisableAll();

Synchroniser.EnableSyncRule("SyncExampleSp", true);

Form.ShowSyncDialog(false);

One the sync rule runs, any in/out or output parameters will be populated, and any subsequent reference to the query will use the recordset returned. Its previous results will be discarded. 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.

// The following is run after the sync rule executes, thus populating the output fields

editCustCode = qSyncExampleSp.pCustCode;

// refresh listview containing the query, will be updated with most recently retrieved resultset

listview1.refresh();

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.

Stored Procedures and BrightWeb

The concept and implementation of Stored Procedures as described above is only applicable to the 'Occasionally Connected' type of application - giving it a 'Semi Online' method of retrieving data. As BrightWeb is 'Always Connected' - purely online, it does not utilise the synchronisation engine, and thus needs to execute stored procedures via alternate means.

This is done via calling the stored procedure itself via the Query object. For example, the following stored procedure via synchronisation:

In web mode, would be:

local.vSql = "exec " & "sp_customerlist" & " " & gVarUserId & "," & gVarAddNew & ",NULL";

Database.ExecuteUpdate(local.vSql);

Exercise

Application

The example is of an application which allows the end user (with user number 42) to create a new customer record, whose ID is uniquely populated by the server. This may be necessary if the application required customer numbers to be created on the fly and be unique, but also requires its use as a reference in many records.

The user may then assign a name to the record ID, and the record is sent to the server on the 'Update + Send' button tap. After sending the data once more, the list of assigned IDs is then refreshed from the server, and displayed.

BEP File

The creation of new customers and assignment of codes to PDA users is handled by BrightServer and the stored procedure on the database. Both the creation and retrieval of assigned records is handled by the same SP, with the device specifying if it needs a new record, or to simply retrieve the list via an input parameter. Once the customer codes have been retrieved by the device, the application may be used to update the records based off this code via conventional synchronisation.

To set the BEP for this process up, create a database on localhost named "BrightTutorial".

Run the two sql files in the following order:

After completing the above, execute the BEP server configuration or deploy/activate it on a BrightServer instance.

BSP File

After opening StoredProcStart.bsp in BrightBuilder, complete the exercise by linking the BSP's queries, sync rules and listview control to the database's stored procedure attached ('sp_customerlist'). Review where necessary the stored procedure for the inputs/outputs of each to transfer into the BSP.

A possible solution is available in StoredProcFinished.bsp.