Can I loop through a recordset only once?

General
1

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
    rs.MoveFirst
    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?

2

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

3

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

4

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 …

5

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

6

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
foo_rs.MoveNext
i = i + 1
wend[/code]

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

What am I doing wrong with the code above?

7

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.
8

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)