LOTUSSCRIPT
Using Lotus Connectors and ODBC to update Lotus Notes records
gary cronin
12.04.2007
Rating: -2.56- (out of 5)
By using Lotus Connectors and ODBC (open database connectivity), this LotusScript code will read records from an external, non-Lotus Notes database. It will then search a Lotus Notes database for the record. If it finds the record, it will update it if necessary. If it doesn't find the record is it searching for, it will create a new Lotus Notes document.
'OPTIONS
Option Public
Uselsx "*lsxlc"
'DECLARATIONS
Dim count As Long
Dim mb As Long
Dim wintitle As String
Dim selectionstatement As String
Dim b_notesRecFound As Boolean
Dim Externalfld_UNIQUE_FIELD As LCField
Dim Externalfld_THIS_FIELD_IS_TEXT
As LCField Dim Externalfld_
THIS_FIELD_IS_NUMBER As LCField
Dim Notes_fld As LCField
'*******************Please fill in your details here
...................
Const YOUR_ODBC_CONNECTION_NAME= |123|
Const YOUR_UID=|123|
Const YOUR_PASSWORD =|123|
'Table*
Const YOUR_EXTERNAL_TABLE_NAME = |123|
'These are the names of your external fields
'1*
Const UNIQUE_EXTERNAL_FIELD=|123|
'2*
Const EXTERNAL_FIELD_TEXT=|123|
'3*
Const EXTERNAL_FIELD_NUMBER=|123|
'==============================================
Const YOUR_NOTES_SERVER=|123|
Const YOUR_NOTES_DATABASE_FILENAME=|123|
'Table/form*
Const THE_NAME_OF_YOUR_NOTES_FORM = |123|
'1*
Const THE_NAME_OF_YOUR_UNIQUE_NOTES_FIELD = |123|
'2*
Const MY_NOTES_FIELD_TEXT = |123|
'3*
Const MY_NOTES_FIELD_NUMBER=|123|
'*******************End of your details
...................................
'SUB INITIALISE
Sub Initialize
%REM
Author: Gary Cronin
Date: Autumn 2007
Location: Ireland
Overview:
the program connects to an external dB via ODBC,
so you will nedd to set-up an ODBC connection first.
it then retrieves records from the external dB. for each
record returned it looks up a Notes database for the
corresponding record.
If it doesn't find one in Notes it creates a new notes
document.
If it does find the record in notes it checks it for any
differences between the external record and the
notes record. If it
find any difference it updates the notes record.
Other:
For the purpose of this example:
The External Table you link to has 3 fields::
field 1: Type VarChar & Primary Key
field 2: Type Varchar
field3 : Type Float
The Notes Form::
field 1: Type Text & unique
field 2: Text
field3 : Number
The bits you need to change
%END REM
Dim session As New LCSession
session.ClearStatus
On Error Goto ErrorHandler
mb = MB_IconInformation + MB_OK
wintitle = "Connection Select and Update Example"
Dim ExternalSrc As New LCConnection ("odbc2")
Dim ExternalfldLst As New LCFieldList
' set the appropriate properties to connect
Externalsrc.Server = YOUR_ODBC_CONNECTION_NAME
Externalsrc.Userid = YOUR_UID
Externalsrc.Password = YOUR_PASSWORD
Externalsrc.Connect
Dim Notes_dst As New LCConnection ("notes")
Dim Notes_fldList As New LCFieldList
' set the appropriate properties to connect to the data source
Notes_dst.Server = YOUR_NOTES_SERVER
Notes_dst.Database = YOUR_NOTES_DATABASE_FILENAME
Notes_dst.Metadata = THE_NAME_OF_YOUR_NOTES_FORM
Notes_dst.Connect
If ( Notes_dst.Execute ("SELECT Form =
'THE_NAME_OF_YOUR_NOTES_FORM' ",
Notes_fldList ) = 0) Then
'THE NOTES FORM NAME MUST BE IN SINGLE
QUOTES 'formname'
Print "No Notes records were fetched."
End If
' now connected, we can execute a selection statement
If ( Externalsrc.Execute ("SELECT * from " &
YOUR_EXTERNAL_TABLE_NAME, ExternalfldLst ) = 0)
Then
Print "No EXTERNAL records were fetched."
End
End If
'Assign External fields to variables
'replace these external field names with your own
'in this example field 1 and 2 are text and field 3 is a number
'so the fields these are to be matched to in Notes should
be the same, text, text & number.
'The names do not have to be the same
Set Externalfld_UNIQUE_FIELD = ExternalfldLst.Lookup
(UNIQUE_EXTERNAL_FIELD)
Set Externalfld_THIS_FIELD_IS_TEXT= ExternalfldLst.Lookup
(EXTERNAL_FIELD_TEXT)
Set Externalfld_THIS_FIELD_IS_NUMBER =
ExternalfldLst.Lookup (EXTERNAL_FIELD_NUMBER)
' fetch each record from the result set
While ( Externalsrc.Fetch ( ExternalfldLst) > 0)
count = count + 1
Print "Processing " & count
b_notesRecFound=True
Let b_notesRecFound = FetchNotesRecord
(Externalfld_UNIQUE_FIELD.text(0) )
If Not b_notesRecFound Then
'So Create New Notes Record
Call CreateNotesRecord
End If
'---------------------------------------------------
Wend
If (count = 0) Then Print "No records were fetched."
Exit Sub
ErrorHandler:
Dim msg As String
Dim result As String
Dim status As Integer
Dim msgcode As Long
If session.status <> LCSUCCESS Then
status = session.GetStatus (result, msgcode, msg)
Else
result = "Error " & Err() & ": " & Error()
End If
Messagebox (result), mb, wintitle
End
End Sub
Function FetchNotesRecord(VALUE_TO_FIND As Variant)
As Boolean
Dim msg As String
Dim errortext As String
Dim msgcode As Long
Dim status As Long
Dim session As New lcsession
Dim src As New lcconnection("notes")
Dim keys As New lcfieldlist
Dim fields As New lcfieldlist
Dim field As lcfield
FetchNotesRecord = True
session.clearstatus
src.Server = YOUR_NOTES_SERVER
src.Database = YOUR_NOTES_DATABASE_FILENAME
src.Metadata = THE_NAME_OF_YOUR_NOTES_FORM
src.Connect
'this is where you set the unqique Notes field that
you are looking for
Set field=keys.append(THE_NAME_OF_YOUR_
UNIQUE_NOTES_FIELD,LCTYPE_TEXT)
field.flags=LCFIELDF_KEY
field.text=VALUE_TO_FIND
If (src.select(keys,1,fields)=0) Then
'no record found in Notes so Insert a new one
Print "Record Not found:"
FetchNotesRecord = False
Exit Function
End
Else
FetchNotesRecord = True
End If
'THE NAMES OF YOUR NOTES FIELDS
YOU WANT TO PROCESS
'FOR THIS EXAMPLE ONE IS OF TYPE TEXT AND THE
OTHER IS A NUMBER
'THESE FIELDS SHOULD MATCH THE EXTERNAL
FIELDS IN DATATYPE, SO TEXT = VARCHAR AND
NUMBER = FLOAT ETC..
src.fieldnames=MY_NOTES_FIELD_TEXT & ","
& MY_NOTES_FIELD_NUMBER
msg1=""
While (src.fetch(fields)>0)
If fields.MY_NOTES_FIELD_TEXT(0) <>
Externalfld_THIS_FIELD_IS_TEXT.text(0) Then
Call ChangeNotesfield(MY_NOTES_FIELD_TEXT,
Cstr( Externalfld_THIS_FIELD_IS_TEXT.text(0)),Cstr
(VALUE_TO_FIND),"Text")
End If
If fields.MY_NOTES_FIELD_NUMBER(0)
<>Externalfld_CLE_ACTIVITY_FLAG.text(0)
Then
Call ChangeNotesfield(MY_NOTES_FIELD_NUMBER,
Cstr(Externalfld_THIS_FIELD_IS_NUMBER.text(0) ),
Cstr(VALUE_TO_FIND),"Number")
End If
Wend
Exit Function
handler:
If (session.status <> LCSUCCESS) Then
status=session.getstatus(errortext,msgcode,msg)
Messagebox "Internal Text Error= " & errortext & Chr(10)
& "Internal Error Code= " & status & Chr(10) _
& "External Error Text= " & msg & Chr(10) &
"External Error Code= " & msgcode
Resume Next
Else
Resume Next
Messagebox "Lotus Notes Error Text= " & Error() &
Chr(10) & "Lotus Notes Error Code= " & Err()
End If
End
End Function
Sub CreateNotesRecord()
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set entry = New NotesDocument( db )
entry.UNIQUE_NOTES_FIELD
=Externalfld_UNIQUE_FIELD.text(0)
entry.MY_NOTES_FIELD_TEXT
=Externalfld_THIS_FIELD_IS_TEXT.text(0)
entry.MY_NOTES_FIELD_NUMBER
=Externalfld_PRODUCT_FAMILY_DESCRIPTION.text(0)
entry.MY_NOTES_FIELD_NUMBER
=Externalfld_THIS_FIELD_IS_NUMBER.text(0)
entry.form
=THE_NAME_OF_YOUR_NOTES_FORM
Call entry.save(1,1)
End Sub
Sub ChangeNotesfield(strFieldToChange As String,
strNewValue As String,strRecordTofind As String
,str_datatype As String)
Dim Notessrc As New LCConnection ("notes")
Dim NotesfldList As New LCFieldList
Dim Notesfld As LCField
' set the appropriate properties to connect to the data source
Notessrc.Server = YOUR_NOTES_SERVER
Notessrc.Database = YOUR_NOTES_DATABASE_FILENAME
Notessrc.Metadata = THE_NAME_OF_YOUR_NOTES_FORM
Notessrc.Connect
' use a key to find certain records to update
Set Notesfld = NotesfldList.Append (THE_NAME_OF_
YOUR_UNIQUE_NOTES_FIELD, LCTYPE_TEXT)
Notesfld.Flags = LCFIELDF_KEY
Notesfld.value =strRecordTofind
' set the field which will be changed, and set the new value
Select Case str_datatype
Case |Number|
Set Notesfld = NotesfldList.Append( strFieldToChange,
LCTYPE_NUMERIC)
Notesfld.text = Cint(strNewValue)
Case |Text|
Set Notesfld = NotesfldList.Append( strFieldToChange,
LCTYPE_TEXT)
Notesfld.text = strNewValue
End Select
Notesfld.text = strNewValue
Notessrc.MapbyName = True
Call Notessrc.Update (NotesfldList)
End Sub
Do you have comments on this tip? Let us know.
This tip was submitted to the SearchDomino.com tip library by member Gary Cronin. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.
|
|
|
|
|
|
