FORMULA LANGUAGE


@DbLookup (ODBC data source)
Example

Uses data source information from the ODBC.INI file to activate the appropriate ODBC driver. The driver then locates the specified DBMS, table, and column, and returns only the values in that column belonging to records whose value in the key column matches the specified key. You can optionally specify whether the returned list of values is sorted, whether duplicate values are deleted, and how null values are handled.

Note @DbLookup can only retrieve data; it can't add, delete, or modify data.

Syntax

@DbLookup( "ODBC" : cache ; "data_source" ; "user_ID1" : "user_ID2" ; "password1" : "password2" ; "table" ; "column" : "null_handling" ; "key_column" ; "key" ; "Distinct" : "sort" )

Parameters

"ODBC"


cache "data_source"
"user_ID1" : "user_ID2"
"password1" : "password2"
"table"
"column"
"null_handling"
"key_column"
"key"
"Distinct"
"sort"
Return value

valuesFound


Note If you use the option button or the check box user interface for a keywords field, Lotus Domino updates the keyword list only when the document is composed or opened for editing. If you use the Standard user interface for the list, the keyword list is updated every time the document is recalculated.

Specifying the data source

The data source name can contain up to 32 alphanumeric characters.

Specifying IDs and passwords

You only need these arguments if your DBMS requires them.

Instead of storing the IDs in the @DbLookup formula, you can replace them with null strings (""). If an ID is required, the user will be prompted for it. This is useful when you do not want other designers to see IDs, or when you want users to enter their own IDs when accessing external data. However, you must include IDs and passwords in formulas that will run automatically (such as an agent) because those formulas don't prompt for information.

The user IDs and passwords for accessing a data source are required only once per Domino database session as long as that database remains open. If the user opens another Domino database and executes a formula that accesses the same data source, the user ID and password will be required again.

Password parameters are necessary only when ID parameters are specified. Like IDs, passwords can either be stored in the @DbLookup formula, or prompted for by the ODBC driver by substituting the null string. If the database password is null, you can omit it from the formula.

For example, for the full ID/password specification, enter:


Specifying the table name

If the DBMS supports it, you can optionally include the name of the table's owner to remove ambiguity. Use the format "owner_name.table_name", with a period separating the owner name and the table name.

For example:

"dbo.author"

Table can also refer to a database view in the DBMS being accessed.

Specifying null handling

To control how null values are handled, specify one of the following, appended to the column parameter with a colon:


Specifying key_column and key

Use "key_column" to indicate which column to search for the specified "key"; enclose the column name in quotation marks. If the DBMS product uses case-sensitive column names, be sure to use the correct capitalization. The values in the key column do not have to be sorted before you retrieve data with @DbLookup.

Specify a value using the Notes type that agrees with the key column in the data source. For example, specify a number or a number-valued expression when the key column is of any numeric type, such as integer, real, float, or double. If the key is a string (text) value, enclose it in quotation marks. A date-time value must be entered in the format of the database, not that of Lotus Domino; for example, use 1996-01-31-12.00.00 for DB2/2, not 1996-01-31-12:00:00.

Together, the key column and the key form the "where" clause of a selection statement:

"SELECT column WHERE key_column = key"

The ODBC Application Interface always tests for equality and only returns data from records where the value in the key column exactly matches the key. To test whether the value in the key column matches one of several possible values, format the key value as a list, separating items with colons as in "Red":"Blue":"Green." This acts like an OR operation, returning data from all records where the value in the key column matches "Red" OR "Blue" OR "Green." To perform an AND operation or to test for inequality, use @DbCommand to pass the appropriate command string to the DBMS. Also use @DbCommand to pass the appropriate command string if the key is a time-date value, because @DbLookup does not always convert the time-date value to the correct format for time-dates in the DBMS command language.

If you cannot get @DbLookup to return the correct values due to typing or other problems, try using a SELECT statement in @DbCommand.

Specifying Distinct

The Distinct string argument is similar to @Unique in Lotus Domino, except that Distinct ensures that duplicate values are removed before the data is returned to Lotus Domino. Using Distinct instead of @Unique has two advantages:


Note Distinct is not supported by all ODBC drivers. If there are null values in the data and you specify Distinct, one null is usually returned.

Specifying sort

If you use the Distinct string argument, you can append the sort parameter to it with a colon. Use one these keywords for the sort parameter to specify sorting of the return values:


If no sort string argument is specified, values are returned in arbitrary order.

Note The sort keywords are not supported by all ODBC drivers. If you attempt to use both Ascending and Descending in your formula, Lotus Domino displays an "Invalid argument" message.

Accessing the values found

If multiple values are returned, they are formatted as a list and are separated with the multi-value separator designated for the current field.

@DbLookup can return no more than 64KB of data. Use the following equations to determine how much of your data can be returned with @DbLookup.


If the user's NOTES.INI file includes the statement:

NoExternalApps=1

the @DbLookup formula is disabled. The user will not see an error message; the formula fails to execute. This applies to @DbLookup only when you use it with ODBC.

Usage

@DbLookup is intended mainly for keyword formulas. Instead of hard-coding a list of keywords and then periodically updating that list, @DbLookup lets you dynamically retrieve a list of values from an external database table.

@DbLookup can't be used in mail agents, although it does work in paste agents. This function only works in Web applications if the remote server hosting the table from which data is being retrieved exists on the same machine as the Domino server, which is rarely the case.

Example
See Also