Working with data retrieved from an external database

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

Use the SetValue and UpdateRow methods to test the return status.

Deleting and adding rows

To delete the current row, use the DeleteRow method:

Call result.DeleteRow(tableName)
Call result.UpdateRow

To add a row:

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

The call to AddRow creates a special AddRow workspace containing exactly one row. If you make another row current, you can go back to the AddRow workspace by specifying DB_ADDROW as the row number. The call to UpdateRow method deletes the AddRow workspace.

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.

Working with rows

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

The call to NextRow is at the top of the loop. The code that processes the row follows. The Until clause is at the end of the loop and conditionalizes the loop on IsEndOfData. Do not conditionalize the loop on the return status of NextRow because a False return status also generates an error.

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

Likewise, if you process the result set back to front, you must explicitly move to the last row and process the last row before entering the loop, as follows. (Alternatively, you can call PrevRow at the bottom of the loop and process the first row after exiting the loop.)

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

Specify FirstRow to start the search at the beginning of the result set. The While loop terminates when LocateRow returns False. The loop explicitly exits if the row being processed (LocateRow is True) happens to be the last row. Otherwise, NextRow signals an error, and if the error is not handled, the loop is infinite on the last row.

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")

It can also be specified by number:

lastName = result.GetValue(2)

Use the IsValueNull method to check for null values.

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 following table lists the SQL data types, as returned by FieldNativeDataType; the corresponding default LS:DO data type constants, as specified for FieldExpectedDataType; and their LotusScript language data types as returned by Typename. The data has to be convertible to the specified type for Typename to return the correct type; otherwise LotusScript generates an exception.

Working with fields

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

Field names are strings that represent the names as they appear in the data source table.

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.

FieldSize returns the maximum size, given the number or name of a field.