Lotus Domino Designer 8 Help - Examples: Working with a result set

LOTUS CONNECTORS

Examples: Working with a result set
1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

Glossary

Feedback on Help or Product Usability?

Help on Help

All Help Contents

Glossary