Accessing external databases through LS:DO using @functions

The following @functions access an external database through ODBC and return a value or list of values:

  • @DbColumn returns all the values in one column of a table, or all the distinct values.
  • @DbLookup returns selected values in one column of a table by matching keys.
  • @DbCommand passes a command to an external DBMS and returns the result.

@DbColumn and @DbLookup can only retrieve data. They can't add, delete, or modify data, or perform other operations. @DbCommand can retrieve data or send other SQL statements that can change data. LotusScript® provides a wider range of capabilities, including the ability to update the external database.

The first four parameters are the same for all three @functions and establish access to the database through ODBC. The parameters are:

  • "ODBC" as a string constant; or "ODBC" : "NoCache"
  • Name of the data source as defined in the table of data sources (odbc.ini in Windows)
  • User ID, list of two user IDs, or a null string, depending on the external data source
  • Password, list of two passwords, or a null string, depending on the external data source
  • (@DbColumn and @DbLookup) Name of the table to be accessed
  • (@DbCommand) Command string to be executed
  • (@DbColumn and @DbLookup) Name of the column to be accessed
  • Option for handling null data returned by the data source
  • (@DbLookup) Name of the column containing the key
  • (@DbLookup) Value of the key as the appropriate data type, or a list
  • (@DbColumn and @DbLookup) List of two elements: "Distinct" as a string argument or null string; "Ascending" or "Descending" as a string argument

Where user IDs and passwords are required, you can specify null strings and let the user supply them when the @function executes.