SetSelectString

SetSelectString(datasource, selectstring, params)

Specify a SQL Select statement which can be used with other Methods taking a SQL Select statement as a parameter or to define the records included in a Custom View.

For Synchronise, LoadDataSource and LoadDatabase, SetSelectString can be used to define the set of records to be downloaded to the mobile device from a remote Target Data Source.

For LoadDatabase this Method also allows you to create a list of Data Sources which should be included in the next download.

For FindRecord, GetNumRecordsWhere and SelectLocal, SetSelectString allows you to specify the record set to be searched, counted or copied, respectively. With these Methods, SetSelectString passes data values to the SQL Select statement as parameters for greater security.

All

datasource - required

Specifies the Data Source to be acted on – click here for the format of this parameter.

For Synchronise, LoadDataSource, LoadDatabase and to define the records in a Custom View, if you want to specify a SQL select statement in the selectstring parameter below, the Data Source specified here must be an ADO Database Data Source, a Custom Table (which has its Mode Property set to Remote ADO) or a Custom View. The remote Target DataSource must be a database which is SQL compatible.

Note, however, that if you want to include database and web service Data Sources in the same call to LoadDatabase, you will need to use SetSelectString to add the required Web Service Data Sources to the list of Data Sources to be included – see Notes below.

For FindRecord, GetNumRecordsWhere and SelectLocal, the Data Source can be any supported Data Source except Custom Views.

 

selectstring - required

For Synchronise, LoadDataSource and LoadDatabase:

• This parameter should contain the SQL Select statement specifying the records to be selected for the transfer or Custom View. Must contain a valid SQL Select string.

• If datasource is a Custom Table, this parameter should contain the full Select statement e.g. "SELECT * FROM table1 WHERE user = 'John'". The SELECT statement can start with SELECT or SELECT DISTINCT and columns can be referred to using the format <table>.<column> but <table>.* is not supported. Only Custom Tables where the Mode Property is set to Remote ADO can be referenced in SetSelectString.

• If datasource is not a Custom Table this parameter should contain only a 'where' clause, excluding the word 'where', e.g. "user = 'John'". Columns can be referred to using the format <table>.<column> but <table>.* is not supported.

• When specifying your SQL Select statement you may want to specify the order in which records should be returned by including an ORDER BY clause, e.g. "SELECT * FROM table1 WHERE user = 'John' ORDER BY table1.userID" or "user = 'John' ORDER BY table1.userID". If you want to include an ORDER BY clause, it must be part of a WHERE clause; you cannot specify it on its own – e.g. "user = 'John' ORDER BY table1.userID" would be valid but "ORDER BY table1.userID" would not.

• For Data Sources other than Custom Views, if you specify this parameter as an empty string, "", all records and all columns for the specified table will be included, unless a SQL Select statement has been specified in a table's Filter or SQL Statement Property, in which case this statement will be used to determine which records and columns to download. The Filter Property applies to ADO Database Data Sources and the SQL Statement Property to Custom Tables where the table's Mode Property is set to Remote ADO.

  For Custom Views, specifying an empty string will return the View to using the default SQL Select statement specified when the View was created.

• For Custom Views, the SQL statement will be run on the local device and hence must be SQLite compatible. For other Data Sources, the SQL statement will be passed to the remote Target data source and used to extract data from the remote database and must, therefore, be compatible with the SQL supported by the remote database.

• If the Data Source specified in the datasource parameter above is a Web Service Data Source, you must specify this parameter as an empty string, "".

 

For FindRecord and GetNumRecordsWhere:

• If datasource is a Custom Table where the Mode Property is set to Remote ADO, this parameter should contain the full Select statement e.g. "SELECT * FROM table1 WHERE user = 'John'". The SELECT statement can start with SELECT or SELECT DISTINCT and columns can be referred to using the format <table>.<column> but <table>.* is not supported.

• For any other datasource, this parameter should contain only a 'where' clause, excluding the word 'where', e.g. "user = 'John'". Columns can be referred to using the format <table>.<column> but <table>.* is not supported.

 

For SelectLocal:

• This parameter should contain a full Select statement e.g. "SELECT * FROM table1 WHERE user = 'John'". The SELECT statement can start with SELECT or SELECT DISTINCT and columns can be referred to using the format <table>.<column> but <table>.* is not supported.

 

For FindRecord, GetNumRecordsWhere and SelectLocal:

• When specifying your SQL Select statement you may want to specify the order in which records should be returned by including an ORDER BY clause, e.g. "SELECT * FROM table1 WHERE user = 'John' ORDER BY table1.userID" or "user = 'John' ORDER BY table1.userID".

• The SQL statement will be run on the local device and hence must be SQLite compatible.

• With these Methods, you can pass data values to the Select statement as parameters. Where you want to pass a parameter substitute the value in the Select statement with a '?', e.g.:

  SetSelectString("db.Users", "SELECT UserID FROM Users WHERE FirstName=? AND LastName=?", userparams)

  In this example, the first and last name column values will be supplied as parameters to the SQL Select statement when the statement is passed to the SQL engine. The values are contained in an array variable whose name is specified in the params parameter, see below.

 

params - optional

This parameter is only relevant for use with FindRecord, GetNumRecordsWhere and SelectLocal. In all other cases, you must omit this parameter.

This parameter specifies the name of an array variable containing the data values to be passed as parameters to the SQL Select statement specified in the selectstring parameter above.

Using the example under the selectstring parameter above, there are two values to be substituted in the Select statement:

SetSelectString("db.Users", "SELECT UserID FROM Users WHERE FirstName=? AND LastName=?", userparams)

In this example, the FirstName and LastName column values have been replaced with '?' meaning that their values will be passed to the SQL engine as parameters to the Select statement. The final parameter specifies the name of the array variable containing the parameter values, in this case the array is called userparams.

The specified array should contain the same number of items as parameters required. For example:

Dim userparams(2)

userparams(0) = GetControlValue("edtFirstName")

userparams(1) = GetControlValue("edtLastName")

SetSelectString("db.Users", "SELECT UserID FROM Users WHERE FirstName=? AND LastName=?", userparams)

SelectLocal("", "cust.Users")

nothing

• When using SetSelectString with Custom Views, calls to this Method may fail if the Custom View definition contains clauses such as ORDER BY, GROUP BY, HAVING, OFFSET etc. In this case, you can workaround the issue by creating a second Custom View on the original Custom View which simply selects all the data in the original view and then use this second View within your SetSelectString parameters.

• When using SetSelectString with FindRecord, GetNumRecordsWhere and SelectLocal, calls to SetSelectString in which the params parameter is omitted do not affect FindRecord, GetNumRecordsWhere and SelectLocal. When calls to Synchronise, LoadDataSource and LoadDatabase clear the Data Source list (see below) this does not affect previous calls to SetSelectString in relation to its use with FindRecord, GetNumRecordsWhere and SelectLocal.

  Likewise calls to SetSelectString in which the params parameter is included do not affect previous calls to SetSelectString in relation to the use of this Method with Synchronise, LoadDataSource and LoadDatabase. Calls to FindRecord, GetNumRecordsWhere and SelectLocal do not clear the Data Source list.

  If you don't want to pass a parameterised Select statement when calling FindRecord, GetNumRecordsWhere or SelectLocal, you must specify the SQL Select statement as a parameter within the actual call to the Method, e.g. FindRecord("MyDataSrc.Users", "user=John", 0), and not via SetSelectString.

 

The remaining notes refer only to the use of SetSelectString in conjunction with the Synchronise, LoadDataSource and LoadDatabase Methods and when specifying the records to be included in a Custom View.

• SetSelectString provides a means to specify a SQL Select statement to define a set of records to be downloaded from a remote database data source to a mobile device or to specify the record set for a Custom View.

  For downloads the SQL Select statement will be sent to the App Server to tell it which records you want to download from the remote database, for example to download a list of jobs assigned to the user from a back-office system. For Custom Views the Select statement is used to select records on the local device, e.g. to allow the user to filter the records displayed in a ListView. This Method provides a mechanism to allow you to customise the SQL Select statement defined when the Custom View was created.

  In addition, when using the LoadDatabase Method to download data, SetSelectString can be used to specify which of the Data Sources using the local database specified in the call to LoadDatabase should be included in the download.

• When you first call SetSelectString within an app and specify a Data Source which is not a Custom View, it creates a Data Source list and adds the specified Data Source to the list. Subsequent calls to SetSelectString add additional Data Sources to the list. If a list exists it may be used by the Synchronise, LoadDataSource or LoadDatabase Methods.

  After a call to any of Synchronise, LoadDataSource or LoadDatabase the current Data Source list is cleared and each of these Methods reverts to its default mode of operation unless and until another call to SetSelectString is made.

  With Synchronise, SetSelectString allows you to override a SQL Select statement specified in the Filter/SQL Statement Property for a Data Source Table.

  With LoadDataSource, SetSelectString allows you to override a SQL Select statement specified in the Filter/SQL Statement Property for a Data Source Table, but if you include a SQL statement in LoadDataSource's selectstring parameter this will be used in preference to any other SQL statement specified elsewhere.

  With LoadDatabase, if a Data Source list exists, only those Data Sources in the download list will be downloaded to the mobile device. The SQL Select statement specified in the list for an individual Data Source will be used to specify the record set to be downloaded for that Data Source.

• You can change a SQL statement specified in the Filter/SQL Statement Property for a Data Source within App Manager. This allows you to change the records to be selected without having to edit and republish your app.

 

• If you want to download both database and Web Service Data Sources in a single call to LoadDatabase and you are using SetSelectString to specify the list of Data Sources to download, you will need to include a SetSelectString call for each Web Service Data Source as well as each database Data Source. With the Web Service calls, specify the selectstring parameter as an empty string.

• With Custom Views, a call to SetSelectString will immediately drop the current View and recreate it using the 'where' clause specified here, either in place of any 'where' clause specified in the original SQL Select statement or by adding a 'where' clause to the original SQL statement if no 'where' clause was originally specified. You can use this Method to change the data records visible via the View at any time within your Scripts.

  This View will persist until you call SetSelectString again or close down the Digitise app. Calls to Synchronise, LoadDataSource or LoadDatabase do not affect the current View. To return to the original default SQL statement specified when the View was originally defined, call SetSelectString specifying the selectstring parameter as an empty string.