Examples: Retrieving data from an external database

This agent declares new ODBCConnection, ODBCQuery, and ODBCResultSet objects; connects to a data source; associates the ODBCConnection object with the ODBCQuery object and the ODBCResultSet object with the ODBCQuery object; specifies and executes a query; and examines the result set from the first to last row.

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
  If Not con.ConnectTo("ATDB") Then
    Messagebox "Could not connect to ATDB",, _
    "Error connecting"
    Exit Sub
  End If
  Set qry.Connection = con
  Set result.Query = qry
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  msg = "Student names:" & Chr(10)
  If result.IsResultSetAvailable Then
    Do
      result.NextRow
      firstName = result.GetValue("FIRSTNAME")
      lastName = result.GetValue("LASTNAME")
      msg = msg & Chr(10) & firstName & " " _
      & lastName
    Loop Until result.IsEndOfData
    result.Close(DB_CLOSE)
  Else
    Messagebox "No data retrieved for STUDENTS",, _
    "No data"
    Exit Sub
  End If
  Messagebox msg,, "Student Names"
  con.Disconnect
End Sub

This script executes when the user exits from the Part_Number field in a Parts document. The script executes a query using the value of Part_Number in the WHERE clause of the SELECT statement, then fills in the PartName, Cost, and Description fields based on values in the retrieved record.

Sub Exiting(Source As Field)
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim res As New ODBCResultSet
  Dim ws As New Notesuiworkspace
  Dim uidoc As Notesuidocument
  Set uidoc = ws.CurrentDocument
  If con.ConnectTo("PARTS") Then
    Set qry.Connection = con
    qry.SQL =  _
    "select * from PARTS where PART_NO = '" + _
    uidoc.FieldGetText("Part_Number") + "'"
    Set res.Query = qry
    res.Execute
    If res.IsResultSetAvailable Then
      res.FirstRow
      Call uidoc.FieldSetText("Part_Number", _
      res.GetValue("PART_NO"))
      Call uidoc.FieldSetText("Part_Name", _
      res.GetValue("PART_NAME"))
      Call uidoc.FieldSetText("Price", _
      res.GetValue("COST"))
      Call uidoc.FieldSetText("Description", _
      res.GetValue("DESCRIPTION"))
    Else
      Messagebox("No information found for " + _  
      uidoc.FieldGetText("Part_Number") )      
    End If
    res.Close(DB_CLOSE)
    con.Disconnect
  Else
    Messagebox("Could not connect to database server")   
  End If
End Sub

This agent sets the parameters in an SQL query before executing it. NumParameters is used as the upper bound of a loop, and GetParameterName is used to prompt for each parameter value.

Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim inputParameter 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" & _
  " WHERE FIRSTNAME = ?firstName? AND LASTNAME = _
  ?lastName?"
  For i = 1 To result.NumParameters
    inputParameter = _
    Inputbox$(result.GetParameterName(i), _
    "Parameter " & i)
    Call result.SetParameter(i, "'" _
    & inputParameter & "'")
  Next
  result.Execute
  msg = "Student name: " & Chr(10)
  If result.IsResultSetAvailable Then
    result.NextRow
    studentNo = result.GetValue _
    ("STUDENT_NO", studentNo)
    firstName = result.GetValue _
    ("FIRSTNAME", firstName)
    lastName = result.GetValue("LASTNAME", lastName)
    msg = msg & Chr(10) & studentNo & " " & _
    firstName & " " & lastName
  Else
    Messagebox "Cannot get result set"
    Exit Sub
  End If
  Messagebox msg,, "Student name"
  con.Disconnect
End Sub