Hello,I am looking for a way to fetch a multirow resultset, store the record indexes to an array,and in a second loop randomly pick indexes to re-fetch some of the rows. I am using DB2 Ver 7. I tried setting the DB2 connection property as scrollable and set the row pointer in the For loop but its not working; The DBA says DB2 scrolling is supported.
My code was successfully executing to retrieve the resultset and fetch rows before adding the conn.LCTOKEN_SCROLLABLE = 1.
I use a counter for indexing the rows to the array. In a subsequent loop I want to re-fetch some of the indexes. I must be missing something key to how LSXLC works. In LSXODBC this code successfully allowed me to get the currentrow for re-positioning in the resultset. My goal is to re-fetch in a loop without doing new Executes. I have provided a section of my code. Thanks for your help in advance.
Dim rowindx as integer, aindx as integer, arry() as integer
Dim num_left as integer, i as integer, m as integer, loopmax as integer
Dim rowcnt as integer, fldfname As LCField, fldlname As LCField
Dim conn As LCConnection, Dim fldLst As New LCFieldList(50)
Set conn = New LCConnection(“db2”)
conn.LCTOKEN_SCROLLABLE = 1
rowcnt = conn.Execute (queryStr, fldLst)
Set fldlname = fldLst.Lookup (“RESPONDER_LN”)
Set fldfname = fldLst.Lookup (“RESPONDER_FN”)
'Do Loop fetches all rows
rowindx = 1
aindx = 0
On Error Goto TrapFetch
Do
count= conn.Fetch (fldlst, 1, 50)
arry(aindx) = rowindx
aindx = aindx + 1
rowindx = rowindx + 1
Loop Until count = 0
num_left = aindx-1 'starting point of array
loopmax = = Int(rowindx * (xPercent/100) )
For i = 1 To (loopmax)
m = Int(num_left * Rnd(1)) ' randomly select an item (a result set row number) from the array
conn.LCTOKEN_POSITION = m
count = conn.Fetch (fldlst)
lastname = fldlname.text(0)
firstname = fldfname.text(0)
For j = m To num_left-2 ' remove the chosen item (row number) from the array
arry(j) = arry(j+1)
Next
num_left = num_left -1 ' one less value in the array
Next
:
:
processing continues
:
:
TrapFetch:
errorStr = "Error fetching rows " & Err & ": " & Error
rl.logerr errorStr
Exit Function