Troubleshooting @DbLookup, @DbColumn, GetAllDocumentsByKey

好文章，转。

https://www-10.lotus.com/ldd/ddwiki.nsf/dx/dblookup-troubleshooting.htm

Many factors must align to get a key-based lookup to work. If yours is not working, this document lists the things you should check. This is primarily about @DbLookup, but any key-based view lookup, such as NotesView.GetAllDocumentsByKey, has essentially the same set of possible issues. Some of these considerations apply to @DbColumn also (which doesn't use a key), so if you're having trouble with @DbColumn this document is also worth reading.

This is about Notes lookups; the functions @DbLookup and @DbColumn can also be used to read ODBC data or with custom drivers provided by an add-in library, but we don't cover that here -- this is only for where the class argument is "" or "Notes".

A separate section of this document covers the use of @DbLookup in XPage server-side JavaScript code. This function works somewhat differently from the formula language version.

Check Your Inputs

Frequently, developers will conclude a function is broken, when the problem really is that they have provided it the wrong input. As a first step, always check your inputs to make sure they have the value you thought they had. When working with formula language, a statement such as the following may be helpful (substitute your variable name for key):

I talked about the key here because it's the argument most often stored in a variable instead of hardcoded where you can see it in the code. But of course, any of the arguments could be wrong.

In addition, before you decide @DbLookup is returning the wrong value, open the view you're trying to look up to and see what data are really in it. It may be your documents or view column formula that's wrong. If the view is hidden, use Preview from Designer, or the menu View / (Ctrl+Shift) Go to... in the Notes client. If the key is a string, type the key value to activate the view quick search, and see whether you can find the row that way.

Entry not found in index

This is the most common error you will encounter, and in a way it's good news because it means that you located the server and the database and the view, and you do have access to them (though you might still have specified the wrong database or view, and that was why the key isn't found). The next sections detail several ways in which your key can be wrong, in addition to other things.

Basic Requirements for a Successful Lookup 
To begin with, if you use the [FailSilent] option or have other code to trap and ignore an error result , take it out. The error text is valuable information for diagnosing the problem.

In case of failure, do a "sanity check" against this list to make sure the conditions for using this function exist. Error messages are indicative of the problem, but some errors may have more than one possible cause.

• The server must be up and available over the network, and you must have access to use that server. Error messages:
• Unable to find path to server.
• You are not authorized to use the server.
• The database must be found and you must have access to open it. Error messages:
• In LotusScript: Database has not been opened yet.
• File does not exist.
• You are not authorized to perform that operation.
• Failure without an error message.
• If the database was not found, consider the following:
• If the database filepath contains a , and your formula contains the database name as a string literal, you must use \ instead of , e.g. db := @DbName[1] : "res\lookup.nsf". If you forget to do this, the formula editor will strip out the when you save, so the problem should be obvious when you look at the formula later. However, you do have to look at it to notice this. :-)
• The view must exist and you must have access to it. Error messages:
• A view of that name cannot be found in the specified database.
• Look out for the following as regards your target view:
• If the view name contains a , and your formula contains the view name as a string literal, you must use \ instead of , e.g. view := "Instruments\by Type". Note, however, that it's preferable to use the view alias, rather than the title, to locate the view, and that it's also preferred to use a view that's hidden from end users (neither of which is likely to include in its name).
• For a keyed lookup, there must be a sorted column in the view. It doesn't have to be the first column. It doesn't have to be the only sorted column.
• Your key must match a value in the first sorted column. Note that a match is based on the datatype of the key and the sorting attributes of the view. Specifically:
• The datatypes must match. A text key value will not match a numeric column value, for instance, even if the text contains the same digits as the number.
  NOTE: The documentation of the @DbLookup function says the key argument is type Text, but actually numbers, dates, and lists may be used also -- a documentation update is pending.
• If the view column is sorted case-sensitive, the case of the key must match the case of the view entry. Same for pitch sensitivity.
• If the key is a date/time value or number, it must match the value of the view column value exactly (e.g. if the column value contains a date and time, the key must contain the same date and time -- not just a date).
• For text keys, there's an optional parameter to specify whether an exact match is desired.
• Even for a non-exact match, the matching characters must be at the beginning of the key and column value. E.g. the key "City" will not match the entry "Mexico City".
• The field or column number you're pulling the result from, must exist. Check your spelling of field names. Read the Designer documentation -- you can't just count columns to find the column number, because certain types of column don't count (those containing "constant" values and @DocNumber, etcetera). Remember the first column is number 1 for @DbFunctions, 0 if you're looking at the Columnvalues array in LotusScript.
• When returning multiple values (as will occur if there are multiple documents matching your key), formula language returns them as a list. But a list can have only one datatype -- it's a text list, a date/time list, or a number list -- not all of the above. Therefore, if the values in the column or field from which you're reading the results are not all the same datatype, your lookup may fail because there is no way to express the result in formula language. Using @Text or other conversion functions on the result will not help, because the data aren't in it -- just an error message. You just have to lookup to a column that contains consistently typed data.
• When using GetAllDocumentsByKey (or similar methods in the Java or LotusScript object model) you have the option to supply an array of keys to match against more than one sorted column. This of course gives you multiple opportunities to provide the wrong value or wrong datatype, but the above principles apply to each key in the array. (Note: unlike formula language, LotusScript doesn't require all elements of an array to be of the same type).
• Don't forget that if you do a cached lookup (the default), then repeat the exac t same lookup without first fully closing and reopening the current application, you'll get the same result as on your last attempt. Fully closing the application means that you've closed all windows on it, including in Domino Designer. In Designer 8.5, you must actually close Designer to get it to close the application, once you have opened it there.

Other Things that Can Go Wrong

If you have specified the database with a replica ID, use the Domino Administrator tool to make sure there is not another database with the same replica ID on the server.

If the column is multivalued, but the column option to display multiple values in separate rows is not selected, you can only match the first value in a lookup.

In views with more than one categorized column, a lookup on the top-level category key will only return the documents from the first subcategory. E.g. if there's a category "Animals" containing "Dog" and "Horse", and you use "Animals" as your lookup key, you will only return values from documents in the "Dog" subcategory -- no "Horses". Instead, do your lookup to a different view that has only one level of categorization (or that is just sorted, not categorized).

In views with re-sort columns, @DbLookup will search the current sort order that the user has chosen for that view, rather than the default sorting. This is one reason it's generally considered a best practice to do your lookups against hidden views, even if they are near duplicates of views used by end users. There is not any way to predict, adjust, or detect what sort order the user has selected.

It's been reported that for a view column that's not sorted case and pitch sensitive, lookups of some non-ASCII strings will always fail. This needs further investigation. It probably applies to specific characters, and so may depend which language you're using. Meanwhile, if you run into this problem, make sure your view is sorted case and pitch sensitive and that the case and pitch of the key matches that in the column.

Sometimes you may get an error result from @DbLookup because the column you read data from contains an error value. If you look at the view you would see ERROR: displayed in the view. But of course, if this were the case, you would have noticed long before you got to this point, because the first thing you did was open the view, as we suggested up above. Right?

For @DbLookup, if your key is multivalued, the lookup will attempt to look up each key separately and combine the results into a single list, except that if the first key in the list is not found, the whole lookup fails even though other keys may be valid. If the first key succeeds, subsequent keys that fail will just be skipped. Of course, the requirement that all the results be of the same datatype still applies. If you have a list of keys and this style of processing doesn't suit you, using the @Transform function to look up the keys one at a time may be a good alternative.

If you're using @DbLookup to get the value of a rich text field or other non-summary field, and you're getting a blank value back, this may be caused by a view column with the same programmatic name as the rich text field. The view column will be used by preference, rather than looking into the document.

Tips for Performance / Effective Lookups

Developers often routinely use "NoCache", and often it's unnecessary. Using caching can significantly improve performance, particularly in forms used in the Notes client, where frequently there are several keyword lookups on the form which can delay loading if they all use NoCache. For an in-depth discussion of cache control and formula coding tips for best performance, see Performance basics for developers (whitepaper)

When you want to lookup to the current database, the correct second a rgument for @DbLookup and @DbColumn is "" -- not "" : "" (which takes a tiny bit longer to evaluate because it contains an operator) and definitely not @DbName (which wastes time reopening the database you already have open).

Example:

• @DbLookup("":"NoCache"; ""; "CustByID"; custID; 3)

To lookup to another database on the same server whose path you know (presumably because it is the same on all servers), use, e.g.:

• @DbColumn(""; @DbName[1] : "whatever.nsf"; 1)

You may have been taught that it's more efficient to use a column number rather than a field name for the value to retrieve in @DbLookup. This is true on a first level, but the real story is a little more complex. Each column in the view has a field name (called the "programmatic name"). For formula columns, the programmatic name is automatically generated and looks like "$" followed by a number. For columns that refer just to a field, the programmatic name is the same as the field name. If @DbLookup field/column argument is a string, Notes will first try to match the name with the programmatic names of the columns, and if it finds a match, return data from that column. If there's not a matching name, it will look at the document for a field with the same name. This looking at a document is the part that's slow. So you can use a column programmatic name and that is still efficient, plus it's less likely to change than the column number. And if the programmatic name is the same as a field name, it looks like you're reading a field from the document -- but you're really not. 
In Web Applications

There is no problem in using @DbLookup or the corresponding LotusScript or Java methods in a web-based application. There are differences because a Notes client-based application, where code is executing on the user's workstation, you have to specify the server name if the application is on a server. In a Domino web application, the code runs on the server, so to access a database on the same server, use the server name "", which means local.

Even if the same lookup that fails in a web application, works when the form is used in the Notes client, the failure is not because it's in a web application per se, but because of the differences in executing on a client vs. server. For instance, if the lookup specifies a server name, there may be DNS issues or an incorrect connection document on the server that prevents the target server from being found, or server trust settings or ACL considerations that block access to the data. The problem is not with the @DbLookup function, but with the different environment. It can be made to work.

On An XPage

The @DbLookup and @DbColumn functions on an XPage are somewhat different from the standard versions of these functions. The first argument, specifying 'class' and caching, is omitted (it's assumed to be "":"NoCache"). The values of the remaining arguments are used to construct a formula language expression to send to the formula engine for evaluation; this expression contains literals, since the formula engine does not know from your JavaScript variable names. E.g. if your JavaScript code says:

view = 'Reqs\by GLORB factor'; use \ when you mean
key = 'SuperGLORB';
column = 7;
db = new Array(@DbName()[0], 'reqLookups.nsf');
result = @DbLookup(db, view, key, column, '[FAILSILENT],[PARTIALMATCH]');

then the actual, exact macro language expression sent to the Session.evaluate method is @DbLookup("":"NoCache"; "":"reqLookups.nsf"; "Reqs\by GLORB factor"; "SuperGLORB"; 7; [FailSilent]:[PartialMatch]).

NOTE: Just as in formula language, is a special character in JavaScript string literals. To have a single in your string, you must use the sequence \, as above. Unlike formula language, if you forget to use \, the JavaScript edi tor will not strip out the single -- instead, it looks at the following characters to interpret it as a special character such as newline (n) or tab (t). So it may still look deceptively correct. This is only an issue if you hardcode the string. Strings read from elsewhere -- a field in a profile document, say -- contain the characters that are in them; they aren't scanned for escape sequences.

Some other points of difference between XPage and other formula lookups:

• The use of a replica ID to specify the database is not supported (as of version 8.5.1). If you supply a scalar string or one-element array as the dbName argument, it will be interpreted as the current database ("").
• The "key" argument to @DbLookup is required to be a string in the XPages environment. The use of number, date/time, and multivalued keys, is not supported (as of version 8.5.1).
• Since the web browser doesn't know how to evaluate formula language, these functions may only be used in server-side JavaScript code.
• The error return value is the special JavaScript constant NaN (not a number). Unfortunately, there seems to be no way to recover the original error message from the formula engine, which might tell you why the function failed.
• We hope that the special characters  (backslash), ' (apostrophe) and " (doublequote) are handled correctly during the conversion of these strings to formula language literals, but if your lookup fails and the key or view name contains one of these characters, check whether that might be the problem by using the view alias (which we're assuming would not contain these characters) or a different key.
• Since JavaScript is case sensitive, @functions must be in the correct case (e.g.  @DBColumn may not raise an error in the problems view, but it will not return results).
  

Given these limitations, all the information in the previous sections is still applicable to lookups from JavaScript server code on an XPage.

Appendix: Understanding Keyword Lookups

The attributes of lookups vis-a-vis case and pitch sensitivity deserve a little more in-depth treatment, so you can understand what's going on and maybe predict what will and will not work.

When you think of a view lookup, think of it as looking up information in a book where the pages are numbered alphabetically, one entry per page. A page in this scenario corresponds to a view row.

If someone hands you this book and asks you to find a particular entry, you might find it by conducting a binary search. If you've ever played the number-guessing game where someone picks a secret number, you make a guess, and they tell you "higher" or "lower", the process is similar. You open the book to the middle page, and decide whether the entry you're looking for comes before or after that point. Let's say it comes before. Then you open the first half of the book exactly in the middle (in other words, open the book 1/4 of the way from the front). You keep narrowing it down to a smaller and smaller range of pages, and looking at the page exactly in the middle of the range, until you either find the value you want, or find two consecutive pages that the value should appear between, and it doesn't (in which case you decide the value is not there).

Of course, this only works if you and the person who arranged the pages have the same idea about how to sort things alphabetically. If you assume that "chose" comes between "Chicago" and "Cooper", and the entries are sorted case-sensitive, you will fail to find "chose" even if it is there. There's no efficient way to do a case-insensitive search against data with a case-sensitive sort (remember, any character could be of a different case in the key and the matching entry).

Thanks to Nathan Freeman and Sean Jack for their contributions to this article.