Overcoming DBLookup Size Limits

Jake Howlett, 24 September 2003

Category: JavaScript; Keywords: JavaScript DBLookup Array Associative

My first ever venture in to the altruistic side of the internet was a contribution to the, then new, Notes.net Sandbox. Response to this was greatly satisfying and in the three years that have passed I've continued to enjoy giving. That first contribution has now been modified almost beyond recognition. It is the "JavaScript Refresh" database and can now be found here.

Obviously, since this first submission I have learnt a lot of new approaches to the same scenario. I now know of many other methods that would have been more than useful back then. The method I used in this first database had a huge flaw. The use of @DBColumn to fetch data from a view and create JavaScript arrays was limited by the infamous 64K limit. A few years later and with some "What if?"s and "How about?"s I have found a better solution. Turns out to be quite simple in hindsight. Oh, if I knew then what I know now....

The problem:

You use a view to store data that you then need to refer to in the JavaScript functions on your forms. How do you get it there? Of all the methods that spring to mind they all seem to have just as many limitations. I am thinking, mainly, of the DBLookup and Embedded View methods.

These methods are limited, yet again, by the amount of data they can supply. What we need is a way of getting the whole view in to our form as available data.

The solution:

Can't remember exactly what made me think of this solution. It must have been a lateral extension to the idea that if you end a view name in ".xml" the browser will believe you and render it so. So, why then can't we do this with a view whose name ends with ".js"? Well, we can!

Let's take an example, similar to the above "Refresh" database, and see how we can remove the limit on the amount of data, normally associated with @DBLookups. As an example I will use a database that records countries from around the world and some information about them. The countries are organised by their "region", or continent. It might be worth downloading the attached example and having a play before continuing.

In the database there's a form called Country that holds the important information - Region, Name, Area, Population, Terrain and Climate. What we need to do is create a view that will make this information available as JavaScript. Have a look at the screenshot below:

What we have is a view categorised by the Region field. For each region we are creating a new JavaScript object with the same name. Then, for each document in that region, we add a country. How this works will become apparent later. Let's look at the code for each column. The formula for the first column is:

"var " + @ReplaceSubstring(Region; " ":"'":"\""; "") + " = new CountryStore();" + @NewLine
And for the second column:
@ReplaceSubstring( Region; " ":"'":"\""; "") + ".addCountry(\"" + @ReplaceSubstring(Name; "'":"\""; "'":"\\\"")+"\", \"" + @Text( @DocumentUniqueID ) + "\", \"" + Area + "\", \"" + Population + "\");" + @NewLine

A few important to things to note about this view is that its name ends in .js and that it has the property "Treat as HTML" enabled. You will see later how we manage to send the data to the browser and make it believe it's of the content-type "text/javascript" rather than the standard "text/html" which some browsers don't like.

Don't worry if this is starting to appear irrelavent. The method I am describing is definitely worth knowing about. I just need to use a suitable scenario to help describe how to implement it.

We now have a view that is, in effect, JavaScript. What we can do now is add the following line to our HTML Head Contents.

"<script type=\"text/javascript\" src=\"/" + DBPath + "/countries.js?openview&count=-1\"></script>"

Now, when we open a form with this line in place, all the country objects are included. Or are they? Not quite. JavaScript doesn't know what a CountryStore object is. We need to create it.

First, create a form and call it "$$ViewTemplate for countries.js". Assuming you are using Domino 6, set the content type to "text/javascript", as in the shot below.

Now, whenever this view is requested, this form is delivered. This means that, effectively, the view and all its contents are treated as JavaScript.

If you are restricted to Domino 5 you will have to make do with setting the above form's property of "Treat document contents as HTML" and hoping the browser doesn't mind having JavaScript delivered as HTML. Most don't.

Now, embed the countries.js view on to the form, making sure that it has its column headers set not to display.

You've probably noticed by now that the JavaScript calls made in the view are going to require some code to make them work. These we shall add to this new View Template form, above the embedded view. This way we can be sure that all necessary functions get included whenever the view is requested.

The first function we need is called CountryStore. Think of it as kind of like a Java class, where you can create new objects that inherit from it. One of these "objects" is created for each category in the view and stores all relevant information for each document in that category. Here's the function:

function CountryStore(){
this.l = 0;
this.Countries = new Array();
this.addCountry = function () {
this.Countries[this.l++ ] =
{
Name: arguments[ 0 ],
Location: arguments[ 1 ],
Area: arguments[ 2 ],
Population: arguments[ 3 ]
}
}
}

It might look a little strange at first, but it's quite straightforward really. For each region a new CountryStore object is created. This object contains within itself an array called Countries. For each country this array holds another array - which contains the values from the view for that country. Get it? There's another little trick in there that increments the property "l" which is, in effect, the length of the Countries array. Useful for looping.

If you look at the design of the view template form you'll see that above and below the embedded view are the lines

var allRegionsHaveLoaded = false;

and

allRegionsHaveLoaded = true;

The point of these should be obvious. Say you have a really big view. It can happen that the browser is still loading the .js file well after the form appears to the user. If the user then interacts with the form and triggers a function that requires the JS code from the view, there will be errors. Instead you can write some code to check that the above variable is true before performing any actions. If it isn't, make the code wait a while, or simply alert the user that they may need to wait.

Using this code:

Now that we have this array of objects, how do we use them in our web-pages? Well, it depends. How you use the objects depends on what you want to do with them (obviously). Let's take an example of creating a dynamic view from the data. The user will be able to select the region they want and we will use JavaScript to create a table that lists all the countries contained within. Here's the code:

function getAllCountriesForRegion( region ) {
if ( typeof ( currentCountryList = eval( region ) ) != "object"){
return alert('Region not found');
}
//start the table
strContent = new String("<table width=\"100%\"><tr><th>Name</th><th>Area</th><th>Population</th></tr>");
for (var i=0; i < currentCountryList.l; i++){
strContent += "<tr><td><a href=\"0/" + currentCountryList.Countries[ i ].Location
+ "?OpenDocument\">" + currentCountryList.Countries[ i ].Name + "</a></td>";
strContent += "<td>" + currentCountryList.Countries[ i ].Area + "</td>"
strContent += "<td>" + currentCountryList.Countries[ i ].Population + "</td></tr>";
}
//end the table
strContent += "</table>";
return strContent;
}

Sorry, it's a bit of a mess. For a better idea of what is going on, download the example and look at that. There are a few lines to note though. The first line of the function is quite important. This uses eval() to get a hold of the object that has the same name as the region we are interested in. If there is no object with the same name we return an error. The rest of the code simply loops all countries and build a table using the values in the arrays.

Using the objects is as simple as that. Get a handle on the object and treat it as an array. There is a better example of how to use this approach more effectively in the example database. In the "Report" form I've used the exact same view data to auto-populate the form based on choices made by the user. All done without any return trip to the server.

Taking it further:

Hopefully by now you're starting to think like I do. You look at this and think - "Hang on, if I change this", "if I add this", "if I do that". There are a zillion different applications for this approach. Wherever you find yourself needing ready access to ALL the data in a view, think of this approach and how you could tailor it to your needs. Don't think that it's limited to DHTML view construction either! Remember the form auto-filling example if any.

Summary:

It's been two years since I dreamt up this approach. Looking back, I'm not 100% sure why I didn't use XML. If I were faced with the same scenario again I don't know which I would choose. Probably it would be down to whether or not the target browsers supported it. That's where this approach comes in to its own. You can use it in almost all browsers and not have to worry about XML support.

Whether you use this approach or XML, or neither, it should demonstrate something else. The idea that, with a combination of a few Domino tricks, you get the whole of a view available in a form. The key points being:

Setting the View Template's content-type to text/javascript
Ending the view name with .js (although nto strictly necessary)
Including the &count=-1 argument when opening the view.

Another approach would be to use one multi-dimension array to store the whole view. In this case, the regions would be the top-level elements with a lot of second-level arrays to record each country. If you do come up with a worthwhile and different approach, let us all know about it.