@DbLookup (ODBC data source) (Formula Language)

Syntax

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

Parameters

"ODBC"

String argument. Indicates that you are accessing an ODBC data source.

cache

String argument. Optional. In the initial lookup, specify either "" or "NoCache." If the former case, subsequent lookups to the same data source, you can specify "ReCache."

• "" (null string) caches the results of the lookup. Each subsequent lookup to the same location (within the same Domino® session and so long as the database executing this lookup remains open) re-uses that data until you specify "ReCache." Cached data improves performance and may be a good choice for stable data.
• "ReCache" refreshes the cache with the latest data from the database. If you want to ensure that this lookup gets the latest information, specify this option.
  Note: "ReCache" is new with Release 6.
• "NoCache" gets the results of the lookup from the database; no cache is used. If you want to ensure that Domino® retrieves the latest information for every lookup, specify this option.

"data_source"

Text. The name of the external data source being accessed. This name is specified as the dsn (data source name) in the Data Source Administrator or the odbc.ini file. A data source indicates the location of one or more database tables. See "Specifying the data source."

"user_ID1" : "user_ID2"

Text-list. The user IDs needed to connect to the external database. You may need up to two IDs, depending on the DBMS being accessed. See "Specifying IDs and passwords."

"password1" : "password2"

Text list. The passwords required by the user IDs. See "Specifying IDs and passwords."

"table"

Text. The name of the database table being accessed.

"column"

Text. The name of the column from which data is being retrieved.

"null_handling"

Text. Specifies how null values are treated when the data is retrieved. See "Specifying null handling."

"key_column"

Text. The name of the column used for key matching.

"key"

Text, number, or date-time, or a list. The value to be looked up in key_column. Use the Notes® type that agrees with the type of the key column in the data source.

"Distinct"

String argument. Optional. Removes duplicate values from the list before returning data. See "Specifying Distinct."

"sort"

String argument. Sorts the list of values into either ascending or descending order before it is returned. See "Specifying sort."

Return value

valuesFound

Text, number, date-time, or a list of these types. The values found in the column you indicated. See "Accessing the values found," later in this chapter.

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:

• "";"" (two null strings, separated by a semicolon) to specify no ID and password, or to prompt for both
• "user_ID1";"password1" to specify one user ID and password combination
• "user_ID1":"user_ID2";"password1":"password2" to specify two user ID and password combinations

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:

• "Fail" generates this error message if the column of data contains any null values:

  Null values found - @Db function

  No data is returned with the message.

• "Discard" discards the null values, thus shortening the returned list of values. If one or more values are discarded when the @DbLookup formula is executed, Domino® displays this message on the status bar:

  Caution: NULL values discarded from @Db list.

• "Replacement value" specifies a replacement value for null values. The replacement value must be a quoted string, but if the column is numeric or date-time, the string must be convertible to that type.

  If your formula includes a sort string argument, the list of values to be returned is sorted before the replacement values are inserted. During sorting, all null values are placed at the beginning of the list for an ascending sort, and at the end for a descending sort. They are not replaced until sorting is complete. This can result in a list that has some values sorted incorrectly. For example, if you specify "zzz" as your replacement value, all those "zzz" values will appear at the beginning of the list even though you sorted it by ascending order.

  If one or more values are replaced when the @DbLookup formula is executed, Domino® displays this message on the status bar:

  Caution: NULL value replaced with user-defined value in @Db list.

  Generally, the replacement value should be one that is not likely to appear in the list as valid data; for example, if the column is text, your replacement value might be "***" so that you can easily find those values.

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 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 Domino®, except that Distinct ensures that duplicate values are removed before the data is returned to Domino®. Using Distinct instead of @Unique has two advantages:

• The formula operates more quickly because the additional work is performed outside of Domino®.
• You can potentially retrieve a larger amount of useful data into Domino® -- since the duplicate values are removed at the back-end, more unique values can be returned to Domino®.

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:

• "Ascending" sorts the list in ascending order.
• "Descending" sorts the list in descending order.

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

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.

• For lookups that return text:

  2 + (2 * number of entries returned) + total text size of all entries

  Each text string is limited to 511 bytes; if only one text string is returned, it is limited to 64KB.

• For lookups that return numbers or dates:

  (10 * number of entries returned) + 6

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.

Examples

1. This formula retrieves from the inventory database the complete list of colors in which company uniforms are available. The data is stored like this:

   Item	Size	Color
   Shirt	Small	Red
   Skirt	Small	Green
   Sweater	Medium	Red
   Trousers	Medium	Yellow

   To retrieve the entire contents of the Color column (column 3) for all records where the first sorted column (column 1, Item) contains "Shirt" or "Trousers":

   @DbLookup("ODBC"; "INVENTORY"; ""; ""; "UNIFORMS"; "Color"; "Item"; "Shirt" : "Trousers")

   Since multiple records contain at least one of the keys, the result is a list:

   Red:Yellow

   Values in the resulting list appear just as they were encountered in the database; they are not sorted and duplicate values are retained.

2. This example uses the sample "pubs" database that is included with Microsoft™ SQL Server. The formula uses an ODBC driver to access the data source called PUBLISHERS and locate the table called "authors" that is owned by user "dbo." In this table, the values in the "state" column are compared with the values "CA" and "TN." For every record whose state field contains either "CA" or "TN", the values stored in the "au_lname" field are returned. The author names are sorted in ascending order; null values are discarded.

   @DbLookup("ODBC";"PUBLISHERS";"dbo";"vanilla";
   "dbo.authors";"au_lname":"Discard";"state";
   "CA":"TN";"Ascending")