Overcoming DBLookup Size Limits
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 tablestrContent = 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 tablestrContent += "</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.
Setting Content-Type of R5 pages
FYI: It is possible to set the Content-Type header item using the DSAPI. It doesn't require more than a few lines of C code to make it work...
Reply
Re: Setting Content-Type of R5 pages
Care to share? I would love to get in to hacking with DSAPI. Put all those wrongs right ;o)
Reply
Show the rest of this thread
View Limitations
Hi Jake,
Nice thinking!!!
Here we have the problem that only the first 1000 documents will be displayed (server settings), so the &count=-1 will only display the first 1000.
But this will solve a nice 64 K problem in R5 we have right now. Nice workaround!
'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.'
In R5 you can also write a simple agent to print the text/javascript content type, and afterwards you can easily print the view with NotesViewEntries (some trouble with categorisation though).
cheers,
Noel
Reply
Re: View Limitations
"In R5 you can also write a simple agent to print the text/javascript content type, and afterwards you can easily print the view with NotesViewEntries (some trouble with categorisation though)."
And with a considerable penalty in speed and scalability. One wouldn't think that a tiny agent would incur that much of a penalty, but the difference is HUGE in use.
Reply
Re: View Limitations
DutchSean,
There's a server document setting that limits the maximum number of rows returned ("Maximum lines per view page"). It defaults to 1000. If you set it to -1 you will get all the rows you want. This is true in R5, at least.
Reply
Show the rest of this thread
Brwoser cahce issues
Great approach, and may i say as usual. one problem that always come up when using notes design elements as a JavaScript files is that most of the browsers will never cache it, which mean that the user need to get them from the server every time (even more of a problem if your database require authentication). to overcome this limitation, it is sometimes better to put the code in a js file located in an external folder (html\ in domino, or images\ in domino with iis) and run an agent that rebuild the file from the view every x hours..
Reply
Re: Brwoser cahce issues
A lack of caching is not a problem in this application -- it's a solution. Any idiot can create a fixed-content JavaScript file, or write an agent that writes such a file with minimal updates, but we are after the web equivalent of @DbLookup. In other words, we WANT the data in the JavaScript file to be a live reflection of the data currently residing in the database.
The "Treat contents as JavaScript" view has been an enormous blessing to the Domino web developer. I use one as the DHTML menu for my intranet, another to populate a categorized web calendar, a third to fill in the taken squares in a sports pool, and so on.
In fact, the only "abnormal" view application that gets more use in my development is the similar tack of putting the view betwen <script> tags in the <head> of a "Treat contents as HTML" form loaded into a zero-size <iframe>. (This allows periodic refreshing to get new values with the ol' <meta>.) How else would one keep a running tally of the high bids in an online auction?
Reply
Show the rest of this thread
Problem to open countries.nsf...
Hi Jake,
I read your article which seems me to answer a problem that I perhaps will meet (currently, I am working on the telephone directory of my company more than 8000 employees...). I do not to open your download base, I have this message "Invalid NSF file version" ("Version de fichier NSF non valide"), I suppose that it's a R6 version ?
Thank you to share your knowledge!
Have a good day,
Thierry
Reply
Re: Problem to open countries.nsf...
Yes, its an R6 database. Mine opens, but doesnt actually work. It gives me a java error, even though I havent modified the code.
:(
Reply
Show the rest of this thread
DBLookup and UNID
Nice one Jake. Always good to see that you are thinking outside of the normal constraints.
You might want to expand the example to show that you can use restricttocategory in the url so that you can pull out subsets of the view.
One thing I've started doing is creating views that only have the UNID of the doc in a column, and then using getdocfield to pull individual field values. This seems to perform quite well, as I suspect Domino caches the most recent documents to speed the field retrievals. It was inspired by the R6 dblookup option to return unid's, which of course doesn't work properly (or does it, anyone?)
I wonder how well your technique works with 100k+ datasets over relatively slow connections, or with large numbers or concurrent connnections?
Reply
Performance degredation
We have been using a similar technique for about a year and a half now, and we have to deal with some seriously large data sets.
The point of doing it was we wanted to avoid paging through the data, as is the domino norm. So we loaded all into JS then render on the fly.
This is ok up to about 9000 entries, by the time you hit 20,000 the html you have to generate on the fly is huge, and v-e-r-y s-l-o-w.
So much so, that we are probably ditching notes altogether, but, in the mean time we have had to return to creating views which output only simple HTML, just to make the db usable.
Reply
Drop down keyword lists
Can this be used to get over the 64k limit when looking at a view with a keyword list, if so how ?
I ask as I am currently suffering this problem and would like to find a way of displaying my full list of options in a keywords list instead of it just dying like it does at the moment.
Thanks
A
Reply
R5 version? please
Hi Jake,
Would you mind to create a R5 db of "Overcoming DBLooking Size Limits"?
Reply
Re: R5 version? please
If I knew how you did it I would. Instead, why don't you just download an R6 client? It's about time you did anyway ;o)
Jake
Reply
Good one Jake
Hi Jake,
As always a great article. Your articles are kind of inspiration when I go back to work on my domino intranet applications. If I come across a limitation I start thinking as to how Jake of codestore.net or Steve (projectdx), Mike (NotesTips) would approach to this problem. Almost three years ago I developed an intranet application (in R4.6) to delete/update multiple documents from web. Last year when I read your article on the same issue I felt good that you used the same approach. Thanks for all the good articles. Mahesh
Reply
Using this techniquefor searching
Hi Jake, I tried to use your technique for enabling a search mechanism in my database.
I did the following: I created a subform, which I embedded on all forms of the database. In that subform, I had a fied that was populated with all the fieldnames on a form WebQueryOpen.
Before saving the doc, I stored the following name, value pairs in the field: FieldName="fld1"/Value="abc", FieldName="fld2"/Value="xyz", . . . and so on for all fields.
Next I cerated a view, and named it vwSearch.js. It shows documents of all forms in the database.
Using that I created an xml of the form: <document unid='unid value'> <form>formName</form> <fld1>fld1Value</fld1> <fld2>fld2Value</fld2> . . . . </document>
In $$ViewTemplateDefault for vwSearch.js, I added some header and footer xml string to this string.
Now whenever a user enters a search string, this view is opened with the search string passed as a query string.
It searches the xml for this value in and then displays the search results accordingly.
It's working fine, except that to display the values of rich text fields I had to use the method you posted earlier.
Please comment, if I could do with some refinements.
Regards, Joy Mowar
Reply
Dblookup clientside with XPath
This prototype only works for IE (at least for now). Using the view argument “readviewentries”, JavaScript and XPath on the client side this function returns an array of values.
function dbLookup(dblDatabase,dblView,dblKey,dblColumn,dblLimit){ //David Schmidt, 2005-10-11 try{ var xmldoc=new ActiveXObject("Microsoft.XMLDOM"); xmldoc.async=false; docLimit=(dblLimit!="")?dblLimit:"-1"; xmldoc.load("/"+dblDatabase+"/"+dblView+"?readviewentries&expandview&count="+doc Limit); if(xmldoc.parseError.errorCode!=0){ var xmlerror=xmldoc.parseError; errorstr="Error code: "+xmlerror.errorCode+"\nReason: "+xmlerror.reason+"\nLine: "+xmlerror.line+"\nLine position: "+xmlerror.linepos+"\nsrcText: "+xmlerror.srcText+"\nUrl:"+xmlerror.url+"\nFile position: "+xmlerror.filepos; alert("XSL error:\n" + errorstr) return "XML error"; }else{ columnNode=xmldoc.documentElement.selectNodes("/viewentries/viewentry/entrydata[ @columnnumber='0' and .='"+dblKey+"']"); NodeLength=columnNode.length; dblArray=new Array(); for(i=0;i<NodeLength;i++){ dblArray[i]=columnNode[i].parentNode.childNodes(dblColumn).text; } return dblArray; } }catch(e){ return e; } }
Reply
New version
function dbLookup(dblDatabase,dblView,dblKey,dblColumn,dblLimit){ //David Schmidt, 2005-10-12 try{ dblColumn=dblColumn-1; var xmldoc=new ActiveXObject("MSXML2.DOMDocument"); xmldoc.async=false; docLimit=(dblLimit!="")?dblLimit:"-1"; xmldoc.load("/"+dblDatabase+"/"+dblView+"?readviewentries&expandview&count="+doc Limit); if(xmldoc.parseError.errorCode!=0){ var xmlerror=xmldoc.parseError; errorstr="XSL error\n\nError code: "+xmlerror.errorCode+"\nReason: "+xmlerror.reason+"\nLine: "+xmlerror.line+"\nLine position: "+xmlerror.linepos+"\nsrcText: "+xmlerror.srcText+"\nUrl:"+xmlerror.url+"\nFile position: "+xmlerror.filepos; alert(errorstr); return "error"; }else{ columnNode=xmldoc.documentElement.selectNodes("/viewentries/viewentry/entrydata[ @columnnumber='0' and .='"+dblKey+"']"); dblArray=new Array(); if(columnNode.item(0).getAttribute("category")){ NodePosition=columnNode.item(0).parentNode.getAttribute("position"); xmldoc.setProperty("SelectionLanguage", "XPath"); xpathquery="/viewentries/viewentry/entrydata[starts-with(../@position,'"+NodePos ition+".') and @columnnumber="+dblColumn+"]"; categoryNode= xmldoc.documentElement.selectNodes(xpathquery); for(x=0;x<categoryNode.length;x++){ dblArray[x]=categoryNode[x].childNodes(0).text; } }else{ for(x=0;x<columnNode.length;x++){ dblArray[x]=columnNode[x].parentNode.childNodes(dblColumn).text; } } return dblArray; } }catch(e){ var errName=e.name; var errNumber=e.number & 0xFFFF; var errDescription=e.description; errMessage=e+"\n\nName: "+errName+"\nNumber: "+errNumber+"\nDescription: "+errDescription; alert(errMessage); return error; } }
Reply