LOTUS CONNECTORS

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

See Also