Real Software Forums
http://forums.realsoftware.com/

About RecordSet and rs.MoveFirst
http://forums.realsoftware.com/viewtopic.php?f=3&t=47801
Page 1 of 1

Author:  BrianOBrien [ Mon May 06, 2013 3:04 pm ]
Post subject:  About RecordSet and rs.MoveFirst

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.

Author:  Bob Keeney [ Mon May 06, 2013 3:18 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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

Author:  BrianOBrien [ Mon May 06, 2013 3:41 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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?

Author:  Bob Keeney [ Mon May 06, 2013 3:57 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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/

Author:  BrianOBrien [ Mon May 06, 2013 4:11 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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

Author:  Jason_Adams [ Mon May 06, 2013 4:20 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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. :)

Author:  Bob Keeney [ Mon May 06, 2013 4:28 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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.

Author:  BrianOBrien [ Mon May 06, 2013 4:40 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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?

Author:  Bob Keeney [ Mon May 06, 2013 4:51 pm ]
Post subject:  Re: About RecordSet and rs.MoveFirst

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.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/