Can I loop through a recordset only once?

I’m reading data from a a database into a record set, rs. It has 15 rows and I’m looking for a row meeting certain criteria. So it looks like this:

sql = "select * from foo"
dim rs as recordset
rs = db.SQLselect( sql )
//now I need to loop through rs a couple of times so I pass it through a global function named rsChomper( rs as recordset ) returning an int4

For i = 1 to n
    foo = rsChomper( rs )
    ... do something with foo ...
next i

The problem is that foo might find what it wants in record 6
The next time it is called in the loop, rsChomper gets record 6 and beyond, but I need it to start over with record 0.

For some reason rs is not able to go to the beginning. So question is: when I step through a recordset, does the link I just read get trashed?
Does the recordset have to be declared local?

I don’t recall having this problem before. I usually read through a rs only once. Do I have to dump the rs into an array or something?

I believe the only database that supports anything other then MoveNext is SQLite. What kind of database?

If you’re only looking for certain criteria why not use a WHERE clause in the select?

Kem, you got it. I’m using PostgreSQL which supports only movenext. Always used SQL Server before.

Tim, the array is a set of probabilities that have to be searched, the where clause restricts to about 15 records and I have to search them several times looking for a different probability value.

Thanks, you’ve solved the problem. No problem dumping the RS into an array. Too bad I wasted a day trying to figure out why my probability sequences were crazy …

Depends on server and settings.
e.g. with MBS SQL Plugin we can open recordsets scrollable or non scrollable.

I have the MBS SQL plugins, but since this a one-off program, I decided it would be faster to just copy the 15-link recordset into an array of doubles and move on, since I haven’t use MBS SQL classes yet. Figured this would be faster and easy. Easy yes, faster … well, hell…

Here is my code:

[code]dim rsArray( -1, -1 ) as double

sql = select IntA, intB, DoubleC from tablename where IntA = 2342 order by DoubleC

//now run the query and get the recordset, containing 3 fields, so this would be a ( 15, 3 ) array. But i don’t like to hardcode even in
//use-once programs, so:

foo_rs = pgDB.SQLselect( sql ) // foo_rs will contain 15 rows by 3 columns of data

// so now copy into rsArray

i = 0
while not foo_rs.eof
for j = 0 to 2
rsArray( i, j ) = foo_rs.idxFeld( j + 1 ).DoubleValue // this throws an out of bound exception
next j
i = i + 1

if I hardcode the dimension rsArray( 15, 3 ), it works fine.

What am I doing wrong with the code above?

You never dim’d the array to hold all the elements. There are a few ways to handle this:

  • Dim your array initially to hold WAY more than you’d need, then Redim after to just the record count - 1.
  • Store your elements into three separate arrays, appending as you go. Then either use those or create the multi-dimensional array from those.
  • Issue a SQL “count” first to find out how many records you’ll be getting, then use that to Redim your array.

or just dump the whole mess in an in memory db and then query that repeatedly
it will be flexible (since its sql) and fast (since its in memory)