Tuesday, 13 September 2011

Final version of DbLookupArray & DbColumnArray

Time to post the final version (I think) of the DbLookupArray and DbColumnArray functions, which I started blogging about a couple of months ago.
To date these functions only worked on the current database. So now I have added the option to also execute on another database on another server (beware that proper authorisation is in place).
The server will only be used when a database name is provided. In both cases, if omitted, the current server/database is used.
And the final added option is to be able to request the results back in a sorted order.

DbLookup
 /**  
  * 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;  
 }  
DbColumn
 /**  
  * 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;  
 }  

Used sources:
xpageswiki.com

4 comments:

quintessens said...

hej Tom

great post, thanks!

David Leedy said...

Does this work with databases that are in subdirectories? I couldn't get that to work.

Tom Steenbergen said...

@ David Leedy
Yes, it does work for subdirectories as well. However because it is passed as a string, you need to escape the backslashes between the foldernames with an extra backslash.
e.g. dir1\\dir2\\dir3

Naseer said...

when I am using this with Partial Refresh, the result is empty.