Uses data source information to activate the appropriate ODBC driver. The driver then locates the specified DBMS, table, and column, and returns all values in that column. You can optionally specify whether the returned list of values is sorted, whether duplicate values are deleted, and how null values are handled.
Note @DbColumn can only retrieve data; it can't add, delete, or modify data.
@DbColumn( "ODBC" : cache ; data_source ; user_ID1 : user_ID2 ; password1 : password2 ; table ; column : null_handling ; "Distinct" : sort )
"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."
Note "ReCache" is new with Release 6.
data_source
Text. The name of the external data source being accessed. 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."
"Distinct"
String argument. Optional. Removes duplicate values from the list before returning data.
See "Specifying Distinct."
sort
String argument. Specify "Ascending" to sort the list of values into ascending order before it is returned; specify "Descending" to sort the list in descending order.
See "Specifying sort."
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.
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 is loaded for editing. If you use the Standard user interface for the list, the keyword list is updated every time the document is recalculated.
The data source name can contain up to 32 alphanumeric characters.
@DbLookup can access data sources that have already been registered in the ODBC.INI file (or similar registry on platforms other than Windows™).
You only need these arguments if your DBMS requires them.
Instead of storing the IDs in the @DbColumn 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 run automatically (such as an agent) because these 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 @DbColumn formula, or prompted for 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:
You can optionally include the name of the table's owner to remove ambiguity; use the format "owner_name.table_name", with a period to separate the owner name from the table name. For example:
"dbo.author"
Table can also refer to a database view in the DBMS being accessed.
Normally, null values are ignored and the resulting list is shortened (same as using the Discard option described below).
To control how null values are handled, specify one of the following, appended to the column parameter with a colon:
Null values found - canceling @Db function
No data is returned with the message.
Caution: NULL values discarded from @Db list.
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 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 @DbColumn formula is executed, you see 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 in Lotus Domino.
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. 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.
If you use the Distinct string argument, you can append the sort parameter to it with a colon. Use one of 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, you see an "Invalid argument" message.
If multiple values are returned, they are formatted as a list and are separated with the multi-value separator designated for the current field.
@DbColumn can return no more than 64KB of data. Use the following equations to determine how much of your data can be returned with @DbColumn.
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.
(10 * number of entries returned) + 6
If the user's NOTES.INI file includes the statement
NoExternalApps=1
the @DbColumn formula is disabled. The user will not see an error message; the formula fails to execute. This applies to @DbColumn only when you use it with ODBC.
@DbColumn is intended mainly for keyword formulas. Instead of hard-coding a list of keywords and then periodically updating that list, @DbColumn allows you to dynamically retrieve a list of values from an external database table.
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.
GetValue method of Lotus Connectors ODBCResultSet class
See examples