The ODBCResultSet class provides properties and methods to manage data retrieved with ODBCQuery. The data is stored in a result set that contains fields (columns) and rows based on what the SELECT query fetches from the external database table.

Creating a table

1. Use the Execute method to execute an SQL query containing a CREATE TABLE statement that specifies the data types and sizes of the fields to be retrieved.

2. Close the result set with the Close method.

3. Execute an SQL query containing a SELECT * FROM [table name] statement to set up the column information.

4. Populate the table using the AddRow, SetValue, and UpdateRow methods in the ODBCQueryResult object.

5. Close the result set.

To re-use this table later in the script, execute an SQL query containing a new SELECT statement; this reinitializes the result set's properties.

To prevent this table from being updated, set the ReadOnly property to True.

Deleting a table

To delete a table, execute an SQL query that contains a DROP TABLE statement.

Updating a table

Updates include changing, deleting, and adding rows.

Changing a value in a row

To change the values in the current row, call the SetValue method one or more times to specify the new value for each field that is changing, then call the UpdateRow method to implement the changes in the external database table. Specify the column by number or name.

Call result.SetValue(1, column1Value)
Call result.SetValue(2, column2Value)
...
Call result.UpdateRow

Deleting and adding rows

To delete the current row, use the DeleteRow method:

Call result.DeleteRow(tableName)
Call result.UpdateRow

1. Use the AddRow method to create the AddRow workspace.

2. Use the SetValue method one or more times to specify the value for each field in the row.

3. Use the UpdateRow method to add the new row to the back-end database and close the AddRow workspace.

Call result.AddRow
Call result.SetValue(1, column1Value)
Call result.SetValue(2, column2Value)
...
Call result.UpdateRow

When you delete and add rows, the updates are not reflected in the result set. The result set retains the same number of rows and the same row numbering as before. If you want the result set to reflect updates, execute another SELECT query.

The UpdateRow method implements SQL INSERT and UPDATE statements, depending on the operation being performed; the DeleteRow method implements SQL DELETE statements. Alternatively, you can issue these statements directly with the SQL property and the Execute method, but this is not recommended.

Getting update status

The following methods provide update status:

• IsValueAltered indicates whether a field in the result set row was changed by SetValue after being fetched or being created by AddRow.
• GetRowStatus indicates whether the result set row was changed by SetValue as above, plus whether the row is deleted by DeleteRow, added by AddRow, updated by UpdateRow, or remains unchanged.
• HasRowChanged indicates whether the table row in the data set has changed since being fetched--that is, whether the table value differs from the result set value as fetched. A True return value indicates that another program modified the row in the table since your program fetched the row.

The rows of a result set are identified by number. Row numbers are integers starting at 1 and ending at the return value of the NumRows method. NumRows does not return an accurate value until the entire result set is fetched into memory.

Moving among rows

To move among rows, use the NextRow, PrevRow, FirstRow, LastRow, and LocateRow methods. The CurrentRow property returns the number of the row that is in position for processing. The IsBeginOfData and IsEndOfData methods return True on the first and last rows, respectively.

Using the NextRow method following the Execute method moves to the first row if no other positioning methods intervene. This behavior permits you to process an entire result set in a loop of the following form:

result.Execute
Do
 result.NextRow
 code for processing one row
Loop Until result.IsEndOfData

If you process the result set front to back after intervening positioning methods occur (for example, for a second pass over the data), you must explicitly move to the first row and process the first row before entering the loop, as follows. (Alternatively, you can call NextRow at the bottom of the loop and process the last row after exiting the loop.)

result.FirstRow
code for processing first row
Do
 result.NextRow
 code for processing one row
Loop Until result.IsEndOfData

result.LastRow
code for processing last row
Do
 result.PrevRow
 code for processing one row
Loop Until result.IsBeginOfData

The LocateRow method positions you on the first row that contains specified values for up to three fields. The search starts with the current row, proceeds in a forward direction, and returns False if a row with the specified values is not found. The following code processes all rows in the result set containing "Cambridge" in field 5 and "MA" in field 6.

result.FirstRow
Do While result.LocateRow(5, "Cambridge", 6, "MA")
 code for processing row
  If result.IsEndOfData Then Exit Do
 result.NextRow
Loop

You can tell that a memory shortage caused truncation of the result set when, on the last row, IsEndOfData is True, but NumRows are still DB_ROWSUNKNOWN.

If you set the CurrentRow property to 0, you raise an error (#545) once data has been fetched.

Modifying rows

The DeleteRow, UpdateRow, and HasRowChanged methods use SQL to complete their operations. These methods can fail when the result set contains columns that are Image or Rich Text because SQL statements cannot handle these data types correctly. To avoid failure, consider keeping two sets of results. One set can include columns of data types that SQL can handle and one set includes columns of data types that SQL cannot handle (that is, the Image and Rich Text types).

The DeleteRow, UpdateRow, and HasRowChanged methods work most efficiently when the specified column has unique values; if the column doesn't contain unique values, set an error to trap DBstsNUNQ to make sure you are accessing unique rows.

To discard the AddRow workspace and any changes that might be in it, call the Close method with the argument DB_CANCELADDROW. The rest of the result set remains open.

Retrieving a field in the current row

The GetValue method returns the value of a field in the current row. The field can be specified by name:

lastName = result.GetValue("LASTNAME")

lastName = result.GetValue(2)

The data type of the return value is determined as follows:

• If you specify a variable as a second argument, the return value is the type of the variable. This technique overrides all others. The following code returns the value of LASTNAME as a String data type.

  Dim lastName As String
  Call result.GetValue("LASTNAME", lastName)

• You can specify the type with FieldExpectedDataType before calling GetValue. This technique has precedence if the second argument of GetValue is not specified. The following code returns the value of LASTNAME as a String data type.

  Call result.FieldExpectedDataType("LASTNAME", DB_CHAR)
  lastName = result.GetValue("LASTNAME")

• If you do not use the above techniques, the LotusScript data type takes a default based on the SQL data type, as shown in the table below.

The fields (columns) of a result set are identified by number and name. Field numbers are integers starting at 1 and ending at the return value of the NumColumns method. To access all the fields in a result set, use a loop of the form:

For n = 1 To resultSetObject.NumColumns

The following ODBCResultSet methods provide information on a field:

• FieldID returns the field number, given the field name.
• FieldInfo returns an array of information, given the number or name of a field. Named constants provide indexes into the array.
• FieldName returns the field name, given the field number.

Example

Glossary