RowSet

From Xojo Documentation

Revision as of 15:17, 10 March 2021 by Gperlman (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Class (inherits from Object)


New in 2019r2

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
AfterLastRow fa-lock-32.png BeforeFirstRow fa-lock-32.png LastColumnIndex fa-lock-32.png


Methods
Close EditRow RemoveRow
Column MoveToFirstRow RowCount
ColumnAt MoveToLastRow SaveRow
ColumnCount MoveToNextRow
ColumnType MoveToPreviousRow

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.

Record Navigation

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

Examples

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
MsgBox("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
MsgBox("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 ListBox with a RowSet. It uses the Name and StringValue properties to obtain the column names and values:

Sub PopulateListBox(dataList As Listbox, 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.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.CellValueAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
Next

rs.MoveToNextRow
Wend
End Sub

See Also

Database, DatabaseColumn, DatabaseRow, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, SQLiteDatabase classes.