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 |
---|---|---|---|
✓ |
|||
✓ |
|||
✓ |
|||
✓ |
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.ColumnCount
ColumnCount As Integer
The number of columns in the RowSet.
This property is read-only.
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.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.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:
ODBCDatabase, although not all ODBC drivers implement this
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:
ODBCDatabase, although not all ODBC drivers implement this
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:
ODBCDatabase, although not all ODBC drivers implement this
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:
ODBCDatabase, although not all ODBC drivers implement this
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.
See also
Object parent class; Database, DatabaseColumn, DatabaseRow, ODBCDatabase, PostgreSQLDatabase, SQLiteDatabase classes.