Retrieving Data with @DbColumn and @DbLookup

All database languages have the capability to look up reference or other information, and nearly all applications use this capability at some point. Domino applications frequently use formulas for keyword fields that can look up information within the same database, other Domino databases, or even foreign databases. @DbColumn() and @DbLookup() are the most frequently used @Functions to retrieve data, although @DbCommand() can also be used against ODBC data sources.

NOTE

@DbColumn() , @DbLookup() , and @DbCommand() can return no more than 64KB of data. If you run into this barrier , use @PickList() .

 

The function @DbColumn() returns a list of values from the specified column of a view. The view can be in the current database or in another database. The function @DbLookup() also returns a list of values from a specified column in a view. Unlike @DbColumn() , which loads whatever is in the column into memory, @DbLookup() enables you to specify a key value. This value is compared against the first sorted column in the view, and only values from documents matching the key are retrieved. The following code, which is from an action button that looks up departments and enables the user to choose a supervisor from a list of supervisors for that department, illustrates the use of @DbColumn() and @DbLookup() :

REM "Look up the departments" ; 
jcHRLookupID := @GetProfileField("GP"; "cHRLookupID");

jcDeptList := @DbColumn( ""; jcHRLookupID ; "LUDEPT";1) ;

jcDept := @Prompt([OKCANCELLIST]; "Departments" ;
 "Choose a department" ; "" ; jcDeptList ) ;
@If(jcDept = ""; @Return(""); @Success) ;

REM "The Department code and description are concatenated";
REM "into a single view column" ;
jcDeptName := @Right(jcDept; " ") ;
REM "The Department code should be the first four characters";
REM "plus 00" ;
jcDeptCode := @Left(jcDept; 4) + "00";

@SetField("cDepartment"; jcDeptName) ;
@SetField("cCostCenter"; jcDeptCode);

REM "Present a list of supervisors from the Cost Center to the user";
jcSuperList := @DbLookup(""; jcHRLookupID; "SDN";
 cCostCenter; "cSuper");
jcSupervisor := @Prompt([OKCANCELLIST]; "Supervisors";
 "Choose a Supervisor"; ""; jcSuperList );
@SetField("cSupervisor";@If(@IsError(jcSupervisor); "";
 jcSupervisor)) ;

The syntax of @DbColumn() and @DbLookup() is quite similar, as you can see in the following:

@DbColumn(Class : NoCache ; server : database ; view name; column number) 
@DbLookup(Class : NoCache ; server : database ; view name; key value; column number or

field name)

Class refers to the database type. You can refer to Notes databases as "Notes" or with double quotes. NoCache tells Notes not to store the results in memory (the default). Storing the results of the lookup can provide performance improvements for lookups against data that doesn't change very often. Server can be represented by the name of the server or with double quotes, indicating the current server. Database can be the current database, represented by double quotes, or it can be the replica ID of a database. It can also be the operating system filename. If you choose to use the filename, you must include the path relative to the data directory on the server. The view name can be either the name of a view or its alias. Note that for @DbColumn() , you must specify the column number, whereas with @DbLookup() , you can specify either the column number or the name of a document field. The document field does not have to be present in the view.

Using Hidden Views

It is often a good idea to use a hidden view to look up reference information. You can hide a view by enclosing its name in parentheses. You can also indicate the purpose of the view by placing the characters "LU," for "look up" at the beginning of the view name. This tells other developers about the purpose of the view. The code sample uses a hidden view with the name (LUDept) and an alias of LUDept.

The functions @DbColumn() and @DbLookup() can also be used to perform ODBC lookups. ODBC stands for Open Database Connectivity, a standard that supports connectivity among different database sources. For example, you can install an ODBC driver for SQL Server, Sybase, or any one of a number of different databases. When the ODBC source is configured, you can use it to look up information in the foreign database by passing a table name, a key column, and a key value for @DbLookup() , or by specifying a table name and column name for @DbColumn() . For example, if you were to look up the department information in a SQL Server table, you might use the following code:

@DbColumn("ODBC" : "NoCache"; "HR Lookup"; skern : nreks; Department; cDeptName;

"Distinct" : "Ascending")

TIP

For those of you with SQL in your background, you can pass a SQL statement to an ODBC source using @DbCommand .

 

CAUTION

A risk is associated with using ODBC sources in your applications. The users must have appropriate access to the data source. If a user cannot read the ODBC source, the lookup will fail. To get around this problem, you can use DECS or a data-integration product such as the Lotus Enterprise Integrator.