ShowTable of Contents
@DbColumn and @DbLookup (mind the case!) expect an array of servername and filepath as first parameter.
Example get a name of all people of the Domino Directory
db = new Array(@DbName()[0], 'names.nsf');
@DbColumn(db, "(People)", 2)
Note: use ($People) instead of (People) as view name.
Note: use "" as first parameter for @DbColumn to use the current database.
Note: doublecheck the case of your @ commands. Its "@DbColumn", not "@DBColumn".
Note: for DbLookup it's important that the first column of the view is sorted, and that "click on column header to sort" is NOT enabled.
Caution with lookups for categories
If you want to lookup values for "category1\category", you have to use this when defining the key string:
var key = "category1\\category2"
Check if there was a result or not
Example:
var result = @DbLookup(@DbName, "viewname", key, 1);
(typeof result == "string") ? "No result" : result
This returns the string "No result" if there was no result.
DbColumn and DbLookup with result as guaranteed array and with a cache
@DbLookup has the issue that it returns a string when it found exactly one result, and an array when it found multiple results.
That means after each @DbLookup you have to check if your result is a string or an array if you want to process it further with JavaScript.
Maybe you want to do the same @DbLookup multiple times on your XPage, for example if you have a listbox which fills it's values from a @DbLookup, but should be hidden when there are no values. For that case it would be nice to have the result of the first @DbLookup cached and re-used when needed the second time.
Both requirements are solved with these functions:
function DbLookupArray(viewname, k, field) {
if (requestScope.get("dblookuparray-"+viewname+"-"+k)) {
return requestScope.get("dblookuparray-"+viewname+"-"+k);
}
var r = @DbLookup("", viewname, k, field);
if (r && typeof r == "string") r = new Array(r);
if (r) requestScope.put("dblookuparray-"+viewname+"-"+k, r);
return r;
}
function DbColumnArray(viewname, column) {
var k = "dbcolumnarray-"+viewname+column;
if (requestScope.get(k)) {
return requestScope.get(k);
}
var r = @DbColumn("", viewname, column);
if (r && typeof r == "string") r = new Array(r);
if (r) requestScope.put(k, r);
return r;
}
DbColumn and DbLookup as above but with cache on request
It is not always desirable to have the results of a DbLookup or DbColumn cached.
The code below is almost similar to above, but allows you to specify if you want the results cached or not.
Replace the sessionScope with the mechanism of your choice.
/**
* Returns @DbLookup results as array and allows for cache
* @param cache -"cache" for using cache, empty or anything for nocache
* @param viewname -name of the view
* @param keyname -key value to use in lookup
* @param field -field name in the document or column number to retrieve
* @return array with requested results
*/
function DbLookupArray(cache, viewname, keyname, field) {
var cachekey = "dblookup-"+viewname+"-"+keyname+"-"+field;
// if cache is specified, try to retrieve the cache from the sessionscope
if (cache.equalsIgnoreCase('cache')) {
var result = sessionScope.get(cachekey);
}
// if the result is empty, no cache was available or not requested,
// do the dblookup, convert to array if not, cache it when requested
if (!result) {
var result = @DbLookup("", viewname, keyname, field);
if (result && typeof result == "string") result = new Array(result);
if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
}
return result;
}
/**
* Returns @DbColumn results as array and allows for cache
* @param cache -"cache" for using cache, empty or anything for nocache
* @param viewname -name of the view
* @param column -column number to retrieve
* @return array with requested results
*/
function DbColumnArray(cache, viewname, column) {
var cachekey = "dbcolumn-"+viewname+"-"+column;
// if cache is specified, try to retrieve the cache from the sessionscope
if (!cache.equalsIgnoreCase('cache')) {
var result = sessionScope.get(cachekey);
}
// if the result is empty, no cache was available or not requested,
// do the dbcolumn, convert to array if not, cache it when requested
if (!result) {
var result = @DbColumn("", viewname, column);
if (result && typeof result == "string") result = new Array(result);
if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
}
return result;
}
Update 14. Sep 2011: here is an improved version by Tom Steenbergen
:
/**
* Returns @DbLookup results as array and allows for cache
* @param server -name of the server the database is on (only used if dbname not empty, if omitted, the server of the current database is used)
* @param dbname -name of the database (if omitted the current database is used)
* @param cache -"cache" for using cache, empty or anything for nocache
* @param unique -"unique" for returning only unique values, empty or anything for all results
* @param sortit -"sort" for returning the values sorted alphabetically
* @param viewname -name of the view
* @param keyname -key value to use in lookup
* @param field -field name in the document or column number to retrieve
* @return array with requested results
*/
function DbLookupArray(server, dbname, cache, unique, sortit, viewname, keyname, field) {
var cachekey = "dblookup_"+dbname+"_"+@ReplaceSubstring(viewname," ","_")+"_"+@ReplaceSubstring(keyname," ","_")+"-"+@ReplaceSubstring(field," ","_");
// if cache is specified, try to retrieve the cache from the sessionscope
if (cache.equalsIgnoreCase('cache')) {
var result = sessionScope.get(cachekey);
}
// if the result is empty, no cache was available or not requested,
// do the dblookup, convert to array if not, cache it when requested
if (!result) {
// determine database to run against
var db = "";
if (!dbname.equals("")) { // if a database name is passed, build server, dbname array
if (server.equals("")){
db = new Array(@DbName()[0],dbname); // no server specified, use server of current database
} else {
db = new Array(server, dbname)
}
}
var result = @DbLookup(db, viewname, keyname, field);
if (result && unique.equalsIgnoreCase("unique")) result = @Unique(result);
if (result && typeof result == "string") result = new Array(result);
if (result && sortit.equalsIgnoreCase("sort")) result.sort();
if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
}
return result;
}
/**
* Returns @DbColumn results as array and allows for cache
* @param server -name of the server the database is on (only used if dbname not empty, if omitted, the server of the current database is used)
* @param dbname -name of the database (if omitted the current database is used)
* @param cache -"cache" for using cache, empty or anything for nocache
* @param unique -"unique" for returning only unique values, empty or anything for all results
* @param sortit -"sort" for returning the values sorted alphabetically
* @param viewname -name of the view
* @param column -column number to retrieve
* @return array with requested results
*/
function DbColumnArray(server, dbname, cache, unique, sortit, viewname, column) {
var cachekey = "dbcolumn_"+dbname+"_"+@ReplaceSubstring(viewname," ","_")+"_"+@ReplaceSubstring(column," ","_");
// if cache is specified, try to retrieve the cache from the sessionscope
if (cache.equalsIgnoreCase('cache')) {
var result = sessionScope.get(cachekey);
}
// if the result is empty, no cache was available or not requested,
// do the dbcolumn, convert to array if not, cache it when requested
if (!result) {
// determine database to run against
var db = "";
if (!dbname.equals("")) { // if a database name is passed, build server, dbname array
if (server.equals("")){
db = new Array(@DbName()[0],dbname); // no server specified, use server of current database
} else {
db = new Array(server, dbname)
}
}
var result = @DbColumn(db, viewname, column);
if (result && unique.equalsIgnoreCase("unique")) result = @Unique(result);
if (result && typeof result == "string") result = new Array(result);
if (result && sortit.equalsIgnoreCase("sort")) result.sort();
if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
}
return result;
}