I need to use a record set twice.
This record set comes from a mySQL database and as I understand it, the rewind method does not work for MySQL database.
So is there a way to copy the record set?
Is a subclass of record set needed perhaps to implement my own rewind that does work? anyone got one?
dim rs1, rs2 as RecordSet
rs1 = database.select(sql)
rs2 = rs1 'One is probably just a reference to the other so touching one effect the other.
rs2 = new recordset(rs1) // ???
rs1 = database.select(sql)
rs2 = database.select(sql) // seems like a lot of overhead just to get a copy.
The latter is the way to do it, but I would think there wouldn’t be much overhead due to caching (unless you’re getting back a ton of records, I guess).
I think the biggest mistake people make is assuming that a RecordSet actually “exists”… when in most cases (I believe) it is actually an encapsulation of a Database Cursor… and hosts in memory a Pointer (which is affected by the MOVE commands), the data content of the current record, and in some (probably most) cases a read-ahead buffer of “N” records. But rarely (unless the return result is small) does a RecordSet physically contain the complete records involved in the query.
This allows the recordset view to only transmit the actually records needed at any given moment, and to page/buffer the rest
Querying the database twice is guaranteed to produce identical record sets.
Dave may be correct but if you can’t rely on the class to isolate you from the issue Dave raises and you can’t duplicate it what then?
Still unsure of what the solution is.
If you have only a Forward moving Cursor, the only way to affect a “Rewind” is to reissue the query… Which can be done with the same record set, unless there is a reason to have multiple record sets pointing to different places within the database
If you DO need to insure you have all the records… create a class that mimics the record structure, then create an array or dictionary, use the recordset to fill that. and then you can jump around to your hearts content… What you CANNOT do (unless you have a primary key)… is insure you can UPDATE the record.
You could use a class based structure like ActiveRecord and simply put the ‘records’ into an array and then do whatever you want with the array. If you’re talking a million rows of data it may not be a great idea but it will work find with tens of thousands (unless you have a boatload of binary data).
Not necessarily if the db is multi user
Between one select & another a different user could do something that inserts data that affects the second query result
Pardon me that was a type. I mean to say it doesn’t guarantee identical record sets. (But I couldn’t edit it.)
I’ve created a simple object to replace the record set in my classes… However it assumes every field is a string value, which of course it may not be… Perhaps I should have stored the value as a variant?
[code]Sub Constructor(rs as RecordSet)
dim d as Dictionary
ReDim Records(-1)
While Not rs.EOF
d = new Dictionary
For i As Integer = 0 To rs.FieldCount-1
d.Value(rs.IdxField(i+1).Name) = rs.IdxField(i+1).StringValue
Next
Records.Append(d)
rs.MoveNext
Variants can be slow and there’s implicit type conversion that can happen if you’re not careful. But, unless you want to create a class with properties to mimic your database that is probably the easiest way to do it.
