Domino Code Fragment

Code Name*
Updating Oracle database from Lotus Notes		Date*
04/28/2024		Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.18.116.62.45
Description*
You can use the ODBC (Open Database Connectivity) Version 2.0 standard to access data in non-Notes databases. Using formulas or scripts embedded in Notes objects, you can integrate the data from many external databases into Notes applications. For example, a customer call-tracking application in Notes can access customer financial data from an ODBC-compliant relational database management system.		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:
Have a look in Notes Help. You can find a lot of examples.

Uselsx "*LSXODBC"
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Sub Postopen(Source As Notesuidocument)
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
On Event AfterFirstRow From result Call AfterPositionChange
On Event AfterLastRow From result Call AfterPositionChange
On Event AfterNextRow From result Call AfterPositionChange
On Event AfterPrevRow From result Call AfterPositionChange
con.ConnectTo("ATDB")
qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
result.Execute
If Not source.EditMode Then
source.EditMode = True
End If
result.FirstRow
Call source.FieldSetText("Student_No", _
Cstr(result.GetValue("STUDENT_NO")))
Call source.FieldSetText("FirstName", result.GetValue("FIRSTNAME"))
Call source.FieldSetText("LastName", result.GetValue("LASTNAME"))
Call source.FieldSetText("Address", result.GetValue("ADDRESS"))
Call source.FieldSetText("City", result.GetValue("CITY"))
Call source.FieldSetText("State", result.GetValue("STATE"))
Call source.FieldSetText("Zip", result.GetValue("ZIP"))
Call source.FieldSetText("Phone", result.GetValue("PHONE"))
Call source.FieldSetText("Cr_to_date", _
Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Click(Source As Button)
REM Action to get the next row
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc = workspace.CurrentDocument
If Not result.IsEndOfData Then result.NextRow
Call uidoc.FieldSetText("Student_No", _
Cstr(result.GetValue("STUDENT_NO")))
Call uidoc.FieldSetText("FirstName", result.GetValue("FIRSTNAME"))
Call uidoc.FieldSetText("LastName", result.GetValue("LASTNAME"))
Call uidoc.FieldSetText("Address", result.GetValue("ADDRESS"))
Call uidoc.FieldSetText("City", result.GetValue("CITY"))
Call uidoc.FieldSetText("State", result.GetValue("STATE"))
Call uidoc.FieldSetText("Zip", result.GetValue("ZIP"))
Call uidoc.FieldSetText("Phone", result.GetValue("PHONE"))
Call uidoc.FieldSetText("Cr_to_date", _
Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Click(Source As Button)
REM Action to get the previous row
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc = workspace.CurrentDocument
If Not result.IsBeginOfData Then result.PrevRow
Call uidoc.FieldSetText("Student_No", _
Cstr(result.GetValue("STUDENT_NO")))
Call uidoc.FieldSetText("FirstName", result.GetValue("FIRSTNAME"))
Call uidoc.FieldSetText("LastName", result.GetValue("LASTNAME"))
Call uidoc.FieldSetText("Address", result.GetValue("ADDRESS"))
Call uidoc.FieldSetText("City", result.GetValue("CITY"))
Call uidoc.FieldSetText("State", result.GetValue("STATE"))
Call uidoc.FieldSetText("Zip", result.GetValue("ZIP"))
Call uidoc.FieldSetText("Phone", result.GetValue("PHONE"))
Call uidoc.FieldSetText("Cr_to_date", _
Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
result.Close(DB_CLOSE)
con.Disconnect
End Sub
Sub AfterPositionChange(res As ODBCResultSet)
Dim ws As New NotesUIWorkspace
Dim source As NotesUIDocument
Set source = ws.CurrentDocument
Call source.FieldSetText("RowNumber", Cstr(res.CurrentRow))
End Sub