Random observations of a very experienced software artist.

    Fun with @DbLookup

    John M McCann  August 24 2012 09:45:54 AM
    This is a re-post of an earlier item to fix a bug in the code, add more diagnostic information, and recreate the item after an update exceed the 32K limit on a field.

    After spending considerable wasted time on XPages Server Side JavaScript @DbLookup results, I thought I would share my findings. My results are specific to 8.5.3, though I suspect they are applicable to all 8.5.x versions.

    The official IBM documentation indicates that @DbLookup (JavaScript) "Returns view column or field values that correspond to matched keys in a sorted view column". The "Return value" is "any" and the description is "An array containing the column or item values". Most of us now know this is incorrect. If one or zero records matched the key, the value returned is a string, not an array.

    What many don't know are the circumstances where the result is "undefined". I found two. If you have specified the database incorrectly, e.g. made a typo, the result will be "undefined". The second situation is where you don't have access to the database. Mine was a keyword lookup type of database that had an ACL with maximum Internet name and password set to "No Access". The parameter [FAILSILENT] seems to have no affect on this.

    In the days attempting to debug my code, I also confirmed the multiple undocumented database specification techniques available, not just the server name array. All the following worked (once I increased maximum Internet access):

    // the defined way, an array of two elements
    var db = @DbName();

    // arrays pointing to another database on the same server
    var db = [@DbName()[0], "folder\\filename.nsf"]; // note double slashes
    var db = new Array(session.getServerName(), "folder/filename.nsf"); // slash the other way not doubled

    // C API at lowest level still uses old Notes 2 conventions of double bang
    var db = @DbName()[0], + "!!" + "folder\\filename.nsf";

    // And, you can specify replicaId either as a string or single element array
    var db = ["85256FF7:12345678"];
    var db = "85256FF7:12345678";

    // all work in this lookup
    @DbLookup(db, viewname, "key", column, "[FAILSILENT");


    With all this additional information about DbLookup that I uncovered the hard way, I thought it only appropriate to share.

    I also decided to update Tom Steenbergen's excellent wrapper routine for DbLookup with this information. While doing so, I identified that I really needed control over where I wanted the caching to occur. This lead to the discovery that @Unique affected the cache, and other issues. Therefore, I came up with the following derivation:

    /* *****************************************************************
     * Returns @DbLookup results as array and allows for cache  
     * Author: John McCann - derived from work by Tom Steenbergen
     * @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 -empty for nocache, otherwise scope at which to cache  (application, request, view, session)
     * @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
     * @param keywords - one or more comma separate strings containing [FAILSILENT], [PARTIALMATCH], or [RETURNDOCUMENTUNIQUEID]  
     * @return array with requested results  
      ****************************************************************** */
     
    function DbLookupArray(server, dbname, cache, unique, sortit, viewname, keyname, field, keywords) {  
            var result;
            try {
                var cachekey = "dblookup_"+dbname+"_"+@ReplaceSubstring(viewname," ","_")+"_"+@ReplaceSubstring(keyname," ","_")+"-"+@ReplaceSubstring(field," ","_");
                    // if cache is specified, try to retrieve the cache from the appropriate scope
                switch (cache.toLowerCase()) {
                case "application":
                     result = applicationScope.get(cachekey);  
                     break;
                case "request":
                     result = requestScope.get(cachekey);  
                     break;
                case "view":
                     result = viewScope.get(cachekey);  
                     break;
                case "session":
                     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 if (dbname.indexOf("!!")!=-1 || dbname.indexOf(":")!=-1){
                                db = dbname;  // string value if double bang or replicaID spec
                        } else {
                                            db = new Array(server, dbname);
                        }  
                     }
                     var result = @DbLookup(db, viewname, keyname, field, keywords);
                     if (result==undefined){
                             // if Mark Leusink's debug toolbar installed, put out diagnostics there
                             if (dBar) {
                                     dBar.error("DbLookupArray returned undefined, cachekey=" + cachekey);
                                     // additional diagnostics
                                     var dbCheck:Notesdatabase;
                                     if (!db.equals("")){
                                         if (server.equals("")){
                                                dbCheck = session.getDatabase(@DbName()[0],dbname,false);  
                                        } else if (dbname.indexOf("!!")!=-1 || dbname.indexOf(":")!=-1){
                                                dbCheck = session.getDatabase("",dbname,false)
                                        } else {
                                                            db = session.getDatabase(server, dbname, false);
                                        }  
                                         if (dbCheck == null || dbCheck==undefined){
                                                 dBar.debug ("DbLookupArray unable to access database, server=" + server + ", name=" + dbname);
                                         }
                                     } else {
                                             dbCheck = session.getCurrentDatabase()
                                     }
                                     if (dbCheck.isOpen()){
                                                    var vwCheck:NotesView = dbCheck.getView(viewname);
                                             if (vwCheck==null || vwCheck==undefined){
                                                     dBar.debug ("DBLookupArray Unable to find view, name=" + viewname);
                                             }
                                     }
                                     dBar.debug("DbLookupArray key value=" + keyname + ", field=" + field + ", keywords=" + keywords);
                             }

                    // have result, process it
                     } else {
                             if (result) {
                                     // cache before manipulating
                                          switch (cache.toLowerCase()) {
                                                     case "application":
                                                     applicationScope.put(cachekey,result);  
                                                     break;
                                                case "request":
                                                     requestScope.put(cachekey,result);  
                                                     break;
                                                case "view":
                                                     viewScope.put(cachekey,result);  
                                                     break;
                                                case "session":
                                                     sessionScope.put(cachekey,result);  
                                             }  
                                     if (typeof result == "string") {
                                             result new Array(result);  
                                     } else {
                                             // sort and unique only apply if multiple results
                                             if (unique.toLowerCase()=="unique") result = @Unique(result);  
                                             if (sortit.toLowerCase()=="sort") result.sort();
                                     }
                             }
                     }
                     
                // we cached before operations on result set performed, so redo these if necessary
                }  else {
                     if (typeof result == "string") {
                             result new Array(result);  
                     } else {
                                     // sort and unique
                            if (unique.toLowerCase()=="unique") result = @Unique(result);  
                            if (sortit.toLowerCase()=="sort") result.sort();
                     }
            }
            } catch(e){
                    // this is our own error capture routine
                    result=jsError(e);
                    if (dBar) {
                            dBar.error("DbLookupArray Error " + result);
                    }
            } finally {
                    return result;
            }
    }