DBListGet

(Engine-Level Function)

Maintained only to provide backward compatibility with legacy applications. Do not use in new code.

Description:	Executes in its own thread to retrieve certain records from a list in a VTScada database and returns an indication of parameter errors.
Returns:	Numeric
Usage:	Script Only.
Function Groups:	Database and Data Source
Related to:	DBAdd \| DBInsert \| DBGetStream \| DBListSize \| DBRemove \| DBSystem \| DBTransaction \| DBUpdate \| DBValue
Threaded:	Yes
Format:	DBListGet(DBSysVal, Result, Orientation, List, Start, Number, Method [, Events, IDKey, Filters, Sort, StartTime, StopTime, FieldVals]) (Note that FieldVals may be an array or a series of parameters: Field1, Field2, etc.)
Parameters:

DBSysVal

Required. The database value to use. This is the return value from a DBSystem call.

Result

Required. A variable in which the resulting array will be stored. The dimension of the array will match the number of fields requested.

Orientation

Required. 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

Required. Any numeric value or array of numeric values that define(s) the list(s) to search.

(See: Bitwise Parameters & Operations)

Value	List
-1	Entire database
0	Transaction log
Number, starting at 1	The specific list number

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

Start

Required. Any numeric expression for the first match to include, beginning at 0.

Number

Required. Any numeric expression for the maximum number of matches to return.

Method

Required. Any numeric expression that determines what elements to include in the list. This parameter is one or a combination of the following values

Method	Bit No.	Method Description
0	-	No filtering (include everything)
1	0	Event filtering
2	1	ID key filtering
4	2	Record value filtering
8	3	Sort as per the Sort parameter
16	4	Limits the records returned to the times supplied in the StartTime and StopTime parameters
32	5	Pull records starting from the current offset or record position in the file. Applies only to VTScada database log files and only if they are not sorted.

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.

Notes for bit 5: do not use a number of matches to skip, even though this would work properly. (That is, starting from the existing position in the log file, n matches would be skipped.) Care must be taken, since if something else changes the current position in the file (a transaction or another DBListGet), then you may not get the records you are expecting. After the initial DBListGet, subsequent DBListGet calls can set bit 5, adding a value of 32 to the method, to use the offset as it was left by the last pull.

Events

Optional. Used for filtering the records in the list and should only be included if Method designates event filtering (1). 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 following list 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

Events	Added to List	Removed from List	No Changes
0	none (nothing is ever selected)
1 or +	√
2 or -		√
3	√	√
4 or <Space>			√
5	√		√
6		√	√
7 or *	√	√	√

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

Value	Added to List
0 or 4	None (nothing is ever selected)
1, 5, or +	On the list
2, 6, or -	Not on the list
3, 7, or *	All (everything is selected)

IDKey

Optional. 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

Optional. Designates the record(s) to use to filter the resulting list by using logical ANDing and ORing and checking for field matches. It should be included only if Method designated record value filtering.
At its simplest, this parameter may hold a 1-dimensional array with 2 or 3 numeric elements.

Element	Description
0	Field to use (-1 filters on date/time values. -2 filters on ID key)
1	Limiting value
2	Comparison value

If (and only if) you are creating an alarm filter that is specifically for use only with the alarm name field (\AlarmNameField -2), then two additional parameters are required, having the following values. This allows the translation of the alarm UniqueID, which is held in the record, to the friendly name used in the search string.

Element	Value
3	VTSDB
4	\AlarmSeparatorString

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:

Comparison Value	Comparison	Case Sensitive
0	Equal to	no
1	Greater than	no
2	Less than	no
3	Specified by wildcard (field value is text)	no
4	Not equal to	no
5	Less than or equal to	no
6	Greater than or equal to	no
7	Opposite of wildcard specification (field value is text)	no
8	Equal to	yes
9	Greater than	yes
10	Less than	yes
11	Specified by wildcard (field value is text)	yes
12	Not equal to	yes
13	Less than or equal to	yes
14	Greater than or equal to	yes
15	Opposite of wildcard specification (field value is text) yes
16	Is the specified bit set? 20 (16 + 4) for not-set.	no

Note that the comparison values of 3, 7, 11, and 15 are 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 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.

VTScada 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

Optional. 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 meanings

Element	Description
0	Method of sorting (1) for bin sort
1	Field number to sort on
2	Flag indicating descending order sort
3	Number of values (bins) to use. Valid range is 1 to 1024
4	Lower limit (start) of range

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.

StartTime

Optional. Specifies the oldest record in the file to be used, in the case that bit 4 is set in Method.

StopTime

Optional. Specifyies the newest record in the file to be used, in the case that bit 4 is set in Method.

FieldVals / FieldValN

Optional. A 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

FieldVals	Field Type Attribute
-2	ID key
-1	Date/time value
0	Event/status for record
1	or more Field value

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

Value	List
1	Active alarm list
2	Unacknowledged alarm list
3	Disabled alarm list

For information about the standard alarm lists in VTScada 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;
    ); 
  ]