LotusScript provides an integral programming interface to Lotus Notes. The LotusScript Extension for Lotus Domino Connectors enhances the power of Notes by extending its scripting to data outside of Notes. The programming model is independent of the individual Connector. This eliminates the need to learn each individual system, while at the same time allowing experienced users to access the individual features of a specific system.

For example, through Lotus Connectors, Notes and web applications have the ability to retrieve and act upon data within agents, during document events, or at the click of a button.

This release of the LSX LC supports access to the following Connectors:

· DB2/UDB

· EDA/SQL

· File System

· Notes

· ODBC

· Oracle

· Sybase

It is important to note that the LotusScript Extension for Lotus Domino Connectors may be used alone or in conjunction with the Domino Enterprise Connection Services (DECS). Respectively, these two technologies provide programmatic and declarative access to external data for application development.

Connectivity Software Requirements

Access to supported Lotus Connectors may require software to be installed on the Domino Server or the Notes client from which the Lotus Connector scripts are run. Refer to the Domino Connectors Setup Guide for information about software that may be required in order to access any particular data source. The Domino Connectors Setup Guide is provided online in NSF format (LCCON.NSF).

Registration and Loading of the LSX LC

The LSX LC is registered when the Domino Server is installed.

You must load the LotusScript Extensions for Lotus Connectors using the UseLSX "*lsxlc"statement in the script.

Terms and Concepts

Metadata

This is a generic term referring to a Connector's data definition. The data definition includes the names of data elements, their datatypes, and implies the order of the elements. For example, Notes uses 'forms' to describe both the names of data fields as well as the data type of each field; Sybase metadata describes a 'table'. Refer to Appendix C, "Connector Properties", for information specific to each Connector metadata.

Alternate Metadata

Available through some Lotus Connectors, this is an alternate source for the data definition. For example, DB2 metadata is in the form of a 'table', while its alternate metadata form is a 'SQL view'. Refer to Appendix C, "Connector Properties", for information on whether a specific Connector supports alternate metadata.

Result Set

The return from an information request through a connection. Each connection can have a single active result set. The LCConnection methods Execute, Select, Call and Catalog each produce a result set, replacing any existing result set. The result set describes the collection of data or information from the connection, which matched the input criteria. It does not return the actual data until fetched. If desired, these methods will build a fieldlist representing the metadata as part of generating the result set.

When using other connection methods which read or write data of a result set, the implied order of the metadata may be suspended by using the connection's OrderByName property to indicate that, regardless of the order of the data elements within the metadata, match the names in the metadata to the names in the external system.

Writeback Result Set

A Writeback result set is an optimized form of result set supported by some Connectors. A Writeback result set provides both sequential read and write operations on the data, and may be used for efficient Update and Remove operations by directly operating on the most recently fetched record in the result set, rather than having to locate the information in the external system a second time. Some Connectors may implement locking in the back end for Writeback result sets.

Token

A token is an integer used to identify a property of a connection. All connection properties have a token value. Common properties have predefined tokens represented by constants. Connection-specific properties do not have predefined tokens. (For a list of property tokens and names for each connection, see Appendix C.) Connection properties may be accessed by token or by name. The token method may be used when testing if a property is supported. The name may be used when it is known that a given property exists.

LSX LC Classes

The Lotus Connectors provides external data and system access to the Domino LotusScript environment. The LSX LC classes consist of LCConnection, LCFieldlist and LCField, and four advanced datatypes, LCStream, LCNumeric, LCCurrency, and LCDatetime. In addition to these 7 classes, there is also an LCSession class. Each of these classes and their primary usage is described below:

LCSession

The LCSession class provides error information useful in error handlers. It also provides for query and lookup of available Lotus Connectors.

LCConnection

The LCConnection class represents an instance of a Lotus Connector. This class provides query and data access to the external system. Multiple connections may be allocated to a single Connector.

LCFieldlist

The LCFieldlist class is the primary class for manipulating data through a connection. It binds a group of fields together with names and an implied order.

Fieldlists are used primarily for a number of connection operations; result sets and selection criteria, as well as reading and writing data.

When a result set is generated, and an empty fieldlist was initially passed in, the fieldlist is automatically populated by the Connector. For each data element of the result set, the fieldlist receives the element's name and a field object of the corresponding datatype. The result set may be controlled by manually building the fieldlist before the result set is constructed or by using the FieldNames property of the connection.

The Select and Call connection methods use an optional fieldlist of keys or parameters to restrict the result set. This fiedllist is manually constructed and passed to the select method. A key or parameter list is constructed by appending or inserting names and datatypes to the list. These methods create fields in the fieldlist and return these fields for further manipulation. These fields are then given values and, using field flags, may be given conditions such as greater-than, not-equal, etc.

LCField

LCField is the storage class that contains one or more data values. The datatype of a field is for all values contained within and may be any of the four advanced datatypes below, as well as long integer and double precision floating point, and, in some advanced usage, fieldlist or connection.

LCStream

LCStream is a general purpose text and binary datatype. The contents of a stream are marked with a format that details the character set of the text or any special attributes of the binary data.

LCNumeric

The LCNumeric class is a container for very high precision numbers.

LCCurrency

The LCCurrency class is a fixed point decimal datatype with 4 decimal places and 19 digits of precision. (This is mathematically equivalent to the LotusScript datatype and is provided to support connections with a dedicated currency.)

LCDatetime

The LCDatetime class is a date and time datatype which is accurate to the hundredth of a second and which is aware of time zones and daylight savings time.

Working with the LSX

A typical use of the Lotus 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 script to take the form data and establish a connection to Oracle, locating corresponding information form one or more tables and updating the Notes form. The script is responsible for:

1. accessing the active Note form

2. gathering the input data

3. create 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 location 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 Lotus Connectors. The UseLSX statement accomplishes this step. Additional Options may be used to check variables, simplify string comparison, etc.

Option Public
Option Explicit
Uselsx "*lsxlc"

The remainder of the script is located in the 'Click' event of the form's button. Errors should be displayed to the user. A simple error handler is written at the bottom 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 Lotus 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 Lotus 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. This 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 datatype to match the LSX LC datatypes.

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. Using the expanded class properties locates the field each time it is used and allocates an array of values, not just a single value. Here is an sample of this code. The %REM has been used to indicate that this code is not part of the actual example.

%REM
Dim address as LCField
Dim city as LCField
Dim state as LCField
Dim phone as LCField

Set address = fieldlist.Lookup ("Address")
Set city = fieldlist.Lookup ("City")
Set state = fieldlist.Lookup ("State")
Set phone = fieldlist.Lookup ("OfficePhone")

while (src.Fetch (fields) > 0) Then
Call uidoc.FieldAppendText ("Address", address.Text(0))
Call uidoc.FieldAppendText ("City", city.Text(0))
Call uidoc.FieldAppendText ("State", state.Text(0))
Call uidoc.FieldAppendText ("Phone", phone.Text(0)) End If
%END REM

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 Lotus Connectors, the classes, and the relationship between the connection, metadata result set, fieldlist, fields and data.