Looping ADODB recordset from Lotusscript do not finish or it return rows in random order

Question

I am running an SQL stored procedure using ADODB from Lotusscript in a scheduled Agent in IBM Domino and get very strange results.

This is the script I run

Sub test

    On Error GoTo e 
    Dim sqluser As String,sqlpw As String
    Dim srv As String, database As String, username As String, pw As String, sqlquery As String
    Dim tmp As String,t As Long

    sqlsrv = "..."
    sqldb = "..."
    sqluser = "..."
    sqlpw = "..."

    strConn = |Provider=SQLOLEDB.1;Server=| + sqlsrv + |;Database=| + sqldb + |;UID=| + sqluser + |;PWD=| + sqlpw
    Set adoConn = CreateObject("ADODB.Connection")
    Call adoConn.Open(strConn)
    Set rs = createobject("ADODB.Recordset")
    tmp = |Select ArtGr,KA,Sum(Belopp) Belopp, Sum(Kvantitet) Kvantitet From CrmStatTmp Where Len(KA)>0 Group By ArtGr,KA Order By KA,ArtGr|
    rs.open tmp,adoConn
    rs.MoveFirst
    Do Until rs.eof
        Print "Processing " + CStr(t)
        t = t + 1
        rs.movenext
    Loop

    Set rs = Nothing
    adoConn.close
    Set adoConn = Nothing
    Print "Ready"
    Exit sub
e:

    Print "Error"
    Print Error,Erl
    Set rs = Nothing
    adoConn.close
    Set adoConn = Nothing


End Sub

There does not seem to be any errors, the script just seem to stop executing after approx 345, (the number can be different I change the SQL query).

I do not belive there is anything wrong with the SQL query as this is working fine when we try it in other tools.

The image below show how the counter count up to 344 and then suddenly start to count from 326 again. also the script do not seem to get finished as the last line "Ready" is never printed out, and there do not seem to be an error as the print statement in the error handler is never printer out.

I have tried to limit the number of rows returned from the SQL query but that does not help much, at one point I set it to only return the top 20 records and then the counter got to 15 and seem to run the whole script again. so the output was 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,0,1,2...

I have used ADODB in Lotusscript many times and this kind of script have worked many times before.

How can I change the code to get my script to process all the rows and in correct order

note: some object are declared in another module.

Update: It gets even more strange if I change the loop to this because this time the scripte runs to the end, but the numbers are still not in sequence

Do While Not rs.eof
        Print "Processing=" + CStr(t) + "/Value=" + CStr(rs.fields("ArtGr").value) + "/Status=" + CStr(rs.status) 
        t = t + 1
        rs.movenext
    Loop

I don't know anything about the Lotus environment but if I saw that behaviour I would assume the code was executing on more than 1 thread or there was more than one process running that code. Print a message at the top of the sub and see if its invoked more than once. — Alex K.
You are printing the local variable t++ in a loop, you would not see it change its value out of sequence if a single instance of test() were running — Alex K.
you are probably right, I wonder how that is possible, I am only executing the code once. maybe some IBM people can answer this one. it is however strange that the script "restart/change" the counter at 344 every time — Thomas Adrian
Always interesting with these types of problems. As this is LS you could easily debug the code (step through it) to see what happens.... — John Dalsgaard

Begbie007 Begbie007 · Accepted Answer · 2015-12-03T09:52:12

I don't have enough rep to just write a comment, but if this is a scheduled agent it will run immediately when you make a change to the code and save it - it will then kick off on schedule as well, so could be running twice because of this. Hope that helps.

Looping ADODB recordset from Lotusscript do not finish or it return rows in random order

2 Answers