Class

RowSet

Description

A RowSet provides a way to access a group of Database rows (or records) from a query. Use the methods and properties of the RowSet class to navigate among this set of rows and view, edit, remove, and save changes to individual rows.

Properties

Name

Type

Read-Only

Shared

AfterLastRow

Boolean

✓

BeforeFirstRow

Boolean

✓

LastColumnIndex

Integer

✓

Property descriptions

RowSet.AfterLastRow

AfterLastRow As Boolean

True when the row pointer is at the end of the set of rows.

This property is read-only.

The following method populates a ListBox with a RowSet:

Sub PopulateListBox(dataList As DesktopListbox, rs As RowSet)
  If rs Is Nil Then Return

  ' set up listbox state for population
  dataList.RemoveAllRows

  ' Add the DB columns as the heades for the ListBox
  dataList.ColumnCount = rs.ColumnCount
  dataList.ColumnAt(-1).WidthExpression = "100"
  For i As Integer = 0 To rs.LastColumnIndex
    dataList.Heading(i) = rs.ColumnAt(i).Name
  Next

  ' Add the data from the table
  While Not rs.AfterLastRow
    dataList.AddRow("")

    For i As Integer = 0 To rs.LastColumnIndex
      dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
    Next

    rs.MoveToNextRow
  Wend
End Sub

RowSet.BeforeFirstRow

BeforeFirstRow As Boolean

Returns True when the RowSet is before the first row or the RowSet is empty.

This property is read-only.

A new RowSet is always at the first row (if any rows were returned). The only way to set the RowSet to be before the first row is to use RowSet, which is not supported by all data sources.

This moves the RowSet pointer so that BeforeFirstRow is True:

Var rs As RowSet
rs = db.SelectSQL("SELECT * FROM data")
If rs <> Nil Then
  rs.MoveToPreviousRow
  If rs.BeforeFirstRow Then MessageBox("You are before the first row!")
End If

RowSet.LastColumnIndex

LastColumnIndex As Integer

The index of the last column.

This property is read-only.

Method descriptions

RowSet.Close

Close

Closes an open RowSet.

When the RowSet goes out of scope, it is automatically closed.

If you try to use a RowSet after it has been closed, an UnsupportedOperationException is raised.

The following method populates a ListBox with a RowSet and closes the RowSet when finished:

Sub PopulateListBox(dataList As DesktopListbox, rs As RowSet)
  If rs Is Nil Then Return

    ' set up listbox state for population
    dataList.RemoveAllRows

    ' Add the DB columns as the heades for the ListBox
    dataList.ColumnCount = rs.ColumnCount
    dataList.ColumnAt(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.LastColumnIndex
      dataList.HeaderAt(i) = rs.ColumnAt(i).Name
    Next

    ' Add the data from the table
    While Not rs.AfterLastRow
      dataList.AddRow("")

      For i As Integer = 0 To rs.LastColumnIndex
        dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
      Next

      rs.MoveToNextRow
    Wend
    rs.Close
End Sub

RowSet.Column

Column(Name As String) As DatabaseColumn

Returns a DatabaseColumn containing information about the column (specified by Name) from the current position in the RowSet.

If the column name does not exist, an InvalidArgumentException is raised.

Get the string value of a column in a RowSet:

' rs is a RowSet with a column called "ProductName"
Var productName As String
productName = rs.Column("ProductName").StringValue

Set the string value of a column in a RowSet:

' rs is a RowSet with a column called "ProductName":
rs.EditRow
rs.Column("ProductName").StringValue = "Generic Widgets"
rs.SaveRow

RowSet.ColumnAt

ColumnAt(index As Integer) As DatabaseColumn

Returns a DatabaseColumn containing information about the column from the current position in the RowSet.

If the RowSet has no rows, the returned DatabaseColumn only contains the Name of the column.

If index is less than 0 or greater than the highest column index then an OutOfBoundsException is raised.

Like all methods in API 2.0, ColumnAt is 0-based.

Get the string value of a column in a RowSet:

' rs is a RowSet
Var productName As String
productName = rs.ColumnAt(0).StringValue

Changes the string value of a column in a RowSet:

' rs is a RowSet
rs.EditRow
rs.ColumnAt(0).StringValue = "Generic Widgets"
rs.SaveRow

RowSet.ColumnCount

ColumnCount As Integer

The number of columns in the RowSet.

The following method populates a DesktopListBox with a RowSet. Is uses ColumnCount to create the header for the ListBox with the names of the columns in the table:

Sub PopulateListBox(dataList As DesktopListBox, rs As RowSet)
If rs Is Nil Then Return

  ' set up listbox state for population
  dataList.RemoveAllRows

  ' Add the DB columns as the headers for the ListBox
  dataList.ColumnCount = rs.ColumnCount
  dataList.ColumnAt(0).WidthExpression = "100"
  For i As Integer = 0 To rs.LastColumnIndex
    dataList.HeaderAt(i) = rs.ColumnAt(i).Name
  Next

  ' Add the data from the table
  While Not rs.AfterLastRow
    dataList.AddRow("")

    For i As Integer = 0 To rs.LastColumnIndex
      dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
    Next

    rs.MoveToNextRow
  Wend
End Sub

RowSet.ColumnType

ColumnType(index As Integer) As Integer

Returns an integer value for the data type of the passed column.

Use the table below to identify the Column Type based on the index Integer (Not all databases use all these types).

Type

Value

Description

Null

0

Denotes the absence of any value, i.e., a missing value.

Byte

1

Stores the byte representation of a character string.

SmallInt

2

A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. SQLiteDatabase supports 4-byte smallints. If you are using another data source, check the documentation of your data source.

Integer

3

A numeric data type with no fractional part. The maximum number of digits is implementation-specific. SQLiteDatabase supports 8-byte integer columns and the ColumnType evaluates to 19 (64-bit integer).

Char

4

Stores alphabetic data, in which you specify the maximum number of characters for the column, i.e., CHAR (20) for a 20 character column. If a row contains fewer than the maximum number of characters for the column, the remaining characters will be padded with blanks. Text or VarChar, 5, Stores alphabetic data, in which the number of characters vary from row to row, but you don't want to pad the unused characters with blanks. For example, VARCHAR (20) specifies a VARCHAR column with a maximum length of 20 characters.

Text

5

Text up to the amount specified by the database for this column type.

Float

6

Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5).

Double

7

Stores double-precision floating-point numbers.

Date

8

Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits.

Time

9

Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero.

TimeStamp

10

Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component.

Currency

11

This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.

Boolean

12

Stores the values of TRUE or FALSE.

Decimal

13

Stores a numeric value that can have both an integral and fractional part. You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999.

Binary

14

Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary column.

Long Text (BLOB)

15

Stores a text object. Consult the documentation of your data source for information on the maximum size of a BLOB.

Long VarBinary (BLOB)

16

Stores a binary object. SQLiteDatabase supports BLOBs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source.

MacPICT

17

Stores a Macintosh PICT image. No longer supported. Use a BLOB to store images.

String

18

Text up to about 2 billion bytes. The same as VarChar.

Int64

19

Stores a 64-bit integer. Integer columns in SQLiteDatabase are 64 bits and ColumnType returns 19.

Unknown

255

Unrecognized data type.

This example puts the values of an Text/VarChar or String columns into an array

Var colType As Integer
Var values() As String

For i As Integer = 0 To rs.LastColumnIndex
  colType = rs.ColumnType(i) ' ColumnType is 0-based
  If colType = 5 Or colType = 18 Then ' The column is a String
     values.AddRow(rs.ColumnAt(i).StringValue)
  End If
Next

RowSet.EditRow

EditRow

Call EditRow prior to performing modifications to the current row. Be sure the SQL includes the primary key column (or columns) so that you can update the table after making changes. Changes are only saved to the DB when RowSet.SaveRow is called.

Set the string value of a column in a RowSet:

' rs is a RowSet with a column called "ProductName":
Try
 rs.EditRow
 rs.Column("ProductName").StringValue = "Generic Widgets"
 rs.SaveRow
Catch error As DatabaseException
 MessageBox("DB Error: " + error.Message)
End Try

RowSet.Iterator

Iterator As Iterator

Allows for iterating through the RowSet.

RowSet.MoveToFirstRow

MoveToFirstRow

Moves the current row to the first row in the RowSet.

Currently only supported by these databases:

This example moves the current row to the first row in the RowSet.

' move to the first row
rs.MoveToFirstRow

RowSet.MoveToLastRow

MoveToLastRow

Moves the current row to the last row in the RowSet.

Currently only supported by these databases:

This example moves the current row to the last row in the RowSet.

' move to the last row
rs.MoveToLastRow

RowSet.MoveToNextRow

MoveToNextRow

Moves the current row to the next row in the RowSet. After the last row, AfterLastRow is True.

The following method populates a DesktopListBox with a RowSet. It uses the Name and StringValue properties to obtain the column names and values:

Sub PopulateListBox(dataList As DesktopListBox, rs As RowSet)
  If rs Is Nil Then Return

    ' set up listbox state for population
    dataList.RemoveAllRows

    ' Add the DB columns as the headers for the ListBox
    dataList.ColumnCount = rs.ColumnCount
    dataList.ColumnAttributesAt(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.LastColumnIndex
      dataList.HeaderAt(i) = rs.ColumnAt(i).Name
    Next

    ' Add the data from the table
    While Not rs.AfterLastRow
      dataList.AddRow("")

      For i As Integer = 0 To rs.LastColumnIndex
        dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
      Next

      rs.MoveToNextRow
    Wend
    rs.Close
End Sub

RowSet.MoveToPreviousRow

MoveToPreviousRow

Moves the current row to the previous row in the RowSet. Before the first row, BeforeFirstRow is True.

Currently only supported by these databases:

A RowSet is populated in memory when you call the SelectSQL method. If you use MoveToPreviousRow to go back to prior rows (including those that you may have changed using the EditRow method), then you will get the values that were originally populated, not the changed values.

This example moves the current row to the previous row in the RowSet.

' move to the previous row
rs.MoveToPreviousRow

RowSet.RemoveRow

RemoveRow

Removes the current row in the RowSet.

' Remove the current row in an existing RowSet
' db is an existing database connection
Try
 rs.RemoveRow
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

RowSet.RowCount

RowCount As Integer

The number of rows in the RowSet.

Currently only supported by these databases:

For databases that do not support this function, an UnsupportedOperationException is raised.

RowSet.SaveRow

SaveRow

Call to save changes to the current row in the RowSet.

Set the string value of a column in a RowSet:

' rs is a RowSet with a column called "ProductName":
Try
 rs.EditRow
 rs.Column("ProductName").StringValue = "Generic Widgets"
 rs.SaveRow
Catch error As DatabaseException
 MessageBox("DB Error: " + error.Message)
End Try

Warning

RowSets from MySQL, PostgreSQL and SQLite are cached thus the RowSet will appear unchanged after calling SaveRow. You will need to query the database again to refresh the data in the RowSet. ODBC is not cached locally by the Xojo framework but may be cached by client drivers.

Notes

If you are creating an application as a front-end in a multi-user environment, keep in mind the following. The EditRow method will attempt to lock the current row to other users. If a user tries to access a locked row, a DatabaseException is raised. You can then check its Error and Message properties for details.

A RowSet is closed when it goes out of scope.

If you try to use a RowSet after it has been closed, an UnsupportedOperationException is raised.

The MoveToNextRow method unlocks the current row if you previously locked it with the EditRow method. Previously locked rows are also unlocked by the Close method of the class and the Close method of the Database class.

Iterator

You can iterate through a RowSet using For Each and a DatabaseRow. This lets you get at the column values of the row but does not let you make any changes to the row (if you do then a DatabaseException is raised).

' db is a valid connection to a SQLite database
Var rows As RowSet
Var postalCode As String = "12345"
Try
  rows = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", postalCode)
  If rows <> Nil Then
    For Each row As DatabaseRow In rows
      ListBox1.AddRow(row.Column("Name").StringValue)
    Next
    rows.Close
  End If
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Move methods

Not all data sources support all four record navigation methods, MoveToFirstRow, MoveToNextRow, MoveToPreviousRow, MoveToLastRow. The limitations of some of the data sources are presented in the following table.

Data Source

Limitation

SQLiteDatabase

Supports all row navigation methods

Oracle

Supports all row navigation methods

ODBC

Supports all row navigation methods

mySQL

Supports only MoveToNextRow method

MS SQL Server

Supports only MoveToNextRow method

PostgreSQL

Supports only MoveToNextRow method

Sample code

This code gets the data from the Team table using the iterator and displays it in a ListBox:

' mDB is a previously connected database

Var sql As String
sql = "SELECT * FROM Team"

Var results As RowSet
Try
  results = mDB.SelectSQL(sql)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  Return
End Try

If results <> Nil Then
  For Each row As DatabaseRow In results
    DataList.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
      row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue)
  Next
  results.Close
End If

This code gets the data from the Team table and displays it in a ListBox:

' mDB is a previously connected database

Var sql As String
sql = "SELECT * FROM Team"

Var results As RowSet
Try
  results = mDB.SelectSQL(sql)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  Return
End Try

If results <> Nil Then
   While Not results.AfterLastRow
    DataList.AddRow(results.ColumnAt(0).StringValue, results.ColumnAt(1).StringValue, _
      results.ColumnAt(2).StringValue, results.ColumnAt(3).StringValue)

    results.MoveToNextRow
  Wend
  results.Close
End If

The following method populates a DesktopListBox with a RowSet. It uses the Name and StringValue properties to obtain the column names and values:

Sub PopulateListBox(dataList As DesktopListBox, rs As RowSet)
  If rs Is Nil Then Return

    ' remove all rows
    dataList.RemoveAllRows

    ' Add the DB columns as the headers for the ListBox
    dataList.ColumnCount = rs.ColumnCount
    dataList.ColumnAttributesAt(-1).WidthExpression = "100"
    For i As Integer = 0 To rs.LastColumnIndex
      dataList.HeaderAt(i) = rs.ColumnAt(i).Name
    Next

    ' Add the data from the table
    While Not rs.AfterLastRow
      dataList.AddRow("")

      For i As Integer = 0 To rs.LastColumnIndex
        dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
      Next

      rs.MoveToNextRow
    Wend
End Sub

Interfaces

The RowSet class implements the Iterable class interface.

Compatibility

All project types on all supported operating systems.