How To Work With One Row in RowSet

I know that my SQL RowSet is going to contain either 0 or 1 rows. How can I make use of the data in that row without a for each loop?

Var rs As RowSet
rs = App.DB.SelectSQL("SELECT * FROM planets WHERE name = 'Earth';")

How do I extract all the planet data about “Earth” from rs?

Why not still use the for each if you know there’s 0 or 1 row?

One option:

Var rs As RowSet
rs = App.DB.SelectSQL("SELECT * FROM planets WHERE name = 'Earth';")
If rs <> nil and rs.RowCount > 0 then
  'Do something with the data in the first row
Else
  'Do something when there is no data available
End if

Just seems a little overkill to extract data from one row.

[quote=481606:@Douglas Handy]One option:

Var rs As RowSet rs = App.DB.SelectSQL("SELECT * FROM planets WHERE name = 'Earth';") If rs <> nil and rs.RowCount > 0 then 'Do something with the data in the first row Else 'Do something when there is no data available End if [/quote]

Hi, @Douglas Handy ! Thanks for the sample. That’s kinda where I’m at. The part I’m asking about is the,

'Do something with the data in the first row

How do I reference the first row of the RowSet? I tried with parens, but it tells me it’s not an array.

http://documentation.xojo.com/api/databases/rowset.html ?

The first row of data is already available in the RowSet, so if you know the names of the columns you can do something like:

Var rs As RowSet
rs = App.DB.SelectSQL("SELECT * FROM planets WHERE name = 'Earth';")
If rs <> nil and rs.RowCount() > 0 then
  Var distanceFromSun as Double = rs.Column("average_distance").DoubleValue
  Var planetName as String = rs.Column("name").StringValue
  Var orbitDays as Double = rs.Column("orbit").DoubleValue
  Var diameter as Double = rs.Column("diameter").DoubleValue
End if

That is, creating a valid rowset shoud inherently position you at the first row (assuming there is one, hence the check for RowCount) unless you first do one of the rs.MoveTo… methods, close the rowset, or it goes out of scope.

Ah ha! OK, thank you. Paradigm received and understood. :smiley: