DbLookup & DbColumn, with cache, sort and unique


/**
 * 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;
}
All code submitted to OpenNTF XSnippets, whether submitted as a "Snippet" or in the body of a Comment, is provided under the Apache License Version 2.0. See Terms of Use for full details.
No comments yetLogin first to comment...