LotusScript Versions of DBLookup and DBColumn Using Evaluate

I was looking through Jamie Magee’s slides from a 2008 ILUG presentation to review the performance tips he showed us at IamLUG2009. When reading slide #6 I found an interesting tidbit: “When only reading data from documents – @DBLookup and @DBColumn is fastest.” I assumed he meant through an Evaluate statement.

Could this be true? I always considered it a best practice to avoid the Evaluate statement. Then I found an article on ibm.com from 1998 called “Simplifying your LotusScript with the Evaluate statement.” The article is a great synopsis of the Evaluate statement and it confirmed Jamie’s opinion.

Here’s their view:

The reason the Evaluate is faster is because the @DbLookup gets values from a view index, without having to loop through and access each document. In contrast, pulling values using GetAllDocumentsByKey requires you to loop through and access each document.

That’s great but writing Evaluate strings for DBLookup and DBColumn calls isn’t exactly fun. So… I decided to wrap the calls in a handy class.

To call the code is pretty easy:

use "libLSViewTools"

Dim tools as New LSViewTools
dim result as variant

'** function call is DBlookup(server As String,database As String,
'** view As String,key As String,pickMe As Variant,exactMatch As Boolean) As Variant

result = tools.DBLookup("myserver","mydatabase.nsf","myview","myfield",true)
print result(0)

result = tools.DBColumn("myserver","mydatabase.nsf","myview",1)
print result(0)

The parameter “pickme” in the dblookup function is a variant because I allow a number (column) or string (field name) to be sent (just like @DBLookup).

The lss file can be downloaded here: libLSViewTools.lss

The full code is here for your perusal:

'libLSViewTools: 

'***** libLSViewTools
'* Library with on class LSViewTools.
'* Class LSViewTools currently has two functions: DBlookup and DBColumn. Refer to the the Notes help
'* for a description of the formula equivalents

Option Public
Option Declare
%INCLUDE "lsconst.lss"

Class LSViewTools
	Private boolPrintErrors As Boolean
	Private boolThrowErrors As Boolean

	Sub New()
		boolPrintErrors = True
		boolThrowErrors= False
	End Sub

	Property Set printErrors As Boolean
		boolPrintErrors = printErrors
	End Property

	Property Set throwErrors As Boolean
		boolThrowErrors = throwErrors
	End Property

	Function DBlookup(server As String,database As String,view As String,key As String,pickMe As Variant,exactMatch As Boolean) As Variant
		Dim session As New NotesSession
		Dim evalBuilder As String
		Dim exactMatchStr As String
		Dim result As Variant

		On Error Goto error_proc
' Start Validation
		If view = "" Then
			Error 7000, "View cannot be null"
		End If

		If Typename(pickme) <> "STRING" And Typename(pickme) <> "INTEGER" Then
			Error 7001, "5th Parameter must be type string or integer."
		End If

		If server <> "" And database = "" Then
			Error 7003, "Database cannot be null if the server is specified"
		End If

		If exactMatch = False Then
			exactMatchStr = ";[PARTIALMATCH]"
		Else
			exactMatchStr = ""
		End If

		If server = "" And database <> "" Then
			server = session.CurrentDatabase.Server
		End If

		If Typename(pickme) = "STRING" Then
			evalBuilder = {@DbLookup("";@Name([CN];"}+ server + {"):"} + database + {";"} + view + {";"} + key + {";"} + pickme + {"} + exactMatchStr + {);}
		Else
			evalBuilder = {@DbLookup("";@Name([CN];"}+ server + {"):"} + database + {";"} + view + {";"} + key + {";} + pickme + exactMatchStr + {);}
		End If

		result = Evaluate(evalBuilder)

		If Isarray(result) Then
			dblookup = result
		Else
			dblookup = Evaluate("NULL")
		End If

		Exit Function
error_proc:
		If boolPrintErrors Then
			Print "LSViewTools " + Cstr(Getthreadinfo(LSI_THREAD_PROC)) + " Error: " + Cstr(Err) + " " + Error + ""
		End If
		If boolThrowErrors Then
			Error 7554, "LSViewTools " + Cstr(Getthreadinfo(LSI_THREAD_PROC)) + " Error: " + Cstr(Err) + " " + Error + ""
		End If
		DBLookup = Evaluate("NULL")
		Exit Function
	End Function

	Function DBColumn(server As String,database As String,view As String,column As Integer) As Variant
		Dim session As New NotesSession
		Dim evalBuilder As String
		Dim exactMatchStr As String
		Dim result As Variant

		On Error Goto error_proc
' Start Validation
		If view = "" Then
			Error 7000, "View cannot be null"
		End If

		If server <> "" And database = "" Then
			Error 7003, "Database cannot be null if the server is specified"
		End If

		If server = "" And database <> "" Then
			server = session.CurrentDatabase.Server
		End If

		evalBuilder = {@DbColumn("";@Name([CN];"}+ server + {"):"} + database + {";"} + view + {";} + Cstr(column) + {);}

		result = Evaluate(evalBuilder)

		If Isarray(result) Then
			If Cstr(result(0)) = "1.#INF" Then
				Error 7010, "DBColumn returned too many values"
			End If

			dbColumn = result
		Else
			dbColumn = Evaluate("NULL")
		End If

		Exit Function
error_proc:
		If boolPrintErrors Then
			Print "LSViewTools " + Cstr(Getthreadinfo(LSI_THREAD_PROC)) + " Error: " + Cstr(Err) + " " + Error + ""
		End If
		If boolThrowErrors Then
			Error 7555, "LSViewTools " + Cstr(Getthreadinfo(LSI_THREAD_PROC)) + " Error: " + Cstr(Err) + " " + Error + ""
		End If
		DbColumn = Evaluate("NULL")
		Exit Function
	End Function

End Class