Select Method for LCConnection

This method produces a result set from the current METADATA property and other properties.

Defined In

LCConnection

Syntax

count = lcConnection.Select (keyFieldlist, recordIndex, destFieldlist)

Parameters


Parameter	Description
keyFieldlist	LCFieldlist. Selection keys. All fields in KeyFieldlist with the LCFIELDF_KEY flag set are used as the selection keys. Only records in the connection with the same value for key fields will be selected. Additional LCFIELDF_KEY_XXX flags (GT -- greater than, LT -- less than, NE -- not equal) allow inequality keys to be used. Zero or more key fields may be supplied to restrict the result set. No keys or Nothing will select all records for the result set.
recordIndex	Long, >=1. Record index position in the key fieldlist from which to obtain the record containing key field values.
destFieldlist	LCFieldlist. Fieldlist to contain the metadata of the selected result set. The fields in the result set will be appended to this fieldlist. If the result set metadata is not required, use Nothing.

Return Value


Value	Description
count	Long. Number of records in the result set. If this number cannot be determined by the connector, the constant LCCOUNT_UNKNOWN is returned.

Usage Notes®

The following considerations are helpful when working with the Select method for LCConnection.

The Select call adds fields to the destFieldlist; any fields already there will not be removed or reassigned, even if their names duplicate the names of the fields in your metadata. For this reason, the destFieldlist should generally not contain any fields before you issue a Select call.
To remove all existing fields from destFieldlist in order to reuse it for multiple Select calls, discard the old fieldlist and create a new one as follows:

Set destFieldlist = New LCFieldList(args)

If this approach is not convenient, perhaps because you want to map fields in the records you fetch to fields in another fieldlist, there is an alternative. If you already know what fields are in the metadata, rather than fetch the data into the same fieldlist returned by the select statement, use the keyword Nothing for the destFieldlist argument and use a fieldlist that you have already created manually to do your fetching.
If you supply a key fieldlist, the selection conditions in the fieldlist will be "and"ed together -- LCConnection will only select records that match all the conditions. Only the key list fields with the LCFIELDF_KEY flag set, will be used as selection criteria.
If you set the Condition property of the connection, this search condition also will be "and"ed with the conditions in the key list to further limit the records returned.
LCConnection does not directly support logical "or" operations of multiple conditions. You can, however, compose the query yourself using the back end system's query language. Either use LCConnection.Execute to execute the custom query, or use the LCConnection.Condition property with Select. See the documentation for the connector for information about its supported syntax.
Set the Writeback property before using Select to indicate whether you want the result set to be writeback capable. This does not affect which records are returned in the result set, but it does affect whether you can do a writeback Update or Remove on them as you're iterating through them.
When using inequality key flags GT, LT, and NE, it is important to remember that the default of no flags is equal. The following combinations are valid for inequality flags:
- equal to LCFIELDF_KEY
- greater than or equal to LCFIELDF_KEY + LCFIELDF_KEY_GT
- less than or equal to LCFIELDF_KEY + LCFIELDF_KEY_LT
- not equal to LCFIELDF_KEY + LCFIELDF_KEY_NE
- greater than LCFIELDF_KEY + LCFIELDF_KEY_GT + LCFIELDF_KEY_NE
- less than LCFIELDF_KEY + LCFIELDF_KEY_LT + LCFIELDF_KEY_NE
- string pattern matching LCFIELDF_KEY + LCFIELDF_KEY_LIKE
The rules for pattern matching in a "like" comparison vary depending on the connector. Consult your database documentation for details.

Example

Option Public
Uselsx "*lsxlc"

Sub Initialize
  Dim src As New LCConnection ("db2") 
  Dim fldLst As New LCFieldList
  Dim count As Integer

  ' set the appropriate properties to connect to the data sources
  src.Database = "Gold"
  src.Userid = "JDoe"
  src.Password = "xyzzy"
  src.Metadata = "customer"

  ' connect to the two data sources
  src.Connect

  ' now connected, we can execute a selection statement
  count = src.Select (Nothing, 1, fldLst)
  Select Case count
  Case LCCOUNT_UNKNOWN
  Print "An overview number of records were located."
  Case 0
    Print "No data were located."
  Case Else
    Print "The table contains " & Cstr(count) & " records."
  End Select
End Sub

Example Output

An overview number of records were located.

Select Method for LCConnection for DB2®

This method produces a result set from the defined METADATA object in DB2®.

The following properties are used by Select to refine the result set contents and properties:


Properties	Description
METADATA	Indicates the metadata in the connection containing the target records.
WRITEBACK	If supported, determines whether to produce a writeback result set or not. If TRUE, the result set produced should support writeback versions of LCXUpdate and LCXRemove. A writeback result set generally locks the source metadata.
FIELDNAMES	Provides a list of fields to include in the result set. Only fields in this text list should be included in the result set, in the order provided. The fields in the fieldlist will be in the order in this list (to order the actual record values, use ORDERNAMES). Specify no value to include all metadata fields. Multiple fields can be specified with a text list or as text with comma or semicolon separated values.
ORDERNAMES	Provides a text list of fields by which to order the result set data in ascending order, from first to last. Specify no value for an unordered result set. Multiple fields can be specified with a text list or as text with comma or semicolon separated values.
CONDITION	Provides a conditional clause to be embedded in the internal statement produced. This value is supplied in a syntax defined by each individual connector.
STAMPFIELD	Provides a field name to be used as a timestamp/boundary. If this value is set, then the connector should check BASESTAMP for a minimum datetime value. In addition, the connector should request the current time from the data provider, set the MAXSTAMP property to this value, and include it as an upper time bound.
BASESTAMP	If STAMPFIELD is set, provides the base datetime for the selection. Only select records with STAMPFIELD greater than BASESTAMP. Ignore this property if STAMPFIELD is not set.
MAXSTAMP	If STAMPFIELD is set, obtains the current datetime from the data source and sets it as the value of this property. Only select records with STAMPFIELD less than or equal to this value. Ignore this property if STAMPFIELD is not set.
SCROLLABLE	If supported, determines whether the result set produced should support backwards movement as well as forwards.
RECORD_LIMIT	If supported, any result set produced should be limited to this many records. A value of zero (the default) indicates no limit.

Key Flag Rules

Keys in the key fieldlist provide conditional key values. The default condition is equality, but field flags allow other options.

When checking inequality key flags GT, LT, and NE, the default of no flags is equal.

The following combinations are valid for inequality flags:

equal to LCFIELDF_KEY
greater than or equal to LCFIELDF_KEY + LCFIELDF_KEY_GT
less than or equal to LCFIELDF_KEY + LCFIELDF_KEY_LT
not equal to LCFIELDF_KEY + LCFIELDF_KEY_NE
greater than LCFIELDF_KEY + LCFIELDF_KEY_GT + LCFIELDF_KEY_NE
less than LCFIELDF_KEY + LCFIELDF_KEY_LT + LCFIELDF_KEY_NE

In addition, the flag LCFIELDF_KEY_LIKE may be combined with LCFIELDF_KEY to perform a similarity comparison. Support for and the behavior of a LIKE comparison depends on the connector being used.

Note: The LIKE key flag may not be used in conjunction with any inequality key flags.