Tip

Using Lotus Connectors and ODBC to update Lotus Notes records

Related resources from SearchDomino.com:
Tip: ODBC connection to SQL Server using LotusScript

Expert Advice: Why can't I create an ODBC resource name in the ODBC connections?

LotusScript Reference Center

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.

This was first published in December 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.