Domino Code Fragment

Code Name*
Using LotusScript to Read from Non-Notes Databases - Challenge*		Date*
04/28/2024		Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.3.145.201.71
Description*
Using LotusScript to Read from Non-Notes Databases - Challenge*		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:

Exercise Title : Challenge

Where : Populate Agent

Solution :



(Options)

Option Public
Uselsx "*lsxodbc"
%INCLUDE "LSCONST.LSS"

(Initialise)

Sub Initialize

'-- Make ODBC Connection
Dim Con As New ODBCConnection
RetCode% = Con.ConnectTo("MRP System")

'-- Define ODBC Query
Dim Qry As New ODBCQuery

'-- Set Connection property of ODBCQuery Object
Set Qry.Connection = Con

'-- Set SQL property of ODBCQueryObject
Qry.SQL = "SELECT * FROM BIKEPART"

'-- Set Query property of ODBCResultSet
Dim res As New ODBCResultSet
Set Res.Query = Qry

'-- Execute the ODBC Query
Res.Execute

'-- Drill down to current database object
Dim Session As New NotesSession
Dim db As NotesDatabase
Set db = Session.currentdatabase

'-- Get View object for RecID view to check whether document exists already before adding
Dim view As NotesView
Set view = db.getview("RecId")

'-- Create document object reference
Dim Doc As NotesDocument

'-- Get First Row in ResultSet
RetCode% = Res.FirstRow

'-- Iterate through result set creating a new document for each row
Do While RetCode% = True

'-- See if document exists with RecId from Row
key$ = Cstr(Res.GetValue("RECID") )
Set Doc = view.getdocumentbykey( key$ )

'-- if document exists in database, delete it
If Not ( Doc Is Nothing ) Then
Call Doc.Remove( True )
End If

'-- Create new notes document object
Set Doc = New NotesDocument(db)

'-- Set form item
doc.form = "Bikepart"

'-- Populate document object with values from row
doc.recid = Cstr( Res.GetValue("RECID") )
doc.partno = Res.GetValue("PARTNO")
doc.vendor = Res.GetValue("VENDOR")
doc.type = Res.GetValue("TYPE")
doc.dimension = Res.GetValue("DIMENSION")
doc.cost = Res.GetValue("COST")

'-- Save document
Call doc.save (True, True)

'-- Get Next Row in ResultSet
RetCode% = Res.NextRow
Loop

RetCode% = Res.Close( DB_CLOSE )
RetCode% = Con.Disconnect

End Sub