A highly reusable recordset script for DB/Text

by Peter Tyrrell Tuesday, August 05, 2008 10:26 PM

This javascript function performs a search against a DB/Text database and returns a sorted array of dictionary objects with fieldnames as keys.

Parameters

Search(commandQuery, textbase, password, fieldsToReturn, sortFields)

commandQuery (string): A query in command syntax.
textbase (string): The name of the textbase to query.
password (string): A textbase password that allows querying.
fieldsToReturn (array): An array of field names whose values the query will retrieve from result records.
sortFields (array): An array of field names to sort the recordset by.

Dependencies

SortByField(recordset, sortFields): A method that sorts a recordset by the fields provided.

   1: // returns array of dictionaries with fieldsToReturn as keys
   2: function Search(commandQuery, textbase, password, fieldsToReturn, sortFields)
   3: {
   4:     var rs;
   5:     var output = [];
   6:     
   7:     rs = Application.newRecordset(textbase, Application.activeTextbase.path, password);
   8:     if (rs != null)
   9:     {
  10:         try {
  11:             rs.Open(commandQuery);
  12:             if (rs.Errors.Count > 0)
  13:             {
  14:                     // log error
  15:             }
  16:             else if (rs.RecordCount > 0)
  17:             {
  18:                 // sort the recordset
  19:                 rs = SortByField(rs, sortFields);
  20:                 
  21:                 rs.MoveFirst();
  22:                 while (!rs.EOF)
  23:                 {                    
  24:                     // create dictionary of field values, add to output list
  25:                     var record = new Object();
  26:                     for (var i = 0;i < fieldsToReturn.length;i++)
  27:                     {
  28:                         record[fieldsToReturn[i]] = rs.Fields(fieldsToReturn[i]).Value;
  29:                     }
  30:                     output[output.length] = record;
  31:                     
  32:                     rs.MoveNext();                    
  33:                 }
  34:             }    
  35:         }
  36:         catch (e) {
  37:             // log exception
  38:         }
  39:         finally 
  40:         {
  41:             rs.Close();    
  42:         }
  43:     }
  44:     return output;
  45: }
  46:  
  47: // returns recordset sorted by fields provided
  48: function SortByField(recordset, sortFields)
  49: {
  50:     var sort = recordset.NewSortDescriptor();
  51:     for (var i = 0;i < sortFields.length;i++)
  52:     {
  53:         if (i >= (sort.MaxSORTFIELDS - 1))
  54:         {
  55:             break;    
  56:         }
  57:         sort.sortFieldName(i) = sortFields[i];
  58:     }
  59:     recordset.Sort(sort);    
  60:     return recordset;
  61: }

Overview

The main advantage of this function is its agnosticism towards the fields it is to retrieve. It relies on the fact that objects in javascript inherently behave as associative arrays, e.g.

var myObject = new Object();
myObject["myKey"] = "some value";

For each record in the query's result set, an object is created with fieldsToReturn as keys, and the function's return type is simply an array of these objects. The fact that I am using these objects solely for their key/value behaviour leads me to refer to them as dictionaries. They are not dictionaries in the sense of specialized collections as with other languages outside of javascript.

Example

The following is a simplified method that I would never use in production code. It grabs search text from a box, creates a command query, then searches the Catalog. The result set will be sorted by Title, and will contain values from Title and Author fields. It then shows an alert message with the title from the first result in the set.

Note that field values are retrieved by using field names as keys on the objects in the results array.

   1: function btnSearch_onClick()
   2: {
   3:     var searchText = Form.boxes("boxSearchText").content;
   4:     var query = "find (Title ct {0}) or (Author ct {0})".replace("{0}", searchText);
   5:     var results = Search(query, "Catalog", "password", ["Title", "Author"], ["Title"]);
   6:     
   7:     var msg = "First result Title is '{0}'".replace("{0}", results[0]["Title"];
   8:     Application.message(msg);
   9: }
blog comments powered by Disqus

Month List