I’ve written a LotusScript agent that builds a result set from a physical file on an AS/400, reads through that result set, and for each record it retrieves, it might update several Notes documents, and then delete the record from the AS/400.
The agent works perfectly if I run it from my client. If there are 9 records in the physical file, is processing all 9 one-by-one and deletes all records one-by-one once they have been processed; i.e. once the corresponding Notes documents have been updated.
The problem is that if I all my Domino server to execute the agent, it only reads the first record, updates the corresponding Notes documents, and then deletes that first record. It doesn’t process any additional records. It seems as though the DeleteRow method is leaving the file pointer at end-of-file so that my subsequent NextRow returns false. I’ve tried numerous things, but none have worked. I’ve tried using FirstRow after the delete thinking I need to reposition to the first row now that the original first row has been deleted. That didn’t work either.
Below is my code without all the declarations:
Status% = con.ConnectTo(“ALPHA”, user, pswd)
Set qry.Connection = con
qry.Sql = “select * from plcustf.tlfrompl”
Set res.Query = qry
Status% = res.Execute
Status% = res.FirstRow
While Status% = True
key = res.GetValue(“UNIQID”)
aYear = res.GetValue(“VHEXYR”)
aMonth = res.GetValue(“VHEXMN”)
Set collection = view.GetAllDocumentsByKey
(key)
If collection.count >= 0 Then
For i = 1 To collection.count
Set doc = collection.GetNthDocument(i)
doc.AccountingYear = aYear
doc.AccountingMonth = aMonth
Call doc.Save (True, True)
Next
End If
res.deleteRow(“plcustf.tlfrompl”)
Status% = res.NextRow
Wend
res.close (db_close)
con.disconnect