I’ve been using Lotus Script to query SQL databases for the last few years, and initially used do loops within my initial SQL loop to grab a certain number of records to create each notes doc, or update each notes doc.
This was always cumbersome, because using the nextRow method of the ODBCResultSetClass to get the next value to check and see if the loop should continue created problems and a great deal of extra code at the end to escape the loop and do whatever was necessary left to do.
So I’ve always broken down queries into very small fragments, creating an initial “select distinct” query for master records and then looping through each and creating individual queries with however many rows will make up each new notes document.
I’d like to return to larger queries, to speed up my agents and simplify some code as well.
I’ve been trying to use the locateRow method of the ODBCResultSetClass to do something similar like:
Do
x = 0
rs.firstRow
strDist = rs.getValue(“s_dist”)
Do While rs.LocateRow(3, strDist)
If x = 0 Then
Redim aStoreList(x), aAddr1(x), aCity(x), aState(x), aZip(x)
Else
Redim Preserve aStoreList(x), aAddr1(x), aCity(x), aState(x), aZip(x)
End If
aStoreList(x) = rs.getValue(“s_store”)
aAddr1(x) = Left(rs.getValue(“s_addr1”),25)
aCity(x) = Left(rs.getValue(“s_city”),20)
aState(x) = rs.getValue(“s_state”)
aZip(x) = rs.getValue(“s_zip”)
x = x + 1
%rem
Problem here is that I can cannot make sure
nextRow is valid because I really want the end of the locateRow, not the end of the whole resultset
%rem
If rs.IsEndOfData Then Exit Do
rs.NextRow
Loop
CreateNewDoc 'subroutine not included in
'my sample here
Loop Until rs.IsEndOfData
This is just one thing that I’m trying, but I’m open to any code that provides a more streamlined solution.
Thanks