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.


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.


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 = [];
   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);
  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;
  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: }
  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: }


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.


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"]);
   7:     var msg = "First result Title is '{0}'".replace("{0}", results[0]["Title"];
   8:     Application.message(msg);
   9: }

Updated Andornot Utility scripts for Webpublisher 11

by Peter Tyrrell Friday, June 13, 2008 11:34 PM

As reported in my last post, Inmagic Webpublisher 11 broke some of our utility scripts. This release is compatible with WPP 11, and is still backwards-compatible with earlier versions.

Download them from our demo site at ask.andornot.com:

Andornot Search Utilities 1.9

Andornot Result Utilities 1.11


CSS absolute positioning while using ASK

by Peter Tyrrell Friday, June 13, 2008 10:22 AM

is NOT recommended.

Usually not added when you first install WPP, check your DBTWPUB.ini file in your WPP installation folder on your web server. Make sure that you do not have the line of code "WebCSSOpt=2" as our ASK web forms will not display properly.

You can edit the DBTWPUB.ini file in Notepad and either change it to WebCSSOpt=0 or delete that line entirely.

Below is the DB/TextWorks Help file article for more information....

WebPublisher PRO: Form layout

By default, WebPublisher version 4.1 and later uses Cascading Style Sheets (CSS) and absolute positioning, so that report, display, and edit forms look as much as possible as they would look in DB/TextWorks.

WebPublisher detects whether the browser supports Cascading Style Sheets. If not, WebPublisher uses simple HTML (boxes will be left-justified, box labels will appear on top rather than to the left, and so forth).

Forms that contain Raw HTML or inline images are incompatible with absolute positioning. Forms containing these elements will have overlapping text unless very carefully designed. Therefore, simple HTML is used for this type of form unless you explicitly set WebCSSOpt=2 in your DBTWPUB.INI file (see below).

Note: DB/TextWorks tabular forms do not use absolute positioning, so this issue does not affect them.

You can use picture boxes in place of inline images to retain CSS formatting. The Image size option specified using Box Properties>Position in the Form Designer is used. In order for the image sizing to work properly, the images must reside either in the textbase folder (for example, C:\CATALOG\) or the folder specified in the Image field in the record (for example, C:\IMAGES\COYOTE.JPG).

To control whether the site uses CSS, set the WebCSSOpt= parameter in the [WebPublisher] section of your DBTWPUB.INI file equal to one of the following:

0 = do not use CSS 1 = the default behavior 2 = use CSS regardless of whether forms contain Raw HTML or inline images


[WebPublisher] WebCSSOpt=0

Note: The WebCSSOpt= parameter affects all forms and all textbases, unless you override it using the CS= parameter in an individual query screen.

Webpublisher 11 breaks some Andornot scripts

by Peter Tyrrell Tuesday, June 10, 2008 10:51 AM

Inmagic Webpublisher 11, just released, breaks some Andornot Utility scripts that rely on the dbtw_params variable: Rewind, Next/Previous, and Change Form On Dropdown Value are affected. Possibly others.

A fix will be released as soon as possible, and I will update this post and provide download links to the script files at that time.

UPDATE Jun 13 2008: Fix released.

More Info

dbtw_params is a javascript variable injected into the page by Webpublisher that holds a query string representation of the current result page. As with all query strings, Key/value pairs are separated by the & character, except that in WPP 11, this character is HTML-encoded as &amp;.

Webpublisher 10


Webpublisher 11


How to install Webpublisher on 64-bit IIS 7

by Peter Tyrrell Friday, May 02, 2008 5:37 PM

Further to my post called Webpublisher on Windows Vista, here is how to install Inmagic Webpublisher on IIS 7 in a 64-bit environment such as Windows Vista x64 or Windows Server 2008 x64.

These instructions are valid for DB/Text Webpublisher as well as CS/Webpublisher. Steps 1 - 5 are valid for 32-bit IIS 7 as well.

1. Ensure IIS is enabled

This screenshot shows which IIS features are enabled on my Vista x64 machine. The highlighted one is particularly important (IIS metabase and IIS 6 configuration compatibility).


2. Install Inmagic Webpublisher

Follow the usual Inmagic install instructions.

3. Enable ISAPI-dll handler mapping on dbtw-wpd or ics-wpd virtual directory

Open the IIS Manager.

Open the Handler Mappings for the dbtw-wpd or ics-wpd virtual directory. (Figure below shows dbtw-wpd.)


The ISAPI-dll handler is disabled by default.


Enable the ISAPI-dll handler: right-click it, choose "Edit Feature Permissions", and check the "Execute" box. Click OK.


4. Set the Webpublisher dll as an allowed restriction

In the IIS Manager, click on the machine root - this is the top level of the tree in the left column, which shows the machine name. You should see a number of machine-level features, including "ISAPI and CGI Restrictions", as below. If you do not see "ISAPI and CGI Restrictions", you have not properly enabled IIS settings in step 1.


Open ISAPI and CGI Restrictions.

You may see Inmagic Webpublisher as an allowed restriction already. The path will point to the install location of dbtwpub.dll or icswppro.dll, depending on which version of Webpublisher you installed.

If you do not see Inmagic Webpublisher as an allowed restriction, you must add it. (Figure below shows DB/Text Webpublisher.)


5. Create an application pool for Webpublisher

Open Application Pools from the IIS Manager.

Add a new application pool to be used with Webpublisher, called "WebpublisherAppPool".


Go to the dbtw-wpd or ics-wpd virtual directory and open its Advanced Settings. Set the Application Pool to the new WebpublisherAppPool just created.

6. Enable 32-bit applications on the Webpublisher application pool

Open Application Pools from the IIS Manager.

Right-click on WebpublisherAppPool and select Advanced Settings.

Set "Enable 32-Bit Applications" to true. Click OK.


7. Get a drink. You're done.

Whatever drink you like. I like red wine, or on a hot day, gin and tonic.

Some more explanation

Whereas in IIS 6 you could run worker processes in either 32-bit or 64-bit mode, but not both, IIS 7 can run 32-bit and 64-bit worker processes simultaneously. And, as the above instructions make implicitly clear, you can set this behaviour on individual application pools.


Month List