Examples: Working with a result set

This agent examines all the fields (columns) in the STUDENTS table of the ATDB data source.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim msg As String
  Dim m2 As String
  con.ConnectTo("ATDB")
  If Not con.IsConnected Then
    Messagebox "Could not connect to ATDB",, _
    "No connection"
    Exit Sub
  End If
  Set qry.Connection = con
  Set result.Query = qry
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  If Not result.IsResultSetAvailable Then
    Messagebox "Couldn't get result set",, "No data"
    Exit Sub
  End If
  msg = "Fields in STUDENTS table:" & Chr(10)
  For i = 1 To result.NumColumns
    fieldInfo = result.FieldInfo(i)
    If fieldInfo(DB_INFO_READONLY) = DB_READONLY Then
      m2 = "read-only"
    Else
      m2 = "read-write"
    End If
    msg = msg & Chr(10) & _
    i & " -> " & _
    result.FieldName(i) & ", " & _
    "size " & " " & result.FieldSize(i) & ", " & _
    m2
  Next
  Messagebox msg,, "Fields"
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub

This agent accesses all the rows of a result set twice starting from the first row. The first time you do not need to explicitly set FirstRow because the first NextRow following an Execute implicitly sets FirstRow. The second time you must explicitly set FirstRow and process the first row before entering the loop.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim firstName As String
  Dim lastName As String
  Dim msg As String
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  msg = "Student names:" & Chr(10)
  Do
    result.NextRow
    firstName = result.GetValue("FIRSTNAME", firstName)
    lastName = result.GetValue("LASTNAME", lastName)
    msg = msg & Chr(10) & firstName & " " & lastName
  Loop Until result.IsEndOfData
  Messagebox msg,, "Student Names"
  msg = "Student names:" & Chr(10)
  result.FirstRow
  firstName = result.GetValue("FIRSTNAME", firstName)
  lastName = result.GetValue("LASTNAME", lastName)
  msg = msg & Chr(10) & firstName & " " & lastName
  Do
    result.NextRow
    firstName = result.GetValue("FIRSTNAME", _
    firstName)
    lastName = result.GetValue("LASTNAME", lastName)
    msg = msg & Chr(10) & firstName & " " & lastName
  Loop Until result.IsEndOfData
  Messagebox msg,, "Student Names"
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub

This agent locates all the rows in a result set with "Cambridge" in field 5 and "MA" in field 6.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim msg As String
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = _
  "SELECT STUDENT_NO, LASTNAME, FIRSTNAME" & _
  " FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  msg = "Students from Cambridge MA:" & Chr(10)
  result.FirstRow
  Do While result.LocateRow(5, "Cambridge", 6, "MA")
    msg = msg &Chr(10)
    For i = 1 To result.NumColumns
      msg = msg & result.GetValue(i) & "  "
    Next
    If result.IsEndOfData Then Exit Do
    result.NextRow
  Loop
  Messagebox msg
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub

This agent displays a message containing the values of the STUDENT_NO, FIRSTNAME, and LASTNAME fields for each row in the result set. The variable into which the result set value is stored is also used as the second argument to GetValue to make the data typing explicit.

Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim studentNo As String
  Dim firstName As String
  Dim lastName As String
  Dim msg As String
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  msg = "Student names:" & Chr(10)
  If result.IsResultSetAvailable Then
    Do
      result.NextRow
      If result.IsValueNull("STUDENT_NO") Then
        studentNo = "None"
      Else
        studentNo =  _
        result.GetValue("STUDENT_NO", _
        studentNo)
      End If
      If result.IsValueNull("FIRSTNAME") Then
        firstName = "None"
      Else
        firstName = _
        result.GetValue("FIRSTNAME", firstName)
      End If
      If result.IsValueNull("FIRSTNAME") Then
        lastName = "None"
      Else
        lastName = _
        result.GetValue("LASTNAME", lastName)
      End If
      msg = msg & Chr(10) & _
      studentNo & " " & firstName & " " & lastName
    Loop Until result.IsEndOfData
  Else
    Messagebox "Cannot get result set for STUDENTS"
    Exit Sub
  End If
  Messagebox msg,, "Student Names"
  con.Disconnect
End Sub

This agent examines field 1 of the first row of the STUDENTS table and reports its LotusScript Typename when the expected data type is DB_CHAR. Use the constants that are remarked out to test the field for other expected data types.

Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  If result.IsResultSetAvailable Then
    result.NextRow
    REM DB_CHAR DB_SHORT DB_LONG DB_DOUBLE DB_DATE 
    REM DB_TIME
    REM DB_BINARY DB_BOOL DB_DATETIME 
    REM DB_TYPEUNDEFINED
    Call result.FieldExpectedDataType(1, DB_CHAR)
    Messagebox result.GetValue(1) & " " & _
    "DB_CHAR" & " " & _
    Typename(result.GetValue(1)),, _
    "Field, expected data type, type name"
  Else
    Messagebox "Cannot get result set"
    Exit Sub
  End If
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub

This example is based on a form and view, both named "Phone book." The form has three fields: lastName, firstName, and phoneNumber. The view displays the three fields and has actions to create a table based on the contents of the view, add selected documents from the view to the table, delete selected documents from the table and the view, and display the table.

The action for creating a table demonstrates how to create a table, delete a table, and add rows to a table. The action for adding rows demonstrates how to add rows to a table. The action for deleting rows demonstrates how to locate and delete rows from a table.

Uselsx "*LSXODBC"
%INCLUDE "lsconst.lss"
Dim session As NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Sub Postopen(Source As Notesuiview)
  Set session = New NotesSession
  Set db = session.CurrentDatabase
  Set view = db.GetView("Phone book")
End Sub
Sub Click(Source As Button)
REM Create new table
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "CREATE TABLE Phone (LASTNAME CHAR(32), " & _
  "FIRSTNAME CHAR(32), PHONENO CHAR(16))"
  result.Execute
  If qry.GetError <> DBstsSUCCESS And _
  Mid$(qry.GetExtendedErrorMessage, 31, 19) = _
  "File already exists" Then
    If Messagebox _
    ("Do you want to delete the existing table?", _
    MB_YESNO, "Table already exists") = IDYES Then
      result.Close(DB_CLOSE)
      qry.SQL = "DROP TABLE Phone"
      If Not result.Execute() Then
        Messagebox "Couldn't drop",, "Error"
        con.Disconnect
        Exit Sub
      End If
      result.Close(DB_CLOSE)
      qry.SQL = _
      "CREATE TABLE Phone (LASTNAME CHAR(32), " & _
      "FIRSTNAME CHAR(32), PHONENO CHAR(16))"
      result.Execute
    Else
      result.Close(DB_CLOSE)
      con.Disconnect
      Exit Sub
    End If
  End If
  If qry.GetError <> DBstsSUCCESS Then
    result.Close(DB_CLOSE)
    con.Disconnect
    Messagebox qry.GetExtendedErrorMessage,, _
    qry.GetErrorMessage
    Exit Sub
  End If
  result.Close(DB_CLOSE)
  qry.SQL = "SELECT * FROM Phone"
  result.Execute
  Set doc = view.GetFirstDocument
  While Not(doc Is Nothing)
    result.AddRow
    Call result.SetValue("LASTNAME", doc.lastName(0))
    Call result.SetValue("FIRSTNAME", _
    doc.firstName(0))
    Call result.SetValue("PHONENO", _
    doc.phoneNumber(0))
    result.UpdateRow
    Set doc = view.GetNextDocument(doc)
  Wend
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub
Sub Click(Source As Button)
REM Add new row(s)
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM Phone"
  result.Execute
  Set dc = db.UnprocessedDocuments
  Set doc = dc.GetFirstdocument()
  If dc.Count = 0 Then
    result.Close(DB_CLOSE)
    con.Disconnect
    Exit Sub
  End If
  While Not(doc Is Nothing)
    result.AddRow
    Call result.SetValue("LASTNAME", doc.lastName(0))
    Call result.SetValue("FIRSTNAME", _
    doc.firstName(0))
    Call result.SetValue("PHONENO", _
    doc.phoneNumber(0))
    result.UpdateRow
    Set doc = dc.GetNextDocument(doc)
  Wend
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub
Sub Click(Source As Button)
REM Delete row(s)
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM Phone"
  result.Execute
  Set dc = db.UnprocessedDocuments
  If dc.Count = 0 Then
    result.Close(DB_CLOSE)
    con.Disconnect
    Exit Sub
  End If
  Set doc = dc.GetFirstDocument
  While Not(doc Is Nothing)
    If result.LocateRow(1, doc.lastName(0), _
    2, doc.firstName(0)) Then
      result.DeleteRow("Phone")
    End If
    Call doc.Remove(True)
    Set doc = dc.GetNextDocument(doc)
  Wend
  view.Refresh
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub
Sub Click(Source As Button)
REM Display table
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM Phone ORDER BY LASTNAME"
  result.Execute
  msg = "Phone entries:" & Chr(10)
  Do
    result.NextRow
    firstName = result.GetValue("FIRSTNAME", _
    firstName)
    lastName = result.GetValue("LASTNAME", lastName)
    phoneNo = result.GetValue("PHONENO", phoneNo)
    msg = msg & Chr(10) & firstName & " " _
    & lastName & " " & phoneNo
  Loop Until result.IsEndOfData
  Messagebox msg,, "Phone numbers"
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub