Lotus Domino Designer 8 ヘルプ - 例:結果セットを操作する

LOTUS CONNECTOR

例:結果セットを操作する
1 次のエージェントは ATDB データソースの [STUDENTS] テーブルのすべてのフィールド (列) を検査します。

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 次のエージェントは、結果セットのすべての行を先頭行から 2 回アクセスします。初回は、Execute に続く最初の NextRow が暗黙的に FirstRow を設定するので、FirstRow を明示的に設定する必要はありません。2 回目は明示的に FirstRow を設定し、ループに入る前に先頭行を処理する必要があります。

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 このエージェントは、結果セット内でフィールド 5 に「Cambridge」、フィールド 6 に「MA」の入ったすべての行を検索します。

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 次のエージェントは、結果セット内の各行の [STUDENT_NO]、[FIRSTNAME]、および [LASTNAME] フィールドの値を含むメッセージを表示します。結果セットの値が格納される変数もまた、GetValue の 2 番目の引数として明示的なデータ型指定に使われます。

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 次のエージェントは [STUDENTS] テーブルの先頭行のフィールド 1 を検査し、期待したデータ型が DB_CHAR の場合にその LotusScript の Typename を報告します。注釈になっている定数を使って、他のデータ型に関してフィールドをテストします。

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 次の例は「Phone book」というフォームとビュー (両方同じ名前) に基づいています。フォームには、[lastName]、[firstName]、[phoneNumber] という 3 つのフィールドがあります。ビューは 3 つのフィールドを表示し、4 つのアクションを備えています (ビューの内容に基づいてテーブルを作成するアクション、ビューから選択された文書をテーブルへ追加するアクション、選択された文書をテーブルとビューから削除するアクション、テーブルを表示するアクション)。

テーブルを作成するアクションでは、テーブルの作成方法、削除方法、テーブルへの行の追加方法を示します。行を追加するアクションでは、テーブルへの行の追加方法を示します。行を削除するアクションでは、行を検索しテーブルから削除する方法を示します。

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

関連項目

外部データベースから取得したデータを操作する

用語集

フィードバック ヘルプ または プロダクトユーザビリティ?

ヘルプの使い方

すべてのヘルプ項目

用語集