DBListGet

Description:              This function executes in its own thread to retrieve certain records from a list in a VTS database and returns an indication of parameter errors.

Returns:                    Numeric

Usage:                       Script

Format:                      DBListGet(DBSysVal, Result, Orientation, List, Start, Number, Method [, Events, IDKey, Filters, Sort, FieldVals])

                             Or

                             DBListGet(DBSysVal, Result, Orientation, List, Start, Number, Method [, Events, IDKey, Filters, Sort, FieldVal1, FieldVal2, FieldVal3, …])

Parameters:             DBSysVal    { handle }  { required }  { no default }
          The database value to use; this is the return value from a DBSystem call.

                                    Result     { Array (return value) }  { required }  { no default }
          A variable in which the resulting array will be stored. The dimension of the array will match the number of fields requested.

                                    Orientation     { numeric }  { required }  { no default }
          Any logical expression that denotes the orientation of the resultant array. If true (non-0) each record retrieved forms its own row, with each column representing a field; if false (0), the reverse holds true. This means that with Orientation set to 1, if 5 fields were requested in the FieldVals parameter(s), the resultant array would be R[n][5], where n is the number of matching records found.

                                    List     { varies }  { required }  { no default }
          Any numeric value or array of numeric values that define(s) the list(s) to search.  

                                      

                             The database and transaction log cannot be combined with other lists.

                                    Start     { numeric }  { required }  { no default }
          Any numeric expression for the first match to include, beginning at 0.

                                    Number     { numeric }  { required }  { no default }
          Any numeric expression for the maximum number of matches to return. This value must not exceed 16375.

                                    Method     { numeric }  { required }  { no default }
          Any numeric expression that determines what elements to include in the list. This parameter is one or a combination of the following values:

 

                             The filtering/sorting parameters follow (in the specified order), with only those that are applicable being included. If the bit for a certain type of filtering has been set, a parameter corresponding to that option must exist; if a bit is not set, the parameter corresponding to that option should not be included, setting it to invalid is not acceptable. If a Sort is requested, but the Sort Options parameter is passed in as invalid, then the function will behave as if a sort had not been requested.

                                    Events     { text }  { optional }  { no default }
          An optional parameter used for filtering the records in the list and should only be included if Method designated event filtering. This value is a text string containing one byte for each list in the system; any missing bytes default to all for that list, while bytes with values not included in the list below default to no matches (i.e. nothing will be found).

                                    Note that there is one byte for each list in the system, not for each list that has been selected by the List parameter.

                             If the transaction log is being used (List = 0), the following codes are compared with the Event field of each record:

                             If one of the lists is being used (List > 0), the following codes are used:

 

                             IDKey     { text }  { optional }  { no default }
          An optional parameter used for designating the record(s) to use and should only be included if Method designated ID key filtering. It is any text expression and can contain the wildcard characters "*" and "?".

                                    Filters     { array }  { optional }  { no default }
          An optional parameter that designates the record(s) to use to filter the resulting list by using logical ANDing and/or ORing and checking for field matches; it should only be included if Method designated record value filtering.

                             At its simplest, this parameter may hold a 1-dimensional array with 2 or 3 numeric elements:

 

 

                             The comparison value is a numeric value or expression that indicates the type of comparison to be made, and may be omitted if desired; in this case, the comparison will be taken to mean "is equal to". Valid values for this third element are as follows:

 

 

                             Notice that the comparison values of 3, 7, 11, and 15 are really only useful when the field value is a text string. For example, if you want to get only those entries whose value starts with "d", the field value should be "d*", and the comparison value should be 3. If however, you want all entries that don't start with "d", the field value should still be "d*", with a comparison value of 7.

                             If a more detailed filtering criterion is required, a 2-dimensional array may be used, where additional rows are added, each with the same elements as the first ([n][0] is field number, [n][1] is limiting value, [n][2] is comparison type). All rows will then be ANDed together to form the filtering statement. Once again, the third column may be omitted entirely, however, if it exists it must have valid values in all rows.

                             The most detailed filtering array occurs when an ORing of field specifications is also required. In this case, this parameter is a 1-dimensional array, where each element contains a pointer to an array as described previously. The elements in each AND array will be ANDed together, then the results from these ANDed arrays will be ORed.

                             VTS differentiates between the various options for this parameter by checking its first element. If it is not a pointer, then the parameter is assumed to contain a single AND array directly (i.e. no OR is performed); it should only be included if Method designated record value filtering.

                                    Sort     { numeric }  { required }  { no default }
          An optional parameter used for defining the type of sort to be done. Currently, only the bin type of sort is supported where, in a single pass through the array, records are grouped together based on having the same value in the specified field. Sorting may only be performed on numeric data.

                             This parameter is a 1-dimensional array (not one that is created via a New function call), whose elements have the following meaning:

 

                             In the case of elements 3 and 4, if the third element is 6, for example, and the fourth element is 5, all records whose specified field has a value from 5 (lower limit) through to 10 (includes 6 elements) will be sorted, while all others will be discarded.

                                    FieldVals / FieldValN     { numeric }  { required }  { no default }
          An optional parameter or series of parameters that is either an array or a list of values that indicate which field(s) for which data is requested. If omitted, all fields are returned in their default order. Field numbers range from 1 to 255; the valid values for this parameter are:

 

 

                             Note: In the case of an array, it must be a static array – a dynamically declared array (one that is created via a New function call), it will not work here.

Comments:               This function executes in the thread created by the DBSystem call, so it will not block other statements from executing. This does mean, however, that the timing for Result becoming valid is unpredictable and should therefore be checked for validity prior to being used.

                             The return value for this function indicates if any of its key parameters (DBSysVal, Orientation, List, Start, Number or Method) are invalid:

                             If all of the key parameters are valid, DBListGet will immediately return a value of false (0).

                             If any of the key parameters are invalid, DBListGet will not perform the required operation, and will instead immediately return a value of 1.

                             If the database uses lists then the list parameter may be greater than 0 to select values that appear on that list.  For example, when retrieving alarms from their db instance, the following values can be used:

 

                             For information about the standard alarm lists in VTS see Alarm Manager Service.

                             Note that the return value only signals completion of the function's execution if it is true, otherwise the function will continue executing in the thread created for it.

                             The dimension of Result will depend on the number of fields requested.

                             If the database file has its read-only attribute set when this function is executed, it will be cleared automatically by execution of the function.

Example:

dbVal = DBSystem("c:\vts5\app6\equip.db", "", 0, 0, 64 { key },

    3 { field 1 }, -2 { field 2 }, -3 { field 3 }); 

If Valid(dbVal) && ! retrieved;

[

  retrieved = 1; 

  DBListGet(dbVal { Database value }, 

            final { Resultant array }, 

            0 { Orientation }, 

            1 { Use entire database }, 

            0 { Include from first match on }, 

            20 { Number of matches to get }, 

            2 { Method - filter by ID key }, 

            { Events parameter not required } 

            "Motor*" { Match with ID key }, 

            { Filters/Sort parameters not required } 

            2 { Get ID key field }, 

            2 { Get field 2 also }); 

]

If there is any doubt as to the validity of the parameters and further statements rely on final becoming valid, the following version of the script might be more appropriate.

If Valid(dbVal) && ! retrieved;

[

  retrieved = 1; 

  IfThen(DBListGet(dbVal, final, 0, -1, 0, 20, 2, 

         "Motor*", -2, 2), 

  final = 0; 

  ); 

]

See Also:

DBAdd | DBGetStream | DBListSize | DBRemove | DBSystem | DBTransaction | DBUpdate | DBValue