About RecordSet and rs.MoveFirst

BrianOBrien · **Posted:** Mon May 06, 2013 3:04 pm

I have a method that returns a record set.
I take that record set and pass it to a display Record Set method.
Then I 'rewind' the record set using MoveFirst.
After that i pass the record set on for further processing.
However rs.EOF is false, but... the IdxField values are Nil.

Bob Keeney · **Posted:** Mon May 06, 2013 3:18 pm

Not all databases support MoveFirst (or MoveLast, or MovePrevious). I think MoveNext is about the only universal method amongst all the databases.

BrianOBrien · **Posted:** Mon May 06, 2013 3:41 pm

Any ideas on how I might copy the recordset into a new structure that I can use more than once?

Private Function RSToDictionary(rs as RecordSet) As Dictionary
  // Assumes RSIdx(1) to be a string and key for the dictionary
  dim d as new dictionary
  
  while not rs.EOF
    redim v(-1) as Variant
    for i as integer=2 to rs.FieldCount
      v.Append(rs.IdxField(i))
    next
    d.Value(rs.IdxField(1).StringValue) = v
  wend
  return d
End Function

Or perhaps use json some how to get column names and values as well?

Bob Keeney · **Posted:** Mon May 06, 2013 3:57 pm

There are a number of ways to do it. You can create an array of dictionaries or a dictionary that contains dictionaries and similar ways of doing it.

We tend to use ActiveRecord http://www.bkeeney.com/rbinto/activerecord/. Not saying it's the best way but it abstracts all the database work from the RB coding work. We recently released ARGen an ActiveRecord Generator for Real Studio/Xojo at http://www.bkeeney.com/allproducts/argen/

BrianOBrien · **Posted:** Mon May 06, 2013 4:11 pm

So given a database schema this class generates an interface to the database whereby you no longer need the RecordSet or SQL class?

Jason_Adams · **Posted:** Mon May 06, 2013 4:20 pm

I can't speak for Bob's ActiveRecord class, but I wrap the table/view in a class which presents an OOP interface to the database. It includes the ability to return aggregates of columns, an active record and MoveNext (wherein another object that has all the columns) is populated, etc.. That way when I interact with the table/view anywhere else, I can think of it in OOP terms.

Hope this helps.

Bob Keeney · **Posted:** Mon May 06, 2013 4:28 pm

BrianOBrien wrote:

So given a database schema this class generates an interface to the database whereby you no longer need the RecordSet or SQL class?

Behinds scene it's still using the RB classes - you just never see them. It has the advantage that you never have to switch between DatabaseRecord or Recordset in your own code.

Other advantages that we like:
1) IDE autocomplete (most of the time) for Table/Fields
2) Compiler awareness of field data types
3) Debug mode warnings of missing fields in your classes (you add one in the db but forget to add it to the class)
4) Before and After events are fired for Create, Save, Delete, etc. and there's also a validation event.

You can use ActiveRecord on your own - you just have to create the classes. Or you can use ARGen to create the classes for you.

BrianOBrien · **Posted:** Mon May 06, 2013 4:40 pm

comments on the following please?

Private Function RSToDictionary(rs as RecordSet) As Dictionary
  // Assumes RS.IdxField(1) to be a string and key for the dictionary
  dim d as new dictionary
  dim v(-1) as string
  
  while not rs.EOF
    dim sd as new Dictionary
    redim v(-1)
    for i as integer=2 to rs.FieldCount
      sd.Value(rs.IdxField(i).Name) = rs.IdxField(i).Value
    next
    d.Value(rs.IdxField(1).StringValue) = sd
    rs.MoveNext
  wend
  return d
  
End Function

So given this should it not be possible to now create a class that provides the same interface as the RecordSet class?

Bob Keeney · **Posted:** Mon May 06, 2013 4:51 pm

BrianOBrien wrote:

So given this should it not be possible to now create a class that provides the same interface as the RecordSet class?

I see no reason why it wouldn't work. In AR we are essentially doing the same thing to check whether or not a record has changed values.

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

About RecordSet and rs.MoveFirst