Domino Code Fragment
Code Name* @DbLookup( ODBC) | Date* 04/29/2024 | Source (or email address if you prefer)* Rlatulippe@romac.com IP address:.3.14.132.214 | |
Description* Given data source information from the ODBC.INI file, uses this information 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. | Type* Formula | Categories* (Misc) |
Implementation: | Required Client: | Server: |
Limitations: | Comments: @DbLookup can only retrieve data; it can't add, delete, or modify data. @DbLookup is intended mainly for keyword formulas. Instead of hardcoding 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 macros, although it does work in paste macros. |
Parameters
"ODBC"
If you omit NoCache, you do not have to replace it with anythingthe lookup results are cached automatically, but you can specify "Cache" for readability. See "Specifying NoCache."
For example, if youre using lookups to a database that stores customer addresses, its generally safe to use caching because you dont expect the addresses to change very often. But if youre using a lookup to a database where stock prices are stored, and that database gets updated hourly, its safer to omit caching and force a new lookup each time.
@DbLookup can access data sources that have already been registered in the ODBC.INI file (or similar registry on platforms on other than Windows). It can also autoregister a data source not present in the registry. For autoregistration, specify the data source as "data_source" : "type" : "path" where data_source is the name to be assigned, type identifies the DBMS (for example, "Oracle7"), and path is a server name, directory, or other string that locates the DBMS.
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 Notes database session as long as that database remains open. If the user opens another Notes 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:
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:
If your formula includes a sort keyword, 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 "zz" as your replacement value, all the "zzz" values will appear at the beginning of the list, even if you sorted it in ascending order.
If one or more values are replaced when the @DbLookup formula is executed, Notes displays this message on the status bar:
The key can be any value; if its a string (text) value, enclose it in quotation marks.
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.
Specifying Distinct
The Distinct keyword is similar to @Unique in Notes, except that Distinct ensures that duplicate values are removed before the data is returned to Notes. Using Distinct instead of @Unique has two advantages:
Specifying sort
If you use the Distinct keyword, 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:
Note The sort keywords are not supported by all ODBC drivers. If you attempt to use both Ascending and Descending in your formula, Notes displays an "Invalid argument" message.
Accessing 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 55K bytes of data. Use the following equations to determine how much of your data can be returned with @DbLookup.
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 55K bytes.
(10 * number of entries returned) + 6
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.