Working with the LC LSX

A typical use of the Connectors is to gather, create, or modify data in an external system. For example, a Notes® application has a number of data fields on a form. Once the user input is complete, a button activates the script to take the form data and establish a connection to Oracle, locating corresponding information from one or more tables and updating the Notes® form. The script is responsible for the following tasks:

  1. Accessing the active Notes® form
  2. Gathering the input data
  3. Creating a connection to the external system
  4. Selecting the data based on the input values
  5. Loading the data from the external system
  6. Storing the results in the active Notes® form

Here is a simple script to accomplish the task. The assumption is that the Notes® form has a single text input field called "Customer". The script will use the value of the customer field to locate the corresponding customer information in DB2® and return an address and phone number for the customer storing the return values in the form using fields called "Address", "City", "State", and "Phone".

Note: No attempt has been made to prescribe a code style. The practice of grouping object declarations together at the beginning of a script versus locating declarations close to the code is a preference and does not affect the execution. In this example, declarations and code are grouped to facilitate explaining the process.

The first step in writing the script is to load the LotusScript® Extensions for Connectors. The Uselsx statement accomplishes this step. Additional options may be used to check variables, simplify string comparison, and so on. 

Option Public
Option Explicit

Uselsx "*lsxlc"
Note: For HEI you must also use Dim LCSession as a named session (this becomes the default Log Document name) and LCConnections must be dimensioned as existing named connection documents that have been created in the HEI Administrator. See the overview section of each of the method chapters for more information and an example.

The remainder of the script is located in the Click Event of the form's button. Errors are displayed to the user. A simple error handler is written at the end of this example. The LSX Session class has a Status property that may be used to determine if the error handler was triggered by an LSX error or a LotusScript® error. In all cases where the LSX reports an error, the LotusScript® "Error$" will contain error information. However, when first creating LSX objects, the LSX has additional error information not available through the LotusScript® error statements. Creating and initializing the Session status provides this additional information for the error handler.

The creation of the session object is not necessary for normal error handling.

Sub Click (Source as Button)
    On Error Goto Handler
    Dim session As New LCSession
    session.ClearStatus

The input values are in the current active document. This information is accessible via the NotesUIDocument, which may be located through the NotesUIWorkspace from its "CurrentDocument" property.

    Dim wksp As New NotesUIWorkspace
    Dim uidoc As NotesUIDocument
    Set uidoc = wksp.CurrentDocument

The next step establishes a connection to the Connector for DB2®. After the connection has been created, all of its properties are accessible to customize the connection to the target system. Common properties include Database and/or Server, UserID, and Password. Properties are not case sensitive.

For a complete list of properties for each Connector, see Appendix B.

The following code connects to the DB2® system called Rainbow as jdoe with the password gold.

    Dim src As New LCConnection ("db2")
    src.Database = "Rainbow"
    src.UserID = "jdoe"
    src.Password = "gold"
    src.Connect

There are four connection methods for querying through a connection - Catalog, Execute, Select and Call.

  • The Catalog operation is used to return metadata information within the external system, for example, the tables of a DB2® database or the columns of a specific Sybase table. For a complete list of Catalog options, see the Catalog method.

    The remaining methods, Execute, Select and Call, create result sets of data from the connection. The methods differ significantly in their interface.

  • The Execute statement uses a connector-specific command statement to determine the contents of the result set. This interface is helpful when the external system's command structure is familiar and when cross-connector portability is not an issue.
  • The Select method uses a combination of key names, values, and condition flags to indicate the desired contents of the result set. This interface works across connectors and does not require knowledge of the connector's command language.
  • The Call method is similar to Select, but is used for calling back-end procedures or functions. Instead of keys, parameters are provided.

For our example, the important data are stored in the "Customer" DB2® table, as indicated by the Metadata property of the connection. The only record of interest is the customer named by the input value from the Notes® form.

Customer selection is accomplished by creating a key list. The default key flag, LCFIELDF_KEY, indicates match exactly. If an inequality match such as 'greater-than' or 'like' is needed, then the field's flags property would be ORed with the corresponding constant. In all cases, key fields must have the LCFIELDF_KEY constant in addition to any optional conditional flag constants.

    Dim keys As New LCFieldList
    Dim field As LCField

    src.Metadata = "Customer"
    Set field = keys.Append ("Name", LCTYPE_TEXT)
    field.Flags = LCFIELDF_KEY
    field.Text = uidoc.FieldGetText ("Customer")

The Select connection method creates a result set of all records from the external system which match the keylist. If the LotusScript® keyword "Nothing" is substituted for the key list, then all records of the specified metadata would be selected. In this case, all records from the "Customer" DB2® table would be selected. This example is interested in just the customer record matching the input value from the Notes® form. The key list is created to make this restriction.

The fieldlist receiving the result set is currently empty. The selection will populate the fieldlist will the fields from the DB2® table. If all of the fields of the metadata are not needed, the result set may be restricted to just the fields of interest either by creating the fieldlist prior to the selection or by setting the 'FieldNames' property of the connection.

    src.FieldlNames = "Address, City, State, OfficePhone"

The selection returns one of three values: the number of records selected; zero (0) if no matching records were found; or LCCOUNT_UNKNOWN, when records were found but the connection does not know the total. Since a return of zero is the only case where data was not found, it is the test case for error handling or branching.

    Dim fields As New LCFieldList
    If (src.Select (keys, 1, fields) = 0) Then End

A result set has been created and there is a match. The result set has not retrieved the data. The Fetch connection method reads the data from the external system into the fieldlist. The individual data values may be accessed from a fieldlist using the expanded class properties. For each field in a fieldlist, there is a property with the corresponding name. This property is an array of values using the closest available LotusScript® data type to match the LSX LC data types.

    If (src.Fetch (fields) > 0) Then
        Call uidoc.FieldSetText ("Address", fields.Address(0))
        Call uidoc.FieldSetText ("City", fields.City(0))
        Call uidoc.FieldSetText ("State", fields.State(0))
        Call uidoc.FieldSetText ("Phone", fields.OfficePhone(0))
    End If
Note: When writing scripts that act on more than one record, it is more efficient to locate the field from within the fieldlist, outside the loop, and then use the field for data access. Refer to "Performance" in this chapter for details.

The data has been retrieved from the external system and placed in the Notes® form. This completes this example. The final step is to refresh the Notes® document to display the new data to the user.

    uidoc.Refresh
    End

An error handler was designated as the first line of this example. Testing for an LSX error first provides additional information in the case of an object creation error. Without the session object and subsequent test in the error handler, failure while creating a connection to DB2® generates the LotusScript® message, "Error creating product object". However, for the same error condition, the LSX reports "Error: Cannot load LSX library 'db2'."

Handler:
    If (Session.Status <> LCSUCCESS) Then
        Messagebox Session.GetStatusText, 0, "The following Lotus Connector error has occurred"
    Else
        Messagebox Error$, 0, "The following LotusScript error has occurred"
    End If
    End
End Sub

This example is very simple. It is meant only to provide an understanding of the Connectors, the classes, and the relationship between the connection, metadata result set, fieldlist, fields and data.