LotusScript's List bug strikes again

Last week i was working with a LotusScript agent which uses List data type for storing text values. The agent was not working properly as it could not find matching values in the second list(which was populated with the same listtags as the first list). I suspected it was something wrong with processing of List, but couldn't find what part of the code was wrong, as I got no error messages. The List was more than 1000 elements and in the debugger I could only see about 200 first elements, which was not very helpful. I have earlier experienced similar problem with IsNull(mylist("listtag")) function, but in this agent there were no such IsNull checks (use IsElement instead of IsNull). After 2 hours of commenting parts of the code out, I finally found what was wrong. The fact of calling tst procedure with nonexistant list value mylist("b") as parameter, creates "b" list element with value ""! It does not generate "List item does not exist" error message as one would expect!

I have hard to think that it works "as designed", as a programmer clearly doesn't want to create a new list element by simply passing it to a procedure.
Using IsElement(mylist("b")) before calling the procedure/function helps to avoid the problem, but that shouldn't be necessary, as the programmer expects an error if the List element does not exist.

Sub Initialize
Dim mylist List As String
mylist("a")="a"
' Msgbox mylist("b") 'properly results in error "List item does not exist"

Call tst(mylist("b")) 'erroneously creates "b" list element

' Msgbox Isnull(mylist("b")) 'erroneously creates "b" list element

Msgbox mylist("b") 'shows "" as list value for "b" instead of an error

End Sub

Sub tst(tmp)

End Sub



Tags: LotusScript

LotusScript to translate text between languages

I am currently working to update our Sametime Bot with a new feature: translation of text. It will be a prompted flow where user is asked for input language, output language and text. The answer from the bot is the translated text. The translation is done by using a LotusScript agent which gets the translation from Google Translate page. LotusScript agent uses MSXML object for web communication with the Goggle server.
See below for an example of translation between English and German.
Pictures show the messagebox for German, Spanish, French and Russian translations. The translation is of course not perfect, but enough good to understand the general meaning.
The best automated translator I've seen so far is by the company "Prompt": http://www.e-promt.com/ It produces very correct translations and even sees the difference between "hello all Domino developers" and "hello to all Domino developers".

Sub Initialize
 Dim xmlhttp, MySoap
 Dim StartTag As String,EndTag As String, FromLang As String, ToLang As String, TextToTranslate As String
 StartTag= |<div id=result_box dir="ltr">|
 EndTag = "</div>"
 FromLang="en"
 ToLang="de"
 TextToTranslate="hello all Domino developers"
 TextToTranslate=Replace(TextToTranslate," ","%20")
 
 WebServer="http://translate.google.com/translate_t?text="+TextToTranslate+"&hl=en&langpair="+FromLang+"|"+ToLang+"&ie=utf-8"
 Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")
 Call xmlhttp.open("GET", WebServer, False)
 Call xmlhttp.setRequestHeader("Content-Type", "text/html; charset=utf-8")
 Call xmlhttp.setRequestHeader("User-Agent","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)")
 Call xmlhttp.setRequestHeader("Accept","image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, */*")
 Call xmlhttp.setRequestHeader("Accept-Language","sv")
 Call xmlhttp.setRequestHeader("Content-Type","application/x-www-form-urlencoded")
 Call xmlhttp.send(Null)
 
 strxml = xmlhttp.responseText
 
 Msgbox Strleft(Strright(strxml,StartTag),EndTag) 
 
End Sub

Tags: LotusScript

Run scheduled agent every 60 seconds

The shortest time between executions of a scheduled agent is 5 minutes. But very often you want to run some important function with only 1 minute interval. Your function takes maybe only 2 seconds to run, but you still are required to wait 5 minutes before it can be started again at the next agent invocation.

The agent I created makes it possible to simulate running agent every 60 seconds, or even every 2 seconds if you wish so. Some restrictions apply :)

It uses the fact that Notes counts 5 minutes from the START of the previous instance, not from the END. So if your agent takes 4 minutes 50 seconds to run, the next execution of the agent (assuming perfect conditions) will be just 10 seconds after the previous execution is finished. Delays caused by server load and other unforeseen delays can cause the agent to wait from 30 seconds to 2 minutes until the next execution. During many tests, I havent's observed execution delay longer than 2 minutes.


Even with the worst case 2 minutes delay, it's much better than standard 5 minutes delay. Note that this delay is only for the time between agent executions, within the started agent you can call the function every 2 seconds if you wish.

What if the function takes more than 5 minutes to run? Well, the next instance of the agent will wait until the current instance is finished and then start after a certain delay. In my tests the delay was almost always 2 minutes. The delay if the agent ends 15 seconds BEFORE the 5-minutes period (4m 45s) was according to my tests always 1 minute 5 seconds. These numbers will most certainly be somewhat different on your server.


The main disadvantage of this method is that one of the Agent Manager's threads will be constantly busy, so you would need to increase the number of agent threads to make it possible for other scheduled agents to run too.

To enable your agent to run every 60 seconds, do following:
1) In your existing agent, move the code from Initialize method to MainAction method.
2) Paste the code from the agent below into the Initialize method of your agent.
3) Change variable values as needed.

Agent is configured to execute the MainAction() function every 60 secondss (runinterval), max 100 times (runmaxtimes), during the 5 minute period (agentschedinterval).

Agent execution flow. Click to enlarge.

"Gone in 60 seconds" agent:

Sub Initialize
    'Created by Andrei Kouvchinnikov, www.botstation.com
 
 Print  "*******************  Agent started  *******************"
 
 Dim session As New NotesSession
 Dim db As NotesDatabase
 Dim expectedruntime As Integer 'time which take to run the function. required only for deciding about ending the loop.
 Dim runinterval As Integer 'interval (sec) between runs
 Dim runmaxtimes As Integer ' max number of times the function will run
 Dim runcounter As Integer 'counter of number of times the function run
 Dim runstart As Long, runend As Long, rundiff As Long
 Dim notimeleft ' loop must be closed now, no time left to run another round
 Dim agentstart As Long 'initial time when agent started. required only for deciding about ending the loop.
 Dim agentschedule As Integer 'nr of minutes this agent is scheduled to run. required only for deciding about ending the loop.
 Dim dynadjust 'instead of hardcoded runtime value, use last run period for calculation of next period
 Dim uselongestperiod 'instead of the last run period, use the longest period the function took to run 
 
 expectedruntime=10 ' function is initially expected to take max 10 seconds
 runinterval=60  ' function is called with interval of X seconds
 runmaxtimes=100  'if function's run time variates, you can limit max number of times function runs
 agentschedinterval=5 'agent is scheduled to run every X minutes. From the agent properties.
 margininterval=5 'nr of seconds to add to the final round. used for situations when function's execution time is 0.
 runcounter=0
 dynadjust=True
 uselongestperiod=True
 
 agentstart=Timer
 
 While runcounter<runmaxtimes And notimeleft=False
  runstart=Timer
  
  Call MainAction()
  
  runend=Timer
  If dynadjust=True Then expectedruntime=Int(runend-runstart)  'dynamically adjust expected time to actual time it take to run the function
  If uselongestperiod Then
   If expectedruntime<Int(runend-runstart) Then expectedruntime=Int(runend-runstart)
  End If
  timeleft=runinterval-expectedruntime
  
  If Int((agentschedinterval*60)-Int(Timer-agentstart))<timeleft+(expectedruntime+Int(expectedruntime/100*30)+margininterval) Then  'assumes that function can take 30% longer time to run than the last time
   notimeleft=True
   Print "Exit. Function will not manage to finish one more run. Computed time: "+Cstr(Now)+" + "+Cstr((expectedruntime+Int(expectedruntime/100*30)))
  End If
  
  If timeleft>0 And notimeleft=False Then 
   Sleep timeleft 'finished before the expected time. sleep until next execution.
  Else
   Sleep Int((agentschedinterval*60)-Int(Timer-agentstart))-margininterval 'sleep X seconds-margin
  End If
  runcounter=runcounter+1
 Wend   
 Print  "*******************  Agent finished  *******************"
End Sub


Sub MainAction()
 Print "Triggered "+Cstr(Now)
 
%REM
Here goes your code
Delete demo code below.
%END REM
 
 Randomize
 sleeprand=Int(Rnd()*10)  
 Sleep sleeprand 'Simulates time taken by the function's operations by sleeping X seconds
 
End Sub

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

Output from the agent. Note the 1m 5s delay between instances.

Technorati: Lotus Notes

The fastest way to programmatically import data from Excel to Lotus Notes

There are several ways to import data from Excel to Lotus Notes/Domino. One of the most popular is the built-in Import menu option, which can be used manually and works fine in most cases.

But if you want to import Excel data programmatically, the easiest way is to use Excel's OLE Automation Objects. There are several ways to read data from Excel using OLE, and the one most often mentioned is reading data cell-by-cell. This is a very slow method, and should only be used if you want to read/write special cell properties such color, fonts, etc.

The fastest method I found so far is to read and write Excel data using blocks of data with ExcelSheet.Range method. You can with a single operation read the whole Excel sheet into an array, which extremely efficient, especially if there are many columns.

I have created an example which can be used to import people from Excel to Notes. Sample Excel file can be downloaded here.

Depending on the type of data and number of columns, the speed of this method can be up to 100 times faster than reading cell-by-cell. It imports 100 person documents per second, and the most of this time is used on creating new Notes documents, not on getting data from Excel.

Sub Initialize
'This agent imports person records from Excel to Notes. It uses Range method which makes it very fast. 
'Created by Botstation Technologies (www.botstation.com) 

Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim xlApp As Variant, xlsheet As Variant, xlwb As Variant, xlrange As Variant
Dim filename As String, currentvalue As String
Dim batchRows As Integer, batchColumns As Integer, totalColumns As Integer
Dim x As Integer, y As Integer, startrow As Integer
Dim curRow As Long, timer1 As Long, timer2 As Long
Dim DataArray, fieldNames, hasData

timer1=Timer
filename="C:\people.xls"
batchRows=200 'process 200 rows at a time 

Set db=session.CurrentDatabase
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'set Excel program to run in foreground to see what is happening 
Set xlwb=xlApp.Workbooks.Open(filename)
Set xlsheet =xlwb.Worksheets(1)

Redim fieldNames(1 To 250) As String

DataArray=xlsheet.Range("A1").Resize(batchRows, 250).Value 'get worksheet area of specified size 

For y=1 To 250 'we assume max 250 columns in the sheet 
currentvalue=Cstr(DataArray(1,y))
If currentvalue<>"" Then 'abort counting on empty column 
fieldNames(y)=currentvalue 'collect field names from the first row 
totalColumns=y
Else
y=250
End If
Next

Redim Preserve fieldNames(1 To totalColumns) As String

curRow=2
hasData=True
While hasData=True 'loop until we get to the end of Excel rows 
If curRow=2 Then startrow=2 Else startrow=1
For x=startrow To batchRows
curRow=curRow+1
If Cstr(DataArray(x,1))+Cstr(DataArray(x,2))<>"" Then 'when 2 first columns are empty, we assume that it's the end of data 
Print Cstr(curRow-2)
Set doc=New NotesDocument(db)
doc.Form="Person"
doc.Type = "Person"
For y=1 To totalColumns
currentvalue=Cstr(DataArray(x,y))
Call doc.ReplaceItemValue(fieldNames(y), currentvalue)
Next
doc.ShortName=doc.FirstName(0)+" "+doc.LastName(0)
Call doc.save(True, False)
Else
hasData=False
x=batchRows
End If
Next
If hasData=True Then DataArray=xlsheet.Range("A"+Cstr(curRow)).Resize(batchRows, totalColumns).Value 'get worksheet area 
Wend
timer2=Timer
Call xlApp.Quit() 'close Excel program 

Msgbox "Done in "+Cstr(timer2-timer1)+" seconds"
End Sub

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

The Excel sample file was generated using this online tool: data generator

Tags: Lotus Domino Excel LotusScript

Callback functionality for COM/ActiveX objects in LotusScript

Unfortunately there is no such callback functionality in Lotus Notes. The only type of callback which developers can use are for Notes native objects.

The only solution I found is to periodically poll a variable from COM object and when the variable's value is changed from the previous poll value, it means something happened and you can take an appropriate action in LotusScript.

For example, you have an ActiveX with function which returns statuses "Busy", "Idle", "In progress", "Finished" for a telephony application. You can not make callback when status changes to immediately notify LotusScript, but you can poll status every 1 second using LotusScript:

Dim status as String
Set MyPhone=CreateObject("PhoneApp")
Call MyPhone.MakeCall("+1818222333444")
status=MyPhone.GetStatus()
While status<>"Finished" And status<>"Idle"
status=MyPhone.GetStatus()
if status="In Progress" then
Print "Call is in progress, continue looping."
end if
if status="Busy" then
Print "Phone is busy, status will change to Finished automatically soon."
end if
Sleep 1
DoEvents
Wend
Call LogCall(StartTime, EndTime, PhoneNumber)

This solution works pretty well in most cases. Make sure you have Sleep and Doevents in your code, otherwise Notes client will hang until the loop is finished.
To avoid locking Notes client while code is running, set agent's property "Run in background client thread" and user will be able to continue working with Notes client even if the agent is still running.

A similar solution was described in my article in The View magazine for working with Skype API.

To make a shorter delay time than 1 second, you can use Sleep 0.5

Another work-around is to use Visual Basic as a wrapper to the COM object.
Visual Basic supports all callbacks, so compiling a Visual Basic project into ActiveX component and exposing variables which are populated from the callback functions inside VB code makes it possible to have access to callbacks which are not accessible with the technique described above.

Tags: LotusScript