Rambling, Petrol and DBLookups

I'm off to spend a couple of days in the Peak District. Myself and a friend are going to walk Grains in the Water & Alport Castles (hopefully finding this plane crash) and Edale Circuit from Hope the following day, with a stop over in Bamford.

It's not very often said friend and I find the time to get in t'ills and even now I feel like it might be a stupid thing to do. We've hired a car for a few days and it comes with half a tank of petrol (gas to the Americans). This might be all the fuel we have available. The nation has gone stupid again and is panic-buying fuel, despite being warned not to. Chances are we'll get stuck in them there hills for quite a while, unless we find a supply en route.

Anyway, while I'm gone, I have something for you. Remember when I got all fired up about Ajax? Well, I created a sample database to show how useful it could be to us Domino-heads. The database is meant as an accompaniment to the article I planned. That article might take longer than I originally thought, so here's the demo site to play with in the mean time.

It shows how you can perform @DBLookups using Ajax. It works, but I'm not sure if it makes sense as a demonstration. What do you think? It contains documents that record details of books - author, cover type, title, pages etc. The five fields at the top of the form are the arguments to a standard @DBLookup. You can change the last three of these, press the button and get the new results without a page refresh. Try changing the last field from 3 to 2 and instead of number of pages you will see the name of the book's author.

My idea is to make the code available as a unit to plugin to any database. You then have a JavaScript function call doDBLookup() that you can use when needs be. I'll do this within a week or so hopefully. I just wondered what you would make of the demo database.


    • avatar
    • Andrew Tetlaw
    • Tue 13 Sep 2005 07:44 PM

    Awsome idea!

  1. A sample would be great. I did some Ajax for DBlookup and found that it's great if your list is really hugh multi selections. But for small selection list, passing the whole list as a javascript array will be sufficient. Looking forward to see your demo

  2. You may want to show the use of AJAX in various type of dbLookup scenarios. Maybe an example of a keyword list that gets populated after another keyword is selected. Maybe even a type ahead where the lookup can return options similar to what the user has typed. I did an AJAX demo of a phone directory that did a lookup based upon what the user typed in.


    BTW: since you and Bob Obringer showed me the application value of AJAX, I have used it twice in applications where alternative methods would have been reeeealy messy. I highlighted one of them a few weeks ago.


  3. Funny, I was thinking about this over the past few days. This would be great to provide to even developers of non-notes applications (using either SSO or anonymous access).

    I might be able to use this in dozens of types of applications. I've been puting off my work on this because I wanted to really figure out how to do categories and readers field protected documents. Do we just load up the entire view and keep it in memory? This won't be possible in every situation depending on the server configuration.

    I have a feeling this calls for a Notes View javascript object that exposes the info we are looking for via various functions. Been doing a little OO javascript work but by no means an expert. This should be interesting...

    Very interested to see what you're working on...

    @Jeff: Not sure how I missed your post. Been reading your blog but completely missed it. That's some good stuff right there!

    • avatar
    • Jon Hart
    • Wed 14 Sep 2005 07:16 AM


    I emailed you an example db the other day you might find interesting utilizing some AJAX.

    Was wondering if you got it?


  4. Thanks Bob. Wouldn't you know, I used another smattering of AJAX today with an approval button that can do a document approval without leaving read mode.

    useful stuff this is!

    BTW, everyone should take a peak at Bob's site where he is definitely onto something neat with his JS solution.

    • avatar
    • Indy
    • Fri 16 Sep 2005 09:00 AM

    How about performance? Can anyone tell me the Break Even Point of @DbLookup vs AJAX dbLookup?

    • avatar
    • Jake Howlett
    • Fri 16 Sep 2005 09:06 AM

    Indy. It's not really the two methods in direct speed comparison that's of interest. An @DBLookup call on teh server is almost undoubtedly quicker than a round-trip AJAX call. The point of AJAX is that it can be done without the whole web page doing the round-trip to teh server.

    • avatar
    • Ayhan
    • Sat 17 Sep 2005 01:26 PM

    Hmm... Does iwa work with ajax? If you scroll down the inbox, there is a box "retrieving data...".. is this solved with ajax too?

  5. Indy. @dblookups perform serial lookups per field. If you have 7 - 10 @dblookups or @DBcommand [odbc] 's you will feel the performance toll big time. Ajax is the way to do it.

  6. Jake, the demo is easy to use but it only brings 1 single field.

    How about looking up several fields that receive multiple items?

    put the key and process a recordset

    thats the best way to show how much better Ajax is compared than @dblookingup

  7. 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-12



    var xmldoc=new ActiveXObject("MSXML2.DOMDocument");





    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;


    return "error";


    columnNode=xmldoc.documentElement.selectNodes("/viewentries/viewentry/entrydata[@columnnumber='0' and .='"+dblKey+"']");

    dblArray=new Array();




    xmldoc.setProperty("SelectionLanguage", "XPath");

    xpathquery="/viewentries/viewentry/entrydata[starts-with(../@position,'"+NodePosition+".') and @columnnumber="+dblColumn+"]";

    categoryNode= xmldoc.documentElement.selectNodes(xpathquery);










    return dblArray;



    var errName=e.name;

    var errNumber=e.number & 0xFFFF;

    var errDescription=e.description;

    errMessage=e+"\n\nName: "+errName+"\nNumber: "+errNumber+"\nDescription: "+errDescription;


    return error;



  8. I've recently jumped on the, very late, Ajax train. Lotusphere 2006 tweaked my interest a whole lot.

    Your demo database here is a prime example of somthing I'd like to experiment with. Any chance you could make this demo available for download?

    It would really help out a newbie to see it in action. (On the designer side of Notes)

  9. I'm trying to use this method is for getting the related Counties for selected District. But unfortunately i have more than 5000 documents in my view this works upto 1000....How can i get it work done with no limit on documents in my view ...even i have passed the DBLLimit like 10000...but did not help....

    Any help!!!

Your Comments


About This Page

Written by Jake Howlett on Wed 14 Sep 2005

Share This Page

# ( ) '


The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social

About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content