Examples: SQL property

The following example of a view script sets a default query in the Postopen script, then allows the user to specify alternate queries in the Click event of an action. A second action displays the current query. A third action executes the query and displays fields from the result set.

Uselsx "*LSXODBC"
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim defaultQuery As String
Sub Postopen(Source As Notesuiview)
  Set con = New ODBCConnection
  Set qry = New ODBCQuery
  Set result = New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  defaultQuery = "SELECT * FROM STUDENTS"
  qry.SQL = defaultQuery
End Sub

Sub Click(Source As Button)
  Dim inputSQL As String
  inputSQL = Inputbox("Defaults to: " & defaultQuery, _
  "SQL statement", qry.SQL)
  If inputSQL = "" Then inputSQL = defaultSQL
  qry.SQL = inputSQL
End Sub

Sub Click(Source As Button)
  Messagebox qry.SQL,, "SQL statement"
End Sub

Sub Click(Source As Button)
  Dim firstName As String
  Dim lastName As String
  Dim msg As String
  con.ConnectTo("ATDB")
  result.Execute
  If result.IsResultSetAvailable Then
    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"
    result.Close(DB_CLOSE)
  Else
    Messagebox "Cannot get result set"
    Exit Sub
  End If
  con.Disconnect
End Sub